15.7.2 MERGE Table Problems
The following are known problems with
In versions of MySQL Server prior to 5.1.23, it was possible to create temporary merge tables with nontemporary child MyISAM tables.
From versions 5.1.23, MERGE children were locked through the parent table. If the parent was temporary, it was not locked and so the children were not locked either. Parallel use of the MyISAM tables corrupted them.
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.
MERGEtables do not support partitioning. That is, you cannot partition a
MERGEtable, nor can any of a
MyISAMtables be partitioned.
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.
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.
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 264 (~1.844E+19; 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.
Use of underlying
MyISAMtables of differing row formats with a parent
MERGEtable is currently known to fail. See Bug #32364.
You cannot change the union list of a nontemporary
LOCK TABLESis in effect. The following does not work:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; ALTER TABLE m1 ... UNION=(t1,t2) ...;
However, you can do this with a temporary
You cannot create a
CREATE ... SELECT, neither as a temporary
MERGEtable, nor as a nontemporary
MERGEtable. For example:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
Attempts to do this result in an error:
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)