MySQL Bugs: #95644: jdbc getdate/gettime/gettimestamp interface behavior change after upgrade 8.0

Bug #95644	jdbc getdate/gettime/gettimestamp interface behavior change after upgrade 8.0
Submitted:	5 Jun 2019 7:35	Modified:	12 Jan 2021 21:19
Reporter:	zheng hazheng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	jdbc;getdate

Description:
Hi,
1. I met this issue when I try to upgrade the jdbc driver from 5.0 to 8.0
2. In my application i use the resultset.getdate(columnindex) to get the date data from the driver, here i didn't pass the second cal value to the interface. after get the object , i use the Calendar.getInstance to get the default  calender to convert the object  into detail year,month,minutes values.
3. I reviewed the source code of jdbc from https://github.com/mysql/mysql-connector-j.git finally find in the 8.0  implementation, if i didn't pass the second cal info in those interfaces(getdate,gettime,gettimestamp), and didn't specific the serverTimezone in the connection string, it would use the dbserver's timezone as default which is totally different from the behavior of 5.0 version. In 5.0 if i didn't pass the cal, it would use the default cal of the client. 

How to repeat:
1. set database server's timezone to UTC.
2. run the test ut on UTC-5 timezone.
call the resultset.getdate(columnindex) resultset.gettime(columnindex) resultset.gettimestamp(columnindex) interface using 5.0 and 8.0 drivers.

Suggested fix:
I found the related logic was under configureTimezone in NativeProtocol.java, the comments of the funtion is  Configures the client's timezone if required,however, the default timezone was set to the server's.

Hello zheng,

Thank you for the report and feedback.
Could you please provide exact repeatable test case(java test file, create table and sample data, MySQL server version etc) to reproduce the issue at our end? Thank you.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

It's easy to recreate the symptom:

1. In /etc/mysql/my.cnf, set 'defult-time-zone' to '+00:00' which means to use UTC; (IMPORTANT: make sure that your OS is NOT working in UTC+0. Mine is in UTC+1.)

2. In a scheme, create a table with a column of type DATE/DATETIME;

3. Create a Java programme using mysql connector 8+ (I'm using mysql-connector-java-8.0.17.jar);

4. Insert a row to that table giving the DATE/DATETIME column a date/time; (I inserted '2020-01-10 00:00:00')

5. Query the table in the CLI and you should see the date in UTC+0 zone; (I saw '2020-01-09 23:00:00')

6. Query the table in the same Java programme. What date do you expect to get and actually get? (I expected the same date that I inserted, but I got '2020-01-09 23:00:00')

Let me know if there is anything I didn't clearly explain or misunderstood. Thanks.

Thank you for the feedback.
This looks similar to Bug #96276, but for now will verify it independently.

regards,
Umesh

== Java client on UTC+05:30 and MySQL Server with --default-time-zone='+00:00'

- CLI

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql>   CREATE TABLE `theTable` (
    ->     `theDate` DATETIME DEFAULT NULL
    ->   );
Query OK, 0 rows affected (0.03 sec)

mysql> create user 'ushastry'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'ushastry'@'%';
Query OK, 0 rows affected (0.00 sec)

-- java

import java.sql.*;

public class Bug95644 {

    /**
     * @param args the command line arguments
     */
  public static void main(String[] args) throws Exception {
      
    try {
        Class.forName("com.mysql.jdbc.Driver");      // for 5.x
        //Class.forName("com.mysql.cj.jdbc.Driver"); // for 8.x
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    Connection conn = DriverManager.getConnection("jdbc:mysql://xxxxx.no.oracle.com:3333/test?user=ushastry&password=&characterEncoding=UTF-8&useSSL=false");
    DatabaseMetaData meta = conn.getMetaData();
    System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
    System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
    
    Statement st = conn.createStatement();
    st.executeUpdate("INSERT INTO theTable (theDate) VALUES ('2020-01-10 00:00:00')");
    ResultSet rs = st.executeQuery("SELECT theDate FROM theTable");
    rs.next();
    Timestamp theDate = rs.getTimestamp("theDate"); // Exception is thrown.
    System.out.println(theDate);
  
    conn.close();
  }
    
}

-- C/J 8.0.18, MySQL Server 8.0.18 with --default-time-zone='+00:00'
ant -f C:\\Work\\MySQLJava\\Bug95644 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug95644\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug95644\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLJava\Bug95644\build\classes
compile:
run:
MySQL Server: 8.0.18
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.18 (Revision: fef2894d751d47223192b706977b4a5bc41e6be4)
2020-01-10 05:30:00.0
BUILD SUCCESSFUL (total time: 3 seconds)

mysql> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from theTable;
+---------------------+
| theDate             |
+---------------------+
| 2020-01-10 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

-- C/J 5.1.48, MySQL Server 8.0.18 with --default-time-zone='+00:00'

ant -f C:\\Work\\MySQLJava\\Bug95644 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug95644\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug95644\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLJava\Bug95644\build\classes
compile:
run:
MySQL Server: 8.0.18
Driver Name & Ver: MySQL Connector Javamysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa )
2020-01-10 00:00:00.0
BUILD SUCCESSFUL (total time: 3 seconds)

-- CLI

mysql> select * from theTable;
+---------------------+
| theDate             |
+---------------------+
| 2020-01-10 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Bug#80201 is closed as a duplicate of this one.

Posted by developer:
 
Added the following entry to the Connector.J 8.0.23 changelog: 

" After upgrading from Connector/J 5.1 to 8.0, the results of saving and then retrieving DATETIME and TIMESTAMP values became different sometimes. 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."

Posted by developer:
 
Corrected the changelog entry to the following: 

"After upgrading from Connector/J 5.1 to 8.0, the results of saving and then
retrieving DATETIME and TIMESTAMP values became different sometimes. 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 do 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."