MySQL Bugs: #3580: PreparedStatement hangs/crashes server

Bug #3580	PreparedStatement hangs/crashes server
Submitted:	27 Apr 2004 15:52	Modified:	20 Dec 2004 23:58
Reporter:	Karl Niemeier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	mysql-connector-java-3.1-nightly-2004042	OS:	Windows (Windows XP Pro 2002)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
MySQL Server: 5.0.0-alpha-nt
Connector/J: mysql-connector-java-3.1-nightly-20040427
JDK: 1.4.1_02

A sequence that includes a Connection.prepareStatement("delete from test") and Connection.close() causes the server to either crash or hang with the cpu 100% busy. The statement being prepared happens to have no value placeholder ('?'). However, preparing a statement such as "delete from test where cint > ?" will both hang in the driver and crash or hang the server when the client is terminated. 

How to repeat:
DDL:

drop table if exists test;
create table test(cint int);
insert into test values (1);

Java:

    Connection conn = null;
    try {
      try {
        Class.forName("com.mysql.jdbc.Driver");
      } catch (ClassNotFoundException e) {
        e.printStackTrace();
      }
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sd?user=root&password=sd");
      PreparedStatement stmt = conn.prepareStatement("DELETE FROM test");
      stmt.execute();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }finally {
      try {
        conn.close();  // <-- this call crashes or hangs the server
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

As mentioned in the description, if we replace the above call to prepareStatement with the following two statements, then the call to the prepareStatement will hang and the server will crash or hang when the client is killed:

      PreparedStatement stmt = conn.prepareStatement("DELETE FROM test where cint > ?");
      stmt.setInt(1, 0);  // this was added for testing the WHERE clause

After I found this problem, I was curious whether I could get the unit tests that are part of the Connector/J distribution to run successfully. I tried running StatementsTest.java and it passed the first few tests but hit the same problem that I described in this bug when it was in testPreparedStatement. It is hanging on the InputStream read in readFully(). Server not responding?

The issue arrises because the driver checks if it's talking to a MySQL server version 4.1.2 or newer...The protocol for prepared statements changed slightly in 4.1.2...(there's more data sent back to the client)...

However, those changes have not been merged 'forward' into 5.0 yet, so the driver hangs forever waiting to read data that is not there.

You will have to wait until 5.0.1 is released which will contain the changes in 4.1.2, or disable server-side prepared statements entirely by using the 'useServerPrepStmts=false' property in your JDBC connection URL.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/