| Bug #31331 | MyISAM or Merge Table upgrade incompatibility with 5.1 | ||
|---|---|---|---|
| Submitted: | 2 Oct 2007 6:50 | Modified: | 30 Mar 2008 20:17 |
| Reporter: | Venu Anuganti | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | bfsm_2007_10_18, Merge upgrade can not open file, MyISAM Incorrect key file | ||
[2 Oct 2007 6:54]
Venu Anuganti
change of Synopsis
[2 Oct 2007 6:54]
Venu Anuganti
change of Synopsis
[2 Oct 2007 7:28]
MySQL Verification Team
simplified table to show same problem: CREATE TABLE `t1` (`a` tinytext,KEY (`a`(20))) ENGINE=MyISAM;
[2 Oct 2007 12:38]
MySQL Verification Team
Setting the bug report as verified. If a fix is not possible, then an explanation should at least me made as to why the index goes corrupt in 5.1. To repeat: on 4.1, create the simple table as I noted above. Then, copy the raw MYI,MYD,frm files onto a 5.1.22 installation and run 'select * from t1'.
[2 Oct 2007 13:01]
MySQL Verification Team
debug trace has this: T@5 : ha_myisam.cc: 345: 11: | | | | | | | | | | >check_definition T@5 : ha_myisam.cc: 402: 11: | | | | | | | | | | | error: Key segment 0 (key 0) has different definition T@5 : ha_myisam.cc: 406: 11: | | | | | | | | | | | error: t1_type=17, t1_language=8, t1_null_bit=1, t1_length=20 T@5 : ha_myisam.cc: 410: 11: | | | | | | | | | | | error: t2_type=15, t2_language=8, t2_null_bit=1, t2_length=20 T@5 : ha_myisam.cc: 412: 11: | | | | | | | | | | <check_definition
[2 Oct 2007 13:12]
MySQL Verification Team
00196 /* Varchar (0-255 bytes) with length packed with 1 byte */ 00197 HA_KEYTYPE_VARTEXT1=15, /* Key is sorted as letters */ 00198 HA_KEYTYPE_VARBINARY1=16, /* Key is sorted as unsigned chars */ 00199 /* Varchar (0-65535 bytes) with length packed with 2 bytes */ 00200 HA_KEYTYPE_VARTEXT2=17, /* Key is sorted as letters */ 00201 HA_KEYTYPE_VARBINARY2=18, /* Key is sorted as unsigned chars */
[2 Oct 2007 22:51]
Xing Li
This also happens even on a fixed row myisam table with no varchar columns. Trying to upgrade a fully checked, zero-error, clean 5.0.46 mysql-ent myisam table to 5.1.22rc. Even used myisamchck from 5.1.22rc to clean out any problems. Myisamchk has no problems with the table but mysqld has which doesn't make add up. CREATE TABLE `myalerts` ( `userid` int(10) unsigned NOT NULL default '0', `myid` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`userid`,`storyid`), KEY `storyid` (`myid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Exact same error. Invalid key file for table xxx, repair...repair via mysql query fails and throws corrupt table message. 1/3 of all my myisam tables from 5.0.46 system cannot be migrated to 5.1.22rc with this problem.
[3 Oct 2007 8:43]
MySQL Verification Team
Xing, your table structure doesn't make sense. Also, I couldn't repeat a problem from 5.0-> 5.1 using empty tables. Can you show us SHOW TABLE STATUS and SHOW CREATE TABLE output for that table? Thanks
[3 Oct 2007 17:36]
Xing Li
Shane, Sorry about that. Tried to change the names of some fields. I have ftped bug-data-31331-xing.tar.gz to the /pub/mysql/uploads/ folder which is a complete snapshot of the table, index, and frm file. The table/index was verified/cleaned up with myisamchk -rav from 5.1.22rc x64. Loading up the tables via 5.1.22 should give you the errors that you can trace. Original setup: 5.0.26 x64.
[4 Dec 2007 20:28]
Sveta Smirnova
Our manual says "As a general rule, we recommend that when you upgrade from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 4.0 and wish to upgrade to a newer series, upgrade to MySQL 4.1 rather than to 5.0 or 5.1." at http://dev.mysql.com/doc/refman/5.1/en/upgrade.html But if I do next: 1. Create table with key with version 4.0 or 3.23 2. Move table to 5.0 datadir 3. repair it 4. move repaired table to 5.datadir 5. get " Incorrect key file for table 'a'; try to repair it" error So bug can be considered verified. This incompatibility should be at least documented.
[8 Dec 2007 17:44]
Sveta Smirnova
Bug #32636 was marked as duplicate of this one.
[8 Dec 2007 18:07]
Venu Anuganti
If you read the original bug report it says, the problem happens when you upgrage in the same lines of ... upgrade from 4.1 -> 5.0 -> 5.1 I have made my own patch by skipping the check_defination from MyISAM or Merge tables on this and nothing broke so far .. as the default KEY type is changes between the versions.
[18 Dec 2007 18:38]
Timothy Smith
Regretably, it's not supported to upgrade between major versions of MySQL (e.g., 4.1 to 5.0, or 5.0 to 5.1) without a dump and restore. In some cases it does work, but it is not a supported configuration. To ease the transition, it's possible to run a 4.1 master and a 5.0 slave, to get all of your data into the 5.0 server without service interruption. Regards, Timothy
[18 Dec 2007 19:32]
Timothy Smith
I beg your pardon, I was mistaken in my previous comment. While it's true that binary upgrades between major versions are not always possible, it should be possible except in certain cases which make it technically impossible. Those cases are listed in the manual. In addition, it should always be possible to at least read the table and dump the data out from the new version (e.g., with a full table scan), so that the dump and restore can be done from within the new version. My apologies for a hasty decision.
[9 Jan 2008 14:36]
Xing Li
It has been 3 months. Can we get an update on this bug? Sveta? Overview: 1) Confirmed that the problem is not isolated to 4.X to 5.1 upgrade but even from new 5.0 to 5.1 upgrade. 2) Inconsistent behavior between server and cli tool. If server check chokes, why is myisamchk okay with it? Nonsensical. 3) There is no documentation/changelog/warning available to suggest that 5.1's myisam data structure has changed to a point leading to incompatibility with even 5.0 generated tables. All road point to a major bug critical to the success of anyone on the path to a smooth 5.1 migration. If this bug is not fixed, I fully expect an explosion of queries for the mysql support team once 5.1 goes gold.
[18 Jan 2008 19:11]
Ken Johanson
I am encountering this problem but with some interesting clues (perhaps)!: I have two servers: Server A: Fedora Core 6 X64_64 Mysql 5.0.45 Server B: Fedora Core 8 X64_64 Mysql 5.0.45 The EXACT same binaries and data files exists on both servers. Only the OS and dependent libs differ. On server A a check table of the culprit tables (all MyISAM) reports no errors. On server B the culprint tables all report this bug's error message. Additionally, EACH of those tables generates an error log message: 080118 18:50:35 [Warning] './schema/table' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed. Additionally, some strings returned via unixodbc seem to return invalid character encoding, including server error messages. For example the "error" and "check" cells:: check table deptlog_map: Table Op Msg_type Msg_text chermox.deptlog_map che�� Err�� Incorrect key file for table 'deptlog_map'; try to repair it chermox.deptlog_map che�� err�� Corrupt All of the culprit table share one thing in common: They contain not text types at all, only numeric values! I can provide a tgz'd image of one of these if requested; (PM me at ken2006 at onnet.cc). My conf file is: [mysqld] set-variable=collation_server=utf8_unicode_ci set-variable=character_set_server=utf8 set-variable=max_allowed_packet=16M set-variable=max_connections=1000 set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPES set-variable=relay-log=/usr/local/mysql/data/sql01-relay-bin set-variable=back_log=250 basedir=/usr/local/mysql/ lower_case_table_names=1 datadir=/usr/local/mysql/data skip-name-resolve server-id=1 log-bin=sql01-bin I'm speculating that there is a change in a dependency OS lib, something relating to IO?
[18 Jan 2008 19:56]
Ken Johanson
If I alter the table (on the server which is able to work with it), adding a char column to it, then test the table on the non-working (FC8) server, the table reports as OK, although the response encoding is still damaged: Table Op Msg_type Msg_text chermox.deptlog_map che�� sta��� OK Here are are the table's MyIsam files, if anyone on FC8 can run a check table and get an OK please tell me: http://65.125.174.105/sys/public/deptlog.tgz I have also tried server 5.1.22-rc-log and it does not handle the int-only tables either.
[18 Jan 2008 21:18]
Ken Johanson
All the culprit tables contain a tagged charset can collation of utf8_unicode_ci. If I alter table asset_modified convert to charset 'utf8' (on the FC6 server), the charset then becomes utf8_general_ci. The table files copied onto FC* are then usable. "utf8_unicode_ci" appears to be invalid on newer OSs, presumably it is OS provided converter or alias that was removed. If this is the case Mysql should log more verbose and accurate exceptions when the charset name cannot be found, and should allow altering the table (not failfast).
[25 Jan 2008 0:16]
Xing Li
For those you that want to upgrade 5.0 to 5.1 and want a bypass to this problem without having to resort to the horrific slow sql dump/reload. All the "corrupt" tables in my 5.0 install had myisam version of "9". You can see this by issuing "show table status like 'mytable_name'\G;". To make 5.1 happy, online rebuild the myisam table with latest 5.0.X server. "alter table mytable engine=myisam". Afterwards, your table should have myisam version of "10" and now ready to be used by 5.1. Verify with 'show table status'. You need to do this with all myisam tables with version number lower than 10. Hope this will assist users not wanting to wait for mysql to make a patch.
[6 Feb 2008 20:26]
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/41830 ChangeSet@1.2518, 2008-02-06 21:26:05+01:00, istruewing@stella.local +1 -0 Bug#31331 - MyISAM or Merge Table upgrade incompatibility with 5.1 A table with BLOB/TEXT prefix key part, created with version 4.1, could not be opened by a 5.1 server. The routine check at table open, if the frm file matches the MyISAM table, was too picky regarding old and new implementation of such keys. Added relaxed check for blob prefix key part. No test case. It requires to create a table in 4.1 and open it in 5.1.
[9 Feb 2008 5:24]
Venu Anuganti
What about merge tables ?
[9 Feb 2008 7:50]
Venu Anuganti
Just wondering in case if the patch fixes the following merge table scenario as it looks like the patch is taking care only BLOB/CLOB
In 4.0:
--------
mysql> CREATE TABLE IF NOT EXISTS base_t1 (
-> id int NOT NULL PRIMARY KEY
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS merge_t1 (
-> id int NOT NULL PRIMARY KEY
-> ) TYPE = MRG_MyISAM INSERT_METHOD=LAST UNION=(base_t1);
Query OK, 0 rows affected (0.01 sec)
And when upgraded to new 4.1.23 or 5.0 or 5.1:
----------------------------------------------
mysql> DESC base_t1;
+——-+———+——+—–+———+——-+
| FIELD | Type | NULL | KEY | DEFAULT | Extra |
+——-+———+——+—–+———+——-+
| id | int(11) | | PRI | 0 | |
+——-+———+——+—–+———+——-+
1 row IN SET (0.00 sec)
mysql> DESC merge_t1;
ERROR 1168 (HY000): Unable TO open underlying TABLE which IS differently defined OR of non-MyISAM type OR doesn‘t exist
[9 Feb 2008 9:45]
Ingo Strüwing
IMHO this is a different issue. Please compare Bug#17713 (merge table error using table from 4.1 to 5.0). If you still think your MERGE table problem is different to that one, please file a new bug report.
[9 Feb 2008 16:58]
Venu Anuganti
Filed a new bug #34444 as I was not sure whether that is same or not. http://bugs.mysql.com/bug.php?id=34444
[15 Feb 2008 19:10]
Ingo Strüwing
Queued to 6.0-engines, 5.1-engines.
[27 Mar 2008 11:21]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:53]
Bugs System
Pushed into 6.0.5-alpha
[30 Mar 2008 20:16]
Jon Stephens
Documented in the 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:
A table having an index that included a BLOB or TEXT column, and that
was originally created with a MySQL server using version 4.1 or earlier,
could not be opened by a 5.1 or later server.
Description: When I have the following MyISAM table in MySQL 4.1 with no rows (or with rows) in the table, it works as expected. But the same table fails with an error “ERROR 1034 (HY000) at line 1: Incorrect key file for table ’statSite’; try to repair it” when it was upgraded to 5.1 server using the same old 4.1 data. If you re-create the table in 5.1, everything starts working back (ofcourse). This is a similar case for Merge Tables as wells. How to repeat: Create the following with 4.1: [vanugant@escapereply:v1 ~]$ mysql test Welcome TO the MySQL monitor. Commands end WITH ; OR \g. Your MySQL connection id IS 2 TO server version: 4.1.23-5b-Yahoo-SMP-log Type ‘help;’ OR ‘\h‘ FOR help. Type ‘\c‘ TO clear the buffer. mysql> CREATE TABLE `statSite` ( -> `siteID` varchar(16) NOT NULL DEFAULT ”, -> `siteName` tinytext, -> `siteDesc` mediumtext, -> `username` tinytext, -> `password` tinytext, -> `acctype` int(1) DEFAULT NULL, -> `name` tinytext, -> `addr` tinytext, -> `addr2` tinytext, -> `city` tinytext, -> `state` tinytext, -> `zip` tinytext, -> `payDue` tinytext, -> `billingType` char(1) DEFAULT NULL, -> `lastStatsView` varchar(12) DEFAULT NULL, -> `trackServerID` varchar(16) NOT NULL DEFAULT ”, -> `refID` varchar(16) DEFAULT NULL, -> `refStatus` int(1) NOT NULL DEFAULT ‘0′, -> `refAmount` float DEFAULT NULL, -> `refDate` varchar(8) NOT NULL DEFAULT ”, -> `isAfl` int(1) DEFAULT ‘0′, -> `country` tinytext, -> `ppEmail` tinytext, -> `returntourl` tinytext, -> `adultContent` char(1) DEFAULT NULL, -> `dbServerID` varchar(10) DEFAULT NULL, -> `db_group` varchar(20) DEFAULT NULL, -> PRIMARY KEY (`siteID`), -> KEY `username_idx` (`username`(20)) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM statSite; Empty SET (0.00 sec) mysql> exit Now, Upgrade to 5.0 and see that it works as in case of 4.1: [vanugant@escapereply:v1 ~]$ mysql 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.37-2-Yahoo-SMP-log Yahoo SMP (i386) Type ‘help;’ OR ‘\h‘ FOR help. Type ‘\c‘ TO clear the buffer. mysql> SELECT * FROM statSite; Empty SET (0.00 sec) mysql> exit Now, upgrade to 5.1 server (5.1.22 or any other version); then it fails to open the table: [vanugant@escapereply:v1 ~]$ mysql 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.21-beta-Yahoo-SMP-log (RHEL i386) Type ‘help;’ OR ‘\h‘ FOR help. Type ‘\c‘ TO clear the buffer. mysql> SELECT * FROM statSite; ERROR 1034 (HY000): Incorrect KEY file FOR TABLE ’statSite’; try TO repair it mysql> repair TABLE statSite; +—————+——–+———-+———————————————————–+ | TABLE | Op | Msg_type | Msg_text | +—————+——–+———-+———————————————————–+ | test.statSite | repair | Error | Incorrect KEY file FOR TABLE ’statSite’; try TO repair it | | test.statSite | repair | error | Corrupt | +—————+——–+———-+———————————————————–+ 2 rows IN SET (0.00 sec) mysql> SELECT * FROM statSite; ERROR 1034 (HY000): Incorrect KEY file FOR TABLE ’statSite’; try TO repair it mysql> This happens even when there is an upgrade from 4.1 to 5.1 directly, but not when you (re-)create it with 5.0 or directly with 5.1. As the error indicates, even the repair could not fix the problem. Suggested fix: Expecting the backward compatibility or a easy way to fix instead of users to dump and import it back.