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

[30 May 2005 7:53] Vasily Kishkin
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
[24 Feb 2006 14:25] Ingo Strüwing
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.
[3 May 2006 22:45] Bugs System
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
[4 May 2006 16:04] Bugs System
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
[9 May 2006 21:30] Bugs System
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
[9 May 2006 23:26] Antony Curtis
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.
[10 May 2006 15:41] Paul DuBois
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)
[12 May 2006 15:51] Bugs System
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
[10 Jul 2006 18:46] Bugs System
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
[11 Jul 2006 20:11] Paul DuBois
The fix went into 5.0.23 (not 5.0.22) and has been reverted in 5.0.24.
[10 Aug 2006 13:42] Dave Pullin
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?
[30 Jun 2009 2:09] Roel Van de Paar
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)
-----------