Resolving ERROR 1050 42S01 At Line 1 Table Already Exists
When ALTER TABLE crashes the MySQL server, it leaves orphaned records in the InnoDB dictionary. It is annoying because next time you run the same ALTER TABLE query, it will fail with error:
ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists
This post explains why it happens and how to fix it.
When you run ALTER table, InnoDB follows the plan:
- Block the original table.
- Create an empty temporary table with the new structure. The name of the new table is something like #sql-ib712.
- Copy all records from the original table to the temporary one.
- Swap the temporary and original tables.
- Unblock the original table.
A temporary table is a usual InnoDB table except it’s not visible to users. InnoDB creates a record in the dictionary for the temporary table as for any other table.
If MySQL crashes in the middle of the ALTER process, the dictionary ends up with an orphaned table.
We wouldn’t care much if the temporary table names were random. But they’re not and when you run ALTER TABLE again, InnoDB picks up the same name for the temporary table. As long as a record for a table with the same name already exists in the dictionary, the subsequent ALTER fails.
How to fix “ERROR 1050 (42S01) At Line 1: Table ‘sakila/#sql-ib712’ already exists”
MySQL suggests quite a cumbersome method. In short, you need to fool MySQL with a fake .frm file so you can DROP the temporary table with an SQL query. It works fine, but the structure of the fake table in the .frm file must match the structure in the dictionary. It’s not that easy to find out. Fortunately, you don’t need to.
The idea is as follows.
It’s not only DROP TABLE that removes records from the InnoDB dictionary, DROP DATABASE does it too.
In case of DROP TABLE, you need to specify the exact name of the table, while in the case with DROP DATABASE InnoDB will delete all tables for a given database.
To get a clean dictionary for a given database, we need to do following:
- Create an empty temporary database. Let it be tmp1234.
- Move all tables from the original database to tmp1234.
- Drop the original database (it’s empty by now, all tables are in tmp1234).
- Create the original database again.
- Move all tables from the temporary database to the original one.
- Drop the empty temporary database.
Here’s the script that performs this task. It must be run by root, and mysql command should connect to the server without asking the password. Stop all writes to the database before running the script.
for db in `mysql -NBe "SHOW DATABASES" | grep -wv -e mysql -e information_schema -e mysql -e performance_schema`; do
c=`mysql -NBe "select COUNT(*) from information_schema.tables WHERE TABLE_SCHEMA = '$db' AND TABLE_TYPE <> 'BASE TABLE'"`
if [ "$c" -ne 0 ]; then
echo "There are non-base tables (views etc) in $db"
mysql -e "CREATE DATABASE `$db_tmp`"
for t in `mysql -NBe "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$db' AND TABLE_TYPE = 'BASE TABLE'"`; do
echo "Moving $db.$t to $db_tmp.$t"
mysql -e "RENAME TABLE `$db`.`$t` TO `$db_tmp`.`$t`"
n=`mysql -e "SHOW TABLES" "$db"| wc -l`
if [ $n -ne 0 ]; then
echo "there are $n tables in $db , not gonna drop it!"
datadir=`mysql -NBe "SELECT @@datadir"`
rm -f "$datadir/$db/"*
mysql -e "DROP DATABASE `$db`"
mysql -e "CREATE DATABASE `$db`"
for t in `mysql -NBe "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$db_tmp' AND TABLE_TYPE = 'BASE TABLE'"`; do
echo "Moving $db_tmp.$t to $db.$t"
mysql -e "RENAME TABLE `$db_tmp`.`$t` TO `$db`.`$t`"
n=`mysql -e "SHOW TABLES" "$db_tmp"| wc -l`
if [ $n -ne 0 ]; then
echo "there are $n tables in $db_tmp , not gonna drop it!"
mysql -e "DROP DATABASE `$db_tmp`"