Description:
Even with useLegacyDatetimeCode=false, the getString() method in Connector/J 5.1.46 does not retrieve the correct values from columns whose underlying MySQL type is DATETIME when the time zone of the Java client differs from the time zone of the MySQL server.
As expected, the setString() method does no time zone conversions on the value: the value stored in the database exactly matches the string value passed to setString(), regardless of the client and server time zones.
By contrast, the getString() method interprets the value stored in the database as being in the MySQL server time zone and converts it to a value in the Java client time zone. As a result, the string value retrieved with getString() does not match the string value originally stored with setString().
How to repeat:
Here is code for a test case:
package net.avax.cj.jdbc.tztest;
// Before running test for first time:
//
// CREATE DATABASE tztest;
// CREATE USER 'tztest'@'localhost' IDENTIFIED BY 'insecure';
// GRANT ALL PRIVILEGES ON tztest.* TO 'tztest'@'localhost' WITH GRANT OPTION;
import java.sql.*;
import java.util.TimeZone;
public class Main {
public static void main(String[] args) throws SQLException {
Connection c = DriverManager.getConnection("jdbc:mysql://localhost"
+ "/tztest?user=tztest&password=insecure&useSSL=false"
+ "&useLegacyDatetimeCode=false");
String jdbcDriverVersion = c.getMetaData().getDriverVersion();
Statement s = c.createStatement();
s.execute("SELECT @@time_zone");
ResultSet rs = s.getResultSet();
rs.next();
String serverTimeZone = rs.getString(1);
String clientTimeZone = TimeZone.getDefault().toZoneId().toString();
System.out.println("jdbcDriverVersion: " + jdbcDriverVersion);
System.out.println("serverTimeZone: " + serverTimeZone);
System.out.println("clientTimeZone: " + clientTimeZone);
s = c.createStatement();
s.execute("DROP TABLE IF EXISTS tztest");
s.execute("CREATE TABLE tztest (id INTEGER NOT NULL AUTO_INCREMENT,"
+ " as_timestamp DATETIME(3) NOT NULL,"
+ " as_string DATETIME(3) NOT NULL, PRIMARY KEY (id))");
Timestamp storedAsTimestamp = new Timestamp(System.currentTimeMillis());
String storedAsString = storedAsTimestamp.toString();
PreparedStatement ps = c.prepareStatement("INSERT INTO tztest("
+ "as_timestamp, as_string) VALUES (?,?)",
Statement.RETURN_GENERATED_KEYS);
ps.setTimestamp(1, storedAsTimestamp);
ps.setString(2, storedAsString);
ps.executeUpdate();
rs = ps.getGeneratedKeys();
rs.next();
int id = rs.getInt(1);
System.out.println("id: " + id);
System.out.println("storedAsTimestamp: " + storedAsTimestamp);
System.out.println("storedAsString: " + storedAsString);
ps = c.prepareStatement("SELECT as_timestamp, as_string FROM tztest"
+ " WHERE id = ?");
ps.setInt(1, id);
ps.execute();
rs = ps.getResultSet();
rs.next();
Timestamp retrievedAsTimestamp = rs.getTimestamp(1);
String retrievedAsString = rs.getString(2);
System.out.println("retrievedAsTimestamp: " + retrievedAsTimestamp);
System.out.println("retrievedAsString: " + retrievedAsString);
}
}
Here is a sample run of the test case code:
$ java -classpath .:$GRADLE_CACHE/mysql-connector-java-5.1.46.jar net.avax.cj.jdbc.tztest.Main
jdbcDriverVersion: mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e )
serverTimeZone: UTC
clientTimeZone: America/Los_Angeles
id: 1
storedAsTimestamp: 2018-03-20 16:02:18.613
storedAsString: 2018-03-20 16:02:18.613
retrievedAsTimestamp: 2018-03-20 16:02:18.613
retrievedAsString: 2018-03-20 09:02:18.613
$ mysql --user=tztest --password=insecure --database=tztest --execute='SELECT * FROM tztest;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------------------+-------------------------+
| id | as_timestamp | as_string |
+----+-------------------------+-------------------------+
| 1 | 2018-03-20 23:02:18.613 | 2018-03-20 16:02:18.613 |
+----+-------------------------+-------------------------+
Suggested fix:
Do not do any time zone conversions when the getString() method is used to read a value from a column whose underlying MySQL type is DATETIME.
Description: Even with useLegacyDatetimeCode=false, the getString() method in Connector/J 5.1.46 does not retrieve the correct values from columns whose underlying MySQL type is DATETIME when the time zone of the Java client differs from the time zone of the MySQL server. As expected, the setString() method does no time zone conversions on the value: the value stored in the database exactly matches the string value passed to setString(), regardless of the client and server time zones. By contrast, the getString() method interprets the value stored in the database as being in the MySQL server time zone and converts it to a value in the Java client time zone. As a result, the string value retrieved with getString() does not match the string value originally stored with setString(). How to repeat: Here is code for a test case: package net.avax.cj.jdbc.tztest; // Before running test for first time: // // CREATE DATABASE tztest; // CREATE USER 'tztest'@'localhost' IDENTIFIED BY 'insecure'; // GRANT ALL PRIVILEGES ON tztest.* TO 'tztest'@'localhost' WITH GRANT OPTION; import java.sql.*; import java.util.TimeZone; public class Main { public static void main(String[] args) throws SQLException { Connection c = DriverManager.getConnection("jdbc:mysql://localhost" + "/tztest?user=tztest&password=insecure&useSSL=false" + "&useLegacyDatetimeCode=false"); String jdbcDriverVersion = c.getMetaData().getDriverVersion(); Statement s = c.createStatement(); s.execute("SELECT @@time_zone"); ResultSet rs = s.getResultSet(); rs.next(); String serverTimeZone = rs.getString(1); String clientTimeZone = TimeZone.getDefault().toZoneId().toString(); System.out.println("jdbcDriverVersion: " + jdbcDriverVersion); System.out.println("serverTimeZone: " + serverTimeZone); System.out.println("clientTimeZone: " + clientTimeZone); s = c.createStatement(); s.execute("DROP TABLE IF EXISTS tztest"); s.execute("CREATE TABLE tztest (id INTEGER NOT NULL AUTO_INCREMENT," + " as_timestamp DATETIME(3) NOT NULL," + " as_string DATETIME(3) NOT NULL, PRIMARY KEY (id))"); Timestamp storedAsTimestamp = new Timestamp(System.currentTimeMillis()); String storedAsString = storedAsTimestamp.toString(); PreparedStatement ps = c.prepareStatement("INSERT INTO tztest(" + "as_timestamp, as_string) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); ps.setTimestamp(1, storedAsTimestamp); ps.setString(2, storedAsString); ps.executeUpdate(); rs = ps.getGeneratedKeys(); rs.next(); int id = rs.getInt(1); System.out.println("id: " + id); System.out.println("storedAsTimestamp: " + storedAsTimestamp); System.out.println("storedAsString: " + storedAsString); ps = c.prepareStatement("SELECT as_timestamp, as_string FROM tztest" + " WHERE id = ?"); ps.setInt(1, id); ps.execute(); rs = ps.getResultSet(); rs.next(); Timestamp retrievedAsTimestamp = rs.getTimestamp(1); String retrievedAsString = rs.getString(2); System.out.println("retrievedAsTimestamp: " + retrievedAsTimestamp); System.out.println("retrievedAsString: " + retrievedAsString); } } Here is a sample run of the test case code: $ java -classpath .:$GRADLE_CACHE/mysql-connector-java-5.1.46.jar net.avax.cj.jdbc.tztest.Main jdbcDriverVersion: mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) serverTimeZone: UTC clientTimeZone: America/Los_Angeles id: 1 storedAsTimestamp: 2018-03-20 16:02:18.613 storedAsString: 2018-03-20 16:02:18.613 retrievedAsTimestamp: 2018-03-20 16:02:18.613 retrievedAsString: 2018-03-20 09:02:18.613 $ mysql --user=tztest --password=insecure --database=tztest --execute='SELECT * FROM tztest;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+-------------------------+-------------------------+ | id | as_timestamp | as_string | +----+-------------------------+-------------------------+ | 1 | 2018-03-20 23:02:18.613 | 2018-03-20 16:02:18.613 | +----+-------------------------+-------------------------+ Suggested fix: Do not do any time zone conversions when the getString() method is used to read a value from a column whose underlying MySQL type is DATETIME.