Bug #11763 Incorrect error when tried to drop a table used in merge table.
Submitted: 6 Jul 2005 9:23 Modified: 17 Jul 2005 19:48
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7 Beta OS:Windows (Windows)
Assigned to: Ingo Strüwing CPU Architecture:Any

[6 Jul 2005 9:23] Disha
Description:
If we try to drop a table used in merge table then incorrect error is displayed.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create two tables as follows and insert values in it:

   create table t1(f1 int, f2 char(10))//
   create table t2(f1 int, f2 char(10))//
   insert into t1 values(1,'a')//
   insert into t2 values(2,'b')//

4. Create a merge table as follows and insert values in it:

   create table t3(f1new int, f2ne2 char(10)) type=merge union=(t1,t2) insert_method=first//
   insert into t3 values(3,'c')//

5. Now try to delete the base table (that is either t1 or t2) as follows:

   drop table t1//

Expected Results: 
1. Appropriate error should be displayed as the table is used in a merge table.

Actual Results: 
1. Incorrect error is displayed as follows:
"ERROR 6 (HY000): Error on delete of '.\test\t1.MYI' (Errcode: 13)"
[17 Jul 2005 19:45] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is documented as

* You can't 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 a MERGE table that is ``open.'' If you do this, the MERGE table may still refer to the original table and you get unexpected results. The easiest way to work around this deficiency is to issue a FLUSH TABLES statement to ensure that no MERGE tables remain ``open.'' 

http://dev.mysql.com/doc/mysql/en/merge-table-problems.html
[17 Jul 2005 19:48] Sergei Golubchik
The same url also mentions:

* DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE storage engine does the table mapping hidden from the upper layer of MySQL. Because Windows doesn't allow you to delete files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.