Error : SqlExceptionHelper : HOUR_OF_DAY: 2 -> 3

Question

Full error log:

2019-09-20 08:35:37.860 INFO 1 --- [nio-8081-exec-1] o.a.c.c.C.[Tomcat-1].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'

2019-09-20 08:47:29.726 ERROR 1 --- [nio-8081-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : HOUR_OF_DAY: 2 -> 3

2019-09-20 08:47:29.769 ERROR 1 --- [nio-8081-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause

java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3

at java.base/java.util.GregorianCalendar.computeTime(Unknown Source) ~[na:na]

at java.base/java.util.Calendar.updateTime(Unknown Source) ~[na:na]

at java.base/java.util.Calendar.getTimeInMillis(Unknown Source) ~[na:na]

This problem has been solved at Java level, but how do I avoid it at mysql level.
In fact the query does not even have date or time.

@Query("select o from Order o where o.tickets is not null")
List<Order> ordersWithExistingTickets();

---

EDIT 1:

Order.java

@Entity
@Data
@Table(name="orders")
public class Order {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "PK")
private Long pk;

@Column(name = "createdTS")
private ZonedDateTime creationTime;

@Column(name = "tickets")
private String tickets;

public String getTickets() {
    return tickets;
}

public void setTickets(String tickets) {
    this.tickets = tickets;
}}

---

EDIT 2:

OrderRepository.java

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("select o from Order o where o.tickets is not null")
    List<Order> ordersWithExistingTickets();
}

Filibeg

Does Order have a date/time field? Maybe a computated column? What is the query that is actually executed? – Indemnity

yes...Order has many date/time fields – Filibeg

I'm not sure if you can fix this on mysql level, since parameters are supplied by your application. – Cargo

Which hibernate version do you use? It could very well be that your version is too old for java 8 support – Indemnity

java.lang.IllegalArgumentException - this is not even an sql exception, HOUR_OF_DAY - this only exists in your java code, not in mysql. Again, not sure what you want to do about this in mysql. – Cargo

@Cargo : It is indeed calling Java files, but I am getting the error when I am hitting the query in repository (edit 2) – Filibeg

That still does not mean that the error comes from and therefore can be prevented at mysql level. Nothing of the exception details indicate that this has anything to do with mysql. You really need to debug your code and data (invalid datetime data in mysql?) to understand where things go south. This is the drawback of using ORMs - you do not really know what gets executed. – Cargo

@Shadow: Yes, but this is all the java code that I have....so can you please suggest something I can add before calling the query method....something like.... cal.setLenient(true) – Filibeg

Nope, cannot suggest anything. I only know mysql, but not java. If you want a solution in mysql, you need to identify what causes the issue in mysql - if anything. – Cargo

It’s thrown from the line throw new IllegalArgumentException(getFieldName(field) + ": " + s); near the end of GregorianCalendar.computeTime(). It’s aome validation message, but I don’t understand what exactly it means nor why it happens. – Berrie

By the way, a creation moment should be tracked as an Instant generally, or as a OffsetDateTime when retrieved via JDBC 4.2+, not a ZonedDateTime. – Meaghan

Answer 1

The reason is there's a date time field (not timestamp) that is not available in your timezone. So it can't convert it correctly.

Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3 at java.base/java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2826) at java.base/java.util.Calendar.updateTime(Calendar.java:3428) at java.base/java.util.Calendar.getTimeInMillis(Calendar.java:1812) at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:108)

There's information on https://confluence.atlassian.com/jirakb/illegalargumentexception-hour_of_day-after-changing-mysql-database-timezone-1063564762.html

but generally you have to locate the errant record and remove or update it from the database.

Assuming you're using JOOQ or have access to do it (as using the JPA query will cause issues). Here's a code snippet I put in my JUnit Integration test

@Test
void ensureDatesAreOkayDuringDaylightSavings() {
  var fallbackDayClauses = IntStream
    .range(2000, LocalDate.now().getYear() + 1)
    .mapToObj(year ->
      LocalDate.ofYearDay(year, 1)
        .withMonth(3)
        .with(TemporalAdjusters.next(DayOfWeek.SUNDAY))
        .with(TemporalAdjusters.next(DayOfWeek.SUNDAY))
        .atTime(1, 59, 0)
    )
    .map(startDateTime -> 
        MYDB.CREATED_ON.between(
          startDateTime, 
          startDateTime
            .plusHours(1)
            .plusMinutes(2)))
    .collect(Collectors.toSet());

  var potentialProblemIds = dsl.select(MYDB.ID)
    .from(MYDB.TABLE)
    .where(or(fallbackDayClauses))
    .fetch(MYDB.ID);

  assertThat(potentialProblemIds)
    .isEmpty();

}

Grudging

Answer 2

The issue is that the date is the spring forward date for DST and there's no minutes between 2 and 3am. If the field should be read as UTC (aka Z)

  rs.getTimestamp(index, Calendar.getInstance(TimeZone.getTimeZone(UTC_ZONE)));

Gesso

Thanks for wanting to contribute. Your diagnosis agrees with the other answers. The OP is wisely using java.time, the modern Java date and time API (though ZonedDateTime may not work here, that depends). Please don’t suggest the cumbersome, ill designed and decade-long outdated Calendar and TimeZone classes. – Berrie

They are not suggesting those classes, just showing what to do when working with a ResultSet. java.sql.ResultSet simply does not support java.time classes. And if you read the OP, you will see that the exception is in fact within java.util time classes -- older versions of MySQL Connector don't support Java Time, either. – Blossom

Answer 3

I tried suggestions here, and various other places. Nothing worked out. I had to set the MySQL server time to UTC. Followed the steps mentioned here -> https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html Ensure you pay attention to the section "Populating the Time Zone Tables" which actually was the issue we were facing.

Caressa

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Nowicki

This is an answer. Changing the database zone can be cumbersome, but if it worked for this error then it is absolutely a solution. – Blossom

Answer 4

I ran into this issue after upgrading MySQL Connector from 5.1 to 9.0 due to changes in behavior introduced in 8.0. The fix was to add connectionTimeZone=UTC to my connection string, because it was incorrectly defaulting to a zone with DST instead. You might also need to set the serverTimezone if that is not being correctly detected either.

See "Time zone configuration properties" at https://dev.mysql.com/blog-archive/support-for-date-time-types-in-connector-j-8-0

Blossom