| Bug #37631 | Incorrect key file for table after upgrading from 5.0 to 5.1 | ||
|---|---|---|---|
| Submitted: | 25 Jun 2008 13:56 | Modified: | 12 May 2009 2:25 |
| Reporter: | jocelyn fournier (Silver Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.25-rc, 5.1.26-rc | OS: | Any |
| Assigned to: | V Venkateswaran | CPU Architecture: | Any |
| Tags: | qc | ||
[25 Jun 2008 13:56]
jocelyn fournier
[25 Jun 2008 14:04]
jocelyn fournier
Well it seems the "It seems either CHECK TABLE ... FOR UPGRADE needs to be extended in 5.0.x to detect all the cases where the tables need to be updated" correspond to the bug 36055 fixed in 5.0.62. It would have been great to have the 5.1.25-rc and 5.0.62 release synchronised to avoid this kind of issue and to be able to test more thoroughly 5.1.25. When is the 5.0.62 version expected to be released ? Thanks, Jocelyn
[30 Jun 2008 14:13]
Susanne Ebrecht
Jocelyn, many thanks for writing a bug report. Unfortunately, I don't understand your request. According to bug #36055 it's fixed in 5.1.25-rc.
[30 Jun 2008 14:26]
jocelyn fournier
Hi Susanne,
The fix in 5.1.25-rc is to avoid the use of USE_FRM if the file version is not compatible with the current version of MySQL.
But 36055 contains also the following fix :
2. REPAIR TABLE query (without USE_FRM clause) evaluation has been
modified to upgrade .FRM files to current version.
It's this modification we need in 5.0, and which is present in 5.0.62 only.
Currently, with 5.0.51b, doing a mysql_upgrade/CHECK TABLE ... FOR UPGRADE doesn't consider this table needs an upgrade.
And if this table is used in 5.1, 5.1 considers the table as crashed, and there's no way to upgrade it anymore.
So there are two possibility :
- changing 5.1 so that those tables are not considered as crashed (which is perhaps not an expected behaviour and a real bug ?), and can update them successfully
- using 5.0.62 to do the mysql_upgrade and ensure the table are usable in 5.1
Thanks,
Jocelyn
[17 Jul 2008 6:53]
jocelyn fournier
Hi, Any progress on this issue ? I think it could entail really bad migration issue for people using 5.0 ! Thanks, Jocelyn
[19 Jul 2008 21:17]
jocelyn fournier
This bug still occurs with 5.1.26-rc
[31 Jul 2008 22:03]
Mauricio Roman
I observed that myisamchk reports no errors on the tables that are placed on the upgraded version of mysql, but mysqlcheck reports an error.
[2 Aug 2008 20:40]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with current development sources: $tar -xzf vip.tar.gz $mv vip* ~/build/mysql-5.0/data/test $mysql50 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.68-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CHECK TABLE vip FOR UPGRADE; +----------+-------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+-------------------------------------------------------------------+ | test.vip | check | error | Table upgrade required. Please do "REPAIR TABLE `vip`" to fix it! | +----------+-------+----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> repair table vip; +----------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+----------+ | test.vip | repair | status | OK | +----------+--------+----------+----------+ 1 row in set (0.18 sec) mysql> \q Bye $mv ~/build/mysql-5.0/data/test/vip* ~/build/mysql-5.1/data/test/ $mysql51 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 48 Server version: 5.1.28-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> check table vip; +----------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+----------+ | test.vip | check | status | OK | +----------+-------+----------+----------+ 1 row in set (0.04 sec)
[2 Aug 2008 20:49]
Sveta Smirnova
I am sorry: missed your comment "[30 Jun 16:26] jocelyn fournier". Currently if there is no MySQL 5.0.62 or newer is available there is a problem: $tar -xzf ovip.tar.gz $mv vip* ~/build/mysql-5.1/data/test $mysql51 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.28-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select count(*) from vip; ERROR 1034 (HY000): Incorrect key file for table 'vip'; try to repair it mysql> check table vip; +----------+-------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+------------------------------------------------------+ | test.vip | check | Error | Incorrect key file for table 'vip'; try to repair it | | test.vip | check | error | Corrupt | +----------+-------+----------+------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REPAIR TABLE vip; +----------+--------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+------------------------------------------------------+ | test.vip | repair | Error | Incorrect key file for table 'vip'; try to repair it | | test.vip | repair | error | Corrupt | +----------+--------+----------+------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REPAIR TABLE vip USE_FRM; +-------+--------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------+--------+----------+-----------------------------------------+ | vip | repair | error | Failed repairing incompatible .frm file | +-------+--------+----------+-----------------------------------------+ 1 row in set (0.00 sec) So bug reclassified as "Verified", although one can always dump/reload table when upgrading to major version.
[2 Aug 2008 21:27]
jocelyn fournier
Hi, I'm not sure asking MySQL users to : - first upgrade to >= 5.0.62 not yet available for the community to do a mysqlcheck -g --auto-repair before finally upgrading to 5.1 or - dump/reload the tables before upgrading to 5.1 is the best way to encourage people to upgrade to 5.1 ;) Just my 2 cents. Thanks, Jocelyn
[25 Aug 2008 14:59]
Scott Baker
Is there any update on this? my web host just tried to upgrade and due to this exact bug just nuked 4 nation-wide databases and several smaller ones. Is there any workaround/fix/anything we can do or are we forced to revert to old backups and loose data?
[10 Sep 2008 16:05]
Wolfgang Purrer
http://forums.mysql.com/read.php?11,225985,225985#msg-225985 .. I have the same problem.
[10 Sep 2008 16:47]
jocelyn fournier
Hi, I don't really understand why this issue has been classified as a feature request ? Having a table crashed after upgrading to 5.1, and not being able to repair it unless switching back to 5.0.67 is a major issue, not a feature request. Regards, Jocelyn Fournier
[11 Sep 2008 7:07]
MySQL Verification Team
although we *should* dump/reload between major versions, MySQL *should* also try to remain compatible.
[11 Sep 2008 7:34]
MySQL Verification Team
repair table should be more robust than this testcase demonstrates.
[25 Sep 2008 16:29]
Adrian Chapela Cordeiro
I have the same issue on many tables. I am trying to upgrade from 5.0.56sp1-enterprise-gpl-log to 5.1.28-rc-log.
[1 Oct 2008 7:26]
Xing Li
Also running into this exact issue and frankly this bug is preventing me from testing 5.1 in production slaves. Why I consider this a bug: error output does not make much sense, and the steps to fix this is beyond cryptic and no sane person is going to come to the bug database for 5.1 upgrade support.
[14 Oct 2008 21:26]
Joe Chapman
still experiencing this same problem with MySQL-server-community-5.1.28-0.rhel5.
[9 Dec 2008 10:52]
Adrian Chapela Cordeiro
With 5.1.30-enterprise-gpl-advanced-log I have the same.
[15 Dec 2008 0:10]
Scott M. Shell
I have the exact same issue
[4 Jan 2009 1:39]
Philip Gladstone
This still fails with 5.1.30 when upgrading from 5.0.27. At least until this is fixed, there ought to be a HUGE WARNING in the release notes and on the 5.1 page that says that upgrading from 5.0 to 5.1 is (in general) not possible without following some set of steps (which I don't know). I have reverted to 5.0.27, but did end up losing some data. Yes, this will teach me to do better backups of my databases.
[15 Jan 2009 21:45]
Izaak Branderhorst
i just went from 5.0.67 to 5.1.30. the upgrade came down through a popular centos repo, and i didn't think twice - i haven't had any database migration issues before. i've lost half the tables in a couple databases, with exactly the kind of behaviour described here. i.e., mysqlcheck finds the tables corrupt, myisamchk finds everything dandy. even a myisamchk --safe-recover didn't seem to make a difference! is there anything i can do?
[30 Jan 2009 21:43]
Jon Doelman
I have the same problem upgrading from 5.0 to 6.0
[2 Mar 2009 10:43]
Sveta Smirnova
test table
Attachment: ovip.tar.gz (application/x-gzip, text), 9.55 KiB.
[5 Mar 2009 9:15]
Sergey Vojtovich
CHECK TABLE ... FOR UPGRADE has been fixed by patch for BUG#36055. The same patch refuse to run REPAIR TABLE ... USE_FRM against old tables, since it seems to be unsafe. So far the provided table seem to be created by 4.0 (or even earlier) mysql version. The problem here is that conformance checker is not aware of older tables and needs to be fixed accordingly.
[12 Mar 2009 11:58]
Sveta Smirnova
Possible error message in 5.1 could be improved also to avoid confusion: report version is incompatible with any CHECK TABLE statement, not only if one uses USE_FRM.
[27 Mar 2009 20:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/70750 2837 Narayanan V 2009-03-28 Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 The conformance checker was not taking into account, and, making concessions for acceptable incompatibilites in tables created by versions earlier than 4.1. The current patch relaxes the conformance checker to ignore differences in key_alg and language for tables created by versions earlier than 4.1. @ storage/myisam/ha_myisam.cc Modify check_definition to ignore differences in key_alg and language for tables created by versions earlier than 4.1. @ storage/myisammrg/ha_myisammrg.cc pass the table object to check_definition.
[8 Apr 2009 6:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/71607 2855 Narayanan V 2009-04-08 Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 The conformance checker was not taking into account, and, making concessions for acceptable incompatibilites in tables created by versions earlier than 4.1. The current patch relaxes the conformance checker to ignore differences in key_alg and language for tables created by versions earlier than 4.1. @ storage/myisam/ha_myisam.cc Modify check_definition to ignore differences in key_alg and language for tables created by versions earlier than 4.1.
[24 Apr 2009 8:06]
Christof Damian
I am having the same problem after upgrading a 5.0 to 5.1.33. It just happens with some tables, all of them created by typo3 and probably leftover from some mysql 4.x install. Is there a workaround to fix these tables? As I can't access them at all at the moment I can't do a mysqldump and reload. I have backups from before the upgrade, but they are a few days old as I haven't noticed the problem until this week. Is there any news in which version the patch will be integrated?
[25 Apr 2009 13:04]
Mark Kramer
I'm getting the exact same thing! I just painstakingly upgraded to MySQL 5.1.34, coming from 5.0.77, on FreeBSD. So, I run: "/usr/local/bin/mysql_upgrade", and get these all over the place (see below; just a few lines: the error list is too long to post in full). Also, all known (to me) methods of repairing failed. Even started with a huge mysqldump, and imported everything anew that way. With no luck, either. Honestly, folks, this is a serious, if not a very serious issue. Essentially it means there's no way to upgrade to 5.1.34 at all! ----- Repairing tables albatross.banned Error : Incorrect key file for table 'banned'; try to repair it error : Corrupt albatross.bridge Error : Incorrect key file for table 'bridge'; try to repair it error : Corrupt albatross.categories Error : Incorrect key file for table 'categories'; try to repair it error : Corrupt albatross.comments Error : Incorrect key file for table 'comments'; try to repair it error : Corrupt albatross.config Error : Incorrect key file for table 'config'; try to repair it error : Corrupt albatross.dict Error : Incorrect key file for table 'dict'; try to repair it error : Corrupt albatross.ecards Error : Incorrect key file for table 'ecards'; try to repair it error : Corrupt albatross.exif Error : Incorrect key file for table 'exif'; try to repair it error : Corrupt albatross.favpics Error : Incorrect key file for table 'favpics'; try to repair it error : Corrupt albatross.filetypes Error : Incorrect key file for table 'filetypes'; try to repair it error : Corrupt albatross.hit_stats Error : Incorrect key file for table 'hit_stats'; try to repair it error : Corrupt albatross.plugins Error : Incorrect key file for table 'plugins'; try to repair it error : Corrupt Etc, etc.
[25 Apr 2009 14:07]
Mark Kramer
P.S. Judging from the comments thereto, the mentioned patch is rather unstable still. Also, I don't get this whole "Created with MySL 4.0" thingy. I did a complete mysldump on the old server, and imported all data with the client on the new one; so, essentially all databases get RECREATED from scratch, no? So, what's the problem then?
[27 Apr 2009 8:19]
V Venkateswaran
This problem should be fixed in the next version.
[28 Apr 2009 3:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72863 2874 Narayanan V 2009-04-28 Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 The conformance checker needs to relax the check for tables created by a mysql server version < 4.0. The behaviour currently relaxes the check for the complimentary case which is wrong. The current patch 1) fixes the conformance checker to relax checking for the correct version of the mysql server. 2) Adds tests to verify that the conformance checker relaxes checking for the correct version. @ mysql-test/r/upgrade.result Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Result file for test case @ mysql-test/std_data/bug37631.MYD Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.MYI Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.frm Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/t/upgrade.test Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Adds test for checking that upgrade works on a table which is created by a mysql server version <= 4.0. @ storage/myisam/ha_myisam.cc Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Fix the conformance checker to relax checking for the correct version of the tables (for tables created by mysql server version <= 4.0)
[28 Apr 2009 8:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72883 2874 Narayanan V 2009-04-28 Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 The conformance checker needs to relax the check for tables created by a mysql server version < 4.0. The behaviour currently relaxes the check for the complimentary case which is wrong. The current patch 1) fixes the conformance checker to relax checking for the correct version of the mysql server. 2) Adds tests to verify that the conformance checker relaxes checking for the correct version. @ mysql-test/r/upgrade.result Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Result file for test case @ mysql-test/std_data/bug37631.MYD Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.MYI Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.frm Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/t/upgrade.test Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Adds test for checking that upgrade works on a table which is created by a mysql server version <= 4.0. @ storage/myisam/ha_myisam.cc Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Fix the conformance checker to relax checking for the correct version of the tables (for tables created by mysql server version <= 4.0)
[28 Apr 2009 9:26]
Mark Kramer
Well, pardon my daftness, but I can't seem to get a working diff file out of the suggested patch. At any rate, doing a complete 'rm -rf' on the mysql dir (sans the mysql database itself, of course; and after mysqld shut down) 'solved' the issue: with all the metadata being recreated from scratch (because of the < mysqldump.sql import), all errors disappear, and the databases are useable again. I wouldn't exactly call it elegant, but it gives me a workable solution for now. Maybe it will for others, too.
[30 Apr 2009 12:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/73145 2874 Narayanan V 2009-04-30 BUG#37631 - Incorrect key file for table after upgrading from 5.0 to 5.1 This patch adds corrections to the original patch submitted 2009-04-08 (http://lists.mysql.com/commits/71607): - fixed that the original patch didn't work because of an incorrect condition; - added a test case. @ mysql-test/r/upgrade.result Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Result file for test case @ mysql-test/std_data/bug37631.MYD Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.MYI Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/std_data/bug37631.frm Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 table created in mysql 4.0 @ mysql-test/t/upgrade.test Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Adds test for checking that upgrade works on a table which is created by a mysql server version <= 4.0. @ storage/myisam/ha_myisam.cc Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 Fix the conformance checker to relax checking for the correct version of the tables (for tables created by mysql server version <= 4.0)
[5 May 2009 19:39]
Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:08]
Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:v.narayanan@sun.com-20090430125505-wfp36ijz4w1cw3cg) (merge vers: 6.0.11-alpha) (pib:6)
[12 May 2009 2:25]
Paul DuBois
Noted in 5.1.35, 6.0.12 changelogs. CHECK TABLE did not properly check whether MyISAM tables created by servers from MySQL 4.0 or older needed to be upgraded. This could cause problems upgrading to MySQL 5.1 or higher.
[14 May 2009 13:31]
Pete Clapham
Can we have a link to the location of 5.1.35 binary tar so we can roll a quick check to confirm that all is working as intended ? Many thanks Pete
[15 Jun 2009 8:26]
Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:05]
Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:46]
Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[2 Jul 2009 8:55]
James Smith
Hello, I have just upgraded from MySQL 4.1.x to 5.1.35 and this bug still persists. Around 10% of the databases have corrupt tables: ukrpgblo_wordpressmu.wp_blogs Error : Incorrect key file for table 'wp_blogs'; try to repair it error : Corrupt etc .... Anyway to resolve, or is the data within these tables totally screwed? I dumped all databases, but these are now several days out of date, only discovered the issue today (and this bug report).
