MySQL Bugs: #14854: Cannot restore one table out of merge table collection

Bug #14854	Cannot restore one table out of merge table collection
Submitted:	11 Nov 2005 9:29	Modified:	11 Nov 2005 10:08
Reporter:	Dino Tsoumakis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL-server-4.1.14-0.i386.rpm	OS:	Linux (SuSE 8.1 (2.4.21-273-default))
Assigned to:		CPU Architecture:	Any

Description:
If you backup one table out of the collection of a merge table, drop that table and try to restore that table, you get the following error message:
'Failed generating table from .frm file'

If you also drop the merge table, restore works fine. But afterwards you always have to recreate the merge table.

How to repeat:
Create a merge table containing two tables and some data in them. (It's not happening with empty tables!).
CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int);
CREATE TABLE merge (c1 int) ENGINE='MERGE' UNION(t1, t2);

INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (3), (4);

Backup one table out of collection:
BACKUP TABLE t1 TO '/tmp';

Drop the this table:
DROP TABLE t1;

Try to restore this table:
RESTORE TABLE t1 FROM '/tmp';
You will get an error:
+-------+---------+----------+----------------------------------------+
| Table | Op      | Msg_type | Msg_text                               |
+-------+---------+----------+----------------------------------------+
| t1    | restore | error    | Failed generating table from .frm file |
+-------+---------+----------+----------------------------------------+

Drop the merge table, too:
DROP TABLE merge;

Try to restore again:
RESTORE TABLE t1 FROM '/tmp';
Works fine.

Suggested fix:
???

Thank you for a problem report. For me it looks like not a bug, but intended and documented behaviour. Read the manual (http://dev.mysql.com/doc/refman/4.1/en/merge-table-problems.html), please:

"You cannot 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 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 issue a FLUSH TABLES statement prior to performing any of these operations to ensure that no MERGE tables remain open."

Try to issue FLUSH TABLES before dropping t1, and then restore it. Inform about the results.

I'm sorry for the bug report.
You are right. I forgot to do a 'flush tables'.
If I do that, the restore works fine.