MySQL Bugs: #28574: repair table causes queries to fail with various corruption errors: 126,134,145

Bug #28574	repair table causes queries to fail with various corruption errors: 126,134,145
Submitted:	21 May 2007 19:49	Modified:	20 Jun 2007 1:12
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.36, 5.0.44-debug, 5.1.19-debug	OS:	Linux (suse 9.3 x86)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
If somebody performs a 'repair table' on tables which are being accessed by other connections inserting, updating, selecting, failures can be seen such as:

query failed (126) - Incorrect key file for table './test/t2.MYI'; try to repair it: update t2 set b='suspenseful'

query failed (1062) - Duplicate entry '2259' for key 2: insert into t2 values(@v,'v4XSS [...]

query failed (1030) - Got error 134 from storage engine: select distinct(b) from t1 wher [...]

And in the mysqld server error logs we see messages like this:

070521 21:21:41 [Note] Found 1547 of 1544 rows when repairing './test/t2'
070521 21:22:29 [ERROR] /home/sbester/server/5.0/mysql-5.0.44-linux-i686/bin/mysqld: Incorrect key file for table './test/t2.MYI'; try to repair it
070521 21:22:34 [Note] Found 1758 of 1755 rows when repairing './test/t1'

This bug can potentially cause server crashes in some circumstances.
No insert delayed or merge tables are used here. (those are known in some other bug reports to cause a problem).

How to repeat:
I will upload a testcase later.

Suggested fix:
.

see header of file for host, username, password, compile instructions, etc

Attachment: bug28574.c (text/plain), 10.80 KiB.

See testcase which is attached.  Run it and tail the mysql error log or watch for output of errors:

sbester@www:~> ./bug28574 
running initializations..
pre-generating 16777216 bytes of random data
about to spawn 50 threads
..................................................
completed spawning new database worker threads
testcase is now running, so watch for error output
........................................query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1030 (Got error 134 from storage engine)
query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1030 (Got error 134 from storage engine)
query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1030 (Got error 134 from storage engine)
query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1030 (Got error 134 from storage engine)
query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1030 (Got error 134 from storage engine)
query failed 'insert into t1 values(@v,'dkdguggdjdvdzdieatzunrxxzhwavbfvsdaoymotmjbhhywnctzrdkzztpyriitvtibqhhfvlltchjnaqjdjxhnlvzqywkstpbgmqfzduhxoqqojvvcsnlrqbvscuobzcwtuopdzkwbtahuszvacbjvgfxukovxusielw'),(@v+1,'ejaozpxpuxcfybemvgkctvklfjxrmhugatrhdcbsqhoevtjidgwrjopbykapsippvot'),(@v+1,'kbpfzxxgyrnitlnqtiofvvpcqabeedzcckcuedjiwyvdillkftdbxcovmzau')' : 1062 (Duplicate entry '0' for key 2)
query failed 'delete from t1 where a=@v-1' : 1194 (Table 't1' is marked as crashed and should be repaired)

optimize table has the same problem

affected CHECK TABLE too.  With check table, I received:

testcase is now running, so watch for error output
........................................................query failed 'select distinct (b) from t2 where a=@v+1 limit 10' : 1194 (Table 't2' is marked as crashed and should be repaired)

and in error log, the infamous errno 127

Version: '5.1.19-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
070521 22:09:14 [ERROR] Got error 127 when reading table './test/t2'

analyze table also affected

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

ChangeSet@1.2500, 2007-05-31 15:14:09+05:00, svoj@mysql.com +1 -0
  BUG#28574 - repair table causes queries to fail with various
              corruption errors: 126,134,145
  
  When one thread attempts to lock two (or more) tables and another
  thread executes statement that aborts these locks (e.g. REPAIR
  TABLE) we may get a table object with wrong lock type in a table
  cache.
  
  For example if SELECT FROM t1,t2 was aborted, subsequent INSERT
  INTO t1 may be executed under read lock.
  
  As a result we may get various table corruptions and even a server
  crash.
  
  This is fixed by resetting lock type in case lock was aborted by
  another thread.
  
  I failed to create reasonable test case for this bug.

Sergey, can you list all possible scenarios that this bug can be exposed ?
I only tried check/repair/optimize/analyze and they all caused problems so far.
Anything else (such as KILL ?)

Shane, I don't think KILL may cause this problem. The only other statements I could suspect are ALTER TABLE or probably RENAME TABLE.

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

ChangeSet@1.2660, 2007-06-01 13:50:13+05:00, svoj@mysql.com +1 -0
  BUG#28574 - repair table causes queries to fail with various
              corruption errors: 126,134,145
  
  When one thread attempts to lock two (or more) tables and another
  thread executes statement that aborts these locks (e.g. REPAIR
  TABLE) we may get a table object with wrong lock type in a table
  cache.
  
  For example if SELECT FROM t1,t2 was aborted, subsequent INSERT
  INTO t1 may be executed under read lock.
  
  As a result we may get various table corruptions and even a server
  crash.
  
  This is fixed by resetting lock type in case lock was aborted by
  another thread.
  
  I failed to create reasonable test case for this bug.

Pushed into 5.1.20-beta

Pushed into 4.1.24

Pushed into 5.0.44

Noted in 4.1.24, 5.0.44, 5.1.20 changelogs.

Didn't make it into 5.0.44, will be in 5.0.46.

Moved 5.0.44 changelog entry to 5.0.46.