MySQL Bugs: #34444: Merge table breaks when upgrading from 4.1 to 5.0 or 5.1

Bug #34444	Merge table breaks when upgrading from 4.1 to 5.0 or 5.1
Submitted:	9 Feb 2008 16:51	Modified:	15 Jul 2008 22:47
Reporter:	Venu Anuganti	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	5.0 or 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	merge table upgrade, Merge upgrade can not open file

Description:
This is bit similar and related to bug #31331 where it was taken care only for BLOB/CLOB; and there is a similar bug #17713 with a different symptoms.

How to repeat:
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

Suggested fix:
Please fix by relaxing the check on default key type in 4.1.23 and latest 5.0 and 5.1 under check_defination from ha_myisam.cc as the table can be created from older versions.

By the way this is related to HA_KEY_ALG_UNDEF as the default key in older versions where as the new one has either BTREE, RTREE, HASH or FULLTEXT). So, when you see UNDEF, then you may need to another level check what the underlying type is (may be only BTREE) and continue further without raising an error.

Thank you for a problem report. Have you tried to run CHECK TABLE ... FOR UPGRADE for all the underlying tables in 5.0.x and 5.1.x?

verified using 4.0 -> 5.0.56.

flush tables;
drop table if exists merge_t1;
drop table if exists base_t1;
create table if not exists base_t1 (id int not null primary key)type = myisam;
create table if not exists merge_t1 (id int not null primary key)type = mrg_myisam insert_method=last union=(base_t1);

1) execute the above on 4.0.
2) shutdown 4.0 and copy the files to 5.0.56
3) start 5.0.56 and try access the merge table. it fails
4) repair/check doesn't have any affect

note - binary upgrades aren't recommended, but if this is avoidable (or a real bug), it could be fixed if possible.

workaround: mysqldump and reload

Duplicate of bug#31331.