mysql 10501_MySQL Resolving ERROR 1050 42S01 at line 1 Table already exists

When ALTER TABLE crashes MySQL server it leaves orphaned records in InnoDB dictionary. It is annoying because next time you run the same ALTER TABLE query it will fail with error:

Shell

ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists

1

ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists

The 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

The temporary table is a normal InnoDB table except it’s not visible to a user. 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 name were random. But it’s 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 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 .frm file must match the structure in the dictionary. It’s not that easy to find out. Fortunately you don’t need to.

An idea is following.

Not only DROP TABLE removes a records from InnoDB dictionary, DROP DATABASE does it too.

In case of DROP TABLE you need to specify exact name of the table while in case of 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 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 a 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.

#!/usr/bin/env bash

set -eu

for db in `mysql -NBe "SHOW DATABASES" | grep -wv -e mysql -e information_schema -e mysql -e performance_schema`; do

db_tmp=tmp$RANDOM

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"

continue

fi

mysql -e "CREATE DATABASE `$db_tmp`"

IFS="

"

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`"

done

n=`mysql -e "SHOW TABLES" "$db"| wc -l`

if [ $n -ne 0 ]; then

echo "there are $n tables in $db , not gonna drop it!"

exit -1

fi

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`"

done

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!"

exit -1

fi

mysql -e "DROP DATABASE `$db_tmp`"

done