MySQL Bugs: #4854: Use of RETURN_GENERATED_KEYS in PrepareStatement hangs after repeated use.

Bug #4854	Use of RETURN_GENERATED_KEYS in PrepareStatement hangs after repeated use.
Submitted:	2 Aug 2004 15:30	Modified:	28 Mar 2014 14:15
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0.14	OS:	Windows (Windows 2000, FreeBSD (+ more))
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
When connecting to MySQL 4.0.16 with a preparedstatement containing an INSERT statement using the constant Statement.RETURN_GENERATED_KEYS hangs the servlet container after 7-10 uses of this type of statement. The servlet container hangs with no exception & subsequent requests fail. The hang happens on the connection.prepareStatement line. Probably effects other versions as well, but I have no ability to test.

How to repeat:
c = MySQLDAOFactory.createConnection();
ps = c.prepareStatement("INSERT xxxx", Statement.RETURN_GENERATED_KEYS);

after 7-10 uses of this statement, the servlet container hangs (the database server continues operation without interuption).

Suggested fix:
Stop the hang :)

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

My Apologies. We actually tested the problem with 3.0.14, which is the latest production version of the mysql jdbc driver.

Can you post a testcase? We've never had this or a similar bug reported before in this section of code, which leads me to believe it is related to how you are using it. 

Inspection of the code in question shows that there should be no hangs (no I/O is done in Connection.prepareStatement().

Are you using the
 connection in question from more than one thread at a time?

Changed status to 'Needs Feedback'

ok, more info:

First, a test case:
    	try {
            Connection connection = Torque.getConnection("default");
    	    PreparedStatement ps = connection.prepareStatement("ANY SQL QUERY GOES HERE",  Statement.RETURN_GENERATED_KEYS); 
    	    ps.executeQuery(); // ps.executeUpdate() also appears to not work. I'm now unsure whether it's hanging on this line, or the the one above
    	    ps.close();
       	    connection.close();
           
        	}
        	catch (Exception e) {
            }

Running this 7-10 times causes my servlet container to hang. Has been tested in Tomcat 4.x + 5.x & Jetty, so it's not the servlet container. It's also been tested on FreeBSD 4 + 5 and Windows 2000. Note that the test case uses Torque for connection pooling. Non-connection pooled servlets appear to work properly. I'm not ruling out Torque as a cause, but I seem to remember trying this using non-Torque related connection pooling sometime last week, and had the same issue.

Also, this apparently isn't a threading problem as the connection is being tested one thread at a time.

What state is the thread in that is blocked?

Please send a thread dump from your appserver. 

(i.e send us the output when you send 'kill -QUIT' to the JVM (if on unix), or by pressing ctrl-break in the shell where the JVMwas started if on Windows).

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.