Bug #30114 crash during RENAME TABLE leads to loss of data
Submitted: 28 Jul 2007 13:24
Reporter: Guilhem Bichot Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0-bk,5.1-bk OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[28 Jul 2007 13:24] Guilhem Bichot
RENAME TABLE first renames the table in the engine and then the table's FRM file. If the server dies between the two operations (power failure, crash, out of memory...), the table cannot be SELECTed (neither under the old or new name): data is lost.

How to repeat:
To provoke the crash, insert "abort()" here in mysql_rename_table():
  if (!file || !(error=file->rename_table(from_base, to_base)))
    if (!(flags & NO_FRM_RENAME) && rename_file_ext(from,to,reg_ext))
Then run mysqld:
[INS 15:18 /m/mysql-5.1/sql $] ./mysqld --skip-grant-tables --datadir=/tmp/data --language=./share/english

then connect to it:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table t(a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
| a    |
|    1 |
1 row in set (0.00 sec)

mysql> rename table t to u;
ERROR 2013 (HY000): Lost connection to MySQL server during query
(the error above is due to the crash we provoked).
Now, restart the same mysqld, and:
mysql> select * from test.t;
ERROR 1146 (42S02): Table 'test.t' doesn't exist
mysql> select * from test.u;
ERROR 1146 (42S02): Table 'test.u' doesn't exist
So, where is my precious table?
Trying to get the table back:
mysql> rename table t to u;
ERROR 1025 (HY000): Error on rename of './test/t' to './test/u' (errno: 120)
mysql> rename table u to t;
ERROR 1050 (42S01): Table 't' already exists

Only a manual operation by the DBA ("mv t.frm u.frm") will resolve the situation. Or restore from backup+binlogs.

Suggested fix:
Possibly extend the DDL log (present in sql_table.cc and used for atomicity of partitioning-related DDLs) to non-partitioning-related DDLs.
Bugs are not limited to RENAME TABLE:
- ALTER TABLE too (internally it renames tables)
- DROP TABLE and CREATE TABLE too though less severe (no data loss)
- RENAME DATABASE renames tables one by one and if there is a crash in the middle, it stays half-done (some tables moved to the new database, others still in the old database).
[28 Jul 2007 19:27] Guilhem Bichot
data loss so it's a P2
[5 Nov 2007 19:53] suhana safar
i have renamed a table and generated sql and when i preview the data ,its gone,table is empty.how to get back the data.
[24 Jan 2008 22:55] Scott Munday
I tried renaming a fairly large InnoDB table in a similar way. The data became corrupt. I could not successfully DROP the table afterward either or do anything with the table. It became completely unusable. It had occurred during a cache script that was run, and probably broke down mid-execution. 

I'm using MySQL 5.0.33 similar to Mr. Bichot and my table is about 500,000 rows.
[12 Aug 2011 2:31] hui liu
Please refer to the DDL lost table issue: