MySQL Bugs: #22716: MERGE table creation does not verify existence of underlying tables

Bug #22716	MERGE table creation does not verify existence of underlying tables
Submitted:	27 Sep 2006 0:22	Modified:	14 Apr 2007 1:17
Reporter:	Arjen Lentz
Status:	Won't fix
Category:	Server: Merge	Severity:	S2 (Serious)
Version:	5.0/5.1/4.1BK	OS:	Linux (Linux Suse)
Assigned to:		Target Version:
Tags:	merge, bfsm_2007_03_15

Description:
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)

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.

When related bug#457 is fixed, this problem may
also be solved.

Thank you for the bug report. Verified as described.

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