7.2.1 MERGE Table Problems

The following are the known problems with MERGE tables:

• A MERGE table cannot maintain UNIQUE constraints over the whole table. When you do INSERT, the data goes into the first or last table (according to INSERT_METHOD=xxx) and this MyISAM table ensures that the data are unique, but it knows nothing about others MyISAM tables.
• DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables.
• RENAME TABLE on a table used in an active MERGE table may corrupt the table. This will be fixed in MySQL 4.1.x.
• Creation of a table of type MERGE doesn't check if the underlying tables are of compatible types or if they exists. MySQL will do a quick check if the record length is equal between mapped tables when the MERGE table is used, but this is not a fullproof check. If you use MERGE tables in this fashion, you are very likely to run into strange problems.
• If you use ALTER TABLE to first add an UNIQUE index to a table used in a MERGE table and then use ALTER TABLE to add a normal index on the MERGE table, the key order will be different for the tables if there was an old non-unique key in the table. This is because ALTER TABLE puts UNIQUE keys before normal keys to be able to detect duplicate keys as early as possible.
• The range optimiser can't yet use MERGE table efficiently and may sometimes produce non-optimal joins. This will be fixed in MySQL 4.1.x.
• DROP TABLE on a table that is in use by a MERGE table will 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 drop files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table. We will fix this at the same time we introduce VIEWs.

User Comments

If your key values are not unique across all
tables - a row in each table with a primary key
value of 100, for instance - then an exact key
lookup - "... where x = 100" - gives you only the
matching row from the first table from the UNION.
A range or pattern match query - " .... where x
like 100" - however, gives you both rows. If
that's not considered a "problem", then it's
certainly an
interesting feature. It's one thing to say that
unique keys
can't be enforced across the multiple tables. It's
quite
different to simply ignore non-unique key values - and
to only ignore them sometimes, at that.

When using select max(<varname>) from a merge
table with only one matching entry, it returns a NULL
result, however, using select min(<varname>)
returns the correct result. The workaround is to use
a select max(<varname>) on the underlying tables
comprising the merge table.

Add your own comment.