Bug #75956 Inserting timestamps using a PreparedStatement and useLegacyDatetimeCode=false
Submitted: 18 Feb 2015 14:28 Modified: 30 Mar 2016 20:04
Reporter: Jörg Eichhorn Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.34 OS:Any
Assigned to: Jess Balint CPU Architecture:Any

[18 Feb 2015 14:28] Jörg Eichhorn
Description:
When inserting multiple timestamps into one table with disabled legacy datetime code (useLegacyDatetimeCode=false) and enabled cursor fetching (useCursorFetch=true) further timestamps are null, when a timestamp in the previous insert was null.

How to repeat:
Table:
create table test (
    id int not null primary key auto_increment,
    dt1 datetime null,
    dt2 datetime null
);

JDBC URL: 
    jdbc:mysql://localhost/test?useCursorFetch=true&useLegacyDatetimeCode=false

Code snippet:
PreparedStatement stmt = con.prepareStatement("insert into test (dt1, dt2) values (?, ?)");
stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
stmt.addBatch();

stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
stmt.setTimestamp(2, null);
stmt.addBatch();

stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
stmt.addBatch();

stmt.close();

Expected result:
+----+---------------------+---------------------+
| id | dt1                 | dt2                 |
+----+---------------------+---------------------+
|  1 | 2015-02-18 15:27:30 | 2015-02-18 15:27:30 |
|  2 | 2015-02-18 15:27:30 | NULL                |
|  3 | 2015-02-18 15:27:30 | 2015-02-18 15:27:30 |
+----+---------------------+---------------------+

Actual result:
+----+---------------------+---------------------+
| id | dt1                 | dt2                 |
+----+---------------------+---------------------+
|  1 | 2015-02-18 15:27:09 | 2015-02-18 15:27:09 |
|  2 | 2015-02-18 15:27:09 | NULL                |
|  3 | 2015-02-18 15:27:09 | NULL                |
+----+---------------------+---------------------+
[18 Feb 2015 14:29] Jörg Eichhorn
Sample JUnit Test to reproduce the problem

Attachment: MySQLConnectorJTest.java (application/octet-stream, text), 5.15 KiB.

[18 Feb 2015 14:36] Jörg Eichhorn
The code snipped missed the call of 'stmt.executeBatch();' - this is part of the unit test.
[18 Feb 2015 20:27] Jörg Eichhorn
Possible fix for the problem

Attachment: ServerPreparedStatement.diff (application/octet-stream, text), 866 bytes.

[11 Mar 2015 12:20] Filipe Silva
Hi Jörg,

Thank you for this bug report. Is was verified as described.
[8 Jul 2015 12:04] Jörg Eichhorn
Is the a plan to fix this issue?
Without the fix there is a data loss when using prepared statements and new date time code.
[27 Oct 2015 17:08] Jörg Eichhorn
Any news on this issue?
[30 Mar 2016 20:04] Daniel So
Added the following entry to the Connector/J 5.1.39 changelog:

"When inserting multiple timestamp values into a table with useLegacyDatetimeCode=false and useCursorFetch=true, after a null value had been inserted, further inserts could not change a timestamp's value. This fix makes sure the binding of the value is reset before a new insert takes place."