MySQL Bugs: #3921: Data inserts using preparedStatement fail + hang db

Bug #3921	Data inserts using preparedStatement fail + hang db
Submitted:	27 May 2004 23:17	Modified:	27 May 2004 23:44
Reporter:	Charles Syers	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.1-alpha + 5.0.0-alpha-max	OS:	Microsoft Windows (Windows XP / SP1)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Executing a PreparedStatement with replaceable parameters hangs database
and spontaneously terminates connection when using Connector/J 3.1.1

This problem seems to occur regardless of whether running generic mysqld
or mysqld-max.  Have not tried others, but expect similar problems.

Using stock Sun j2sdk1.4.2_04.

Note - Inserts using updateable ResultSets  ( ala. rs.insertRow() )
       *do* work successfully.

We do need to set column data via function rather than inline in
SQL due to potential character conflicts within the column data.

How to repeat:
Create trivial table:

mysql> desc testtable1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| value | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
(Type = InnoDB )

Build trivial Java app w/ db connect containing the
following code:

<Code excerpt>
    PreparedStatement s2 = 
        conn.prepareStatement("insert into testtable1 values ( ?, ? )");
    s2.setString( 1, "NameOne" );
    s2.setInt( 2, 114 );
    s2.executeUpdate();
    System.out.println( "Data inserted" );
</Code excerpt>

Existing connection works fine with selects on any table.
When running code above, system reports "Data inserted",
however we get a subsequent exception during a conn.close()
and checking MySQL, we find the database hung.  Shutdown does
not respond, must terminate process.

Upon restart, we find the data properly inserted into the table:

mysql> select * from testtable1;
+---------+-------+
| name    | value |
+---------+-------+
| NameOne |   114 |
+---------+-------+
1 row in set (0.00 sec)

This behavior happens because there are changes in MySQL-4.1's prepared statement protocol that have yet to be merged into 5.0, and supporting both versions of the protocol is not straightforward, and will not be supported (ever).

You will either have to wait for MySQL-4.1.2 to be released, at which point all of the changes in 4.1.2 will be propagated into MySQL-5.0.1 before it is released, or you can disable server-side prepared statements by adding 'useServerPrepStmts=false' as a JDBC URL property.

I had a similar problem using ConnectorJ 3.1.1 with MySQL 4.1.2 Linux. (The problem did NOT happen when using ConnectorJ 3.1.1 with MySQL 4.1.1 Windows.) I solved the problem using the tip from Mark Matthews (useServerPrepStmts=false). But then again, isn't it better/more efficient to use useServerPrepStmts=true?

It is a bit of a struggle to find out which version of ConnectorJ is -exactly- compatible with which version of MySQL daemon. Maybe a table in the docs would be nice.

Koen Heene