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).