MySQL Bugs: #27660: Falcon: merge table possible

Bug #27660	Falcon: merge table possible
Submitted:	4 Apr 2007 23:53	Modified:	3 Dec 2007 9:34
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.2.4-falcon-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
It's possible to create a MERGE table based on a Falcon table.
It's possible to select from it, if I've selected before.
The result of the select is wrong, and it shouldn't be possible.

How to repeat:
mysql> create table tb1 (s1 int, s2 int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tb2 (s1 int, s2 int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> create table tb3 (s1 int, s2 int) engine=merge union(tb1,tb2);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb3;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> alter table tb1 engine=falcon;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> select * from tb3; /* succeeds but (0,0) is missing */
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

I don't think this is doing exactly what it appears to be doing - as altering the underlying table does not cause the Merge table to refresh. So when you alter t1 to become a Falcon table and insert into it, the Merge is still looking at the now gone MyISAM table.

Thank you for the bug report. Verified as described.

As well as causing display of bad data, this causes
"mysqldump database_name" to stop prematurely, saying:

mysqldump: Couldn't execute 'show fields from `tb3`': Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist (1168)

The same happens in all versions when any available non-MyISAM engine is used. I tested it with MEMEORY.

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.