In this short blog post we will take a look at how to rename a table in MySQL. Sounds fairly simple, but we’ll try to dig a little deeper under the surface.
How to rename a table in MySQL?
As for the table, the solution is very simple:
mysql> USE sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> RENAME TABLE sbtest1 TO sbtest1_renamed; Query OK, 0 rows affected (0.07 sec) mysql> SHOW TABLES LIKE 'sbtest1%'; +-----------------------------+ | Tables_in_sbtest (sbtest1%) | +-----------------------------+ | sbtest10 | | sbtest11 | | sbtest12 | | sbtest13 | | sbtest14 | | sbtest15 | | sbtest16 | | sbtest17 | | sbtest18 | | sbtest19 | | sbtest1_renamed | +-----------------------------+ 11 rows in set (0.01 sec)
This is an instant operation, no data is going to be copied physically however locking may still affect the ability to execute RENAME TABLE. MySQL comes with different locking mechanisms: row level locks that ensure rows are not being updated by multiple transactions at the same time but there are also metadata locks, which prevents table structure from changing on the fly.
Metadata locks in MySQL < 8.0.13
In case of MySQL up to 8.0.12 RENAME TABLE requires metadata lock (MDL) on a table. This means that even though the operation is instant, it may take a while to perform. Here’s an example from MySQL 5.7.35:
On one connection we just execute:
mysql> USE sbtest; Database changed mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM sbtest1 LIMIT 1; +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 801771 | 18034632456-32298647298-82351096178-60420120042-90070228681-93395382793-96740777141-18710455882-88896678134-41810932745 | 43683718329-48150560094-43449649167-51455516141-06448225399 | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.01 sec)
On the second connection we will check the MDL:
mysql> SELECT * FROM performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | 139920102486016 | SHARED_READ | TRANSACTION | GRANTED | | 46 | 119 | | TABLE | sbtest | sbtest1 | 94627960880400 | SHARED_READ | TRANSACTION | GRANTED | | 45 | 19 | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ 2 rows in set (0.00 sec)
As you can see, metadata lock on sbtest1 is granted. Now, if we’ll execute RENAME TABLE, it’ll get stuck:
mysql> USE sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> RENAME TABLE sbtest1 TO sbtest1_renamed; RENAME TABLE sbtest1_renamed TO sbtest1;
It will stay like that until the transaction complete either by COMMIT or by ROLLBACK. Let’s say we committed the transaction. It allows our RENAME TABLE to complete:
Query OK, 0 rows affected (10 min 35.11 sec) Query OK, 0 rows affected (0.01 sec)
As you can see, the first RENAME took 10 minutes due to MDL. Second had no problems grabbing locks it require and it completed instantly.
Metadata locks in MySQL >= 8.0.13
In more recent MySQL 8.0 versions the behavior has changed. MDL’s are now behaving just like a normal locks and instead of queries getting stuck, blocking, long running transactions may be rolled back. Let’s take a look at the same scenario like previously, but on MySQL 8.0.25.
Again, in first connection we run:
mysql> USE sbtest; Database changed mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM sbtest1 LIMIT 1; +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 2 | 56974 | 08491342923-02883352006-84603275603-09990819375-34797292564-08123835089-62518622529-37640723568-74874064754-77169339011 | 03482970681-93695428707-16645177780-84068492795-06270909580 | +----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.01 sec)
Then, on the second connection we check for MDL and it is indeed granted:
mysql> SELECT * FROM performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 139834498785840 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6930 | 44504 | 4 | | TABLE | sbtest | sbtest1 | NULL | 139834810070832 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6930 | 44510 | 53 | | SCHEMA | sbtest | NULL | NULL | 139834810071024 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:107 | 44510 | 53 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ 3 rows in set (0.00 sec)
Now, let’s try to run RENAME TABLE:
mysql> USE sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> RENAME TABLE sbtest1 TO sbtest1_renamed; RENAME TABLE sbtest1_renamed TO sbtest1; Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.02 sec)
Both RENAME TABLE statements were executed instantly. Now, let’s try to commit the transaction on the first connection:
mysql> COMMIT; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
As you can see, the fact that we executed RENAME TABLE did not go unnoticed and the transaction that was open (and that was affected) during the change was rolled back.
Even though renaming the table in MySQL is just one single command, you have to keep in mind everything that’s happening under the hood. Be aware of the MySQL version that you use, be aware of how the metadata locks behave. Do not assume instant execution in older MySQL versions. Rename commands may take a long time to perform if you have long-running transactions. Make sure that your application is designed to handle deadlocks – it should know that the rolled back transactions should be re-executed.
We hope you found this blog post useful and informative. If you have any questions or would like to share some of your feedback, you can do it at any time in the comments section below.
