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: | |
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
[17 Jul 2007 6:34]
MySQL Verification Team
original report from http://forums.mysql.com/read.php?21,148925,148925#msg-148925
[17 Jul 2007 6:36]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
repeated on windows also.
[18 Jul 2007 14:50]
MySQL Verification Team
better testcase. causes error 127 is <1 second.
Attachment: bug29838.c (text/plain), 7.40 KiB.
[18 Jul 2007 14:50]
MySQL Verification Team
affects 4.1, 5.0, 5.1, 6.0
[18 Jul 2007 19:48]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
still affects 4.0.30. I doubt it will be fixed in 4.0 ..
[13 Mar 2008 10:16]
MySQL Verification Team
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