MySQL Bugs: #45859: executeUpdate method hangs when 2 inserts are executed in a particular order

Bug #45859	executeUpdate method hangs when 2 inserts are executed in a particular order
Submitted:	30 Jun 2009 15:52	Modified:	8 Jul 2009 6:13
Reporter:	Joshua Green	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.7	OS:	Linux (Scientific Linux 5.2)
Assigned to:		CPU Architecture:	Any
Tags:	executeUpdate, hang, hung

Description:
This bug is similar to #35307.  However it's slightly different, and as #35307 is a year old and doesn't seem to be being fixed, I thought I'd report this in a new bug.

In Brief:
I have 2 SQL INSERT statements.  The second one should cause a fault (it doesn't specify the value of a non null field).  When executed individually, each produces the expected results.  When executed together, the second statement's executeUpdate() method hangs.

How to repeat:
I have the following table:
CREATE TABLE `testdb` (
  `id` varchar(64) NOT NULL,
  `field` varchar(64) NOT NULL
);

I execute two update statements on it.  These are the two statements:
INSERT INTO `testdb` (`id`, `field`) VALUES ('one', 'value');
INSERT INTO `testdb` (`field`) VALUES ('another-value');

I do so using the following Java code:
// Make sure the database driver is loaded
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "testuser", "testpassword");

String query1 = "INSERT INTO `testdb` (`id`, `field`) VALUES ('one', 'value')";
String query2 = "INSERT INTO `testdb` (`field`) VALUES ('another-value')";

PreparedStatement stmt1 = conn.prepareStatement(query1);
stmt1.executeUpdate();
stmt1.close();

PreparedStatement stmt2 = conn.prepareStatement(query2);
stmt2.executeUpdate();
stmt2.close();

I have removed the print-line statements I used to make the code snippet smaller.  However, using them I found the code hung while executing 'stmt2.executeUpdate()'.  Note that if either of the statements are removed, the code works as expected (the first statement will update the table.  The second one will fail because the non-null 'id' field has not been given a value).  The code only hangs when both statements are executed in that order.  Reversing the order (stmt2.executeUpdate() followed by stmt1.executeUpdate()) works as expected.

I have tried closing both statements at the end of the code but that doesn't change anything. 

Connectors used:
mysql-connector-java-5.1.7 and mysql-connector-java-5.1.6 (both determined by printing the value of conn.getMetaData().getDriverVersion() to the screen)

Java version:
1.5.0_16 and 1.6.0_11 (determined by calling java expliclitly.  i.e. '/usr/lib/jvm/java-1...' instead of just 'java')

Server used: 5.0.45

Thank you for the report.

I can not repeat described behavior with current development version of MySQL server: test fails with:

1) testBug45859(bug45859)java.sql.SQLException: Field 'id' doesn't have a default value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
...

Please try with current version 5.0.83 and if problem still exists provide your server configuration file.

Sorry for the late reply.  Due to the details of my working environment, it is relatively complicated for me to set up another instance of MySQL.  I'm about to leave the job in which I found this bug and am pretty rushed for time so I won't be able to probe further.  Sorry about that.

My guess is that this is a bug with an older version of MySQL and that it has been corrected in later versions (as you couldn't replicate it).  The only reason why this may still be important is that as far as I know, MySQL 5.0.45 is the current version in the Redhat repositories (or at lease, it's the current one on my machine that is populated by said repositories).

Sorry I can't be of more assistance.  Hopefully this bug will not come to light again.

Being that Sveta's exception is expected and in line with correct behavior here I'm closing this as !Bg. However, I'll recheck original post in Bug#45859.