What Unexpected Pitfalls PageHelper Can Teach You After Years of Use

After years of not using PageHelper, the author joined a new company that integrated the library into its framework for an urgent project. Development proceeded smoothly, but during testing several bizarre issues appeared.

Observed Anomalies

Already‑registered usernames could be registered again without error.

A dropdown that should return more than ten categories only returned five rows, even though no pagination parameters were supplied.

Resetting a user password in the admin UI triggered a SQL error complaining about an unexpected LIMIT 5 clause.

All three symptoms share a common root: PageHelper silently appends a LIMIT clause to SQL statements based on pagination parameters stored in a ThreadLocal.

How PageHelper Works

The typical usage pattern is:

@GetMapping("/cms/cmsEssayList")
public TableDataInfo cmsEssayList(CmsBlog cmsBlog) {
    cmsBlog.setStatus("1"); // status = published
    startPage();
    List<CmsBlog> list = cmsBlogService.selectCmsBlogList(cmsBlog);
    return getDataTable(list);
}

startPage()

reads pagination parameters from the HTTP request and calls

PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable)

. The parameters are: pageNum: page number pageSize: number of records per page orderBy: sorting clause reasonable: automatically adjusts unreasonable values (e.g., pageNum < 0 becomes 1)

Internally, startPage() creates a Page object and stores it in a ThreadLocal<Page> LOCAL_PAGE. When a MyBatis mapper method is invoked, the PageInterceptor intercepts the call, retrieves the Page from LOCAL_PAGE, and decides whether to apply pagination.

Interceptor Flow

public Object intercept(Invocation invocation) throws Throwable {
    // extract arguments
    MappedStatement ms = (MappedStatement) args[0];
    Object parameter = args[1];
    RowBounds rowBounds = (RowBounds) args[2];
    // ... build cacheKey and boundSql
    if (!dialect.skip(ms, parameter, rowBounds)) {
        if (dialect.beforeCount(ms, parameter, rowBounds)) {
            Long count = count(...);
            if (!dialect.afterCount(count, parameter, rowBounds)) {
                return dialect.afterPage(new ArrayList(), parameter, rowBounds);
            }
        }
        resultList = ExecutorUtil.pageQuery(dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
    } else {
        resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
    }
    return dialect.afterPage(resultList, parameter, rowBounds);
} finally {
    if (dialect != null) {
        dialect.afterAll();
    }
}

The key call PageHelper.getLocalPage() inside dialect.skip(...) fetches the Page from the thread‑local cache. If a Page is present, the interceptor will eventually invoke ExecutorUtil.pageQuery, which adds the LIMIT clause to the generated SQL.

ThreadLocal Pollution

If startPage() is called but the subsequent SQL is never executed (e.g., the method returns early or throws an exception), the ThreadLocal is not cleared. The next request that reuses the same thread will inherit the stale pagination parameters, causing non‑paginated statements to receive an unwanted LIMIT clause.

Two concrete scenarios are highlighted:

Calling startPage() without executing the corresponding query leaves the pagination state in the thread.

An exception occurring before the finally block prevents clearPage() from running, also polluting the thread.

Cleanup Mechanism

At the end of intercept(), dialect.afterAll() invokes clearPage(), which simply calls LOCAL_PAGE.remove() to delete the thread‑local value.

public static void clearPage() {
    LOCAL_PAGE.remove();
}

Therefore, the safest practice is to ensure that the SQL execution follows startPage() immediately, or manually call clearPage() before any method that must run without pagination.

Why the Issue Appears Intermittently

Application servers such as Tomcat or Netty use thread pools to handle requests. If a thread that still holds a stale Page processes a new request, the pagination bug manifests; otherwise, the request runs correctly. This explains why the problem does not occur on every request.

Recommendations

Always execute the query right after startPage().

If a method may exit without running a query, call clearPage() before returning.

Avoid calling clearPage() on methods that are supposed to be paginated, as it will break pagination.

Conclusion

The analysis shows that PageHelper’s reliance on a thread‑local pagination cache can cause subtle bugs such as duplicate registrations, truncated result sets, and unexpected SQL errors. Understanding the interceptor’s workflow and properly managing the thread‑local state eliminates these issues and deepens one’s knowledge of MyBatis and PageHelper internals.