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

[21 May 2007 19:49] Shane Bester
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:
.
[21 May 2007 20:10] MySQL Verification Team
see header of file for host, username, password, compile instructions, etc

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

[21 May 2007 20:14] MySQL Verification Team
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)
[21 May 2007 20:19] MySQL Verification Team
optimize table has the same problem
[21 May 2007 20:27] MySQL Verification Team
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'
[21 May 2007 20:50] MySQL Verification Team
analyze table also affected
[31 May 2007 11:11] Bugs System
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.
[31 May 2007 16:51] MySQL Verification Team
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 ?)
[1 Jun 2007 8:35] Sergey Vojtovich
Shane, I don't think KILL may cause this problem. The only other statements I could suspect are ALTER TABLE or probably RENAME TABLE.
[1 Jun 2007 9:46] Bugs System
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.
[18 Jun 2007 7:49] Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 7:50] Bugs System
Pushed into 4.1.24
[18 Jun 2007 7:50] Bugs System
Pushed into 5.0.44
[18 Jun 2007 13:45] Paul DuBois
Noted in 4.1.24, 5.0.44, 5.1.20 changelogs.
[19 Jun 2007 10:27] Daniel Fischer
Didn't make it into 5.0.44, will be in 5.0.46.
[20 Jun 2007 1:12] Paul DuBois
Moved 5.0.44 changelog entry to 5.0.46.