The following are known problems with
If you use
ALTER TABLEto change a
MERGEtable to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying
MyISAMtables are copied into the altered table, which then uses the specified storage engine.
INSERT_METHODtable option for a
MERGEtable indicates which underlying
MyISAMtable to use for inserts into the
MERGEtable. However, use of the
AUTO_INCREMENTtable option for that
MyISAMtable has no effect for inserts into the
MERGEtable until at least one row has been inserted directly into the
MERGEtable cannot maintain uniqueness constraints over the entire table. When you perform an
INSERT, the data goes into the first or last
MyISAMtable (as determined by the
INSERT_METHODoption). MySQL ensures that unique key values remain unique within that
MyISAMtable, but not over all the underlying tables in the collection.
MERGEengine cannot enforce uniqueness over the set of underlying tables,
REPLACEdoes not work as expected. The two key facts are:
REPLACEcan detect unique key violations only in the underlying table to which it is going to write (which is determined by the
INSERT_METHODoption). This differs from violations in the
REPLACEdetects a unique key violation, it will change only the corresponding row in the underlying table it is writing to; that is, the first or last table, as determined by the
Similar considerations apply for
INSERT ... ON DUPLICATE KEY UPDATE.
You should not use
TRUNCATE TABLEon any of the tables that are mapped into an open
MERGEtable. If you do so, the
MERGEtable may still refer to the original table and yield unexpected results. To work around this problem, ensure that no
MERGEtables remain open by issuing a
FLUSH TABLESstatement prior to performing any of the named operations.
The unexpected results include the possibility that the operation on the
MERGEtable will report table corruption. If this occurs after one of the named operations on the underlying
MyISAMtables, the corruption message is spurious. To deal with this, issue a
FLUSH TABLESstatement after modifying the
DROP TABLEon a table that is in use by a
MERGEtable does not work on Windows because the
MERGEstorage engine's table mapping is hidden from the upper layer of MySQL. Windows does not permit open files to be deleted, so you first must flush all
FLUSH TABLES) or drop the
MERGEtable before dropping the table.
As of MySQL 5.0.36, the definition of the
MyISAMtables and the
MERGEtable are checked when the tables are accessed (for example, as part of a
INSERTstatement). The checks ensure that the definitions of the tables and the parent
MERGEtable definition match by comparing column order, types, sizes and associated indexes. If there is a difference between the tables, an error is returned and the statement fails. Because these checks take place when the tables are opened, any changes to the definition of a single table, including column changes, column ordering, and engine alterations will cause the statement to fail.
Prior to MySQL 5.0.36, table checks are applied as follows:
When you create or alter
MERGEtable, there is no check to ensure that the underlying tables are existing
MyISAMtables and have identical structures. When the
MERGEtable is used, MySQL checks that the row length for all mapped tables is equal, but this is not foolproof. If you create a
MERGEtable from dissimilar
MyISAMtables, you are very likely to run into strange problems.
Similarly, if you create a
MERGEtable from non-
MyISAMtables, or if you drop an underlying table or alter it to be a non-
MyISAMtable, no error for the
MERGEtable occurs until later when you attempt to use it.
Because the underlying
MyISAMtables need not exist when the
MERGEtable is created, you can create the tables in any order, as long as you do not use the
MERGEtable until all of its underlying tables are in place. Also, if you can ensure that a
MERGEtable will not be used during a given period, you can perform maintenance operations on the underlying tables, such as backing up or restoring them, altering them, or dropping and recreating them. It is not necessary to redefine the
MERGEtable temporarily to exclude the underlying tables while you are operating on them.
The order of indexes in the
MERGEtable and its underlying tables should be the same. If you use
ALTER TABLEto add a
UNIQUEindex to a table used in a
MERGEtable, and then use
ALTER TABLEto add a nonunique index on the
MERGEtable, the index ordering is different for the tables if there was already a nonunique index in the underlying table. (This happens because
UNIQUEindexes before nonunique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results.
If you encounter an error message similar to ERROR 1017 (HY000): Can't find file: '
tbl_name.MRG' (errno: 2), it generally indicates that some of the underlying tables do not use the
MyISAMstorage engine. Confirm that all of these tables are
The maximum number of rows in a
MERGEtable is 232 (~4.295E+09; the same as for a
MyISAMtable). It is not possible to merge multiple
MyISAMtables into a single
MERGEtable that would have more than this number of rows. However, if you build MySQL using the
--with-big-tablesoption, then the maximum number of rows is increased to 264 (1.844E+19); for more information, see Section 2.17.3, “MySQL Source-Configuration Options”.Note
As of MySQL 5.0.4, all standard binaries are built with this option.
MERGEstorage engine does not support
MyISAMtables that have different row formats is possible.
In some cases, differing
PACK_KEYStable option values among the
MERGEand underlying tables cause unexpected results if the underlying tables contain
BINARYcolumns. As a workaround, use
ALTER TABLEto ensure that all involved tables have the same
PACK_KEYSvalue. (Bug #50646)