MySQL Bugs: #26464: insert delayed + update + merge = corruption

Bug #26464	insert delayed + update + merge = corruption
Submitted:	17 Feb 2007 18:25	Modified:	15 Mar 2007 2:54
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	4.1.21, 5.0.36BK, 5.1.16BK, 5.2.4BK	OS:	Any (*)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	bfsm_2007_03_01, corruption, merge

Description:
when doing some simple tests such as this:

create table base1, base2, base3...
create table merge1, merge2, merge3 (over the base tables in different order)

insert delayed into basex...
update basex set ...

corruption occurs almost instantly.

testcase output:
sbester@www:~> ./testcase 
running initializations..
pre-generating 16777216 bytes of random data
about to spawn 35 threads
.............................query failed 'update `t9` set `a`=md5(`a`)' : 1194 (Table 't9' is marked as crashed and should be repaired)
query failed 'update `t9` set `a`=md5(`a`)' : 1194 (Table 't9' is marked as crashed and should be repaired)
query failed 'update `t9` set `a`=md5(`a`)' : 1194 (Table 't9' is marked as crashed and should be repaired)
query failed 'update `t4` set `a`=md5(`a`)' : 126 (Incorrect key file for table './test/t4.MYI'; try to repair it)
query failed 'update `t4` set `a`=md5(`a`)' : 126 (Incorrect key file for table './test/t4.MYI'; try to repair it)
query failed 'update `t4` set `a`=md5(`a`)' : 126 (Incorrect key file for table './test/t4.MYI'; try to repair it)
query failed 'update `t4` set `a`=md5(`a`)' : 126 (Incorrect key file for table './test/t4.MYI'; try to repair it)
query failed 'update `t9` set `a`=md5(`a`)' : 1194 (Table 't9' is marked as crashed and should be repaired)

How to repeat:
compile and run the attached testcase.
change the host, user, password, etc at top of file.

if you get all threads in processlist hung up, shutdown mysqld and start over.
that's probably another bug.

Suggested fix:
don't cause corruption.

testcase

Attachment: bug26464.c (text/plain), 16.51 KiB.

after exiting the testcase, here's the state of affairs:

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> check table t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
+---------+-------+----------+---------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                      |
+---------+-------+----------+---------------------------------------------------------------+
| test.t1 | check | error    | Table './test/t1' is marked as crashed and should be repaired |
| test.t2 | check | error    | Table './test/t2' is marked as crashed and should be repaired |
| test.t3 | check | error    | Table './test/t3' is marked as crashed and should be repaired |
| test.t4 | check | error    | Table './test/t4' is marked as crashed and should be repaired |
| test.t5 | check | error    | Found 138 keys of 136                                         |
| test.t5 | check | error    | Corrupt                                                       |
| test.t6 | check | warning  | Found 0 deleted space in delete link chain. Should be 448     |
| test.t6 | check | error    | Found 0 deleted rows in delete link chain. Should be 9        |
| test.t6 | check | error    | record delete-link-chain corrupted                            |
| test.t6 | check | error    | Corrupt                                                       |
| test.t7 | check | warning  | Found 0 deleted space in delete link chain. Should be 184     |
| test.t7 | check | error    | Found 0 deleted rows in delete link chain. Should be 4        |
| test.t7 | check | error    | record delete-link-chain corrupted                            |
| test.t7 | check | error    | Corrupt                                                       |
| test.t8 | check | error    | Table './test/t8' is marked as crashed and should be repaired |
| test.t9 | check | status   | OK                                                            |
| test.t0 | check | error    | Table './test/t0' is marked as crashed and should be repaired |
+---------+-------+----------+---------------------------------------------------------------+
17 rows in set (0.00 sec)

I have seen messages about corrupted tables by using the test program.

However, the test program does also create a deadlock. It uses INSERT DELAYED on merge tables. According to http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html this is not supported. Does this bug report implicitly request for MERGE tables to be enabled for INSERT DELAYED or shall I reject such attempts from the beginning?

Ingo, insert delayed into the merge might be required. I have to test on the weekend.  Anyway, there still exists a bug.  if insert delayed is not supported, we should get error like this:

mysql> create table g(id int)engine=innodb;
Query OK, 0 rows affected (0.27 sec)

mysql> insert delayed into g values ();
ERROR 1031 (HY000): Table storage engine for 'g' doesn't have this option
mysql>

Then, no set of SQL commands should ever be able to cause table corruption.

I fully agree. It is trivial to change the server so that it sends the mentioned error message. But it is not easy to make INSERT DELAYED working with MERGE. I wouldn't even call it a bugfix because the manual explicitly lists the storage engines that can be used with INSERT DELAYED. MERGE is not in the list.

My first sentence of my previous comment was meant to accept that I could repeat the corruptions and consequently will try to fix them. Unfortunately I cannot repeat them any more since I removed the INSERT DELAYED statements into the MERGE tables from the test program. I reinserted the statements and removed only the DELAYED options. It still works without corruptions. Again tested locally plus on blade08, sol10-amd64-a, and quadita2. So I'm now a bit helpless how to proceed.

my opinion is that the best fix, given the circumstances, is just disable insert delayed into merge tables.  

the bug topic then would be that insert delayed was not giving an error when used against merge tables.  this should prevent corruptions.

Ok. Then I will fix it with the mentioned error message.

Btw. I can now explain that INSERT DELAYED on MERGE is not only good for deadlocks, but also for corruptions.

The problem is that MERGE expects a single table to be involved only. A MYSQL_LOCK  contains an array of table locks. When a statement uses a single table, the array usually contains one entry only. However for a MERGE table there are as many entries as it has MyISAM sub-tables.

The INSERT DELAYED thread opens and locks one table only. So it believes that it needs to handle only the first entry in the lock array. If this entry is by chance the sub-table to be inserted to, then it happens to work. But if it's another sub-table, then the insert is done on a table which is not properly locked...

Chances are better for INSERT_METHOD=FIRST than for INSERT_METHOD=LAST. But we can in no way guarantee that it works. There may even be more issues with MERGE and INSERT DELAYED. The safe way is to prohibit any such attempt with an error message. The documentation is clear enough that it is not intended to work.

One can always do INSERT DELAYED on the MyISAM sub-table directly.

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

ChangeSet@1.2607, 2007-03-05 11:52:28+01:00, istruewing@chilla.local +3 -0
  Bug#26464 - insert delayed + update + merge = corruption
  
  Using INSERT DELAYED on MERGE tables could lead to table
  corruptions.
  
  The manual lists a couple of storage engines, which can be
  used with INSERT DELAYED. MERGE is not in this list.
  
  The attempt to try it anyway has not been rejected yet.
  This bug was not detected earlier as it can work under
  special circumstances. Most notable is low concurrency.
  
  To be safe, this patch rejects any attempt to use INSERT
  DELAYED on MERGE tables.

I approve this patch. Suggest to add a note to "MERGE Table Problems" section, that merge doesn't support insert delayed.

Second reviewer is not needed for this fix.

Queued to 5.1-engines, 5.0-engines, and 4.1-engines.

Fixed in 4.1.23, 5.0.38, 5.1.17.

Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

INSERT DELAYED statements are not supported for MERGE tables, but the
MERGE storage engine was not rejecting such statements, resulting in
table corruption.

Also added a note to Merge Table Problems section explicitly stating
that MERGE does not support INSERT DELAYED.