MySQL Bugs: #25433: KILL causes errno 144 table '.\test\t1' is marked as crashed

Bug #25433	KILL causes errno 144 table '.\test\t1' is marked as crashed
Submitted:	5 Jan 2007 14:07	Modified:	7 Mar 2007 17:41
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.30,5.0.34BK,5.0.32	OS:	Windows (win2003,suse9x86)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	bfsm_2007_01_18, corruption, myisam, table crash

Description:
when running a mix of the following queries with many threads, table t1 is crashing:

1000 inserts
100 deletes
100 updates
1000 kill's
100 replaces
1 repair
1 reconnect to server

errors like this occur:

query failed 'delete from `t1` where `a` in (2102012115,285666672,200783772,1047822643,103755966)' : 144 (Table '.\test\t1' is marked as crashed and last (automatic?) repair failed)
Table   Op      Msg_type        Msg_text
------------------------------------
test.t1 repair  warning Number of rows changed from 0 to 63299
test.t1 repair  status  OK

Table   Op      Msg_type        Msg_text

How to repeat:
edit the testcase.c to reflect the correct user,pass,ip address of the mysqld server, compile using your own lib and include path, as shown at the top of the file.

run the application and watch the output for errors.
typically takes 10 - 30 minutes to see an error.
might be needed to increase or decrease the NUMTHREADS, depending
on hardware, OS, etc.

Suggested fix:
.

see top of file for building instructions

Attachment: testcase.c (text/x-csrc), 5.75 KiB.

in the server's error log, the following messages always appear;

070105 16:11:07 [Note] Retrying repair of: './test/t1' with keycache

followed by many of these:
070105 16:11:08 [ERROR] mysqld-nt: Table '.\test\t1' is marked as crashed and last (automatic?) repair failed

finally:

070105 16:11:11 [Note] Found 46824 of 0 rows when repairing './test/t1'

repeated on suse9 x86 also, with testcase1.c which I'll upload.

070104 19:28:07 [Note] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.32-linux-i686-glibc23/bin/mysqld: ready for connections.
Version: '5.0.32-enterprise-gpl'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server (GPL)
070104 19:39:34 [Note] Retrying repair of: './test/t1' with keycache
070104 19:39:34 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.32-linux-i686-glibc23/bin/mysqld: Table './test/t1' is marked as crashed and last (automatic?) repair failed
...
070104 19:39:38 [Note] Found 33537 of 0 rows when repairing './test/t1'

slightly modified version of previous testcase.

Attachment: testcase1.c (text/x-csrc), 6.26 KiB.

testcase1.c also causes this kind of message sometimes:

Table   Op      Msg_type        Msg_text
------------------------------------
test.t1 repair  info    Delete link points outside datafile at 243160
test.t1 repair  info    Delete link points outside datafile at 261200
test.t1 repair  info    Delete link points outside datafile at 621824
test.t1 repair  info    Delete link points outside datafile at 819280
test.t1 repair  warning Number of rows changed from 0 to 31963
test.t1 repair  status  OK

See bug #25289 too.

testcase1.c seems to repeat the corruptions with higher probablility than testcase.c.

Turning off the keycache and concurrent inserts did not solve the problem.

It may take a while to find the cause.

The problem is that the "kill" is detected deep down in the repair code. This is reported upwards as "some" error. Consequently it is falsely regarded as a repair error and the table is falsely marked as crashed.

I will try to detect the situation at a higher level and not mark the table crashed. However it needs to remain marked crashed if it was so before the repair started.

It turned out that this cannot be solved by a bug fix.

The repair code modifies the index file while it is going. If it is interrupted by a kill, it leaves behind a corrupted table.

This could in theory be solved by backing up the index file and work on a new file instead. But this is a major change in behaviour, which we don't intend to do at the moment.

I am deeply sorry that I cannot do better but quote the fine reference manual:
"Warning: Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM  table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption)." (http://dev.mysql.com/doc/refman/5.1/en/kill.html)

ingo, this testcase crashes a debug server.  can that be fixed at least? see bug #25289

Thanks for reminding me of this. I didn't experience this crash during my tests so far. But now, after I did a fresh clone and full rebuild, I got that crash.

I always believed that this had already been fixed. I'll try to find out what happened with this fix. I'll take care for fixing the problem, working on bug #25289.