Additional languages
« 14.6 The MERGE Storage Engine
14.7 The MEMORY (HEAP) Storage Engine »
Section Navigation      [Toggle]

14.6.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.

  • 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 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.

  • In MySQL 5.1.15 and later, 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 then an error will be returned and the statement will fail.

    Because these checks take place when the tables are opened, any changes to the definition of a single, including column changes, ocolumn ordering and engine alterations will cause the statement to fail.

    In MySQL 5.1.14 and earlier:

    • 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 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.

  • If you encounter an error message similar to ERROR 1017 (HY000): Can't find file: 'mm.MRG' (errno: 2) it generally indicates that some of the base tables are not using the MyISAM storage engine. Confirm that all tables are MyISAM.

  • There is a limit of 232 (~4.295E+09) rows to a MERGE table, just as there is with a MyISAM, it is therefore not possible to merge multiple MyISAM tables that exceed this limitation. However, you build MySQL with the --with-big-tables option then the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.9.2, “Typical configure Options”. Beginning with MySQL 5.0.4 all standard binaries are built with this option.

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


User Comments

Posted by Haitao Jiang on July 21 2004 5:41pm[Delete] [Edit]

One thing to add: you can not create fulltext indexes on merge table.

Posted by David Hammink on August 2 2006 10:38am[Delete] [Edit]

VARCHAR use in tables causes problems with merge
5.0.22

Try this
CREATE TABLE `WLFiles_200608` (
`WL_FileID` int(11) unsigned NOT NULL auto_increment,
`INTELKENNUNG` smallint(4),
`DienstID` smallint(4) ,
`Date` datetime default NULL,
`SerialNo` smallint(3) unsigned ,
`FileName` varchar(100) default NULL,
`Directory` varchar(100) default NULL,
`Sent` bit(1) default NULL,
PRIMARY KEY (`WL_FileID`)
)ENGINE=MyISAM ;
CREATE TABLE `WLFiles_200608` (
`WL_FileID` int(11) unsigned NOT NULL auto_increment,
`INTELKENNUNG` smallint(4) ,
`DienstID` smallint(4) ,
`Date` datetime default NULL,
`SerialNo` smallint(3) unsigned ,
`FileName` varchar(100) default NULL,
`Directory` varchar(100) default NULL,
`Sent` bit(1) default NULL,
PRIMARY KEY (`WL_FileID`)
)ENGINE=MyISAM ;

CREATE TABLE `WLFiles_T` (
`WL_FileID` int(11) unsigned NOT NULL auto_increment,
`INTELKENNUNG` smallint(4) ,
`DienstID` smallint(4) ,
`Date` datetime default NULL,
`SerialNo` smallint(3) unsigned ,
`FileName` varchar(100) default NULL,
`Directory` varchar(100) default NULL,
`Sent` bit(1) default NULL,
INDEX (`WL_FileID`)
)
ENGINE=MRG_MyISAM UNION=(WLFiles_200607,WLFiles_200608) INSERT_METHOD LAST;

SELECT * FROM WLFiles_T;

the last statement will produce
All tables in the MERGE table are not identically defined

Change the varchar into char and the problem is solved


Posted by Brian O'Donnell on September 28 2006 11:11am[Delete] [Edit]

Also worth pointing out that in MySQL 3.23, MERGE tables don't allow INSERT operations. You can't specify INSERT_METHOD as the SQL parser doesn't allow it, and having looked around the web for older versions of MySQL docs, its clearly stated that you can't do this.

These docs should CLEARLY state situations situations where different versions of MySQL have different behaviour, for those of us who have to support customers on different platforms etc.

Posted by Johannes Ullrich on October 31 2006 1:55pm[Delete] [Edit]

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 [name withheld] on January 4 2007 4:09pm[Delete] [Edit]

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.

Add your own comment.