MySQL Bugs: #8710: some strange erraneous cache of merge tables

Bug #8710	some strange erraneous cache of merge tables
Submitted:	22 Feb 2005 20:33	Modified:	28 Jun 2006 11:30
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	FreeBSD (freebsd)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
You can create a merge table,
if you drop one of the union tables,  AFTER selecting content from it,  you can still get
the content. (not from the query cache)

mysql will only realize, if you change the db.

seems to be per session

How to repeat:
create table src1 (a int) select 1 a;
create table src2 (a int) select 2 a;
create table merge1 (a int) ENGINE='MERGE' UNION (src1, src2);

select * from merge1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

drop table src1;

select * from merge1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
# so I though, the query cache, but

reset query cache;
select * from merge1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

# how that ?

# change db, and back and you get an error

Suggested fix:
-

after changing the db, and changing back, or in a new session:

select * from merge1;
ERROR 1017 (HY000): Can't find file: 'merge1.MRG' (errno: 2)

the error is not correct, the file merge1.MRG is there and fine. the problem is with the file that was dropped for src1

after changing the db, and changing back, or in a new session:

select * from merge1;
ERROR 1017 (HY000): Can't find file: 'merge1.MRG' (errno: 2)

the error is not correct, the file merge1.MRG is there and fine. the problem is with the file that was dropped for src1

the error might appear on freebsd only. (at least for the delete part)

I found the following additional behaviour
 create table src3 (a int) select 1 a;
 rename table src1 to src4, src3 to src1;
now the merge engine will still show the content, as if the rename was never done.

It seems that the merge handler has the old file open, on freebsd, (and some other systems) renaming of open fles is possible, and the processes will have the same file open, never minding the name change)

# however not sure, whuy I get the following back (this is the new src1, which is not shown by the merge table)
select * from  src1;
ERROR 1030 (HY000): Got error 127 from storage engine

with regards to the original error, if the table gets droped, while the merge handler has the file open (mysql will tell the op system to delete the files), then freebsd will unlink the files (remove thme from the directory (freebsd will keep the files, untill the last process closes them). Therefore the merge handler still sees the content.

As obviously the merge handler is nowhere kept in sync with otehr operations on the files of the union tables, drop and rename table operations, will have very operating system depending behaviour

I could verify all but the "error 127" problem on linux.
Using FLUSH TABLES it is even easier to reproduce the problems:

/* DROP problem */
create table src1 (a int) select 1 a;
create table src2 (a int) select 2 a;
create table merge1 (a int) ENGINE='MERGE' UNION (src1, src2);

select * from merge1;

drop table src1;

select * from merge1;

flush tables;

select * from merge1;

drop table src2;
drop table merge1;

/* RENAME problem */
create table src1 (a int) select 1 a;
create table src2 (a int) select 2 a;
create table merge1 (a int) ENGINE='MERGE' UNION (src1, src2);

select * from merge1;

 create table src3 (a int) select 3 a;
 rename table src1 to src4, src3 to src1;

select * from merge1;

select * from  src1;

flush tables;

select * from merge1;

select * from  src1;

drop table src1;
drop table src2;
drop table src4;
drop table merge1;

I was able to find a sequence tht reproduces error 127 on freebsd and linux.

create table src1 (a int) select 1 a;
create table src2 (a int) select 2 a;
create table src3 (a int) select 3 a;
create table merge1 (a int) ENGINE='MERGE' UNION (src1, src2) insert_method=last;
select * from merge1;
rename table src2 to src4, src3 to src2;
insert into merge1 select 9;
select * from src2;
#ERROR 1030 (HY000): Got error 127 from storage engine
select * from src4;
drop table merge1;
drop table src1;
drop table src2;
drop table src4;

Part of this is still present in 4.1.13. Except of normal table locks, MERGE sub-tables are not visible to MySQL. In this bug report this makes DROP and RENAME behave wrongly.

Solved is the FLUSH TABLES problem. After FLUSH TABLES, the system works as expected.

Solved is the table crash problem. The insert fails (with a weird error messages). No table crashes.

When a table is dropped on Unix-like systems, the filename is deleted but the actual file doesn't go away until the last file handle on it is closed. So it makes sense that an active MERGE table still can "see" data from a dropped table as it has an open file handle, although, not intuitive.
Perhaps if MERGE tables are not cached in the open-table cache, it would behave more as people would expect.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7124

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/.

It is a documented limitation.
See http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

* You cannot use DROP 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 ensure that no MERGE tables remain open by issuing a FLUSH TABLES statement prior to performing any of those operations.