Search code examples
mysqlspringspring-bootjpaspring-data-jpa

MySQL syntax error in generated JPA Repository Pageable query: near 'offset x rows fetch first y rows only'

I am trying to get some rows from my table with pagination by Spring Boot JPA Repository, but MySQL keeps showing a syntax error in the query that was generated by JPA.
It seems MySQL only supports limit y offset x, instead of offset x rows fetch first y rows only (I am testing with the first page, page: 0 and page_size: 1

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'offset 0 rows fetch first 1 rows only' at line 1

This is my repository

@Repository
public interface MyRepository extends JpaRepository<MyEntity, Integer> {
    ArrayList<MyEntity> findById(Integer id, Pageable pageable);
}

and the generated query by JPA

select ... from myTable c1_0 order by c1_0.id desc offset 0 rows fetch first 1 rows only

Perhaps, I am still using JDBC driver for SQL
Here is a part in pom.xml file

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

And here is application.properties

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.database=mysql

How can I get rid of this?

Solution

  • The problem is my project is using the configuration from a Spring cloud config server.

    The spring.jpa.properties.hibernate.dialect config value was org.hibernate.dialect.H2Dialect. That's why JPA keep generating SQL query that didn't fully work with MySQL (in my case: offset and limit).

    Changing it back to org.hibernate.dialect.MySQLDialect using VM options solved my problem.