The following are known problems with MERGE
tables:
In versions of MySQL Server prior to 5.1.23 and 6.0.4, it was possible to create temporary merge tables with non-temporary child MyISAM tables.
From versions 5.1.23 and 6.0.4, 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.
From 6.0.6 onwards, the children are locked independently from the parent. It is possible to have non-temporary children with a temporary parent. Even though the temporary MERGE table itself is not locked, each non-temporary child MyISAM table is locked anyway.
The reintroduction of support for non-tempporary children with a temporary MERGE table was completed in 6.0.14. Note that 5.1.23 onwards does not currently have the child locking scheme required to support this.
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 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.
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.3.2, “Typical configure 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:
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
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:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
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)
User Comments
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.
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.
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.
Add your own comment.