MySQL Bugs: #57120: cannot create a proper merge table using create table like .. alter .. engine=..

Bug #57120	cannot create a proper merge table using create table like .. alter .. engine=..
Submitted:	30 Sep 2010 7:31	Modified:	30 Sep 2010 8:18
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S1 (Critical)
Version:	5.0, 5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
for years i have been telling people to create merge tables using this procedure:

create table myisam_base(...)engine=myisam
create table base1 like myisam_base;
create table base2 like myisam_base;
create table merge_table like myisam_base;
alter table merge_table engine=merge union=(base1,base2,myisam_base);

it works great for most tables, and the merge is identical
to the base structure(s). but some index/columns don't work...

How to repeat:
flush tables;
drop table if exists `t1_merge`;
drop table if exists `t1`;

create table `t1`(`a` int not null,`b` char(1),`c` int,
unique key(`b`),unique key(`c`),primary key(`c`))engine=myisam;

create table `t1_merge` like `t1`;
show create table t1;       #identical
show create table t1_merge; #identical
alter table `t1_merge` engine=merge union=(`t1`);  #5.0 fails here!
select * from `t1_merge`;   #ERROR 1168
repair table `t1_merge`;    #crashes 5.6.1 !

Suggested fix:
make it work.
don't crash.

For 5.0 and 5.1 this looks like D2 bug:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `t1_merge`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1`(`a` int not null,`b` char(1),`c` int,
    -> unique key(`b`),unique key(`c`),primary key(`c`))engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> create table `t1_merge` like `t1`;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table t1;       #identical
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
| Table | Create Table

                                                                |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` char(1) default NULL,
  `c` int(11) NOT NULL default '0',
  PRIMARY KEY  (`c`),
  UNIQUE KEY `b` (`b`),
  UNIQUE KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1_merge; #identical
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
| Table    | Create Table

                                                                         |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
| t1_merge | CREATE TABLE `t1_merge` (
  `a` int(11) NOT NULL,
  `b` char(1) default NULL,
  `c` int(11) NOT NULL default '0',
  PRIMARY KEY  (`c`),
  UNIQUE KEY `b` (`b`),
  UNIQUE KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table `t1_merge` engine=merge union=(`t1`);
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.08 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.52 sec)

mysql> drop table if exists `t1_merge`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1`(`a` int not null,`b` char(1),`c` int,
    -> unique key(`b`),unique key(`c`),primary key(`c`))engine=myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> create table `t1_merge` like `t1`;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table t1;       #identical
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------+
| Table | Create Table

                                                               |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` char(1) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`c`),
  UNIQUE KEY `b` (`b`),
  UNIQUE KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table t1_merge; #identical
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------------+
| Table    | Create Table

                                                                        |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------------+
| t1_merge | CREATE TABLE `t1_merge` (
  `a` int(11) NOT NULL,
  `b` char(1) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`c`),
  UNIQUE KEY `b` (`b`),
  UNIQUE KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table `t1_merge` engine=merge union=(`t1`);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from `t1_merge`;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist
mysql> repair table `t1_merge`;
+---------------+--------+----------+-------------------------------------------
----------------------------------------------------------+
| Table         | Op     | Msg_type | Msg_text
                                                          |
+---------------+--------+----------+-------------------------------------------
----------------------------------------------------------+
| test.t1_merge | repair | Error    | Unable to open underlying table which is d
ifferently defined or of non-MyISAM type or doesn't exist |
| test.t1_merge | repair | error    | Corrupt
                                                          |
+---------------+--------+----------+-------------------------------------------
----------------------------------------------------------+
2 rows in set (0.00 sec)