MySQL Bugs: #2401: Prepared INSERT fails if another thread SELECTs from same table

Bug #2401	Prepared INSERT fails if another thread SELECTs from same table
Submitted:	15 Jan 2004 10:33	Modified:	13 Apr 2004 3:30
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Prepared INSERT statements begin to fail with invalid Duplicate Key errors if another thread SELECTs from the same table.

How to repeat:
Test case code for performing the INSERTs will be attached.

"Run it (providing a valid user and password and an iteration count).  set the iteration count large enough that you can switch windows to run the read query in another window before the binary finishes.

In mysql session, type:
        connect test
        select max(id) from test;
Repeat the select statement until the binary spits out a failure message."

Suggested fix:
n/a

The failure does not seem to be specific to a particular table handler.  The original bug report used MyISAM.

I also experience this problem. However it also adds a dumm row. The prepared statement I use has one string, one int (primary key) and one geometry field. I can "select from" the table as long as I don't "touch" the rows being inserted. For example if I'm inserting recorded 30000 to 40000 I can do a "select * from table where id<30000" without problems, but if I do a "select * from table where id>30000" it will cause the prepared statement to die as it passes (I assume) the newly entered record. It will also cause a row containing zero length entries (ie a primary key of 0, a string of 0 length and a geometry where length(g)==0).

I'm using 4.1.1alpha with the latest unstable JDBC driver. I will provide information about our production environment as I experience it.

While this bug is serious it's better than the server crash I was experiencing when using prepared statements for "select from a,b ... where id=?". I guess I should report/find that elsewhere. Using statements solved this one.

Hi, Dean!

I can't repeat this bug with latest version of 4.1 tree (but I can easily repeat it with 4.1.1 or with 4.1 tree snapshot done at the end of December). After some investigation about possible causes of this bug I believe that this bug was 
fixed now as many other bugs related to prepared statements (probably by ChangeSet 1.1664.2.1 2003/12/30 14:08:19 hf@deer.(none) "Fix for prepared statements"). So I am closing it.

The fix will appear in 4.1.2. (As alternative one can look into http://www.mysql.com/doc/en/Installing_source.html for more
info about building MySQL from public development source tree. :) )

Please feel free to reopen this bug if you will be able to repeat it with 4.1.2 or recent 4.1 tree snapshot.