MySQL Reference Manual :: 14.2.1 MERGE Table Problems

If you use ALTER TABLE to change a MERGE table to another table type, the mapping to the underlying tables is lost. Instead, the rows from the underlying MyISAM tables are copied into the altered table, which then is assigned the new type.

Before MySQL 4.1.1, all underlying tables and the MERGE table itself had to be in the same database.

REPLACE doesn't work.

You can't use DROP TABLE, ALTER TABLE, DELETE FROM without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the tables that are mapped into a MERGE table that is ``open.'' If you do this, the MERGE table may still refer to the original table and you get unexpected results. The easiest way to work around this deficiency is to issue a FLUSH TABLES statement to ensure that no MERGE tables remain ``open.''

A MERGE table cannot maintain UNIQUE constraints over the whole table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection.

Before MySQL 3.23.49, DELETE FROM merge_table used without a WHERE clause only clears the mapping for the table. That is, it incorrectly empties the .MRG file rather than deleting records from the mapped tables.

Using RENAME TABLE on an active MERGE table may corrupt the table. This is fixed in MySQL 4.1.x.

When you create a MERGE table, there is no check whether the underlying tables exist and have identical structure. When the MERGE table is used, MySQL does a quick check that the record length for all mapped tables is equal, but this is not foolproof. If you create a MERGE table from dissimilar MyISAM tables, you are very likely to run into strange problems.

Index order in the MERGE table and its underlying tables should be the same. If you use ALTER TABLE to add a UNIQUE index to a table used in a MERGE table, and then use ALTER TABLE to add a non-unique index on the MERGE table, the index order is different for the tables if there was an old non-unique index in the underlying table. (This is because ALTER TABLE puts UNIQUE indexes before non-unique indexes to be able to detect duplicate keys as early as possible.) Consequently, queries may return unexpected results.

DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE storage engine does the table mapping hidden from the upper layer of MySQL. Because Windows doesn't allow you to delete files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.