MySQL MyISAM and Merge Tables Incompatibility – II

http://venublog.com/2008/02/08/mysql-myisam-and-merge-tables-incompatibility-ii/

Few months back we noticed the incompatibility change related to MyISAM that was introduced in 4.1.23 and recent 5.0 and 5.1 MySQL versions which breaks when one wants to upgrade to 4.1.23 or latest 5.0 or 5.1 when the MyISAM or Merga table is created with 4.0 or earlier 4.1 version.

I reported a bug to MySQL team way back; and looks like they now pushed a patch to 5.1.

This happens even with Merge tables and looks like merge tables are overlooked in that bug report and I am not 100% sure if the patch fixes the merge table issue. This bug is triggered only when the MyISAM table is created with a key column and Merge is associated with that table.

For example, consider the following MyISAM and Merge tables in 4.1:

Shell

1 2 3 4 5 6 7 8 9 10 11 12

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 it comes to 5.0 or 5.1; the merge table completely fails to open the table files.

Shell

1 2 3 4 5 6 7 8 9 10 11 12

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

So, the only option is to drop and re-create the tables back in the latest MySQL version. In case if the MyISAM table had blob/text fields; then you need to drop and re-create both MyISAM and Merge tables. Even though you can re-create the merge table, the table creation works; but again it fails when you try to do any operation on the table, so one needs to fix MyISAM as well.

Shell

1 2 3 4 5 6 7 8 9 10

mysql > create table mt4 ( id int ) TYPE = MRG_MYISAM INSERT_METHOD = LAST UNION = ( t4 ) ; Query OK , 0 rows affected , 1 warning ( 0.00 sec )   mysql > select * from mt3 ; Empty set ( 0.00 sec )   mysql > select * from mt4 ; ERROR 1168 ( HY000 ) : Unable to open underlying table which is differently defined or of non - MyISAM type

I mad a patch in the internal Yahoo! builds to overcome from this issue and hopefully MySQL will address both the issues as lot of people might start migrating from the existing old tables (even though it is always a good idea to re-create the tables and populate the data when there is major version change)