Bug #35586 CREATE ... engine=merge: too late error message

• Submitted: 26 Mar 2008 23:28
• Modified: 2 Oct 2008 15:52
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Duplicate
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.0.51a
• OS: Any
• CPU Architecture: Any

[26 Mar 2008 23:28] Peter Laursen

Description:
It is possble to create a MERGE table on InnoDB tables.

How to repeat:
create table tab1(id int) engine innodb;
create table tab2(id int)engine innodb;
create table mergetab(id int) engine merge union(tab1, tab2);

-- now 
show table status from `test` where name = 'mergetab';
-- tells engine = NULL so it is not *really* there!

select * from mergetab;
/*
Error Code : 1168
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

.. why then let it create?
*/

alter table tab1 engine MyISAM;
show table status from `test` where name = 'mergetab';
-- still tells engine = NULL

alter table tab2 engine MyISAM;
show table status from `test` where name = 'mergetab';
-- tells engine = MRG_MyISAM

select * from mergetab;
-- voila

-- further I think this shows the inconsistency

alter table tab1 engine innodb;
--no error

alter table tab2 engine innodb;
-- Error on rename of '.\test\tab2.MYI' to '.\test\#sql2-9ac-7.MYI' (Errcode: 17)

alter table tab1 engine myisam;

alter table tab2 engine innodb;
/*again

Error Code : 7
Error on rename of '.\test\tab2.MYI' to '.\test\#sql2-9ac-7.MYI' (Errcode: 17) 

before I could change ONE of the tables to InnDB without error, now I cannot
*/

alter table tab1 engine innodb;
/* now

Error Code : 7
Error on rename of '.\test\tab1.MYI' to '.\test\#sql2-9ac-7.MYI' (Errcode: 17) 

.. so why did it let me change that table before when it won't now?  *)

Suggested fix:
Prevent the MERGE table to create on non-MyISAM tables (not sure about Maria)

[27 Mar 2008 11:38] Susanne Ebrecht

Many thanks for writing a feature request.

[27 Mar 2008 13:59] Heikki Tuuri

MERGE tables are not supported by InnoDB or most table types.

MySQL should block creation of them.

[18 Apr 2008 8:44] Peter Laursen

I strongly agree with Heikki.

using mysqldump to backup database (using --all-databases) aborts with the error

"mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES"

[2 Oct 2008 15:52] Konstantin Osipov

This bug is a duplicate of Bug#34781