{"id":2059,"date":"2010-06-03T09:48:59","date_gmt":"2010-06-03T01:48:59","guid":{"rendered":"http:\/\/www.orczhou.com\/?p=2059"},"modified":"2010-06-03T09:48:59","modified_gmt":"2010-06-03T01:48:59","slug":"innodb-trouble-1","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2010\/06\/innodb-trouble-1\/","title":{"rendered":"InnoDB Trouble:frm file and the InnoDB data directory  out of sync."},"content":{"rendered":"<p><font color=\"red\">\u8d77\u56e0<\/font><\/p>\n<p>\u6628\u5929\uff0c\u5728\u7ed9\u4e00\u4e2a\u6570\u636e\u8868DDL\u64cd\u4f5c\u65f6\uff0cInnoDB\u62a5\u4e86\u5982\u4e0b\u9519\u8bef\uff1a<\/p>\n<blockquote><p>\n100602 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<\/p><\/blockquote>\n<p>MySQL\u7248\u672c\uff1aMySQL5.1.45+InnoDB Plugin1.0.6<\/p>\n<p>\u5f53\u65f6\u7684DDL\uff1a<\/p>\n<div class=\"mycode\">alter table test_user_plugin_0002 drop index USER_ID,add unique index uk_pluser_userid(USER_ID,PLUGIN_ID);<\/div>\n<p><font color=\"red\">\u8ffd\u8e2a<\/font><\/p>\n<p>\u5728<a href=\"http:\/\/bugs.mysql.com\">MySQL Bug System<\/a>\u4e2d\u770b\u5230\uff0c\u95ee\u9898\u7c7b\u4f3c\u4e8e<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=44571\">Bug #44571<\/a>\u4e2d\u7684Case2\uff08<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=47622\">Bug #47622<\/a>\uff09\u3002\u95ee\u9898\u7684\u5927\u6982\u539f\u56e0\u662f\uff0cInnoDB\u7684system table\u4e2d\u7684\u8868\u4fe1\u606f\u548cMySQL\u5b57\u5178\u4e2d\u7684\u8868\u4fe1\u606f\u4e0d\u540c\u6b65\u5bfc\u81f4\u7684\u3002\u5c1d\u8bd5\u4e86\u51e0\u6b21\uff0c\u65e0\u6cd5\u91cd\u73b0\u9519\u8bef\u3002<\/p>\n<p><font color=\"red\">\u5982\u4f55\u907f\u5f00\uff08workaround\uff09<\/font><\/p>\n<p>\u53ef\u4ee5\u901a\u8fc7\u4e0b\u9762\u7684SQL\u6765\u91cd\u5efaInnoDB\u7684\u7d22\u5f15\uff1a<\/p>\n<div class=\"mycode\">alter table test_user_plugin_0002 engine=innodb<\/div>\n<p>\u5728<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=47622\">Bug #47622<\/a>\u770b\u52305.1.46\u4e2d\u8c8c\u4f3c\u5df2\u7ecf\u89e3\u51b3\u4e86\u8fd9\u4e2a\u95ee\u9898\uff0c\u8fd8\u53ef\u4ee5\u5c1d\u8bd5\u5347\u7ea7\u52305.1.46\u3002<\/p>\n<p>\u53e6\u5916\uff0c\u5e94\u8be5\u8fd8\u53ef\u4ee5\u5c1d\u8bd5\u5728DDL\u65f6\u6253\u5f00old_alter_table\u6765\u907f\u514d\uff1a\uff08\u672a\u9a8c\u8bc1\uff09<\/p>\n<div class=\"mycode\">SET [GLOBAL] old_alter_table=ON<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u8d77\u56e0 \u6628\u5929\uff0c\u5728\u7ed9\u4e00\u4e2a\u6570\u636e\u8868DDL\u64cd\u4f5c\u65f6\uff0cInnoDB\u62a5\u4e86\u5982\u4e0b\u9519\u8bef\uff1a 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\u7248\u672c\uff1aMySQL5.1.45+InnoDB Plugin1.0.6 \u5f53\u65f6\u7684DDL\uff1a alter table test_user_plugin_0002 drop index USER_ID,add unique index uk_pluser_userid(USER_ID,PLUGIN_ID); \u8ffd\u8e2a \u5728MySQL Bug System\u4e2d\u770b\u5230\uff0c\u95ee\u9898\u7c7b\u4f3c\u4e8eBug #44571\u4e2d\u7684Case2\uff08Bug #47622\uff09\u3002\u95ee\u9898\u7684\u5927\u6982\u539f\u56e0\u662f\uff0cInnoDB\u7684system table\u4e2d\u7684\u8868\u4fe1\u606f\u548cMySQL\u5b57\u5178\u4e2d\u7684\u8868\u4fe1\u606f\u4e0d\u540c\u6b65\u5bfc\u81f4\u7684\u3002\u5c1d\u8bd5\u4e86\u51e0\u6b21\uff0c\u65e0\u6cd5\u91cd\u73b0\u9519\u8bef\u3002 \u5982\u4f55\u907f\u5f00\uff08workaround\uff09 \u53ef\u4ee5\u901a\u8fc7\u4e0b\u9762\u7684SQL\u6765\u91cd\u5efaInnoDB\u7684\u7d22\u5f15\uff1a [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[6],"tags":[52],"class_list":["post-2059","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-innodb-plugin"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/2059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=2059"}],"version-history":[{"count":9,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/2059\/revisions"}],"predecessor-version":[{"id":2069,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/2059\/revisions\/2069"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=2059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=2059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=2059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}