Recently, I encountered sql state 42000 error in a spring boot application while moving from MySQL version 5.6 to 8.0. After inspecting stack trace and some googling, I got to know that few default configurations have changed in MySQL 8.0 (as expected 😃 ).

This caused our poorly written queries to fail during migration.

Background

Lets look at sample query using spring-data jpa to gather some analytics by aggregating data about a customer ordering products

1
2
3
4
5
6
7
8

 companion object {
        private const val RETURN_OBJ = "de.rks.model.EventAggregate" // event data class  with count, productId, customerId, orderDate
    }

    @Query(value = "select new $RETURN_OBJ(count(distinct e.orderId ), e.productId, e.customerId, orderDate ) from Event e where e.eventDate BETWEEN :fromDate AND :toDate AND e.eventType = :eventType GROUP BY e.productId")
    fun findEventAggregateByDateBetweenAndGroupByProductIdAndCustomerId(@Param("fromDate") fromDate: Date,
                                                                   @Param("toDate") toDate: Date,
                                                                   @Param("eventType") eventType: EventType): Set<EventAggregate>

The above query worked with MySQL 5.6 because sql mode isn’t enabled for full group by. See doc 5.6

Default Value: NO_ENGINE_SUBSTITUTION

With MySQL 8.0, lot of additional features are enabled by default. See doc 8.0

Default Value: ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION

Solution

I needed to fix the query to add customerId to the group by clause and replace orderDate (not used in the code. technical debt 😓 ) with current_timestamp. I could also have used aggregation functions like max,min for the eventDate. Note that, adding eventDate in the groupBy wouldn’t have generated the aggregate data that I was interested in..

1

 @Query(value = "select new $RETURN_OBJ(count(distinct e.orderId ), e.productId, e.customerId, current_timestamp ) from Event e where e.eventDate BETWEEN :fromDate AND :toDate AND e.eventType = :eventType GROUP BY e.productId, e.customerId")

How to view default sql_mode in MySQL

1

SELECT @@GLOBAL.sql_mode;

OR

1

SELECT @@SESSION.sql_mode;