Search code examples
mysqlinnodb

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?

Solution

  • 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.