MySQL Bugs: #9687: merge table does not always release/closes tables from union if dropped

Bug #9687	merge table does not always release/closes tables from union if dropped
Submitted:	6 Apr 2005 16:54	Modified:	7 Apr 2005 19:49
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	FreeBSD (freebsd)
Assigned to:	Geert Vanderkelen	CPU Architecture:	Any

Description:
except for the "flush tables" the 2 examples below are the same.

both drop the merge table first, so the table t1 should not be hold open anymore.
In the first example somehow the file is still held open as indicated by the error.

How to repeat:
# Example 1

CREATE TABLE t1 (a int(11));
CREATE TABLE t2 (a int(11)) ENGINE=MRG_MyISAM UNION=(t1); 

insert into t1 values (1), (1), (1);
update t1 set a = (select max(a)+1 from tm order by rand());

drop table t2;
drop table t1;
CREATE TABLE t1 (a int(11));
#ERROR 1105 (HY000): MyISAM table 't1' is in use (most likely by a MERGE table). Try FLUSH TABLES.
flush tables;

# Example 2

CREATE TABLE t1 (a int(11));
CREATE TABLE t2 (a int(11)) ENGINE=MRG_MyISAM UNION=(t1); 

insert into t1 values (1), (1), (1);
update t1 set a = (select max(a)+1 from tm order by rand());

drop table t2;
drop table t1;

flush tables;
CREATE TABLE t1 (a int(11));
drop table t1;

Suggested fix:
-

Hi Martin,

This is expected behavior and is mentioned in the manual on the following URL:
http://dev.mysql.com/doc/mysql/en/merge-table-problems.html

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

Regards,

Geert

This differs from the subscription.
I am aware that if I drop table t1 (the "real" table, while t2 (the merge) is open, then this will not work.

But in the example I drop the merge table first. I cannot read from the documentation, that a droped merge table could still be open?

So after droping the merge, it should not refer to the myisam table anymore (as in the 2nd example)

Sorry, my fault, there was a typo in one of the queries, mixing up with a different table.

There is no bug at all.

Sorry