Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
1.5.7
-
None
-
Windows 10, Server version: 5.5.53-MariaDB mariadb.org binary distribution. java version "1.8.0_45" (Stockholm time zone)
Description
Background:
I wrote the ticket https://jira.mariadb.org/browse/CONJ-109 a couple of years ago. Back then my opinion was that MariaDB JDBC connector (1.1.7) handled timestamp in a fragile way.
More specifically:
Since TIMESTAMP column data is transferred between JDBC and the server in plain text for example “2017-02-15 10:32:15” care must be taken in what time zone/daylight savings time this String is interpreted. My opinion then (and now) is that the JDBC driver should handle such details for the developer for all data types that are time zone aware on the server. The data type TIMESTAMP is timezone aware since it internally always is stored in UTC in the SQL server. Therefore, my opinion, is that a developer never should bother with specifying timezone information. The methods ResultSet.getTimestamp(int) and PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) returns and accept (respectively) a Java object that is timezone aware (java.sql.Timestamp/ java.util.Date that both internally stores times as UTC). Because of this, both sides are time zone aware and (again my opinion) the developer should not be bothered with time zone details. If the developer want a present a Date-object (to the end user) from a ResulSet in a different timezone than UTC he is free to use a DateFormater/Calender object like normal.
I had some discussions with developers at MariaDB regarding ticket CONJ-109 and finally it was rejected. I found a suitable workaround: By adding
&serverTimezone=UTC&sessionVariables=time_zone='+00:00' |
as an URL parameter both the JDBC connector and the server both interpret the date String in UTC timezone and the developer does not need take any special time zone care when using ResultSet/ PreparedStatement.
Problem:
Since that time (version 1.1.7) much has happened in the JDBC connector. In version 1.5.7 my workaround no longer work. The JDBC code that parses/formats timetstamp in both SELECTs and INSERTs have been re-written. More specifically: the JDBC serverTimezone parameter is now effectively ignored in the parsing methods (unless another parameter also is specified).
Workaround:
By studying the source code I have found a workaround to add to my previous workaround:
&serverTimezone=UTC&useLegacyDatetimeCode=false&sessionVariables=time_zone='+00:00' |
By adding useLegacyDatetimeCode=false the JDBC once again respects the serverTimezone parameter.
To do for MariaDB:
If you don’t want to make any drastic changes to the time zone parsing code or once again change what JDBC parameter that exist I hope that you can perform these tasks:
- Properly document the useLegacyDatetimeCode parameter on https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/. Currently it is only (semi-hidden) documented on https://mariadb.com/kb/en/mariadb/mariadb-connector-j-130-release-notes/. I feel it is important that the documentation should clearly state that there is a dependency between useLegacyDatetimeCode=false and serverTimezone (that serverTimezone cannot be used unless useLegacyDatetimeCode is set to false).
- Add a JUnit test case with this combination of parameters that will break if you in the future changes this (so that the code I work in will not break with future updates of JDBC). Feel free to use my test case I have written (to be inserted into https://github.com/MariaDB/mariadb-connector-j/blob/master/src/test/java/org/mariadb/jdbc/DateTest.java)
/** |
* Tests that formatting and parsing of TIMESTAMP sent as text Strings between JDBC and server
|
* is handled correctly when JDBC and server agrees to communicate in a specific time zone in
|
* the session/connection, different from both what JVM and server is running.
|
*/
|
@Test |
public void testClientServerSessionTimeZone() throws SQLException, ParseException { |
createTable("connection_session_timestamp", "name varchar(40), birth_ts timestamp"); |
|
|
TimeZone utcTimeZone = TimeZone.getTimeZone("utc"); |
SimpleDateFormat utcDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); |
utcDateFormat.setTimeZone(utcTimeZone);
|
|
|
java.util.Date aliceBirthDate = utcDateFormat.parse("2017-11-24 10:32:00"); |
java.util.Date bobBirthDate = utcDateFormat.parse("2016-12-25 16:44:00"); |
|
Connection gmtMinus2Connection = null; |
Connection utCconnection = null; |
|
try { |
//Create a baseline connection object that uses UTC time zone. This connection is used |
//to manually parse/format UTC time Strings without interference of JDBC |
utCconnection = setConnection("&sessionVariables=time_zone='+00:00'"); |
|
|
//Create a row in the database by manually formatting |
//the TIMESTAMP in UTC so we are sure the server has it stored correctly |
String sql = "insert into connection_session_timestamp (name, birth_ts) VALUES('Alice', '" + |
utcDateFormat.format(aliceBirthDate) + "')"; |
utCconnection.createStatement().executeUpdate(sql);
|
|
|
String parameters = ""; |
|
//GMT -2 hours in the Atlantic Ocean is very sparsely populated so it is unlikely that |
//a developer/integration server for MariaDB has this time zone set for either JUnit JVM |
//or MariaDB server. This makes it very likely that JUnit JVM and MariaDB server is run |
//in a different time zone than session time zone. This makes it more likely for this |
//test case to catch problems. |
//This parameter makes the server talk in the specified /time zone. |
parameters += "&sessionVariables=time_zone='-02:00'"; |
|
//Note: Because of a "feature" it is not correct to specify "Etc/GMT-2" for a Java |
//Timezone when you mean GMT -2, instead a real place is used for clarity. For more info |
//see https://bugs.openjdk.java.net/browse/JDK-4813746 or |
//https://github.com/moment/moment-timezone/issues/167 |
//This parameter makes the JDBC client code talk in the specified time zone (must be the |
//same as 'time_zone' above. |
parameters += "&serverTimezone=Atlantic/South_Georgia"; |
|
|
//Without this parameter the serverTimezone does not take effect. This test case may |
//(should?) fail if it is changed. |
parameters += "&useLegacyDatetimeCode=false"; |
|
|
gmtMinus2Connection = setConnection(parameters);
|
|
//Test PreparedStatement.setTimestamp() (without Calendar parameter) |
PreparedStatement preparedStatement = gmtMinus2Connection.prepareStatement(
|
"insert into connection_session_timestamp (name, birth_ts) VALUES(?, ?)"); |
preparedStatement.setString(1, "Bob"); |
preparedStatement.setTimestamp(2, new Timestamp(bobBirthDate.getTime())); |
preparedStatement.executeUpdate();
|
|
|
//Test ResultSet.getTimestamp() method (without Calendar parameter) |
ResultSet resultSet = gmtMinus2Connection.createStatement()
|
.executeQuery("select * from connection_session_timestamp"); |
resultSet.next();
|
assertEquals("Alice", resultSet.getString(1)); |
assertEquals(aliceBirthDate, new java.util.Date(resultSet.getTimestamp(2).getTime())); |
resultSet.next();
|
assertEquals("Bob", resultSet.getString(1)); |
assertEquals(bobBirthDate, new java.util.Date(resultSet.getTimestamp(2).getTime())); |
|
|
|
//Verify that values has been correctly inserted into the database using UTC and |
//manual String parsing of timestamp |
resultSet = utCconnection.createStatement()
|
.executeQuery("select * from connection_session_timestamp"); |
resultSet.next();
|
assertEquals("Alice", resultSet.getString(1)); |
assertEquals(utcDateFormat.format(aliceBirthDate),
|
resultSet.getString(2).replaceAll("\\.0", "")); |
resultSet.next();
|
assertEquals("Bob", resultSet.getString(1)); |
assertEquals(utcDateFormat.format(bobBirthDate),
|
resultSet.getString(2).replaceAll("\\.0", "")); |
} finally { |
gmtMinus2Connection.close();
|
utCconnection.close();
|
}
|
}
|