InnoDB Trouble:frm file and the InnoDB data directory out of sync.

起因

昨天,在给一个数据表DDL操作时,InnoDB报了如下错误:

100602 13:48:39 [ERROR] Index uk_usplugin_plugid of test/test_user_plugin_0002 has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

MySQL版本:MySQL5.1.45+InnoDB Plugin1.0.6

当时的DDL:

alter table test_user_plugin_0002 drop index USER_ID,add unique index uk_pluser_userid(USER_ID,PLUGIN_ID);

追踪

MySQL Bug System中看到,问题类似于Bug #44571中的Case2(Bug #47622)。问题的大概原因是,InnoDB的system table中的表信息和MySQL字典中的表信息不同步导致的。尝试了几次,无法重现错误。

如何避开(workaround)

可以通过下面的SQL来重建InnoDB的索引:

alter table test_user_plugin_0002 engine=innodb

Bug #47622看到5.1.46中貌似已经解决了这个问题,还可以尝试升级到5.1.46。

另外,应该还可以尝试在DDL时打开old_alter_table来避免:(未验证)

SET [GLOBAL] old_alter_table=ON

In:

Leave a Reply

Your email address will not be published. Required fields are marked *