Summary:

Wasn't sure if this was a 'known issue' or not, as its not on the 'Known Errors Page' (btw, since 3.23.34 fixes the Merge table not showing the union, you can probably remove that from the page, first item, 'mysqldump on a merge table ....' ).

The problem comes with alter table. I ALTER the tables under the merge, and the merge table itself, the merge table doesn't work anymore. What I found out is that when you ALTER a Merge table, it truncates the .MRG file, apperantly ALTER isn't aware of the UNION setting.

MySQL Version: 3.23.33

Test case:

mysql> create table test_m1 ( test_1 int, test_2 int ) ; Query OK, 0 rows affected (0.00 sec)

mysql> create table test_m2 ( test_1 int, test_2 int ) ; Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_m1 values ( 1, 1 ) ; Query OK, 1 row affected (0.01 sec)

mysql> insert into test_m2 values ( 2, 2 ) ; Query OK, 1 row affected (0.00 sec)

mysql> create table test_mm ( test_1 int, test_2 int ) type=merge union=(test_m1,test_m2); Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_mm ; +--------+--------+ | test_1 | test_2 | +--------+--------+ | 1 | 1 | | 2 | 2 | +--------+--------+ 2 rows in set (0.00 sec)

mysql> alter table test_m1 add test_3 int ; Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table test_m2 add test_3 int ; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table test_mm add test_3 int ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from test_mm ; Empty set (0.00 sec)

Weslee

P.S. We are now using MERGE tables, replication, and full text searches on our machines. Nothing like going bleeding-edge all the way. Looking forward to trying Innobase as well. :)