mysql加字段时表丢失

在给即将要上线的项目加字段时,表突然不见了。以前遇到过,在DDL过程中,强制中断(CTRL+C)时存在这样的情况,这次没有强制中断,表也不见了。

版本信息:

root@(NONE) 03:08:15>select version();
+------------+
| version()  |
+------------+
| 5.1.45-log |
+------------+

alert.log报如下信息:

110425 4:31:48 [ERROR] Cannot find or open table xxxx_0000/xxxx_id_0005 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

查看表的数据文件:

XXXX_id_0005.frm是正常的;XXXX_id_0005.ibd文件变成了#sql-2f50_23d768d.ibd

master和slave情况:

在master发现已经找不到XXXX_id_0005表了,但在slave上表还是存在的,字段也成功加上去了。为了尽快恢复master上的表,用mysqldump出slave上XXXX_id_0005的所有数据。在master上先drop XXXX_id_0005表,因为mysql发现不了XXXX_id_0005表,会报unknow table的异常,但实际已把表删除。在 master上load data数据。master上成功导入数据,但slave又异常了: Last_Error: Query caused different errors on master and slave. Error on master: ‘Unknown table ‘%-.100s” (1051), Error on slave: ‘no error’ (0). Default database: ‘XXXX_0000’. Query: ‘drop table XXXX_id_0005’,slave并没有同步master上binlog,于是尝试stop slave,start slave,slave开始应用binlog,表恢复。

问题:为什么正常的加字段,也容易导致丢失表呢?这个原因还在进步查找中。


One Response to “mysql加字段时表丢失”

Post a Comment