MySQL 5.1 Reference Manual  /  ...  /  MERGE Table Problems

14.8.2 MERGE Table Problems

The following are known problems with MERGE tables:

  • 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 TABLE to change a MERGE table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying MyISAM tables are copied into the altered table, which then uses the specified storage engine.

  • The INSERT_METHOD table option for a MERGE table indicates which underlying MyISAM table to use for inserts into the MERGE table. However, use of the AUTO_INCREMENT table option for that MyISAM table has no effect for inserts into the MERGE table until at least one row has been inserted directly into the MyISAM table.

  • A MERGE table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT, the data goes into the first or last MyISAM table (as determined by the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not over all the underlying tables in the collection.

  • Because the MERGE engine cannot enforce uniqueness over the set of underlying tables, REPLACE does not work as expected. The two key facts are:

    • REPLACE can detect unique key violations only in the underlying table to which it is going to write (which is determined by the INSERT_METHOD option). This differs from violations in the MERGE table itself.

    • If REPLACE detects 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 INSERT_METHOD option.

    Similar considerations apply for INSERT ... ON DUPLICATE KEY UPDATE.

  • MERGE tables do not support partitioning. That is, you cannot partition a MERGE table, nor can any of a MERGE table's underlying MyISAM tables be partitioned.

  • You should not use ANALYZE TABLE, REPAIR TABLE, OPTIMIZE TABLE, ALTER TABLE, DROP TABLE, DELETE without a WHERE clause, or TRUNCATE TABLE on any of the tables that are mapped into an open MERGE table. If you do so, the MERGE table may still refer to the original table and yield unexpected results. To work around this problem, ensure that no MERGE tables remain open by issuing a FLUSH TABLES statement prior to performing any of the named operations.

    The unexpected results include the possibility that the operation on the MERGE table will report table corruption. If this occurs after one of the named operations on the underlying MyISAM tables, the corruption message is spurious. To deal with this, issue a FLUSH TABLES statement after modifying the MyISAM tables.

  • DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE storage 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 MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.

  • As of MySQL 5.1.15, the definition of the MyISAM tables and the MERGE table are checked when the tables are accessed (for example, as part of a SELECT or INSERT statement). The checks ensure that the definitions of the tables and the parent MERGE table 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.1.15, table checks are applied as follows:

    • When you create or alter MERGE table, there is no check to ensure that the underlying tables are existing MyISAM tables and have identical structures. When the MERGE table is used, MySQL checks that the row 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.

    • Similarly, if you create a MERGE table from non-MyISAM tables, or if you drop an underlying table or alter it to be a non-MyISAM table, no error for the MERGE table occurs until later when you attempt to use it.

    • Because the underlying MyISAM tables need not exist when the MERGE table is created, you can create the tables in any order, as long as you do not use the MERGE table until all of its underlying tables are in place. Also, if you can ensure that a MERGE table 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 MERGE table temporarily to exclude the underlying tables while you are operating on them.

  • The order of indexes 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 nonunique index on the MERGE table, the index ordering is different for the tables if there was already a nonunique index in the underlying table. (This happens because ALTER TABLE puts UNIQUE indexes 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 MyISAM storage engine. Confirm that all of these tables are MyISAM.

  • The maximum number of rows in a MERGE table is 264 (~1.844E+19; the same as for a MyISAM table), provided that the server was built using the --with-big-tables option. (All standard MySQL 5.1 standard binaries are built with this option; for more information, see Section 2.11.4, “MySQL Source-Configuration Options”.) It is not possible to merge multiple MyISAM tables into a single MERGE table that would have more than this number of rows.

  • The MERGE storage engine does not support INSERT DELAYED statements.

  • Use of underlying MyISAM tables of differing row formats with a parent MERGE table is currently known to fail. See Bug #32364.

  • As of MySQL 5.1.23, you cannot change the union list of a nontemporary MERGE table when LOCK TABLES is in effect. The following does not work:

    ALTER TABLE m1 ... UNION=(t1,t2) ...;

    However, you can do this with a temporary MERGE table.

  • As of MySQL 5.1.23, you cannot create a MERGE table with CREATE ... SELECT, neither as a temporary MERGE table, nor as a nontemporary MERGE table. For example:


    Attempts to do this result in an error: tbl_name is not BASE TABLE.

  • In some cases, differing PACK_KEYS table option values among the MERGE and underlying tables cause unexpected results if the underlying tables contain CHAR or BINARY columns. As a workaround, use ALTER TABLE to ensure that all involved tables have the same PACK_KEYS value. (Bug #50646)

Download this Manual
User Comments
  Posted by Johannes Ullrich on October 31, 2006
If a MyISAM table is part of a MERGE table, you can not just copy the table files as you upgrade from MySQL 4.1 to 5.0. Instead, you HAVE TO dump the table and read it back in.

If you don't: you will get errors indicating that the tables are not defined identically.

  Posted by on January 4, 2007
Actually, you don't have to drop and repopulate your MyISAM tables; running an ALTER TABLE statement (for instance, using CHANGE COLUMN to transform the primary key into its current definition) will upgrade the MyISAM table to the current version and the MERGE table will continue to function.

You can see the MyISAM version in SHOW TABLE STATUS; notice that MyISAM tables created by MySQL 4.1 are version 9 and MyISAM tables created by MySQL 5.0 are version 10.
  Posted by Simon Mudd on June 25, 2009
ALTER TABLE can be used (at least in 5.0.68) on the underlying tables to change index definitions. mysqld appears happy to allow you to do this. However, ensure you use FLUSH TABLE after doing this as access to the merge table appears to continue accessing the old underlying table prior to the ALTER TABLE and not the new table. If the underlying tables in you merge table are getting updated it may look as if these INSERTS/UPDATES or DELETES are not working when they are, but you are simply looking at the state of the old table.

This behaviour also means that the disk space of the old tables is not freed as mysqld still has the file handles open and thus altering many underlying tables may apparently fill up the disk for no apparent reason.

Again FLUSH TABLES will solve this, though the problem should be dealt with by mysqld itself.
Sign Up Login You must be logged in to post a comment.