MySQL Bugs: #26379: Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table

Bug #26379	Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
Submitted:	14 Feb 2007 19:59	Modified:	3 Dec 2007 9:33
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S1 (Critical)
Version:	4.0 4.1 5.0 5.1	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	bfsm_2007_03_01

Description:
A combination of factors result in a corrupted table:

1) MERGE table
2) LOCK TABLE, REPAIR TABLE, FLUSH TABLE involving the merge/base tables
3) INSERTs from multiple threads into the merge table

Bug 25700 is a request to address known behavior.  I am reporting unknown behavior, hence the new bug report.

Worse, in this sequence, an INSERT is allowed to complete to a table which should have remained locked throughout the entire operation.

It seems to be the combination of a flush and repair that triggers it.

I have seen instances where the INSERT completes even without REPAIR making any changes (described below).  This occurs even if concurrent inserts are disabled.

How to repeat:
Connection 1:
DROP TABLE IF EXISTS m1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20)) ENGINE=MyISAM;
CREATE TABLE m1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20)) ENGINE=MERGE UNION=(t1) INSERT_METHOD=LAST;
LOCK TABLES m1 WRITE, t1 WRITE;

Connection 2:
INSERT INTO m1 VALUES (NULL, RAND());

Connection 3:
INSERT INTO m1 VALUES (NULL, RAND());

Connection 1:
FLUSH TABLES;
REPAIR TABLE t1;
repeat these two statements, eventually the repair will have changed something, although *no work* is occurring against the table.

At the point that the REPAIR actually does something, either connection 2 or 3 will complete its insert, while the other connection correctly remains locked.

I can only repeat it, so far, if there are at least two threads trying to insert into the merge table.

Here is a log:

Connection 1:

mysql> DROP TABLE IF EXISTS m1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE m1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20)) ENGINE=MERGE UNION=(t1) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.01 sec)

mysql> LOCK TABLES m1 WRITE, t1 WRITE;
Query OK, 0 rows affected (0.00 sec)

Connection 2:
INSERT INTO m1 VALUES (NULL, RAND());
(blocked)

Connection 3:
INSERT INTO m1 VALUES (NULL, RAND());
(blocked)

Connection 1:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> REPAIR TABLE t1;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status   | OK       | 
+---------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.01 sec)

mysql> REPAIR TABLE t1;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status   | OK       | 
+---------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> REPAIR TABLE t1;
+---------+--------+----------+------------------------------------+
| Table   | Op     | Msg_type | Msg_text                           |
+---------+--------+----------+------------------------------------+
| test.t1 | repair | warning  | Number of rows changed from 1 to 0 | 
| test.t1 | repair | status   | OK                                 | 
+---------+--------+----------+------------------------------------+
2 rows in set (0.00 sec)

Connection 3, at this point, unblocks.

Query OK, 1 row affected (25.80 sec)  

Connection 1:

UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec) 

Connection 2 unblocks:

Query OK, 1 row affected (1 min 32.62 sec)

This bug report revealed three problems:

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

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

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

Except of #2 it is not easy to repeat the problem or to watch it. One
can repeat #1 easily with a some seconds taking repair. But how will you
tell if the CPU load comes from the repair or the mentioned problem?

Repeating #3 is even more difficult as it requires the (second) FLUSH
TABLES to run at a certain moment in the execution of another thread. In
the original test case this was possible by exploiting problem #2 and
driving the other thread into a busy waiting. It passed the critical
code region so often that the probability to issue FLUSH TABLES in the
right moment was quite good. However this is still not a 100% repeatable
test as the number of FLUSH TABLES until the problem triggers is random.

Fortunately I found places in the code where the insertion of sleep()
made the problems 100% repeatable. For #1 and #2 use a system monitor to
watch the CPU load. Modify the sleep times to see that the CPU is busy
for these times.

1. To allow for observability it is necessary to enter a multi-second
   sleep in mysql_admin_table() after remove_table_from_cache(), which
   comes after mysql_abort_lock(). The sleep fakes a long running
   operation. One can watch a high CPU load during the sleep time.

   The problem is that mysql_abort_lock() upgrades the write lock to
   TL_WRITE_ONLY. This lock type persists until the final unlock at the
   end of the administration task. The effect of TL_WRITE_ONLY is to
   reject any attempt to lock the table. The trying thread must close
   the table and wait until it is no longer used. Unfortunately there is
   no way to detect that one of the MyISAM tables of a MERGE table is in
   use. When trying to lock the MERGE table, all MyISAM tables are
   locked. If one fails on TL_WRITE_ONLY, all locks are aborted and
   wait_for_tables() is entered. But this doesn't see the MERGE table as
   used, so it seems appropriate to retry a lock...

CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION=(t1);
send REPAIR TABLE t1;
    connection con1;
    INSERT INTO t2 VALUES (1);
connection default;
reap;
DROP TABLE t1, t2;

2. No sleep() in the code required.

   The problem is the same as #1, but TL_READ_ONLY persists beyond the
   end of the administration task until UNLOCK TABLES.

CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION=(t1);
LOCK TABLE t1 WRITE;
send REPAIR TABLE t1;
    connection con1;
    send INSERT INTO t2 VALUES (1);
connection default;
reap;
sleep 4; # con1 performs busy waiting during this sleep.
UNLOCK TABLES;
    connection con1;
    reap;
connection default;
DROP TABLE t1, t2;

3. To increase reproducibility it is necessary to enter a sleep of 2 seconds
   at the end of wait_for_tables() after unlock of LOCK_open. In 5.0 and 5.1
   the sleep must be inserted in open_and_lock_tables() after open_tables()
   instead. wait_for_tables() is not used in this case.

   The problem is that FLUSH TABLES releases LOCK_open while having
   unlocked and closed all tables. When this happens while a thread is
   in the loop in mysql_lock_tables() right after wait_for_tables() and
   before retrying to lock, the thread got the lock. (Translate to
   similar code places in 5.0 and 5.1). And it does not notice that the
   table needs a refresh. So it executes its statement on the table. The
   first FLUSH TABLES kicks the INSERT out of thr_multi_lock() and let
   it wait in wait_for_tables(). (open_table() in 5.0 and 5.1). The
   second FLUSH TABLES must happen while the INSERT was on its way from
   wait_for_tables() (open_tables() in 5.0 and 5.1) to the next call of
   thr_multi_lock(). This needs to be supported by a sleep to make it
   repeatable.

CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
LOCK TABLE t1 WRITE;
    connection con1;
    send INSERT INTO t1 VALUES (1);
    sleep 1; # Let INSERT go into thr_multi_lock().
connection default;
FLUSH TABLES;
sleep 1; # Let INSERT go through wait_for_tables() where it sleeps.
FLUSH TABLES;
# This should give no result. But it will with sleep(2) at the right place.
SELECT * FROM t1;
UNLOCK TABLES;
    connection con1;
    reap;
connection default;
DROP TABLE t1;

The fix for #3 seems to be simple. I do not expect it to raise new
problems. I propose to set THD::some_tables_deleted for all threads with
open tables before releasing LOCK_open for the first time. This will not
prevent other threads from getting locks on the tables that should be
under control of LOCK TABLES, but they will detect
THD::some_tables_deleted after locking and immediately release the locks
again. They will close all tables and detect the changed
refresh_version.

The fix for #1 and #2 is also simple. But it has the potential to break
things at other places. Currently administration tasks like REPAIR TABLE
abort locks on the table before the operation starts. A comment in
mysql_admin_table() says "Close all instances of the table to allow
repair to rename files". If this comment is correct, it means that it is
expected to raise problems when trying to rename files that are open. So
we abort the locks so that no thread can sit and wait for a lock on the
table. Tables are opened before locking. A thread that waits for a lock
will keep the table and its files open all the time. If the lock fails
as aborted, it will close the table and reopen it only after it sees the
table as unused. BUT: Aborting every lock request does not prevent
threads from opening the table before trying to lock it. So there are
moments where the table can be open by other threads in spite of
aborting all locks on the table. Either we get around this by retrying
the rename until we meet a moment where the table is closed, or we
assume that renaming blocks until the file is closed or we live with the
risk that the rename can fail in spite of lock aborting. However, I did
not find any repetition of renaming in the code. Blocking on rename is
probably highly platform dpendend, but perhaps we have it where
necessary.

If we do indeed have that rename problem on platforms relevant for
MySQL, then there might be no solution for problem #1. But as far as I
know we have methods for overcoming the rename/delete restriction at
least in 5.0 and 5.1. We have my_delete_allow_opened(), which is the
base for other functions around replacing old table files by new ones.
It could be backported to 4.1 or we could forgo fixing #1 in 4.1. It can
make table administration operations slower, but it does not hurt data.

I propose to move the branch with mysql_lock_abort and
remove_table_from_cache behind the administration operation. Immediately
after this we should reset the lock type of TL_WRITE_ONLY back to a
normal TL_WRITE. This should happen still within LOCK_open so that there
is no chance for busy waiting even for a short moment. This would fix #1
and #2.

If we cannot make the move in 4.1, then I propose to reset the lock type
of TL_WRITE_ONLY back to a normal TL_WRITE after the administration
operation. This would at least fix #2.

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/23668

ChangeSet@1.2620, 2007-04-03 14:35:50+02:00, istruewing@chilla.local +10 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  This bug report revealed three problems:
  
  1. 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.
  
  2. 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.
  
  3. 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.
  
  Problem #1 and #2 are fixed by moving abortion of locks behind
  the table administration operation and undoing the lock abort
  immediately after all threads have closed the table.
  
  Problem #3 is fixed by setting THD::some_tables_deleted for all
  threads with open tables before releasing LOCK_open when waiting
  for all threads to close their tables.
  
  No test case. The misbehaviour cannot be repeated reliably by a
  deterministic test without sleep()-injection in the code.
  
  Please see the bug report for more information and tests for
  manual testing.

The patch was rejected by Monty.

It turned out that I was wrong in thinking that a table can be opened by other threads after remove_table_from_cache. The table version is set to zero, which prevents a new open. Just MERGE tables bypass this check. The correct fix is to extend MERGE table open so that it can check the table cache entry of all its MyISAM tables.

I believe that the current solution for problem #1 and #2 is not a desired one.

The reason is that many of the admin commands, like repair, check, assumes that the admin thread is the only one who is accessing the table so that it can freely manipulate the table files and objects (on disk and in memory).  The proposed patch would change this with probably drastic consequences.

Another problem is that the patch doesn't solve the problem with MERGE tables, only makes it less likely to happen; Ie, we can still have a REPAIR and a open   of a MyISAM table (through a MERGE table) happening at the same time.  If this happens at the same time REPAIR is creating new files or updating the MyISAM share, we can get strange effects.

A better solution, which is also in line with the long time view of MERGE tables, is to add a callback function to MERGE table open, so that we can do checks between the opened tables and tables in the table cache and have the open fail if there is any conflicts (like too old table->version).

Ingo and I have talked about this and he will try this solution.

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/25671

ChangeSet@1.2630, 2007-04-28 14:31:55+02:00, istruewing@chilla.local +22 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts
              a MERGE table
  
  Not to be pushed. This is kind of a technology preview.
  It is not yet complete. Though it does not seem to make
  anything really worse, it turned out that at least another
  step is required: When closing and opening the MERGE child
  tables, it must be taken into account if a real open/close
  or a reopen/close_old_data_files is going on. In the latter
  case the child tables must not be deleted, but kept for
  reopen. In reopen they must be picked up again.
  
  This bug report revealed three problems:
  
  1. 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.
  
  2. 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.
  
  3. 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.
  
  Problem #1 and #2 are fixed by opening the MERGE child tables
  through the table cache. So they are visible to other threads
  and even for certain statements in the same thread. The child
  tables are chained to the parent table, but do not appear in
  thd->open_tables nor thd->locked_tables. This does also fix
  the bugs 26377, 26867, and 8306 at least. I reverted the
  preliminary fix for 8306.
  
  Problem #3 is fixed by setting THD::some_tables_deleted for all
  threads with open tables before releasing LOCK_open when waiting
  for all threads to close their tables.
  
  No test case. The misbehaviour cannot be repeated reliably by a
  deterministic test without sleep()-injection in the code.
  
  Please see the bug report for more information and tests for
  manual testing.

Same here:
I have a merge table that contains about 25 sub-tables. Each contains about half a million entries. In general this works great.
However today I detected that if I do a repair on one of the 25 sub-tables while someone else is doing a select on the main merge table the repair fails with status code 13. And all the data in the corresponding sub-table are lost. (the MYD-file is missing and there is a 0 byte TMD file) Subsequent repairs to this table fails with the message File '.\dbname\tablename.MYD' not found (Errcode: 2)

Subsequent select queries on the merge table gives the following error message:
ERROR 144 (HY000): Table '.\dbname\tablename' is marked as crashed and
last (automatic?) repair failed

For me this is reproducible and only happens if I make a select query on the merge table while repairing. Tested against mysql 5.0.37 on windows 2003 server.

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/27080

ChangeSet@1.2630, 2007-05-21 17:48:22+02:00, istruewing@chilla.local +23 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts
              a MERGE table
  
  This bug report revealed three problems:
  
  1. 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.
  
  2. 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.
  
  3. 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.
  
  Problem #1 and #2 are fixed by opening the MERGE child tables
  through the table cache. So they are visible to other threads
  and even for certain statements in the same thread. The child
  tables are chained to the parent table, but do not appear in
  thd->open_tables nor thd->locked_tables. This does also fix
  the bugs 8306, 25038, 25700, 26377, 26867, and 27660.
  I reverted the preliminary fix for 8306.
  
  Problem #3 is fixed by setting THD::some_tables_deleted for all
  threads with open tables before releasing LOCK_open when waiting
  for all threads to close their tables.
  
  The misbehaviour for problems #1 and #3 can only be repeated
  reliably with some sleep()-injection in the code. Problem #2
  can be watched by enlarging the sleep time in the test file.
  
  Please see the bug report for more information and tests for
  manual testing. See bug 25700 for a stress test.

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/30888

ChangeSet@1.2530, 2007-07-13 17:58:57+02:00, istruewing@chilla.local +9 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed. This is a partial implementation of a new
  MERGE table open approach. For intermediate review only.
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables().
  Nothing else is done with the MERGE table at this stage.
  I.e. no storage engine object is created yet.
  When the last child is opened, we remove the children from the
  list again and finalize the MERGE table open. This behaves 
  basically like the old open. However it does not open the MyISAM
  tables directly, but grabs them from the already open children.

Back "in progress". The patch is just an intermediate study.

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/32431

ChangeSet@1.2585, 2007-08-13 09:49:54+02:00, istruewing@synthia.local +11 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed. This is a partial implementation of a new
  MERGE table open approach. For intermediate review only.
  It replaces/supersedes the former patch entirely.
  Changed/added test cases not included for easier review.
  
  Changes over the former patch:
  
  - Renamed "finalize open (with cannot use option)" into "attach/detach children".
  
  - On parent open the storage engine structures are allocated and initialized.
    They stay with the open table until its final close.
  
  - Table layout conversion (table2myisam) is done on first attach only. The result
    is stored in the handler object for reuse with later attaches.
  
  - Added detach functionality to close_thread_table().
  
  - The patch survives the whole test suite + bug25038 + bug26379 + bug26867 + bug27660,
    except of the few places where a MERGE table is opened with open_ltable() or
    reopen_table(). Test cases: delayed (open_ltable), merge (ALTER/open_ltable),
    bug25700 (reopen_table). As suggested these are to be fixed in a second patch.
    myisam passes after reverting/fixing test and result for bug8306.
    However the test for bug26377 revealed an unexpected problem: FLUSH t1 after
    LOCK t1, m1(t1). The child is closed, but the parent doesn't notice it.
    It won't re-attach its children as long as LOCK TABLES is in effect.
  
  General patch description:
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables().
  The children are not opened at this stage.
  When the last child is opened, we remove the children from the
  list again and attach the children to the parent. This behaves 
  similar to the old open. However it does not open the MyISAM
  tables directly, but grabs them from the already open children.
  When closing a MERGE table in close_thread_table() we detach the
  children only. Closing of the children is done implicitly because
  they are in thd->open_tables.

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/34133

ChangeSet@1.2609, 2007-09-12 22:11:47+02:00, istruewing@stella.local +20 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed. This is a partial implementation of a new
  MERGE table open approach. For intermediate review only.
  
  This is part 2 (open_ltable(), reopen_tables()). I did not change
  reopen_name_locked_table(). It works similar to reopen_table(),
  but does not copy many important elements from the old table.
  It is unclear which MERGE relevant elements need to be copied.
  The only test I found for it showed that there was something to
  implement for CREATE...SELECT. So I put it into
  create_table_from_items() behind reopen_name_locked_table().
  
  Fixes of first patch became necessary because the part 2
  changes allowed more tests to run, which revealed new problems:
  
  - Added elements 'parent' and 'children_attached' to TABLE.
    'parent' is required to identify MERGE children when the
    TABLE_LIST object is no longer accessible (e.g. within a
    LOCK TABLES ... UNLOCK TABLES sequence). This is required
    to avoid tampering with children locks and to forward lock
    changes and table refreshs to the parent. Since all this is
    relevant in reopen situations, this change may indeed belong
    to this patch instead of part 1.
    'children_attached' enables the server to keep track of the
    MERGE table state. So it can avoid to perform inappropriate
    handler calls.
  - Temporary table handling is new.
  - Respect thd->open_options in attach. Cached MERGE parent may
    be used by other threads or thd->open_options change between
    statements.
  - Handle empty MERGE union by always have children_attached= true.
    This avoids detach/[re-]attach of non-existing children. The
    parent is usually detected by table->child_l, which is NULL
    for an empty union set. In effect the table is not treated as
    a MERGE table, but some handler methods must neverthless work
    and survive DBUG_ASSERT(children_attached) (e.g. ::info, ::close).
  
  General patch description:
  
  Changed from open_ltable() to open_and_lock_tables() in all places
  that can be relevant for MERGE tables. The latter can handle tables
  added to the list on the fly. When open_ltable() was used in a loop
  over a list of tables, the list must be temporarily terminated
  after every table for open_and_lock_tables().
  table_list->required_type is set to FRMTYPE_TABLE to avoid open of
  special tables. Handling of derived tables is suppressed.
  
  In reopen_tables() some of the tables open by a thread can be
  closed and reopened. When a MERGE child is affected, the parent
  must be closed and reopened too. Closing of the parent is forced
  before the first child is closed. Reopen happens in the order of
  thd->open_tables. MERGE parents do not attach their children
  automatically at open. This is done after all tables are reopened.
  So all children are open when attaching them.
  
  There are some more places where MERGE children need to be detached.
  
  Special lock handling like mysql_lock_abort() or mysql_lock_remove()
  need to be suppressed for MERGE children or forwarded to the parent.
  This depends on the situation. In loops over all open tables one
  suppresses child lock handling. When a single table is touched,
  forwarding is done.
  
  Bugs fixed with this patch (plus part 1):
  19627, 25038, 25700, 26377, 26379, 26867, 27660, 30275, and 30273.

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/34477

ChangeSet@1.2610, 2007-09-22 11:08:21+02:00, istruewing@synthia.local +3 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
  
  Not to be pushed. These are fixes on top of the second patch.
  They fix problems detected during first coarse review.

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/34520

ChangeSet@1.2610, 2007-09-24 15:07:25+02:00, istruewing@synthia.local +1 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
  
  Not to be pushed. Internal TEST only.
  
  Stupid "test" lines follow:
  
  The problem was missing dump layout
  
  I added dump layout
  
  No test case. layout is used during debugging only.

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/34923

ChangeSet@1.2610, 2007-10-04 20:38:09+02:00, istruewing@stella.local +3 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed. For intermediate review only.
  
  This is a technology preview for removing wait_for_tables()
  from mysql_lock_tables(). Done during my Heidelberg stay.
  
  All calls to mysql_lock_tables() do now use the flag
  MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN. This means that
  wait_for_tables() is not called any more.
  
  The next step would be to remove the flag and the call
  to wait_for_tables() and the function itself alltogether.
  It is not used elsewhere.
  
  Added MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN flag to mysql_lock_tables()
  and added restart code where necessary.

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/35507

ChangeSet@1.2569, 2007-10-13 12:03:50+02:00, istruewing@stella.local +25 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed. Internal commit. Please ignore.
  Contains more post-review fixes. Will commit a combined patch soon.
  
  Still to be done:
  - Work on review comments regarding the tests.
  - Test if tampering with a child from another thread can require
  to detach (and re-attach later in reopen_tables()) the children in
  close_handle_and_leave_table_as_lock().
  - Test if another thread can break into CREATE...MERGE...SELECT
  as I need to unlock the children in create_table_from_items().
  - Test ALTER TABLE (fast + copy) within LOCK TABLES to see if I
  need to re-attach the children after reopen_table().
  - Decide what value ::reset() should return if children are
  detached.
  
  Forgot to mention in last patch comment:
  Temporary MERGE must have temporary children.
  This is a new behavior. The old behavior was wrong. See
  Bug 19627 - temporary merge table locking.

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/35566

ChangeSet@1.2576, 2007-10-15 16:02:51+02:00, istruewing@stella.local +28 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  Bug 26867 - LOCK TABLES + REPAIR + merge table result in
              memory/cpu hogging
  Bug 26377 - Deadlock with MERGE and FLUSH TABLE
  Bug 25038 - Waiting TRUNCATE
  Bug 25700 - merge base tables get corrupted by
              optimize/analyze/repair table
  Bug 30275 - Merge tables: flush tables or unlock tables
              causes server to crash
  Bug 19627 - temporary merge table locking
  Bug 27660 - Falcon: merge table possible
  Bug 30273 - merge tables: Can't lock file (errno: 155)
  
  Not to be pushed. For intermediate review only.
  This is an implementation of a new MERGE table open approach.
  
  Still to be done:
  - Work on review comments regarding the tests.
  - Test if tampering with a child from another thread can require
    to detach (and re-attach later in reopen_tables()) the children in
    close_handle_and_leave_table_as_lock().
  - Test if another thread can break into CREATE...MERGE...SELECT
    as I need to unlock the children in create_table_from_items().
  - Test ALTER TABLE (fast + copy) within LOCK TABLES to see if I
    need to re-attach the children after reopen_table().
  - Decide what value ::reset() should return if children are
    detached.
  
  General patch description:
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables()
  (the "query_list"). The children are not opened in the handler at
  this stage.
  
  After opening the parent, open_tables() opens each child from the
  now extended query_list. When the last child is opened, we remove
  the children from the query_list again and attach the children to
  the parent. This behaves similar to the old open. However it does
  not open the MyISAM tables directly, but grabs them from the already
  open children.
  
  When closing a MERGE table in close_thread_table() we detach the
  children only. Closing of the children is done implicitly because
  they are in thd->open_tables.
  
  For more detail see the comment at the top of ha_myisammrg.cc.
  
  Changed from open_ltable() to open_and_lock_tables() in all places
  that can be relevant for MERGE tables. The latter can handle tables
  added to the list on the fly. When open_ltable() was used in a loop
  over a list of tables, the list must be temporarily terminated
  after every table for open_and_lock_tables().
  table_list->required_type is set to FRMTYPE_TABLE to avoid open of
  special tables. Handling of derived tables is suppressed.
  These details are handled by the new function
  open_n_lock_single_table(), which has nearly the same signature as
  open_ltable() and can replace it in most cases.
  
  In reopen_tables() some of the tables open by a thread can be
  closed and reopened. When a MERGE child is affected, the parent
  must be closed and reopened too. Closing of the parent is forced
  before the first child is closed. Reopen happens in the order of
  thd->open_tables. MERGE parents do not attach their children
  automatically at open. This is done after all tables are reopened.
  So all children are open when attaching them.
  
  Special lock handling like mysql_lock_abort() or mysql_lock_remove()
  needs to be suppressed for MERGE children or forwarded to the parent.
  This depends on the situation. In loops over all open tables one
  suppresses child lock handling. When a single table is touched,
  forwarding is done.
  
  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 - temporary merge table locking.

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/35994

ChangeSet@1.2576, 2007-10-21 17:16:52+02:00, istruewing@stella.local +31 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  Bug 26867 - LOCK TABLES + REPAIR + merge table result in
              memory/cpu hogging
  Bug 26377 - Deadlock with MERGE and FLUSH TABLE
  Bug 25038 - Waiting TRUNCATE
  Bug 25700 - merge base tables get corrupted by
              optimize/analyze/repair table
  Bug 30275 - Merge tables: flush tables or unlock tables
              causes server to crash
  Bug 19627 - temporary merge table locking
  Bug 27660 - Falcon: merge table possible
  Bug 30273 - merge tables: Can't lock file (errno: 155)
  
  Not to be pushed. For intermediate review only.
  This is an implementation of a new MERGE table open approach.
  This patch was committed to document the current state of work.
  
  Still to be done:
  - Work on review comments regarding the tests.
  - Decide what value ::reset() should return if children are detached.
  
  General patch description:
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables()
  (the "query_list"). The children are not opened in the handler at
  this stage.
  
  After opening the parent, open_tables() opens each child from the
  now extended query_list. When the last child is opened, we remove
  the children from the query_list again and attach the children to
  the parent. This behaves similar to the old open. However it does
  not open the MyISAM tables directly, but grabs them from the already
  open children.
  
  When closing a MERGE table in close_thread_table() we detach the
  children only. Closing of the children is done implicitly because
  they are in thd->open_tables.
  
  For more detail see the comment at the top of ha_myisammrg.cc.
  
  Changed from open_ltable() to open_and_lock_tables() in all places
  that can be relevant for MERGE tables. The latter can handle tables
  added to the list on the fly. When open_ltable() was used in a loop
  over a list of tables, the list must be temporarily terminated
  after every table for open_and_lock_tables().
  table_list->required_type is set to FRMTYPE_TABLE to avoid open of
  special tables. Handling of derived tables is suppressed.
  These details are handled by the new function
  open_n_lock_single_table(), which has nearly the same signature as
  open_ltable() and can replace it in most cases.
  
  In reopen_tables() some of the tables open by a thread can be
  closed and reopened. When a MERGE child is affected, the parent
  must be closed and reopened too. Closing of the parent is forced
  before the first child is closed. Reopen happens in the order of
  thd->open_tables. MERGE parents do not attach their children
  automatically at open. This is done after all tables are reopened.
  So all children are open when attaching them.
  
  Special lock handling like mysql_lock_abort() or mysql_lock_remove()
  needs to be suppressed for MERGE children or forwarded to the parent.
  This depends on the situation. In loops over all open tables one
  suppresses child lock handling. When a single table is touched,
  forwarding is done.
  
  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 - temporary merge table locking.

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/36004

ChangeSet@1.2577, 2007-10-22 09:17:16+02:00, istruewing@stella.local +2 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Forgot to include this new test case in the last commit.

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/37002

ChangeSet@1.2595, 2007-11-02 17:28:19+01:00, istruewing@stella.local +34 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  Bug 26867 - LOCK TABLES + REPAIR + merge table result in
              memory/cpu hogging
  Bug 26377 - Deadlock with MERGE and FLUSH TABLE
  Bug 25038 - Waiting TRUNCATE
  Bug 25700 - merge base tables get corrupted by
              optimize/analyze/repair table
  Bug 30275 - Merge tables: flush tables or unlock tables
              causes server to crash
  Bug 19627 - temporary merge table locking
  Bug 27660 - Falcon: merge table possible
  Bug 30273 - merge tables: Can't lock file (errno: 155)
  
  The problems were:
  
  Bug 26379 - Combination of FLUSH TABLE and REPAIR TABLE
                  corrupts a MERGE table
  
    1. 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.
    
    2. 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.
    
    3. 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 - LOCK TABLES + REPAIR + merge table 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. So we do now prohibit
    non-temporary chidlren of a temporary MERGE table.
  
  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)".
  
  These bugs are fixed by a new implementation of MERGE table open.
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables()
  (the "query_list"). The children are not opened in the handler at
  this stage.
  
  After opening the parent, open_tables() opens each child from the
  now extended query_list. When the last child is opened, we remove
  the children from the query_list again and attach the children to
  the parent. This behaves similar to the old open. However it does
  not open the MyISAM tables directly, but grabs them from the already
  open children.
  
  When closing a MERGE table in close_thread_table() we detach the
  children only. Closing of the children is done implicitly because
  they are in thd->open_tables.
  
  For more detail see the comment at the top of ha_myisammrg.cc.
  
  Changed from open_ltable() to open_and_lock_tables() in all places
  that can be relevant for MERGE tables. The latter can handle tables
  added to the list on the fly. When open_ltable() was used in a loop
  over a list of tables, the list must be temporarily terminated
  after every table for open_and_lock_tables().
  table_list->required_type is set to FRMTYPE_TABLE to avoid open of
  special tables. Handling of derived tables is suppressed.
  These details are handled by the new function
  open_n_lock_single_table(), which has nearly the same signature as
  open_ltable() and can replace it in most cases.
  
  In reopen_tables() some of the tables open by a thread can be
  closed and reopened. When a MERGE child is affected, the parent
  must be closed and reopened too. Closing of the parent is forced
  before the first child is closed. Reopen happens in the order of
  thd->open_tables. MERGE parents do not attach their children
  automatically at open. This is done after all tables are reopened.
  So all children are open when attaching them.
  
  Special lock handling like mysql_lock_abort() or mysql_lock_remove()
  needs to be suppressed for MERGE children or forwarded to the parent.
  This depends on the situation. In loops over all open tables one
  suppresses child lock handling. When a single table is touched,
  forwarding is done.
  
  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 - temporary merge table locking.

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/37766

ChangeSet@1.2596, 2007-11-14 19:37:36+01:00, istruewing@stella.local +16 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Not to be pushed as is.
  Post-review fixes.
  Intermediate patch for review purpose.
  
  Still open issue: Move temporary detach to
  mark_temp_tables_as_free_for_reuse(). This requires a current
  repository.
  
  Not contained in this patch: Reverted creation of new test case
  rpl_merge. It was for CREATE...SELECT only.

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/37833

ChangeSet@1.2617, 2007-11-15 10:13:02+01:00, istruewing@stella.local +2 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
    
  Not to be pushed as is.
  Final post-review fixes.
  Intermediate patch for review purpose.

Approved by email.

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/37883

ChangeSet@1.2619, 2007-11-15 20:25:43+01:00, istruewing@stella.local +35 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  Bug 26867 - LOCK TABLES + REPAIR + merge table result in
              memory/cpu hogging
  Bug 26377 - Deadlock with MERGE and FLUSH TABLE
  Bug 25038 - Waiting TRUNCATE
  Bug 25700 - merge base tables get corrupted by
              optimize/analyze/repair table
  Bug 30275 - Merge tables: flush tables or unlock tables
              causes server to crash
  Bug 19627 - temporary merge table locking
  Bug 27660 - Falcon: merge table possible
  Bug 30273 - merge tables: Can't lock file (errno: 155)
  
  The problems were:
  
  Bug 26379 - Combination of FLUSH TABLE and REPAIR TABLE
                  corrupts a MERGE table
  
    1. 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.
    
    2. 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.
    
    3. 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 - LOCK TABLES + REPAIR + merge table 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. So we do now prohibit
    non-temporary chidlren of a temporary MERGE table.
  
  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)".
  
  These bugs are fixed by a new implementation of MERGE table open.
  
  When opening a MERGE table in open_tables() we do now add the
  child tables to the list of tables to be opened by open_tables()
  (the "query_list"). The children are not opened in the handler at
  this stage.
  
  After opening the parent, open_tables() opens each child from the
  now extended query_list. When the last child is opened, we remove
  the children from the query_list again and attach the children to
  the parent. This behaves similar to the old open. However it does
  not open the MyISAM tables directly, but grabs them from the already
  open children.
  
  When closing a MERGE table in close_thread_table() we detach the
  children only. Closing of the children is done implicitly because
  they are in thd->open_tables.
  
  For more detail see the comment at the top of ha_myisammrg.cc.
  
  Changed from open_ltable() to open_and_lock_tables() in all places
  that can be relevant for MERGE tables. The latter can handle tables
  added to the list on the fly. When open_ltable() was used in a loop
  over a list of tables, the list must be temporarily terminated
  after every table for open_and_lock_tables().
  table_list->required_type is set to FRMTYPE_TABLE to avoid open of
  special tables. Handling of derived tables is suppressed.
  These details are handled by the new function
  open_n_lock_single_table(), which has nearly the same signature as
  open_ltable() and can replace it in most cases.
  
  In reopen_tables() some of the tables open by a thread can be
  closed and reopened. When a MERGE child is affected, the parent
  must be closed and reopened too. Closing of the parent is forced
  before the first child is closed. Reopen happens in the order of
  thd->open_tables. MERGE parents do not attach their children
  automatically at open. This is done after all tables are reopened.
  So all children are open when attaching them.
  
  Special lock handling like mysql_lock_abort() or mysql_lock_remove()
  needs to be suppressed for MERGE children or forwarded to the parent.
  This depends on the situation. In loops over all open tables one
  suppresses child lock handling. When a single table is touched,
  forwarding is done.
  
  Behavioral changes:
  ===================
  
  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 - temporary merge table locking.
  
  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.
  CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
  Gives error message: table is not BASE TABLE.

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/37887

ChangeSet@1.2668, 2007-11-15 21:19:05+01:00, istruewing@stella.local +2 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  Post-merge fix

Queued to 6.0-engines, 5.1-engines.

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/38012

ChangeSet@1.2620, 2007-11-18 20:28:37+01:00, istruewing@stella.local +3 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Post-pushbuild fix for a Valgrind warning.

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/38311

ChangeSet@1.2622, 2007-11-22 21:39:28+01:00, istruewing@stella.local +1 -0
  Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
              corrupts a MERGE table
  
  Post-pushbuild fix. The merge test failed on Windows.
  The MoveFile() function returned the error code
  ERROR_ACCESS_DENIED.
  
  The fix is to use a different name for the file to be
  deleted. This is the same trick as we use for the error
  code ERROR_ALREADY_EXISTS.
  
  Added ERROR_ACCESS_DENIED to the list of error codes that
  require to change the name of the file to be deleted.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

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.

Bug #30491 (q.v.) was also fixed with this bug.