Bug #51329 Inserts from one connection not immediately visible in second connection
Submitted: 19 Feb 2010 15:44 Modified: 25 Feb 2010 17:54
Reporter: David Crimmins Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.36-enterprise-commercial-classic OS:Any
Assigned to: CPU Architecture:Any

[19 Feb 2010 15:44] David Crimmins 
Description:
An application using multiple connections performs an insert using one connection then selects the new row using another connection. The same thread is used for both the write and read, with the read always occurring after the write. Occasionally (~1 in 1000) the select returns an empty result set.
The problem is not seen if the update connection executes a statement before the select. This extra statement can be as trivial as "select 'Hello'".

How to repeat:
C source for a windows application demonstrating this problem is attached. Typical output is:

Unexpected number of results: 0 - Key 150
Unexpected number of results: 0 - Key 204
Unexpected number of results: 0 - Key 484
Unexpected number of results: 0 - Key 485
.... approx 260 lines removed .....
Unexpected number of results: 0 - Key 95275
Unexpected number of results: 0 - Key 97075
Unexpected number of results: 0 - Key 98594
Unexpected number of results: 0 - Key 98597
Test completed in 28 seconds. Number of errors: 265
[19 Feb 2010 15:46] David Crimmins 
Source code to demonstrate problem

Attachment: testcase.cpp (text/plain), 1.70 KiB.
[19 Feb 2010 21:19] MySQL Verification Team 
seen bug #37521 ?
[22 Feb 2010 10:56] David Crimmins 
I agree this is likely to be attributable to the same root cause as bug #37521, so should be marked as a duplicate. This bug appears to be 19 months old, is it likely to be fixed?
[25 Feb 2010 4:02] MySQL Verification Team 
Thank you for the bug report. Could you please try the latest release; with current source I got:

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.45-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >exit
Bye

C:\DBS>cd\temp

C:\temp>bug51329.exe
Unexpected number of results: 0 - Key 7364
Test completed in 25 seconds. Number of errors: 1

C:\temp>
C:\temp>bug51329.exe
Unexpected number of results: 0 - Key 34116
Test completed in 26 seconds. Number of errors: 1

C:\temp>bug51329.exe
Test completed in 28 seconds. Number of errors: 0

C:\temp>bug51329.exe
Unexpected number of results: 0 - Key 37652
Test completed in 26 seconds. Number of errors: 1

C:\temp>
[25 Feb 2010 11:23] David Crimmins 
Thanks for you reply.

Unfortunately we only use binary distributions of MySQL server so are unable to test using 5.1.45. However, your test against 5.1.45 demonstrates the bug still exists in the latest version. In your case the error is much less frequent which could be due to hardware differences or system load. 

If there is a 5.1.45 binary for 32 bit windows available for download please let me known and I will run the test on my PC.
[25 Feb 2010 13:11] MySQL Verification Team 
Thank you for the feedback. You can test the last release 5.1.44. Actually my doubt is that behavior is hardware/OS issue than MySQL's bug itself anyway I am verifying for further investigation.
[25 Feb 2010 14:43] David Crimmins 
Repeated test using:
Server version: 5.1.44-community MySQL Community Server (GPL)

Problem still exists. I don't think its hardware related as we see same issue
on multiple machines including Sun UltraSparc.

Test Output follows:
Unexpected number of results: 0 - Key 290
Unexpected number of results: 0 - Key 291
Unexpected number of results: 0 - Key 292
...
*** Deleted ~90 similar lines ***
...
Unexpected number of results: 0 - Key 98393
Unexpected number of results: 0 - Key 98529
Unexpected number of results: 0 - Key 98530
Unexpected number of results: 0 - Key 98531
Test completed in 28 seconds. Number of errors: 99
[25 Feb 2010 17:54] Dmitry Lenev 
Hello David, Shane, Miguel!

Shane is right this bug has the same reason as bug #37521. It will become even more obvious if you look at one of its duplicates - bug #36618 "myisam insert not immediately visible to select from another client".

Therefore I will mark this report as a duplicate of bug #37521.
[4 Aug 2010 7:50] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 7:55] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 7:56] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 7:57] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 7:58] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 7:59] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:00] Bugs System 
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:10] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:13] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:14] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:15] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:16] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:17] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:26] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 9:00] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:20)
[4 Aug 2010 9:05] Bugs System 
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)