Paging Issues and Solutions in Spring Batch MySqlPagingQueryProvider

Kurly Fintech Development Team developer Lee Kyeong-hun shared two major issues and their solutions encountered when using MySqlPagingQueryProvider in a Spring Batch 5.2.0 environment. JdbcPagingItemReader uses PagingQueryProvider to eliminate DBMS dependency, and for MySQL, MySqlPagingQueryProvider generates the paging query.

Data Omission When Applying the Transactional Outbox Pattern

While applying the Transactional Outbox Pattern in a paper gift certificate project, a paging data omission issue occurred. When created_at was set as the sortKey, some data created at the same timestamp was found to be omitted from the next page after Chunk processing. This happens because MySqlPagingQueryProvider only adds a sortKey > lastValue condition along with the LIMIT clause when generating the Remaining Query. This was a structural limitation where records with the same created_at value were filtered out and not retrieved. As a solution, the id column, which is a MySQL auto_increment column, was changed to the sortKey to ensure order guarantee.

SQL Error When Mixing Group By and Table Alias

In the AML project, an SQL syntax error occurred when using a sortKey that included a Group By clause and a Table Alias. The first page worked normally, but starting from the second page, a SQLSyntaxErrorException occurred with an error stating that the t1 column could not be found. This problem occurred because the WHERE clause outside the subquery in the Remaining Query generated by MySqlPagingQueryProvider could not reference the alias (t1) inside it. This suggests that in complex queries containing a group clause, alias usage should be avoided or the query structure should be redesigned.