Description:
when trying to open a merge table that references a non-myisam, crashed, or non-existent table we get this expected error:
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
However after receiving this error, we cannot open *valid* merge tables after flush tables is executed either!
See the last output of the testcase:
mysql> show create table m1\G
*************************** 1. row ***************************
Table: m1
Create Table: CREATE TABLE `m1` (
`id` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`
1 row in set (0.00 sec)
mysql> create table m2(id int)engine=mrg_myisam
-> union=(notexist) insert_method=first;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table m2\G <--------expecting error
ERROR 1168 (HY000): Unable to open underlying table which is differently d
mysql> show create table m1\G <--------works as expected
*************************** 1. row ***************************
Table: m1
Create Table: CREATE TABLE `m1` (
`id` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`
1 row in set (0.00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table m1\G <--------expecting to work, but fails!!
ERROR 1168 (HY000): Unable to open underlying table which is differently d
mysql>
How to repeat:
run on 5.0.58:
flush tables;
drop table if exists m1,m2,t1,t2;
create table t1(id int)engine=myisam;
create table t2(id int)engine=myisam;
create table m1(id int)engine=mrg_myisam
union=(t1,t2) insert_method=first;
show create table m1\G
create table m2(id int)engine=mrg_myisam
union=(notexist) insert_method=first;
show create table m2\G
show create table m1\G
flush tables;
show create table m1\G
Description: when trying to open a merge table that references a non-myisam, crashed, or non-existent table we get this expected error: ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist However after receiving this error, we cannot open *valid* merge tables after flush tables is executed either! See the last output of the testcase: mysql> show create table m1\G *************************** 1. row *************************** Table: m1 Create Table: CREATE TABLE `m1` ( `id` int(11) default NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1` 1 row in set (0.00 sec) mysql> create table m2(id int)engine=mrg_myisam -> union=(notexist) insert_method=first; Query OK, 0 rows affected (0.00 sec) mysql> show create table m2\G <--------expecting error ERROR 1168 (HY000): Unable to open underlying table which is differently d mysql> show create table m1\G <--------works as expected *************************** 1. row *************************** Table: m1 Create Table: CREATE TABLE `m1` ( `id` int(11) default NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1` 1 row in set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show create table m1\G <--------expecting to work, but fails!! ERROR 1168 (HY000): Unable to open underlying table which is differently d mysql> How to repeat: run on 5.0.58: flush tables; drop table if exists m1,m2,t1,t2; create table t1(id int)engine=myisam; create table t2(id int)engine=myisam; create table m1(id int)engine=mrg_myisam union=(t1,t2) insert_method=first; show create table m1\G create table m2(id int)engine=mrg_myisam union=(notexist) insert_method=first; show create table m2\G show create table m1\G flush tables; show create table m1\G