Description:
One can create MERGE table based on InnoDB ones, but it is useless as one gets ERROR 1168 for any SQL statement that tries to access that table:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table ti1(id int primary key, c1 char(10)) engine=InnoDB;
Query OK, 0 rows affected (0.52 sec)
mysql> create table ti2(id int primary key, c1 char(10)) engine=InnoDB;
Query OK, 0 rows affected (0.19 sec)
mysql> create table timerge(id int primary key, c1 char(10)) engine=merge
-> union=(ti1, ti2);
Query OK, 0 rows affected (0.06 sec)
mysql> select * from timerge;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
or of non-MyISAM type or doesn't exist
mysql> insert into timerge values(1,'a');
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
or of non-MyISAM type or doesn't exist
How to repeat:
drop table if exists ti1;
create table ti1(id int primary key, c1 char(10)) engine=InnoDB;
drop table if exists ti2;
create table ti2(id int primary key, c1 char(10)) engine=InnoDB;
drop table if exists timerge;
create table timerge(id int primary key, c1 char(10))
engine=merge union=(ti1, ti2);
select * from timerge;
insert into timerge values(1,'a');
Suggested fix:
Give error message/warning when MERGE table is created based on non-MyISAM table(s).
Description: One can create MERGE table based on InnoDB ones, but it is useless as one gets ERROR 1168 for any SQL statement that tries to access that table: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table ti1(id int primary key, c1 char(10)) engine=InnoDB; Query OK, 0 rows affected (0.52 sec) mysql> create table ti2(id int primary key, c1 char(10)) engine=InnoDB; Query OK, 0 rows affected (0.19 sec) mysql> create table timerge(id int primary key, c1 char(10)) engine=merge -> union=(ti1, ti2); Query OK, 0 rows affected (0.06 sec) mysql> select * from timerge; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist mysql> insert into timerge values(1,'a'); ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist How to repeat: drop table if exists ti1; create table ti1(id int primary key, c1 char(10)) engine=InnoDB; drop table if exists ti2; create table ti2(id int primary key, c1 char(10)) engine=InnoDB; drop table if exists timerge; create table timerge(id int primary key, c1 char(10)) engine=merge union=(ti1, ti2); select * from timerge; insert into timerge values(1,'a'); Suggested fix: Give error message/warning when MERGE table is created based on non-MyISAM table(s).