Bug #34781 create table ... engine=merge should warn user when non-MyISAM tables are used
Submitted: 24 Feb 2008 6:36 Modified: 24 Feb 2008 6:38
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S4 (Feature request)
Version:5.0.46, 5.0.54 OS:Any
Assigned to: CPU Architecture:Any

[24 Feb 2008 6:36] Valeriy Kravchuk
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).
[24 Feb 2008 7:54] Valeriy Kravchuk
This looks like a duplicate of http://bugs.mysql.com/bug.php?id=22716, actually. That bug is in "Won't fix" state, as current behaviour is intended. So, I'll make this a feature request.
[2 Oct 2008 15:53] Konstantin Osipov
Bug#35586 was marked a duplicate of this bug.