| Bug #109929 | after 8.0.23 upgrade, server time is not converting to JVM timezone | ||
|---|---|---|---|
| Submitted: | 3 Feb 21:39 | Modified: | 16 Mar 4:54 |
| Reporter: | Rajesh Kumar | Email Updates: | |
| Status: | Analyzing | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | OS: | Any | |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[21 Feb 12:52]
MySQL Verification Team
Hello Rajesh Kumar, Thank you for the bug report. Could you please provide below info? 1. Exactly what database types are you talking about? 2. What's your server configured time zone? 3. What's your client/JVM time zone? 4. An example of a value as seen via mysql client running in the server (it's important to run the mysql client in the same machine as the server) 5. How the same value is being returned to the client? 6. A simple java test code that returns such value And, obviously, the specific connection setting you're testing with regard to connectionTimeZone, forceConnectionTimeZoneToSession and preserveInstants configurations. Please do not use any third party component/framework when doing the simple test case. Just plain Java and MySQL Connector/J. Regards, Ashwini Patil
[15 Mar 23:59]
Rajesh Kumar
Hi Ashwini,
Here are my answers.
1) We are connecting to MySQL database.
2) Database timezone is PST
3) JVM timezone is UTC
4) Yes when we see value of time on server it is same as database.
5) The value of datetime field in database is "2023-02-27T00:21:35". When we are using 8.0.17 connector we are getting the converted value "2023-02-27T08:21:35". You can see 8 added to hours and when we are using 8.0.23 connector or above version we are getting the same value without time zone conversion "2023-02-27T00:21:35. We are passing the connection values(preserveInstants=true&forceConnectionTimeZoneToSession=false) in connection also as suggested in documentation to get the converted value. "jdbc:mysql://localhost:3306/db1?connectionTimeZone=SERVER&preserveInstants=true&forceConnectionTimeZoneToSession=false"
6) Here is the java code.
import java.sql.*;
class MySQLTest{
public static void main(String args[]) {
new MySQLTest().MySQLDatabase_Date_Test();
}
public void MySQLDatabase_Date_Test(){
{
System.out.println("------Start MySQLTest Test------");
Connection con= null;
ResultSet rs= null;
try{
//Class.forName("com.mysql.jdbc.Driver");
con= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db1?connectionTimeZone=SERVER&preserveInstants=true&forceConnectionTimeZoneToSession=false","root","******");
Statement stmt=con.createStatement();
rs=stmt.executeQuery("SELECT * FROM Fruit");
while(rs.next()){
System.out.println("Date : " + rs.getObject(2));
}
System.out.println();
}catch(Exception e){
System.out.println(e);
} finally {
try {
if(con !=null) {
con.close();
}
if(rs !=null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.gc();
}
}
Description: We are using MySQL Connector/J 8.0.17 version. When we are upgrading it to 8.0.23 or higher we are facing the date time issue. Below 8.0.23 version it is returning the date after changing to timezone. But after using 8.0.23 or higher version. It is returning the same Database value for time. We are using this connector in mulesoft and tried all settings. But we are getting the same value as in database and it is not changing it to Jvm timezone. Please help here. What we are missing. We are calling the stored procedure which is returning the date time. <db:config name="DB_PFM_Configuration" doc:name="Database Config" doc:id="6890ff78-7ae7-4bd7-99aa-04e79d840480" > <db:my-sql-connection host="${PFM.serverName}" port="${PFM.port}" user="${secure::PFM.userName}" password="${secure::PFM.password}" database="${PFM.databaseName}" > <db:pooling-profile maxPoolSize="${PFM.pooling.maxPoolSize}" minPoolSize="${PFM.pooling.minPoolSize}" maxWait="${PFM.pooling.maxWaitMillis}" maxWaitUnit="MILLISECONDS" > </db:pooling-profile> <db:connection-properties > <db:connection-property key="noAccessToProcedureBodies" value="true" /> <db:connection-property key="connectionTimeZone" value="SERVER" /> <db:connection-property key="forceConnectionTimeZoneToSession" value="false" /> <db:connection-property key="preserveInstants" value="true" /> </db:connection-properties> </db:my-sql-connection> <expiration-policy /> </db:config> How to repeat: But by setting 3 connection properties connectionTimeZone, forceConnectionTimeZoneToSession and preserveInstants. It is not changing the server date time value to JVM datetime zone value.