3

For a Spring project, the mysql-connector-java has been migrated from 6.0.6 to 8.0.11.

Thus with 8.0.11 the problem is the following:

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: 
The server time zone value 'PET' is unrecognized or represents more than one time zone. 
You must configure either the server or JDBC driver (via the serverTimezone configuration property) 
to use a more specifc time zone value if you want to utilize time zone support.

After to do a research

the solution is change the URL (I don't want return to a previous release)

  • from: mysql.jdbcUrl = jdbc:mysql://localhost:3306/web_v01?useSSL=false
  • to: mysql.jdbcUrl = jdbc:mysql://localhost:3306/web_v01?useSSL=false&serverTimezone=UTC

Observe the addition of &serverTimezone=UTC

In my DB I have the following:

mysql> select * from persona;
+-----+--------------+-------------+------------+
| id  | nombre       | apellido    | fecha      |
+-----+--------------+-------------+------------+
...
| 088 | Something    | Something   | 1981-07-06 |
...
+-----+--------------+-------------+------------+

When the Spring application does a retrieve from the db through the RowMapper<Persona> I can confirm that rs.getDate("fecha") returns 1981-07-05 (observe the day has been decreased by one, it is not correct)

If the mysql-connector-java returns to 6.0.6 and thus mysql.jdbcUrl = jdbc:mysql://localhost:3306/web_v01?useSSL=false (no serverTimezone=UTC) rs.getDate("fecha") returns 1981-07-06 (how is expected)

Thus how fix this working with 8.0.11?.

I want have the same behaviour when serverTimezone never was declared from the beginning, of course avoiding the exception.

Therefore the solution would be better if is take it in consideration that does not matter what value for serverTimezone was declared.

Manuel Jordan
  • 15,253
  • 21
  • 95
  • 158

3 Answers3

1

There are several attributes related to timezone:

useTimezone: Convert time/date types between client and server time zones (true/false, defaults to 'false')? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true." Default: false

useLegacyDatetimeCode: Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and statements that consistently handles time zone conversions from client to server and back again, or use the legacy code for these datatypes that has been in the driver for backwards-compatibility? Setting this property to 'false' voids the effects of "useTimezone," "useJDBCCompliantTimezoneShift," "useGmtMillisForDatetimes," and "useFastDateParsing." Default: true

serverTimezone: Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

If mysql-connector-java is 5.1, you should specify three attributes, like this: jdbc:mysql://host:port/dbname?useTimezone=true&useLegacyDatetimeCode=true&serverTimezone=GMT%2B08:00

If mysql-connector-java is 8.0, you should specify one attribute, like this: jdbc:mysql://host:port/dbname?serverTimezone=GMT%2B08:00

user10237001
  • 21
  • 1
1

Try to use

jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=CST
AS Mackay
  • 2,831
  • 9
  • 19
  • 25
Zhongxu Huang
  • 11
  • 1
  • 2
0

For my case the most unlikely suspect was jackson.

If your using Spring Boot add the following property to application.properties

spring.jackson.time-zone=Asia/Colombo

I've answered this with detail here -> https://stackoverflow.com/a/68016006/9183199

ThivankaW
  • 511
  • 1
  • 8
  • 21