Mysql datetime becomes 0000-00-00 00:00:00 in mysql version 5.6

Question

We have a java-based program containing some hundred thousands lines of code that has worked perfectly the past 8-9 years before and up to mysql 5.5. A customer has installed mysql 5.6.17 and now we face a big problem: Datetime values becomes 0000-00-00 00:00:00 Here is one of the tables: As you can see, the default is Null:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Key1  | varchar(10) | NO   | MUL | NULL    |                |
| Key2  | varchar(25) | NO   |     | NULL    |                |
| Date  | datetime    | YES  |     | NULL    |                |
| Value | text        | NO   |     | NULL    |                |

And we insert like this through mysql-connector in the java-code:

String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES(?,?,?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setString(1, i.getKey1());
p.setString(2, i.getKey2());
p.setTimestamp(3, i.getDate()==null?null:new Timestamp(i.getDate().getTime()));
p.setString(4, i.getValue());
p.executeUpdate();

I can take a system out println on the Preparedstatement to see the sql before executeupdate and it looks like this:

com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 14:46:15','')

But the result is still '0000-00-00 00:00:00 in the table. Why is that?? The really strange thing is that if I copy/paste the insert query to the mysql console, the datetime value is correctly set. I have tried upgrading to mysql-connector-java-5.1.31-bin.jar with no luck. SELECT @@sql_mode; gives NO_ENGINE_SUBSTITUTION Alas, we cannot set it to strict mode due to a huge number of legacy tables and code. I don't think ?zeroDateTimeBehavior=convertToNull can help, as we don't want null-values, we want the actual values, and they somehow become converted to 0000-00-00 even though NULL is the default for the table.. (By the way, the same happens for updates) What on earth is wrong in this mysql-setup?

EDIT: I have some more info. I have now extended my code with the following:

System.out.println(p);
p.executeUpdate();
SQLWarning warning = p.getWarnings();
while (warning != null){
    System.out.println("ToString(): "+warning.toString());
    System.out.println("ErrorCode: "+warning.getErrorCode());
    System.out.println("LocalizedMessage: "+warning.getLocalizedMessage());
    System.out.println("State: "+warning.getSQLState());
    warning = warning.getNextWarning();
}

The output is:

com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')
ToString(): java.sql.SQLWarning: Data truncated for column 'Date' at row 1
ErrorCode: 1265
LocalizedMessage: Data truncated for column 'Date' at row 1
State: 01000

So I actually get a data truncation warning, even though I use preparedstatements setTimeStamp correctly (as of all the other mysql versions we have tried) and the sql seems ok when printing the PreparedStatement object before executing it. Why then the reason for the data truncation?

EDIT#2 I was told to try setDate instead of setTimestamp to see if that gives the same error. It does not, but I cannot use setDate since I then don't get the time component of the DATETIME, but only the date. As I said, the System.out.println of the prepared PreparedStatement gives "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')" which is valid sql and works without warnings and truncations when copying it to the mysql console. I have now also tried this sql directly by the following code:

String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.executeUpdate();

This also gives correct result with no warnings and no truncations. It is only when I use setTimestamp on my PreparedStatement that the truncation occurs. I have tried with both old and new versions of the jconnector, 3.1.10, 5.1.18 and 5.1.31. All versions work ok with my old mysql-versions, and produce this peculiar bug in mysql 5.6.17.

EDIT#3 I noticed that setTimestamp(1, new Timestamp(0)); gave no truncation, but the correct date for january 1970. I therefore made some litte testcode:

//CREATE TABLE `test` (Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `Test` DATETIME NULL DEFAULT NULL);
String sql = "INSERT INTO Test (Id, Test) VALUES (?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
long current = System.currentTimeMillis();
for (long t = 0; t < current; t += (long)100000000){
    p.setLong(1,t);
    p.setTimestamp(2, new Timestamp(t));
    p.executeUpdate();
}
p.close();
p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setLong(1,current);
p.setTimestamp(2, new Timestamp(current));
p.executeUpdate();
p.close();

This gives only one incorrect result, the last one. SELECT * FROM test gives the following last rows:

| Id            | Test                |
+---------------+---------------------+
....
| 1407000000000 | 2014-08-02 19:20:00 |
| 1407100000000 | 2014-08-03 23:06:40 |
| 1407200000000 | 2014-08-05 02:53:20 |
| 1407224107714 | 0000-00-00 00:00:00 |

EDIT#4 It seems that there is a bug that if there exists sub-second precision in the timestamp when setting setTimestamp, then instead of truncating the nanoseconds, the whole datetime gets destroyed. It seems to work ok if I do the following:

p.setTimestamp(2, new Timestamp(current/1000*1000));

I will then get the result

| 1407225888000 | 2014-08-05 10:04:48 |

I will probably write up this as a solution soon. But I will have to fix a lot of setTimestamp-values in our code. Can anyone confirm if this is still a bug in mysql databases after 5.6.17?

EDIT#5 (SOLUTION) TheConstructors suggestion of useServerPrepStmts=false solves the issue! And is a much better solution that my own /1000*1000 -solution that I found, as I only have to change the connection String instead of every setTimestamp-call (ok, I have allready done this the last hour, but nice to have the 'real' solution too ;-) )

Answer 1

As the server side prepared statements are disabled by default from Connector/J 5.0.5 on, there is a fair chance that combined with the introduction of fractional second support in MySQL 5.6.4 they cause your problem.

Try adding useServerPrepStmts=false to your connection string (which should also ensure, that System.out.println really prints the SQL which is executed and not just an approximation).