Bug #30275 Merge tables: flush tables or unlock tables causes server to crash
Submitted: 7 Aug 2007 14:07 Modified: 3 Dec 2007 9:34
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.0.48BK, 4.1, 5.1.21BK OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any

[7 Aug 2007 14:07] Shane Bester
Description:
On linux:

0x81bf8a1 handle_segfault + 529
0xffffe410 _end + -142091488
0x400f6b75 _end + 932668037
0x84f8a50 safe_mutex_lock + 290
0x8203f8c _Z15wait_for_tablesP3THD + 104
0x81b8b61 _Z17mysql_lock_tablesP3THDPP8st_tablejjPb + 577
0x8203cbb _Z13reopen_tablesP3THDbb + 513
0x8200452 _Z19close_cached_tablesP3THDbP10TABLE_LIST + 668
0x81e1456 _Z20reload_acl_and_cacheP3THDmP10TABLE_LISTPb + 678
0x81db56f _Z21mysql_execute_commandP3THD + 18655
0x81df9f5 _Z11mysql_parseP3THDPKcjPS2_ + 363
0x81d57b1 _Z16dispatch_command19enum_server_commandP3THDPcj + 2007
0x81d4fcf _Z10do_commandP3THD + 529
0x81d40be handle_one_connection + 874

On Windows:

ntdll.dll!RtlEnterCriticalSection()  + 0x19 bytes	
mysqld-debug.exe!thr_unlock  Line 746
mysqld-debug.exe!thr_multi_unlock Line 1006 
mysqld-debug.exe!mysql_unlock_tables Line 273 
mysqld-debug.exe!close_thread_tables Line 566 
mysqld-debug.exe!unlock_locked_tables Line 121 
mysqld-debug.exe!mysql_execute_command Line 3828 
mysqld-debug.exe!mysql_parse Line 6084 
mysqld-debug.exe!dispatch_command Line 1812 
mysqld-debug.exe!do_command Line 1586 
mysqld-debug.exe!handle_one_connection Line 1197 
mysqld-debug.exe!pthread_start Line 62 
mysqld-debug.exe!_callthreadstart  Line 293 
mysqld-debug.exe!_threadstart Line 277 

How to repeat:
#startup debug server.
#open mysql command line prompt.
#paste this:

use test;
flush tables;
drop table if exists t1;
drop table if exists m1;
create table t1(id int,key(id));
create table m1(id int,key(id))union=(t1) insert_method=first engine=mrg_myisam;
lock table t1 write,m1 write;
flush tables m1,t1;
optimize table t1;
flush tables t1;
unlock tables;
\r
use test
flush tables;
drop table if exists t1;
drop table if exists m1;
create table t1(id int,key(id));
create table m1(id int,key(id))union=(t1) insert_method=first engine=mrg_myisam;
lock table t1 write,m1 write;
flush tables m1,t1;
optimize table t1;
flush tables t1;
unlock tables;
\r
use test
flush tables;
drop table if exists t1;
drop table if exists m1;
create table t1(id int,key(id));
create table m1(id int,key(id))union=(t1) insert_method=first engine=mrg_myisam;
lock table t1 write,m1 write;
flush tables m1,t1;
optimize table t1;
flush tables t1;
unlock tables;
\r
use test;
flush tables;
drop table if exists t1;
drop table if exists m1;
create table t1(id int,key(id));
create table m1(id int,key(id))union=(t1) insert_method=first engine=mrg_myisam;
lock table t1 write,m1 write;
flush tables m1,t1;
optimize table t1;
flush tables t1;
unlock tables;
\r
use test;
flush tables;
drop table if exists t1;
drop table if exists m1;
create table t1(id int,key(id));
create table m1(id int,key(id))union=(t1) insert_method=first engine=mrg_myisam;
lock table t1 write,m1 write;
flush tables m1,t1;
optimize table t1;
flush tables t1;
unlock tables;
\r

Suggested fix:
.
[7 Aug 2007 14:14] MySQL Verification Team
release binary crashes sometimes, mostly it hangs so that it has to be kill -9'd
[21 Oct 2007 10:14] Ingo Strüwing
This is a bug in the server locking system.

Flush tables needs to unlock the locked table, close and reopen it, and finally lock it again. To prevent other threads from breaking in, LOCK_open is hold for the whole operation.

Before unlocking, the lock is aborted by upgrading the first write lock to TL_WRITE_ONLY. In this test case, it happens to be the lock on t1, which is implicitly taken by the merge table. Then the lock on the exlpicit instance of t1 is removed, but it is the second lock on t1 in the list, so the high level lock remains.

When re-locking t1, it collides with its own high level lock TL_WRITE_ONLY and tries to wait until this lock goes away. It uses the function wait_for_tables(), wich tries to acquire LOCK_open. But this is already held by the thread as mentioned above. So the erver crashes.

This bug belongs to the bunch of 8-9 MERGE bugs that will be fixed together. The progress is reported on Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table.
[21 Oct 2007 14:01] Ingo Strüwing
Please exchange "high level lock" by "high priority lock" in the explanation above.
[16 Nov 2007 8:09] Ingo Strüwing
For the patch see Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table.
[28 Nov 2007 10:36] Ingo Strüwing
For documentation see Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table.
[3 Dec 2007 9:34] MC Brown
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.