(mysql.info) merge-table-problems

 
 14.3.1 `MERGE' Table Problems
 -----------------------------
 
 The following are known problems with `MERGE' tables:
 
    * 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.
 
    * `REPLACE' does not work.
 
    * You cannot use `DROP TABLE', `ALTER TABLE', `DELETE' without a
      `WHERE' clause, `REPAIR TABLE', `TRUNCATE TABLE', `OPTIMIZE
      TABLE', or `ANALYZE 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, which yields unexpected
      results. The easiest way to work around this deficiency is to
      ensure that no `MERGE' tables remain open by issuing a `FLUSH
      TABLES' statement prior to performing any of those operations.
 
    * `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 allow
      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.
 
    * 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 (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.
 
    * When you create a `MERGE' table, there is no check to ensure that
      the underlying tables exist 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.
 
    * 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 non-unique index on the `MERGE' table, the
      index ordering is different for the tables if there was already a
      non-unique index in the underlying table. (This happens because
      `ALTER TABLE' puts `UNIQUE' indexes before non-unique indexes to
      facilitate rapid detection of duplicate keys.)  Consequently,
      queries on tables with such indexes may return unexpected results.
Info Catalog
(mysql.info) merge-storage-engine
automatically generated byinfo2html