Bug #22716 MERGE table creation does not verify existence of underlying tables
Submitted: 26 Sep 2006 22:22 Modified: 13 Apr 2007 23:17
Reporter: Arjen Lentz Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.0/5.1/4.1BK OS:Linux (Linux Suse)
Assigned to: CPU Architecture:Any
Tags: bfsm_2007_03_15, merge

[26 Sep 2006 22:22] Arjen Lentz
It is understood that merge tables only have minimal error checking.
However, it seems sensible to at least verify the existence of the underlying tables at time of merge table creation.

How to repeat:
mysql> show tables like 'foobar%';
Empty set (0.01 sec)

mysql> create table foobar (id int) ENGINE=MERGE UNION=(foobar1);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from foobar;
ERROR 1017 (HY000): Can't find file: 'foobar' (errno: 2)
[26 Sep 2006 22:38] Arjen Lentz
No error appears until the merge table is actually used, but when it does appear, the error incorrectly states that the problem is in locating the file for the merge table, and not the file for the table missing in the UNION.

This makes tracking down problems even more difficult, in particular on a system with many tables in merge constructs.
[26 Sep 2006 23:58] Trudy Pelzer
When related bug#457 is fixed, this problem may
also be solved.
[27 Sep 2006 0:30] MySQL Verification Team
Thank you for the bug report. Verified as described.
[16 Oct 2006 11:40] Sergey Vojtovich
This is rather a feature than a bug. Consistency of underlying tables at time of merge table definition is out of the scope of the MERGE engine.

There is no much sense to verify existence of the underlying table at time of merge table creation whereas underlying table can be dropped/altered later, when merge table exist.

Blocking DDL statements for underlying tables that are in use by merge table makes maintenance (including backup/restore) of underlying tables impossible (until it is part of merge table).

It is worth to note that this behaviour is well documented (see below an excerpt from mysql manual).

Confusing error message was fixed as bug#10974.

Quoting Monty (see bug#457):
The intention has always beeen that MERGE tables shouldn't check if the underlaying tables exists until the MERGE table is used.
(I agree that the error message is far from perfect and we shall change that ASAP)

A reason for this is that we don't want to check if a table is used by some MERGE table when doing a DROP table on it, as this would cause a lot of problems when doing backup/restore of tables.

Quoting MERGE Table Problems section of MySQL manual:
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.