| Bug #44750 | SELECT immediately after INSERT sometimes fails | ||
|---|---|---|---|
| Submitted: | 8 May 2009 13:23 | Modified: | 12 May 2009 5:46 |
| Reporter: | Martijn Broenland | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.34 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | concurrent insert, race condition | ||
[8 May 2009 13:24]
Martijn Broenland
SHOW VARIABLES
Attachment: variables.txt (text/plain), 30.28 KiB.
[8 May 2009 13:34]
Martijn Broenland
Compile options: CFLAGS="-O3 -mtune=nocona -march=nocona -mfpmath=sse -mmmx -msse -msse2 -msse3" CXX=gcc \ CXXFLAGS="-O3 -mtune=nocona -march=nocona -mfpmath=sse -mmmx -msse -msse2 -msse3 -felide-constructors -fno-exceptions -fno-rtti" ./configure \ --prefix=/usr/local/mysql \ --enable-assembler \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ --localstatedir=/var/log/mysql \ --enable-local-infile \ --with-charset=utf8 \ --with-mysqld-user=mysql \ --without-debug
[8 May 2009 13:58]
Valeriy Kravchuk
Thank you for the problem report. What storage engine is used for the table? Check SHOW CREATE TABLE results if you are not sure.
[8 May 2009 14:55]
Martijn Broenland
Sorry, MyISAM
[11 May 2009 13:29]
Sergei Golubchik
it's a duplicate, although I couldn't find the first bug's number. workaround - disable concurrent inserts for MyISAM
[11 May 2009 16:03]
Andrei Elkin
bug#36618 is a predecessor.
[12 May 2009 5:46]
Martijn Broenland
Thanks, we'll use the LOCK TABLE tmp WRITE; UNLOCK TABLES; workaround for now
Description: If one MySQL connection inserts a record into a table, and immediately hereafter, another MySQL connection SELECTs this record, the MySQL server sometimes returns zero records, as if the record is not inserted yet. In the query log file the statements are in the correct order. If you add a short delay between the INSERT and SELECT there's no problem. If the script uses the same connection, there's no problem either. Query cache is disabled. The bug is also in 5.1.32. How to repeat: CREATE TABLE test.tmp (value INT UNSIGNED NOT NULL PRIMARY KEY); <?php $db = mysql_connect('localhost', 'foo', 'bar', true); $db2 = mysql_connect('localhost', 'foo', 'bar', true); $n = 1; while(true) { mysql_query('INSERT INTO test.tmp (value) VALUES ('. $n .')', $db); $res = mysql_query('SELECT * FROM test.tmp WHERE value = '. $n, $db2); if(mysql_num_rows($res) == 0) { print("Fail (". $n .")\n"); exit; } $n++; } ?> The bug also happens when you run two PHP scripts simultaneously with each PHP script having just one connection to the MySQL server (script 1 INSERTing and script 2 SELECTing).