Home / Questions / 342777
Accepted
hecnabae
Asked: 2020-04-03 02:53:36 +0800 CST 2020-04-03 02:53:36 +0800 CST

Error HOUR_OF_DAY: 2 -> 3 when executing a query in MySQL

  • 772

It's a bit of a weird bug. When executing a query on certain tables of a MySQL database from the Java driver, the system throws the following error:

java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3
at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2829)
at java.util.Calendar.updateTime(Calendar.java:3393)
at java.util.Calendar.getTimeInMillis(Calendar.java:1782)
at com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:97)
at com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:44)
...

However, when we run the same query on the Workbench, the query executes successfully.

We are using version 8.0.17 of mysql. Regarding the java driver, we are using version 8.0.17.

In this post we have seen that it may be a problem with the configuration of the serverTimeZone parameter of the database, however, in the connection string we already specified the timezone as Europe/Madrid:

<Resource name="jdbc/mysqldb" auth="Container" type="javax.sql.DataSource"
           maxTotal="200" maxIdle="30" maxWaitMillis="3000"
           username="..." password="..." driverClassName="com.mysql.cj.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/bbdd"
           connectionProperties="zeroDateTimeBehavior=CONVERT_TO_NULL;useSSL=false;characterEncoding=utf8;useUnicode=true;useJDBCCompliantTimezoneShift=true;useLegacyDatetimeCode=false;serverTimezone=Europe/Madrid"/>
java

1 Answers

  1. Best Answer

    After verifying that it is not a problem with the configuration of the serverTimeZone parameter , it is possible that said date is not correct ( see post ).

    In our case, the problem was that there was a date field whose value coincided in date and time with the date and time of the daylight saving time change, that is, "03/29/2020 02:00:00", hence the error HOUR_OF_DAY: 2 -> 3 . So the solution was to simply change that value to "03/29/2020 03:00:00".

    Apparently it is a bug in the Java driver , although there is a preventive option ( see post ), which involves setting the lenient parameter of Calendar to false, so that if you try to insert an incorrect date and time value, an exception is thrown . Calendar by default is permissive with the dates that we introduce, so if we tried to load an incorrect date, for example January 32, internally it would try to correct it to February 1 ( see link ).

    • 1