Description:
When a Temporary Merge table is created based on non-temporary MyISAM tables, some locking is not enforced properly.

If the underlying MyISAM table is Write locked, it is still possible to insert into the temporary Merge table. After the write lock is released the inserted rows is gone.

The steps uses 2 separate connection, with 1 connection have a temporary Merger table created and records are inserted while the underlying MyISAM table has been Write locked by another connection. No error and no blocking, insert success, but records lost later on after the Wrtie lock is released from the other connection.

How to repeat:
=================
From 1st terminal
=================

mysql> create table a1 (i int) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> create table a2 (i int) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> create temporary table tm (i int) engine=merge union=(a1,a2) insert_method=first;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tm value (10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a1;
+------+
| i    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)

mysql> select * from tm;
+------+
| i    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)

=================
From 2nd terminal
=================

mysql> select * from a1;
+------+
| i    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)

mysql> lock table a1 write;
Query OK, 0 rows affected (0.00 sec)

=================
From 1st terminal
=================

mysql> insert into tm value (20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tm;
+------+
| i    |
+------+
|    1 |
|   10 |
|   20 |
+------+
3 rows in set (0.00 sec)

mysql> insert into tm value (20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tm;
+------+
| i    |
+------+
|    1 |
|   10 |
|   20 |
+------+
3 rows in set (0.00 sec)

=================
From 2nd terminal
=================
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a1;
+------+
| i    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)

=================
From 1st terminal
=================

mysql> select * from tm;
+------+
| i    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)

Suggested fix:
The insert into the temporary Merge table should block when the underlying MyISAM table is wrtie locked.

Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -uroot db1 --prompt="mysql-1>"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql-1>create table a1 (i int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql-1>create table a2 (i int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql-1>create temporary table tm (i int) engine=merge union=(a1,a2)
    -> insert_method=first;
Query OK, 0 rows affected (0.00 sec)

mysql-1>insert into a1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql-1>insert into tm value (10);
Query OK, 1 row affected (0.00 sec)

mysql-1>select * from a1;
+------+
| i    |
+------+
|    1 | 
|   10 | 
+------+
2 rows in set (0.00 sec)

mysql-1>select * from tm;
+------+
| i    |
+------+
|    1 | 
|   10 | 
+------+
2 rows in set (0.00 sec)

mysql-2>select * from a1;
+------+
| i    |
+------+
|    1 | 
|   10 | 
+------+
2 rows in set (0.00 sec)

mysql-2>lock table a1 write;
Query OK, 0 rows affected (0.00 sec)

mysql-1>insert into tm value (20);
Query OK, 1 row affected (0.01 sec)

mysql-1>select * from tm;
+------+
| i    |
+------+
|    1 | 
|   10 | 
|   20 | 
+------+
3 rows in set (0.01 sec)

mysql-2>unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql-2>select * from a1;
+------+
| i    |
+------+
|    1 | 
|   10 | 
+------+
2 rows in set (0.01 sec)

mysql-1>insert into tm value (20);
Query OK, 1 row affected (0.01 sec)

mysql-1>select * from tm;
+------+
| i    |
+------+
|    1 | 
|   10 | 
+------+
2 rows in set (0.00 sec)

mysql-1>

this bug causes table corruption of underlying base tables:
after running the testcase, look at the state of the base table:

mysql> check table a1;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| test.a1 | check | warning  | Size of datafile is: 28       Should be: 14 |
| test.a1 | check | error    | Record-count is not ok; is 4   Should be: 2 |
| test.a1 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> check table a2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.a2 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.00 sec)

We do never lock temporary tables. Hence there is no chance to lock a child table of a temporary MERGE table.

I will fix it by rejecting to open non-temporary tables as children of a temporary MERGE table. A prerequisite for this is the fix for Bug#26379.

This will be a new restriction for MERGE tables and must be documented.

Temporary MERGE tables would work well with non-temporary children if the users could stay away from tampering with the children while working with the parent. MERGE is a great feature, implemented to help people with a couple of problems. But everyone seems to expect that a database feature must not have drawbacks. One day we might need to remove the MERGE feature altogether to get rid of a plethora of complaints that it is not perfect. More likely, however, we will reduce its capabilities below usefulness.

Another thing worth noting is when we create temporary merge table, it is opened immediately. That means when temporary merge table is being created, it requires underlying tables to be present and have proper structure (in contrast to non-temporary tables, that do not perform any checks when a merge table is being created).

In other words it is not possible to create temporary merge table if underlying tables do not exist or differently defined.

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.