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();
}

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();

}