Bug #28047 Merge doesn't check for compatible version on CREATE/ALTER
Submitted: 23 Apr 2007 21:19
Reporter: Matthew Montgomery Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: CPU Architecture:Any
Tags: merge
Triage: Triaged: D5 (Feature request)

[23 Apr 2007 21:19] Matthew Montgomery
Description:
Any MERGE table created in 5.0 that reference MyISAM tables that were created in 4.1 (Version 9) return an error   

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

The ALTER or CREATE merge table operation does not return any errors or warning at all.

MERGE tables created in 4.1 will continue to function until you attempt to ALTER (recreate) them.   It would be nice for MySQL to check that constituent tables to a MERGE have a compatible version to avoid some user head scratching.

How to repeat:
mysql> show create table m1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                   |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| m1    | CREATE TABLE `m1` (
  `a` int(11) default NULL,
  `b` varchar(10) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table m1 ;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `m1` (
    ->   `a` int(11) default NULL,
    ->   `b` varchar(10) default NULL
    -> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) ;
Query OK, 0 rows affected (0.15 sec)

mysql> show create table m1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

mysql> show table status;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------------------------------------------------------------------------------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment                                                                          |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------------------------------------------------------------------------------+
| m1      | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |      NULL | NULL                | NULL                | NULL                | NULL              |     NULL | NULL           | Unable to open underlying table which is differently defined or of non-MyISAM ty |
| t1      | MyISAM |       9 | Dynamic    |    3 |             20 |          60 |      4294967295 |         1024 |         0 |      NULL | 2007-04-23 11:01:17 | 2007-04-23 11:03:38 | NULL                | latin1_swedish_ci |     NULL |                |                                                                            |
| t2      | MyISAM |       9 | Dynamic    |    3 |             20 |          60 |      4294967295 |         1024 |         0 |      NULL | 2007-04-23 11:01:24 | 2007-04-23 11:04:49 | NULL                | latin1_swedish_ci |     NULL |                |                                                                            |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
Work Around: Reparing the underlying table (Convert to Version 10) will correct the MERGE table or allow you to recreate a working version.

Doing a full dump/restore from 4.1 into 5.0 as recommended in the upgrade procedure avoids this error as well.