MySQL Bugs: #25921: Error with INSERT and temporary MERGE table

Bug #25921	Error with INSERT and temporary MERGE table
Submitted:	29 Jan 2007 16:08	Modified:	16 May 2007 16:02
Reporter:	Andrew Duffy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.22, 5.0.33, 5.1 BK	OS:	Windows (Windows, Solaris, Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a row or rows are inserted into a non-empty MyISAM table which is being queried via a temporary merge table the read query fails with an error referring to the table being crashed. The error message depends upon the server version as merge table errors were improved in 5.0.

How to repeat:
1. Create a MyISAM table with a simple structure:

CREATE TABLE test(a INTEGER, b INTEGER, c INTEGER) ENGINE=MYISAM;

2. Populate it with a very large number of rows. I'll leave this up to you; I created a file with a few hundred thousand rows using a script and LOAD DATAed it.

3. Create a temporary merge table with the test table as its only component:

CREATE TABLE test_merge LIKE test;
ALTER TABLE test_merge ENGINE=MERGE UNION=(test);

4. Issue a select that will take some time, i.e., SELECT SUM(a), SUM(b), SUM(c) FROM test_merge;

5. At the same time and in a seperate instance of the interpreter, insert a single row into the table:

INSERT INTO test VALUES(1,2,3);

6. Just when the SELECT statment should finish, you will get an error about the merge table being crashed.

I have also tested this with 5.0.27 and LOAD DATA CONCURRENT in place of the INSERT statement; despite LOAD DATA CONCURRENT being broken in that version (http://bugs.mysql.com/bug.php?id=20637) the error still happens. I haven't tested to see if the error occurs when the underlying table has empty rows.

Suggested fix:
Don't use temporary merge tables. However, when they are created on the fly the ~50ms required to create a non-temporary table can become irritating.

test

Attachment: bug25921.test (application/octet-stream, text), 643 bytes.

Thank you for the report.

Verified as described on Linux using last BK sources. All versions are affected

This is a duplicate of Bug#19627 (temporary merge table locking).