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.
© 2023, Oracle Corporation and/or its affiliates
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.