MySQL Bugs: #98695: Execution of "LOAD DATA LOCAL INFILE" command through JDBC for datetime column

Bug #98695	Execution of "LOAD DATA LOCAL INFILE" command through JDBC for datetime column
Submitted:	20 Feb 2020 16:26	Modified:	12 Jan 2021 19:19
Reporter:	amod m	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.18, 8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Retrieval of Datetime column value for data ingested through LOAD DATA LOCAL INF

Description:
We have migrated our application from MySQL 5.1.6 to MySQL 8.0.16
Both our server hosting java application & MySQL database are in same timezone.
Note: We were not facing this issue with MySQL 5.1.x server with mysql-connector-java driver 5.1.6

We are using LOAD DATA LOCAL INFILE  command through JDBC for inserting data into table. While retrieving datetime/timestamp column data through JDBC, we are getting incorrect value for the date value which is in between start & end of DST. e.g. Between 10th March 2019 to 3rd November 2019

We are aware of known bug in MySQL: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitatio.... 

Even after adding "serverTimezone=UTC" in jdbc url, we are getting incorrect value.

How to repeat:
1. mysql-connector-java: 8.0.18

2. Table creation Script:
  select * from time_CREATE TABLE `time_test` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `ts` timestamp NULL DEFAULT NULL,
     `dt` datetime DEFAULT NULL,
      `date_input_str` varchar(45) DEFAULT NULL,
     PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

3. Data File: data.csv
2019-03-09 23:01:54,2019-03-09 23:01:54,2019-03-09 23:01:54
2019-03-10 02:00:00,2019-03-10 02:00:00,2019-03-10 02:00:00
2019-03-10 23:01:54,2019-03-10 23:01:54,2019-03-10 23:01:54
2019-03-11 23:01:54,2019-03-11 23:01:54,2019-03-11 23:01:54
2019-04-13 23:01:54,2019-04-13 23:01:54,2019-04-13 23:01:54
2019-11-02 23:01:54,2019-11-02 23:01:54,2019-11-02 23:01:54
2019-11-03 02:00:00,2019-11-03 02:00:00,2019-11-03 02:00:00
2019-11-03 01:00:00,2019-11-03 01:00:00,2019-11-03 01:00:00
2019-11-03 03:00:00,2019-11-03 03:00:00,2019-11-03 03:00:00
2019-11-03 04:00:00,2019-11-03 04:00:00,2019-11-03 04:00:00
2019-11-03 23:01:54,2019-11-03 23:01:54,2019-11-03 23:01:54
2019-11-21 23:01:54,2019-11-21 23:01:54,2019-11-21 23:01:54

4. test code:

//JDBC_URL = "jdbc:mysql://dbserver:3306/dbname1?allowLoadLocalInfile=true
public static void testLoadInFile(){

        File dataFile = new File("../data.csv");
        StringBuilder loadQuery = new StringBuilder();
        loadQuery.append("LOAD DATA LOCAL INFILE '").append(dataFile.getAbsolutePath()).
                append("' INTO TABLE time_test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' " +
                        " (ts,dt,date_input_str)");
        PreparedStatement preparedStmt = null;
        try (Connection connection = getConnection()){
             preparedStmt = connection.prepareStatement(loadQuery.toString());

             connection.setAutoCommit(false);
             preparedStmt.execute(loadQuery.toString());
             connection.commit();

             preparedStmt = connection.prepareStatement("select ts, dt, name from time_test");
             ResultSet resultSet = preparedStmt.executeQuery();

             while(resultSet.next()) {
                Object ts = resultSet.getObject("ts");
                Object dt = resultSet.getObject("dt");
                String name = resultSet.getString("date_input_str");
                System.out.print("ts:"+ ts + " ,");
                System.out.print("dt:"+ dt +", ");
                System.out.println("name:"+ name);

            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            if(preparedStmt!= null) {
                try {
                    preparedStmt.close();
                } catch (Exception e) {
                }
            }
        }
    }

5. Check difference in output for columns date_input_str & ts/dt column

Output
ts:2019-03-09 23:01:54.0 ,dt:2019-03-09 23:01:54.0, name:2019-03-09 23:01:54
ts:2019-03-10 04:00:00.0 ,dt:2019-03-10 03:00:00.0, name:2019-03-10 02:00:00
ts:2019-03-11 00:01:54.0 ,dt:2019-03-11 00:01:54.0, name:2019-03-10 23:01:54
ts:2019-03-12 00:01:54.0 ,dt:2019-03-12 00:01:54.0, name:2019-03-11 23:01:54
ts:2019-04-14 00:01:54.0 ,dt:2019-04-14 00:01:54.0, name:2019-04-13 23:01:54
ts:2019-11-03 00:01:54.0 ,dt:2019-11-03 00:01:54.0, name:2019-11-02 23:01:54
ts:2019-11-03 02:00:00.0 ,dt:2019-11-03 02:00:00.0, name:2019-11-03 02:00:00
ts:2019-11-03 01:00:00.0 ,dt:2019-11-03 01:00:00.0, name:2019-11-03 01:00:00
ts:2019-11-03 03:00:00.0 ,dt:2019-11-03 03:00:00.0, name:2019-11-03 03:00:00
ts:2019-11-03 04:00:00.0 ,dt:2019-11-03 04:00:00.0, name:2019-11-03 04:00:00
ts:2019-11-03 23:01:54.0 ,dt:2019-11-03 23:01:54.0, name:2019-11-03 23:01:54
ts:2019-11-21 23:01:54.0 ,dt:2019-11-21 23:01:54.0, name:2019-11-21 23:01:54

e.g.
ts:2019-04-14 00:01:54.0 ,dt:2019-04-14 00:01:54.0, name:2019-04-13 23:01:54

Appreciate any suggestions.

Java test code

Attachment: TestLoadInFileMySql8.java (text/x-java), 2.93 KiB.

Hello amod m,

Thank you for the report and feedback.

regards,
Umesh

Test results - 8.0.19, 5.1.48

Attachment: 98695.results (application/octet-stream, text), 6.91 KiB.

Posted by developer:
 
Added the following entry to the C/J 8.0.23 changelog:

"After upgrading from Connector/J 5.1 to 8.0, timestamps for a period when Daylight Saving Time was in effect were incorrect when they were saved and then retrieved from the server by Connector/J. It was because while Connector/J 5.1 does not preserve a time instant by default, Connector/J 8.0.22 and earlier tried to so by converting a timestamp to the server's session time zone before sending its value to the server. In this release, new mechanisms for controlling timezone conversion has been introduced—see Preserving Time Instants for details. Under this new mechanism, the default behavior of Connector/J 5.1 in this respect is preserved by setting the connection property preserveInstants=false."