Bug #29838 myisam corruption using concurrent select ... and update
Submitted: 17 Jul 2007 6:34 Modified: 24 Aug 2007 9:57
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.30, 4.1.23, 5.0.46BK, 5.1BK, 6.0.1 OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: concurrent_insert, corruption, error 127

[17 Jul 2007 6:34] Shane Bester
Description:
Running the testcase for a few hours leads to the following messages:

DBD::mysql::st execute failed: Table 'a3' is marked as crashed and should be repaired at ./go.pl line 23.

DBD::mysql::st execute failed: Table 'b4' is marked as crashed and should be repaired at ./go.pl line 23.
DBD::mysql::st execute failed: Table 'b4' is marked as crashed and should be repaired at ./go.pl line 23.

DBD::mysql::st execute failed: Table 'a2' is marked as crashed and should be repaired at ./go.pl line 23.

How to repeat:
See attached perl testcase.  Needs DBD:mysql, DBI, and 

Suggested fix:
.
[17 Jul 2007 6:34] Shane Bester
original report from http://forums.mysql.com/read.php?21,148925,148925#msg-148925
[17 Jul 2007 6:36] Shane Bester
In my error logs:

Version: '5.0.46'  socket: '/tmp/mysql.sock'  port: 3306  yes
070717  4:43:19 [ERROR] Got error 127 when reading table './test/test12'
070717  4:53:56 [ERROR] Got error 127 when reading table './test/test11'
070717  5:56:07 [ERROR] Got error 127 when reading table './test/test11'
070717  5:56:14 [ERROR] Got error 127 when reading table './test/test11'
070717  7:03:21 [ERROR] Got error 127 when reading table './test/test11'
070717  7:29:59 [ERROR] Got error 127 when reading table './test/test5'
070717  7:30:10 [ERROR] Got error 127 when reading table './test/test5'
070717  8:05:28 [ERROR] Got error 127 when reading table './test/test6'
070717  8:07:37 [ERROR] Got error 127 when reading table './test/test10'
070717  8:08:43 [ERROR] Got error 127 when reading table './test/test6'
[17 Jul 2007 6:42] Shane Bester
before running the testcase, I had concurrent inserts enabled:

mysql> show global variables like '%concurrent%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | 2     |
+-------------------+-------+
[17 Jul 2007 15:14] Shane Bester
repeated on windows also.
[18 Jul 2007 14:50] Shane Bester
better testcase. causes error 127 is <1 second.

Attachment: bug29838.c (text/plain), 7.40 KiB.

[18 Jul 2007 14:50] Shane Bester
affects 4.1, 5.0, 5.1, 6.0
[18 Jul 2007 19:48] Shane Bester
For those not accustomed to reading C/Perl, running the following 3 statements concurrently will cause error 127:

select * from t1,t2 where t1.id=t2.link_id
insert into t1 (link_id) VALUES((select id from t2 limit 1))
insert into t2 (link_id) VALUES((select max(id)+1 from t1))
[23 Jul 2007 15:00] Ingo Strüwing
Using the C program, I get only this error message:

No results for 'select *,6 from test1 a4,test2 b4 , test3 c4 where a4.id=b4.link_id and b4.id=c4.link_id'.  (1194) - Table 'test2' is marked as crashed and should be repaired

It repeats constantly. It is always the same statement and always only 'test2' reported.

I stopped the test in several ways. myisamchk -e always reported the table ok. (When I kill the server it is reported as not properly closed).

So this seems to be a fake message, which we should fix of course. However I will have to try the other tests to find the real problem.
[23 Jul 2007 15:13] Ingo Strüwing
Now I found the error log entries as described. I could have sworn that I saw them never before when checking the files...
[25 Jul 2007 10:08] 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/31536

ChangeSet@1.2555, 2007-07-25 12:08:30+02:00, istruewing@chilla.local +5 -0
  Bug#29838 - myisam corruption using concurrent select ... and update
  
  When using concurrent insert with parallel index reads, it could
  happen that reading sessions found keys that pointed to records
  yet to be written to the data file. The result was a report of
  a corrupted table. But it was false alert.
  
  When inserting a record in a table with indexes, the keys are
  inserted into the indexes before the record is written to the data
  file. When the insert happens concurrently to selects, an
  index read can find a key that references the record that is not
  yet written to the data file. To avoid any access to such record,
  the select saves the current end of file position when it starts.
  Since concurrent inserts are always appended at end of the data
  file, the select can easily ignore any concurrently inserted record.
  
  The problem was that the ignore was only done for non-exact key
  searches (partial key or using >, >=, < or <=).
  
  The fix is to ignore concurrently inserted records also for
  exact key searches.
  
  No test case. Concurrent inserts cannot be tested with the test
  suite. Test cases are attached to the bug report.
  
  The real fix is in mi_rkey.c. The other changes are for improved
  diagnostic. They were useful to detect the above problem and some
  follow-up problems. They should be useful for future problems too.
[25 Jul 2007 10:14] Ingo Strüwing
I did only find and fix false reports of corruption.

Did you experience real table corruption? (That is, CHECK TABLE [EXTENDED] or myisamchk [-e] reporting corruption?)
[25 Jul 2007 18:11] Shane Bester
Ingo, in my tests, no real corruption happened either.  However, we very often found this error 127 precedes some other real corruption such as 134, 126.  Not sure if there's another bug, or if this bug triggers a corruption, since we've never repeated them together.
[27 Jul 2007 14:31] Antony Curtis
sent review by email
[28 Jul 2007 17:03] 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/31766

ChangeSet@1.2555, 2007-07-28 19:01:19+02:00, istruewing@chilla.local +1 -0
  Bug#29838 - myisam corruption using concurrent select ... and update
  
  When using concurrent insert with parallel index reads, it could
  happen that reading sessions found keys that pointed to records
  yet to be written to the data file. The result was a report of
  a corrupted table. But it was false alert.
  
  When inserting a record in a table with indexes, the keys are
  inserted into the indexes before the record is written to the data
  file. When the insert happens concurrently to selects, an
  index read can find a key that references the record that is not
  yet written to the data file. To avoid any access to such record,
  the select saves the current end of file position when it starts.
  Since concurrent inserts are always appended at end of the data
  file, the select can easily ignore any concurrently inserted record.
  
  The problem was that the ignore was only done for non-exact key
  searches (partial key or using >, >=, < or <=).
  
  The fix is to ignore concurrently inserted records also for
  exact key searches.
  
  No test case. Concurrent inserts cannot be tested with the test
  suite. Test cases are attached to the bug report.
  
  The fix shall go to 4.1 and up when approved.
[30 Jul 2007 9:17] Sergey Vojtovich
Ok to push.
[1 Aug 2007 7:37] Antony Curtis
Review sent by email. Minor reordering requested. Ok to push.
[1 Aug 2007 9:56] 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/31944

ChangeSet@1.2678, 2007-08-01 11:54:24+02:00, istruewing@chilla.local +1 -0
  Bug#29838 - myisam corruption using concurrent select ... and update
  
  When using concurrent insert with parallel index reads, it could
  happen that reading sessions found keys that pointed to records
  yet to be written to the data file. The result was a report of
  a corrupted table. But it was false alert.
  
  When inserting a record in a table with indexes, the keys are
  inserted into the indexes before the record is written to the data
  file. When the insert happens concurrently to selects, an
  index read can find a key that references the record that is not
  yet written to the data file. To avoid any access to such record,
  the select saves the current end of file position when it starts.
  Since concurrent inserts are always appended at end of the data
  file, the select can easily ignore any concurrently inserted record.
  
  The problem was that the ignore was only done for non-exact key
  searches (partial key or using >, >=, < or <=).
  
  The fix is to ignore concurrently inserted records also for
  exact key searches.
  
  No test case. Concurrent inserts cannot be tested with the test
  suite. Test cases are attached to the bug report.
[3 Aug 2007 7:56] Ingo Strüwing
Queued to 5.2-engines, 5.1-engines, 5.0-engines, 4.1-engines.
[6 Aug 2007 18:12] Bugs System
Pushed into 5.1.21-beta
[7 Aug 2007 14:35] Paul Dubois
Noted in 5.1.21 changelog.

If one thread was performing concurrent inserts, other thread
sreading from the same table using equality key searches could see the
index values for new rows before the data values had been written, 
leading to reports of table corruption.

Resetting report to Patch Queued; it appears that the fix will be pushed
to other trees?
[7 Aug 2007 18:56] Ingo Strüwing
Hi Paul, correct. The automatic is dumb. It does not detect when all pushes are complete.
[14 Aug 2007 7:59] Bugs System
Pushed into 5.0.48
[14 Aug 2007 8:01] Bugs System
Pushed into 4.1.24
[24 Aug 2007 9:57] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in 4.1.24 and 5.0.48 changelogs.
[29 Jan 2008 8:37] Ash Collado
hi, im a mysql newbie and i experience lots of crashed MYISAM tables in our database.

how do i check if this patch is already installed on my version: 5.0.27-standard-log
[29 Jan 2008 9:32] Shane Bester
Ashley, 5.0.27 does not have the fix. Please try 5.0.51 or newer.
http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-51.html
[29 Jan 2008 9:47] Ingo Strüwing
In the bug report you find a comment like "Pushed into 5.0.48".

You can verify it by reading the user manual, apendix "MySQL Community Server Enhancements and Release Notes": http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-51.html

You can also clone the bitkeeper repository and use the "bk changes" command to see which changesets are included. In the bug report you find a link to the changeset and a copy of the changeset comment, which allow you to search for it in the output of "bk changes".
[29 Jan 2008 10:42] Ash Collado
Thanks Shane and Ingo. I have finally replicated this issue on our 5.0.27 server and our next move is to do POC for the 5.051 server since its a huge production server were going to upgrade.
[13 Mar 2008 9:03] Shane Bester
still affects 4.0.30. I doubt it will be fixed in 4.0 ..
[13 Mar 2008 10:16] Shane Bester
workaround for older versions of mysql is to disable concurrent inserts by setting concurrent_insert=0 in my.cnf
[13 Mar 2008 10:55] Ingo Strüwing
Shane, the patch is pretty small. I guess it can quite easily be ported to 4.0. We could push it to our repository if product mamangement agrees. That way it would be usable by those who build from our repository. A new official release is pretty unlikely though.
[11 Dec 2009 8:19] Sveta Smirnova
See also bug #27510