ParserException: syntax error, error in :'it 1 LIMIT ? ', expect LIMIT, actual LIMIT pos , line , column , token LIMIT

error¶

PageHelper's unsafe paging problem, resulting in 

ParserException: syntax error, error in :'it 1 LIMIT?', expect LIMIT, actual LIMIT pos, line, column, token LIMIT

My project uses the PageHlper plug-in for paging. Today, I found that many SQL query statements have the following errors.

com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'it 1 LIMIT ? ', expect LIMIT, actual LIMIT pos 249, line 12, column 16, token LIMIT
at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:284)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(
at com.alibaba.druid.sql.SQLUtils.format(SQLUtils.java:255)
at com.alibaba.druid.filter.logging.LogFilter.statement_executeErrorAfter(LogFilter.java:767)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy467.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at sun.reflect.GeneratedMethodAccessor239.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy137.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy243.getOneSomthing(Unknown Source)
at com.lingyejun.project.impl.GetOneThingServiceImpl.getOneThingFromDb(GetOneThingServiceImpl.java:23)

investigation¶

We inspected the stack information and found that there was a line of key information, which was intercepted by the PageHelper used during the query.

at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)

But looking at the SQL statement, there is no paging code, and the error is not only in this place, there are several other places, check the submission record and find that there has been no change recently.

We thought that it must have been caused by changes elsewhere. After investigating the cause, it was found that a code returned directly after calling PageHelper.startPage, resulting in an error. The approximate code is as follows.

package com.lingyejun.authenticator;
import com.github.pagehelper.PageHelper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;

@Service
public class PageHelperTest {

    @Resource
    private TeacherMapper teacherMapper;

    @Resource
    private StudentMapper studentMapper;

    public void doQueryByPage(byte type) {
        PageHelper.startPage(1,10);

        if (type == 1){
            studentMapper.query();
        }else if (type ==2){
            teacherMapper.query();
        }
        // If the type is not 1 or 2, then the page variable is not released and cleaned up after this method is executed
        // It will cause the query statement in other places to report an error, or the result does not match the expectation
        return;
    }

}

principle¶

The PageHelper method uses a static ThreadLocal parameter, and the paging parameter is bound to the thread. As long as we ensure that the MyBatis query method is immediately followed by the PageHelper method call, this is safe. Because PageHelper automatically clears the objects stored by ThreadLocal in the finally code segment.

The paging process of a PageHelper is as follows

1. Set the page parameter
2. Execute the query method
3. In the Interceptor interface, verify whether there is a set page parameter in the ThreadLocal
4. There is a page parameter, regenerate `count sql` and page sql, and execute the query.
5. There is no page parameter, directly return the query result
6. Execute LOCAL_PAGE.remove() to clear the page parameter

However, if the thread pool is used, the current thread will not be destroyed after its execution. Instead, the current thread will be stored in the pool again and marked as an idle state for subsequent use. In the subsequent use of this thread, because the thread’s threadLocals still has a value, although we did not set the page parameter in step 1, the page parameter can also be obtained in step 3, thereby generating a count sql and page sql, thus affecting our normal query.

solution¶

The above problem is a man-made bug, and the situation where type is other values is not considered, that is, the lack of subsequent logic processing when else occurs, which will cause PageHelper to produce a paging parameter, but it will not be consumed, this parameter will remain Keep it on this thread. When this thread is used again, it may cause methods that should not be paged to consume the paging parameters, which results in inexplicable paging. So we can adjust and modify the corresponding logic, and change else if to else to solve this problem.