MySQL data corruption… useServerPrepStmts=false

I’ve undertaken a new project, which uses Hibernate, MySQL, Spring, Maven2 and a few other technologies. During this, I’ve stumbled on a bug, which I first though came from Hibernate, but later discovered to be a MySQL issue.

As for any bug, the first thing to do is check the obvious: was my code right? Did I use properly the libraries that I included in my project? To be sure of that, I simplified my code, until having a very simple case of Hibernate usage. I then traced it with the Eclipse debugger.

Maven2 did automatically download the source code of Hibernate 3.2.1 for me, and put it where Eclipse can find it, thanks to the command:

mvn -DdownloadSources=true eclipse:eclipse 

This doesn’t work for all libraries: it depends if the source code has been properly referenced to the maven repository that you use (by default IBiblio). It did work here, and was pretty convenient.

Debugging this, I could see Hibernate preparing a PreparedStatement, correctly binging values to it, and then executing it. This execution didn’t do what it was supposed to do, but that code is in the MySQL Connector/J.

To make a long story short, I made a very simple test case

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
		"jdbc:mysql://localhost/dbName",
		"userName","passwd");

PreparedStatement stmt=con.prepareStatement(
		"insert into project (code,x) values (?,?)");
stmt.setString(1, "test2");
stmt.setInt(2,17);

stmt.execute();
con.close();

Well, this doesn’t work. If fact, here is what I got:

mysql> select * from project;
+----+------+----------+
| id | code | x        |
+----+------+----------+
|  1 |      | 50396417 |
+----+------+----------+
2 rows in set (0.00 sec)

So, MySQL lost my String, and corrupted my integer!

I simplified this a bit more to get the next code, which ran as expected

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
		"jdbc:mysql://localhost/dbName",
		"userName","passwd");
Statement stmt2=con.createStatement();
stmt2.execute("insert into project (code,x) values ("ok",43)");
con.close();

Then, carefull reading of MySQL Connector/J documentation lead me to this:

Server-side Prepared Statements - Connector/J 3.1 will automatically detect and use server-side prepared statements when they are available (MySQL server version 4.1.0 and newer). If your application encounters issues with server-side prepared statements, you can revert to the older client-side emulated prepared statement code that is still presently used for MySQL servers older than 4.1.0 with the following connection property:

useServerPrepStmts=false

Changing my connection URL to

jdbc:mysql://localhost/dbName?useServerPrepStmts=false

did correct everything, as well with the PreparedStatement as with Hibernate.

Still, I find it incredible that a project like MySQL documents (poorly) such a bug, instead of fixing it. Dropping data! Corrupting data silently! Was this a joke? What can justify this? Why did I lose 6 hours on this?

Yeah, I’m a bit bitter.

For the reference, here are the versions of software that I was using (all default setups unless specified):

• Distribution:KUbuntu 6.10
• MySQL: 4.1.15-Debian_1ubuntu5-log
• MySQL Connector/J:5.0.4