(mysql.info.gz) MERGE table problems

Info Catalog

(mysql.info.gz) MERGE storage engine

(mysql.info.gz) MERGE storage engine

 
 14.2.1 `MERGE' Table Problems
 -----------------------------
 
 The following are the known problems with `MERGE' tables:
 
    * If you use `ALTER TABLE' to change a `MERGE' table to another
      table type, the mapping to the underlying tables is lost. Instead,
      the rows from the underlying `MyISAM' tables are copied into the
      altered table, which then is assigned the new type.
 
    * Before MySQL 4.1.1, all underlying tables and the `MERGE' table
      itself had to be in the same database.
 
    * `REPLACE' doesn't work.
 
    * You can't use `DROP TABLE', `ALTER TABLE', `DELETE FROM' without a
      `WHERE' clause, `REPAIR TABLE', `TRUNCATE TABLE', `OPTIMIZE
      TABLE', or `ANALYZE TABLE' on any of the tables that are mapped
      into a `MERGE' table that is "open."  If you do this, the `MERGE'
      table may still refer to the original table and you will get
      unexpected results. The easiest way to work around this deficiency
      is to issue a `FLUSH TABLES' statement to ensure that no `MERGE'
      tables remain "open."
 
    * A `MERGE' table cannot maintain `UNIQUE' constraints over the
      whole 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.
 
    * Before MySQL 3.23.49, `DELETE FROM merge_table' used without a
      `WHERE' clause only clears the mapping for the table. That is, it
      incorrectly empties the `.MRG' file rather than deleting records
      from the mapped tables.
 
    * Using `RENAME TABLE' on an active `MERGE' table may corrupt the
      table.  This will be fixed in MySQL 4.1.x.
 
    * When you create a `MERGE' table, there is no check whether the
      underlying tables exist and have identical structure.  When the
      `MERGE' table is used, MySQL does a quick check that the record
      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.
 
    * Index order 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 order will be
      different for the tables if there was an old non-unique index in
      the underlying table. (This is because `ALTER TABLE' puts `UNIQUE'
      indexes before non-unique indexes to be able to detect duplicate
      keys as early as possible.)  Consequently, queries may return
      unexpected results.
 
    * `DROP TABLE' on a table that is in use by a `MERGE' table does 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 delete files that are open, you first must
      flush all `MERGE' tables (with `FLUSH TABLES') or drop the `MERGE'
      table before dropping the table.
 
 

Info Catalog

(mysql.info.gz) MERGE storage engine

(mysql.info.gz) MERGE storage engine