Description:
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)))
{
abort();
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).
Description: 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))) { abort(); 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).