CODEWITHSUNDEEP
mysqlinnodb
David542
David542

Reputation: 110267

Unable to rename table due to metadata lock

I am trying to rename a table but it always just hangs with a Waiting for table metadata lock. Here is how I am trying to do it:

start transaction;
RENAME TABLE `main_territorypricing` TO `main_territorypricing2`;
RENAME TABLE `main_territorypricing1` TO `main_territorypricing`;
COMMIT;

Yet the first rename statement hangs indefinitely. How would I rename the two tables?

Upvotes: 1

Views: 1227

Answers (1)

Rick James
Rick James

Reputation: 142296

I'm pretty sure RENAME TABLE will terminate the transaction.

You can do both renames in a single, atomic, statement (no transaction):

RENAME TABLE `main_territorypricing`  TO `main_territorypricing2`,
             `main_territorypricing1` TO `main_territorypricing`;

It will have to wait for any other connections that are busy with any of the tables; make sure you don't have some connection not letting go.

Upvotes: 2

Related Questions