MySQL Bugs: #25038: Waiting TRUNCATE

Bug #25038	Waiting TRUNCATE
Submitted:	13 Dec 2006 13:23	Modified:	3 Dec 2007 10:33
Reporter:	Nick Kostirya
Status:	Closed
Category:	Server: Merge	Severity:	S2 (Serious)
Version:	4.1.22, 5.0 BK, 5.1 BK	OS:	Any (Linix)
Assigned to:	Ingo Strüwing	Target Version:	5.1.23

Description:
Having upgraded from MySQL version 4.0.x to 4.1.20, I have found one
more strange thing (or is it a bug?):

Say, we have MERGE table "C" that is created from tables "A" and "B". If you
try to make "TRUNCATE TABLE A" at the moment when a query to table "C"
is performed, the following error occurs:

 MyISAM table 'A' is in use (most likely by a MERGE table). Try FLUSH TABLES.

I.e. the "TRUNCATE TABLE A" command doesn't wait for completion of the query to table
"C".

Such errors didn't occur in MySQL version 4.0.x.

Any ideas?

How to repeat:
See description.

Thank you for a problem report. Please, try to repeat with a newer version, 4.1.22, and
inform about the results.

Yes, the error repeats with the 4.1.22. version also. 
Specification: the table can’t be cleared even after the request is completed, until
“Flush Tables” is performed. 

But the two simultaneous requests, “ FLUSH TABLES; TRUNCATE TABLE foo;” can cause the
“race”.

It would be more preferrable if the server wait for the table to get not used, then close
the table (FLUSH) and clear the table after TRUNCATE.

Bug #19627 may be related to this one

test case

Attachment: bug25038.test (application/octet-stream, text), 1.02 KiB.

Thank you for the report.

Verified on Linux using last BK sources and attached test case. All versions are affected.

This problem will be fixed if the current patch for Bug#26379 (Combination of
FLUSH TABLE and REPAIR TABLE corrupts a MERGE table) gets approved and pushed.

For the patch see Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE
table.

For documentation see Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a
MERGE table.

A note has been added to the 5.1.23 and 6.0.4 changelogs: 

Important Change: Incompatible Change: A number of problems existed in the implementation
of MERGE tables that could cause problems. The problems are summarized below:

Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table. This was
caused in a number of situations:

A thread trying to lock a MERGE table performs busy waiting while REPAIR TABLE or a
similar table administration task is ongoing on one or more of its MyISAM tables.

A thread trying to lock a MERGE table performs busy waiting until all threads that did
REPAIR TABLE or similar table administration tasks on one or more of its MyISAM tables in
LOCK TABLES segments do UNLOCK TABLES. The difference against problem #1 is that the busy
waiting takes place after the administration task. It is terminated by UNLOCK TABLES
only.

Two FLUSH TABLES within a LOCK TABLES segment can invalidate the lock. This does not
require a MERGE table. The first FLUSH TABLES can be replaced by any statement that
requires other threads to reopen the table. In 5.0 and 5.1 a single FLUSH TABLES can
provoke the problem.

Bug#26867 - Simultaneously executing LOCK TABLES and REPAIR TABLE on a MERGE table would
result in memory/cpu hogging.

Trying DML on a MERGE table, which has a child locked and repaired by another thread, made
an infinite loop in the server.

Bug#26377 - Deadlock with MERGE and FLUSH TABLE

Locking a MERGE table and its children in parent-child order and flushing the child
deadlocked the server.

Bug#25038 - Waiting TRUNCATE

Truncating a MERGE child, while the MERGE table was in use, let the truncate fail instead
of waiting for the table to become free.

Bug#25700 - MERGE base tables get corrupted by OPTIMIZE/ANALYZE/REPAIR TABLE

Repairing a child of an open MERGE table corrupted the child. It was necessary to FLUSH
the child first.

Bug#30275 - MERGE tables: FLUSH TABLES or UNLOCK TABLES causes server to crash.

Flushing and optimizing locked MERGE children crashed the server.

Bug#19627 - temporary merge table locking

Use of a temporary MERGE table with non-temporary children could corrupt the children.

Temporary tables are never locked. Creation of tables with non-temporary chidlren of a
temporary MERGE table is now prohibited.

Bug#27660 - Falcon: MERGE table possible

It was possible to create a MERGE table with non-MyISAM children.

Bug#30273 - MERGE tables: Can't lock file (errno: 155)

This was a Windows-only bug. Table administration statements sometimes failed with "Can't
lock file (errno: 155)".

The fix introduces the following changes in behavior:

This patch changes the behavior of temporary MERGE tables. Temporary MERGE must have
temporary children. The old behavior was wrong. A temporary table is not locked. Hence
even non-temporary children were not locked. See Bug#19627.

You cannot change the union list of a non-temporary MERGE table when LOCK TABLES is in
effect. The following does not work:

CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...;
      LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE;
      ALTER TABLE m1 ... UNION=(t1,t2) ...;
However, you can do this with a temporary MERGE table.

You cannot create a MERGE table with CREATE ... SELECT, neither as a temporary MERGE
table, nor as a non-temporary MERGE table. For example:

CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
Gives error message: table is not BASE TABLE.