MySQL 4.0 Merge Tables Incompatibility

Recently Yahoo! small bussiness encountered an interesting error when they tried to upgrade from MySQL 4.0.26 to 4.1.23. This is really interesting how MySQL rolled out an in-compatibility change in the minor version from 4.1.22 to 4.1.23 without much of specifying it any where nor how to get around the problem.

Here is the scenario. When you have a regular table with a simple PRIMARY KEY column and a merge table on top of that base table in MySQL 4.0; then upgrading to 4.1.23 (enterprise release or from the current bitkeeper source) causes the table un-usable due to the check on internal data structure used when building the key/index columns. It works fine even with 4.1.22 community release.

Lets consider with the real example now:

1. First connect to MySQL 4.0.26 server; and create a simple table(base_t1) with a integer primary key column(id) and an associated merge table (merge_t1):
   
   Shell

   Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.24-Yahoo-SMP-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 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> 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.00 sec) 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; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> exit

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.24-Yahoo-SMP-log   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   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> 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.00 sec)   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; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id    | int(11) |      | PRI | 0       |       | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)   mysql> exit

2. Now, upgrade the MySQL Server to 4.1.23 by keeping the same old data files and once the server is upgraded; try to select or describe the merge table(mege_t1); and it pops up with an error as shown below:
   Shell

   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> 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 mysql> select * from merge_t1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn''t exist

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

   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> 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 mysql> select * from merge_t1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn''t exist

The reason for this is; MySQL has rolled out a change to identify what data structures are used to build the key/index columns; and by default it is HA_KEY_ALG_UNDEF in MySQL 4.0 and from 4.1.23 onwards it will be either of the following:

• HA_KEY_ALG_BTREE
• HA_KEY_ALG_RTREE
• HA_KEY_ALG_HASH
• HA_KEY_ALG_FULLTEXT

So, if you recreate the base table back in 4.1; everything works fine because it uses the right type as shown below:

Shell

mysql> drop table base_t1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE IF NOT EXISTS base_t1 ( -> id int NOT NULL PRIMARY KEY -> ) TYPE = MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec) 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; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> drop table base_t1; Query OK, 0 rows affected (0.01 sec)   mysql> CREATE TABLE IF NOT EXISTS base_t1 (     -> id int NOT NULL PRIMARY KEY     -> ) TYPE = MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec)   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; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id    | int(11) |      | PRI | 0       |       | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)

But the question is “why did MySQL rolled this incompatible change” ?, can not they mark HA_KEY_ALG_UNDEF as HA_KEY_ALG_BTREE as the default one in 4.1 and above ?

This really breaks most of the existing tables merge tables as they can’t even open the file due to this invalid check; and only options left is to re-create the tables. I released a new version internally in Yahoo! to bipass the check by using a configurable variable; and after that, everything seemed to be working fine