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 17:51	Modified:	16 Jul 2008 0:47
Reporter:	Venu Anuganti
Status:	Duplicate
Category:	Server: Merge	Severity:	S2 (Serious)
Version:	5.0 or 5.1	OS:	Any
Assigned to:		Target Version:
Tags:	Merge upgrade can not open file, merge table upgrade
Triage:	D3 (Medium)

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.