MySQL Bugs: #29838: myisam corruption using concurrent select ... and update

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

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:
.

original report from http://forums.mysql.com/read.php?21,148925,148925#msg-148925

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'

before running the testcase, I had concurrent inserts enabled:

mysql> show global variables like '%concurrent%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | 2     |
+-------------------+-------+

repeated on windows also.

better testcase. causes error 127 is <1 second.

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

affects 4.1, 5.0, 5.1, 6.0

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))

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.

Now I found the error log entries as described. I could have sworn that I saw them never before when checking the files...

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.

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?)

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.

sent review by email

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.

Ok to push.

Review sent by email. Minor reordering requested. Ok to push.

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.

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

Pushed into 5.1.21-beta

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?

Hi Paul, correct. The automatic is dumb. It does not detect when all pushes are complete.

Pushed into 5.0.48

Pushed into 4.1.24

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.

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

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

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".

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.

still affects 4.0.30. I doubt it will be fixed in 4.0 ..

workaround for older versions of mysql is to disable concurrent inserts by setting concurrent_insert=0 in my.cnf

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.

See also bug #27510