MySQL Bugs: #10952: alter table from MyISAM to MERGE lost data without errors and warnings

Bug #10952	alter table from MyISAM to MERGE lost data without errors and warnings
Submitted:	30 May 2005 7:53	Modified:	10 Jul 2006 19:27
Reporter:	Vasily Kishkin	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I change engine from MyISAM to MERGE I lost all data in a table without warnings.

mysql> create table tt (c char(20)) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tt values ("Monty"),("WAX"),("Walrus");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table tt engine=MERGE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tt;
Empty set (0.02 sec)

How to repeat:
create table tt (c char(20)) engine=MyISAM;
insert into tt values ("Monty"),("WAX"),("Walrus");
alter table tt engine=MERGE;
select * from tt;

Suggested fix:
no idea

You created a MERGE table with a set of zero subtables. Obviously it cannot contain rows.

But even with a subtable list it is difficult to decide what to do. The only case that could perhaps work at all is if all subtables exist and have the same definition. While copying the data over we might add them or replace the old table contents. Anyway this could lead to unexpected results.

In my opinion we should forbid to create a MERGE table on behalf of an ALTER TABLE statement. At least if the old table is non-empty.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5920

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5960

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6170

pushed to 5.0.22 repository
For documentation:
Behaviour change - it will not be possible to change a table's storage engine to MERGE or BLACKHOLE using ALTER TABLE.

Noted in 5.0.22 changelog.

To prevent inadvertant loss of data, <literal>ALTER
TABLE</literal> can no longer be used to change the storage
engine of a table to <literal>MERGE</literal> or
<literal>BLACKHOLE</literal>. (Bug #10952)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6310

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9010

The fix went into 5.0.23 (not 5.0.22) and has been reverted in 5.0.24.

Removing this functionality unfortunately removes the best way to create a merge table reliably and programatically. 

To create a merge of a MYISAM table called templatetable, I use 
CREATE TABLE subtable1 like templatetable
INSERT INTO subtable1 SELECT * from templatetable where ....
CREATE TABLE subtable2 like templatetable
INSERT INTO subtable2 SELECT * from templatetable where ....
CREATE TABLE mergeTable like templatetable
ALTER TABLE mergeTable engine=merge union=(subtable1 ,subtable2)

This is done automatically by code that does not know the content of tables or the definition of it.

Did you intend to make this impossible?

Dave, this functionality was not removed. Test on 5.1.35:

-----------
mysql> CREATE TABLE temptable (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO temptable VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE subtable1 LIKE temptable;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO subtable1 SELECT * FROM temptable WHERE ID > 2;
Query OK, 1 row affected (0.29 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE subtable2 LIKE temptable;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO subtable2 SELECT * FROM temptable WHERE ID <= 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE mergeTable like temptable;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE mergeTable ENGINE=MERGE UNION=(subtable1,subtable2);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mergeTable;
+------+
| id   |
+------+
|    3 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)
-----------