CODEWITHSUNDEEP
javamysqljdbc
Amy
Amy

Reputation: 95

Getting incorrect value for timestamp column with MySQL 8 server using mysql-connector-java jdbc driver 8.0.18

We have migrated our application from MySQL 5.1.6 to MySQL 8.0.16

While retrieving timestamp column from MySQL 8 server using mysql-connector-java jdbc driver (version 8.0.18, 8.0.19), we are getting incorrect value for the date value which is in daylight saving time (DST).

Both our server hosting java application & MySQL database are in EST.

Here, we have a column created_date having type datetime. It has value '2020-03-17 23:01:54' for a specific row. While retrieving same data through JDBC as: 

Timestamp timestamp = resultSet.getTimestamp("created_date");

we are getting value as 2020-03-18 00:01:54.0 i.e. DST is getting applied.

There is a known bug in MySQL: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitations.html

It suggest to use add additional parameter in jdbc url serverTimezone=UTC. But we can't use UTC because we want to use deployment specific timezone in our application/database.

Note: We were not facing this issue with MySQL 5.1.

Additional update: While inserting data into database, If I use jdbc url with parameter serverTimezone=UTC, then while retrieving timestamp column I get correct value.

So it seems with serverTimezone=UTC, JDBC driver is not applying DST while storing timestamp in database. So workaround suggested (i.e. serverTimezone=UTC) seems to be working.

But If I insert data using Load Data Local file command using JDBC url containing serverTimezone=UTC i.e.

PreparedStatement stmt = connection.prepareStatement("LOAD DATA LOCAL INFILE.....")

it seems serverTimezone=UTC flag is getting ignored & data is getting stored with DST applied timestamp value. So while retrieving timestamp, we are getting incorrect value.

Appreciate any suggestions.

Upvotes: 4

Views: 1477

Answers (1)

Bárbara Costa
Bárbara Costa

Reputation: 21

I had the same problem, and solved it by installing the new version (8.0.20) of Connector/J 8 driver: https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-20.html

Upvotes: 2

Related Questions