MySQL Bugs: #2514: TRUNCATE table with merge corupts table

Bug #2514	TRUNCATE table with merge corupts table
Submitted:	26 Jan 2004 8:40	Modified:	29 Sep 2008 21:32
Reporter:	Matt Ryan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (w2k)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
When a table below a merge is truncated, that table index is corupted, record count is 10 should be 20 (really should be 10!)

Switching to delete from table works fine.

Errors vary.

Sample below sets up the test

CREATE TABLE `test1` (
  `c1` char(4) NOT NULL default '',
  `c2` char(6) NOT NULL default '',
  `c3` char(3) NOT NULL default '',
  `c4` char(2) NOT NULL default '',
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) TYPE=MyISAM;
CREATE TABLE `test2` (
  `c1` char(4) NOT NULL default '',
  `c2` char(6) NOT NULL default '',
  `c3` char(3) NOT NULL default '',
  `c4` char(2) NOT NULL default '',
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) TYPE=MyISAM;
CREATE TABLE `test3` (
  `c1` char(4) NOT NULL default '',
  `c2` char(6) NOT NULL default '',
  `c3` char(3) NOT NULL default '',
  `c4` char(2) NOT NULL default '',
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) type=MRG_MyISAM UNION=(test1, test2);

CREATE TABLE `test_import` (
  `c1` char(4) NOT NULL default '',
  `c2` char(6) NOT NULL default '',
  `c3` char(3) NOT NULL default '',
  `c4` char(2) NOT NULL default '',
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) TYPE=MyISAM;

INSERT INTO test_IMPORT VALUES 
('S570','W45B93','','WA'),('S570','W45B91','','WA'),('S570','W45B91','','WB'),('S570','W45B93','','WB'),('S570','W45B95','','WA'),('S570','W45B95','','WB'),('S570','W45B97','','WA'),('S570','W45B97','','WB'),('S570','W45B97','','WC'),('S570','W45B98','','WA');

How to repeat:
truncate table test1;

insert into test1 select * from test_import;

SELECT * FROM test3;

check table test1;

truncate table test1;

insert into test1 select * from test_import;

SELECT * FROM TEST3;

check table test1;  

Suggested fix:
Unknown, fails every time after the 2nd truncate & insert.

If you never query the "merge" table, the parent table is not corupted, appears to be a problem with the table merge.

Verified in Linux/Windows.  Corruption does not occur if you FLUSH TABLES after querying the merge table (prior to the TRUNCATE).

This is a documented deficiency:

http://www.mysql.com/doc/en/MERGE.html

"
You can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open". If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain "open".
"

Unfortunately it cannot be fixed until MySQL will have a new table handling code (now in ToDo)

This bug has been fixed in 5.1 I believe. There is also a completely new table cache code in 6.0. Needs to be re-verified.

I couldn't repeat with 5.1/6.0 Servers.