MySQL Bugs: #11275: setLong not working...empty result set on newly inserted row

Bug #11275	setLong not working...empty result set on newly inserted row
Submitted:	13 Jun 2005 4:14	Modified:	18 Jul 2005 12:47
Reporter:	Michael Gillam	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	4.1.12a, 3.1.8a	OS:	Windows (Win XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
In one instance of an EJB, I INSERT a row with a primary key which is a BIGINT.
In another EJB instance, I attempt to retrieve that row with a prepared statement ...

SELECT * from mytable where id = ?

If I use a setLong(1,long) to assign the search argument, it will fail to retrieve the row.  If I use a setString(1,string), it works.

It seems to be a caching/stale-data problem, because the setLong(1,long) code will work if the row didn't not happen to be recently inserted.  For example, if I simply force a reinstantiation of the EJB session object (and force a new prepare of the SELECT) statement, the code works.  I know it sounds like I didn't commit the update, but you can retrieve the row from the command line query tool.

How to repeat:
CREATE TABLE bugtable
(
	id BIGINT,
	Long_Data TEXT,
	PRIMARY KEY
	(
		id
	)
);

In one process:
PreparedStatement insertST = con.prepareStatement("INSERT into bugtable(id,Long_Data) values (?,?)");
insertST.setLong(1,12345);
insertST.setString(2,"My data");
insertST.executeUpdate();
con.commit();

In another process:
PreparedStatement selectST = con.prepareStatement("SELECT * from bugtable where id = ?");
selectST.setLong(1,12345);
ResultSet rs = selectST.executeQuery();
if(rs.next())
{
 .... // doen't get a row if row was inserted in another process
}

By simply replacing the setLong() to setString() fixes it.

Could you please provide full text of test case ?

Correction ... the error occurs when the SELECT is executed from the SAME PROCESS as where the INSERT occurred.  If it happens to execute in the same EJB session object instance, the setLong() and select fails.  If I force the select to execute in another EJB instance, the select works.

Unfortunately, the error occurs inside a fairly complicated persistence layer, and I haven't been able to reproduce the problem in a simple self-contained example.
The error is very erratic, and seems to only occur within the context of the overall transaction.  However, I now know this ... if I set autoCommit=true, the problem goes away.  It would appear there is a bug in the MySql transaction commit logic.  Since I haven't seen this problem occur with my other DB tables, I'm inclined to believe it has something to do with the BIGINT primary key.  This is the first table I've defined with a BIGINT key.  My next step is to experiment with changing the the data type to char and see if the problem goes away.

Really I was not able to reproduce the bug on my test case. Could you please say what engine (MyISAM, InnoDB) of table is ?

I am using the default, which I assume to be MyIsam.

Vasily, could you please attach here (in FILE section) testcase you used

Test case

Attachment: test.java (text/java), 1.28 KiB.

I cannot reproduce as well, but it seems the same problem as in http://bugs.mysql.com/bug.php?id=10990 and in http://bugs.mysql.com/bug.php?id=9777 

Michael, could you check if "useServerPrepStmts=false" makes it run properly?

I appended "&useServerPrepStmts=false" to the connection URL and it did not fix the problem.

Do I need to specify it in a properties file?

Could you please try to repeat the bug on mysql 4.1.13 ? So as I was not able to repeat your bug. Probably the bug was fixed in the latest version of mysql.

I am having a similar problem; I'm not using EJB's, but the results are the same.  I can create rows, see them from the command line console, but until I bounce Tomcat, my software does not see them.  This same code works correctly with Oracle and SQL Server 2000.  I am also running Win XP SP2.  The version of mySql is 4.1.3-beta.

My setup:
I have two browser windows connected to my software.  The first will submit an attachment.  For each attachment, 4 rows are created in the same table at the same time.
The second browser window will allow me to view each attachment (a set of 4 rows).

Scenario 1:
I have autoCommit set to false
The first time I submit an attachment, the 4 rows are created and confirmed via the command line console.  My observer browser window (#2) sees the attachment rows
The second time I submit an attachment, the 4 rows are created and confirmed via the command line console.  My observer browser window (#2) DOES NOT see the attachment rows

Scenario 2:
I have autoCommit set to true
The first time I submit an attachment, the 4 rows are created and confirmed via the command line console.  My observer browser window (#2) sees the attachment rows
The second time I submit an attachment, only 3 rows are created and confirmed via the command line console.  My observer browser window (#2) DOES NOT see the attachment rows

So, I'm having problems either way;  I agree there appears to be something amiss either via XP or the transaction commit logic with mySQL.

Sounds like you might be confused by the default REPEATABLE_READ isolation level that MySQL uses for transactions?

The connection that you're issuing SELECTs from will only see rows that were "there" when the transaction started. If you're not calling commit or rollback on that SELECTing connection, you'll never see rows that were changed on some other connection as part of a transaction.

(this is by design).

Where do I go to download 4.1.13?  4.1.12a is the latest version posted on the download site.

Michael,

Old releases can be found here: http://downloads.mysql.com/archives.php

Geert

Sorry, I missread the message..

Snapshots: http://downloads.mysql.com/snapshots.php

I finally had a chance to try the 'transaction-isolation=READ-COMMITTED' and had success.

Thanks for the help.

The snapshot download link you gave me appears to require a MS Developer Studio/C++ environment to do the make.  Can I download a premade bin version of 4.1.13 from somewhere?  I only have java tools.