CODEWITHSUNDEEP
mysqlamazon-aurora
hsnsd
hsnsd

Reputation: 1823

Aurora MySQL - Add column to table getting stuck

I have an instance of Aurora MySQL v2.10.2

I am trying to alter a small table (3k rows) to add a new column. This is a prod table and is constantly queried/updated.

The alter command is getting stuck and it also blocks all the other running queries in the background. By stuck, I mean its running for more than 1 min and all the queries including the alter statement is in Waiting for table metadata lock state.

This should not take more than a few seconds though.

I can not upgrade to version 3 or change the lab settings as described here to enable Fast/Instant DDL: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.FastDDL.html#AuroraMySQL.Managing.FastDDL-v2

Is there anything I can check for to get this alter to run.

I have tried these so far and each of it gets stuck.

ALTER TABLE table ADD COLUMN `my_col` int DEFAULT 100 AFTER another_col;
ALTER TABLE table ADD COLUMN `my_col` int;
ALTER TABLE table ADD COLUMN `my_col` int NULL;

Upvotes: 0

Views: 817

Answers (1)

user20986458
user20986458

Reputation:

It sounds like you are running into an issue with the table metadata lock when trying to alter your table in an Aurora MySQL v2.10.2 instance. This can happen when the table is being constantly queried/updated, as you mentioned.

Here are a few things you can try to resolve this issue:

Try to reduce the workload on the table during the alter operation. You can do this by temporarily disabling updates to the table or by redirecting queries to a replica.

Increase the innodb_buffer_pool_size parameter in the MySQL configuration file. This parameter controls the amount of memory used for caching data and index pages, and increasing it can help reduce the impact of table locks.

Increase the innodb_lock_wait_timeout parameter in the MySQL configuration file. This parameter controls the time that a session waits for a lock before giving up and returning an error. By increasing this value, you can allow the alter statement more time to complete.

Try running the alter statement during a maintenance window or low-usage period.

Try breaking the ALTER command into multiple commands. For example, you can create a new table with the new column and then use a SELECT INTO statement to transfer the data, after that you can drop the original table and rename the new table.

If none of the above solutions work, you might consider using the "pt-online-schema-change" tool from Percona. This tool can perform the alter table operation

Upvotes: 0

Related Questions