Error selecting key or setting result to parameter object. Cause: java.sql.SQLSyntaxErrorException

## 通用mapper insertSelective方法报语法错误

报错信息如下

Caused by: java.sql.SQLSyntaxErrorException: 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 '?' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2958)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2473)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2956)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:147)
	at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:619)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.StatementLogger.invoke(StatementLogger.java:57)
	at com.sun.proxy.$Proxy405.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.SimpleStatementHandler.query(SimpleStatementHandler.java:73)
	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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.didichuxing.erp.log.interceptor.LogMybatisInterceptor.intercept(LogMybatisInterceptor.java:62)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy394.query(Unknown Source)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy394.query(Unknown Source)
	at tk.mybatis.mapper.mapperhelper.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:77)
	at tk.mybatis.mapper.mapperhelper.SelectKeyGenerator.processAfter(SelectKeyGenerator.java:63)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:50)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.didichuxing.erp.log.interceptor.LogMybatisInterceptor.intercept(LogMybatisInterceptor.java:62)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy394.update(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy394.update(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	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)
	... 123 more

核心报错如下

Error selecting key or setting result to parameter object. Cause: java.sql.SQLSyntaxErrorException: 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 '?' at line 1

报错的大概意思是

为参数对象选择键或设置结果时出错。原因：java.sql.SQLSyntaxErrorException：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以了解在“？”附近使用的正确语法在第 1 行

这个问题在我们线上项目偶发，大概三四天出现一次，可以说是非常折磨人。这个报错非常明确，但是找遍全网，也没找到相关的问题，这里记录一下我排查这个问题的思路，以及解决办法。

进入正文

• 分析日志
  
  日志打印了三条执行sql的记录，sql看不出来问题，不知道为什么会报错。

• 分析源码
  找到 mybatis Interceptor 这个类的实现 PageInterceptor
  Interceptor 讲解去这里看

上源码

public Object intercept(Invocation invocation) throws Throwable {
        Object var22;
        try {
            Object[] args = invocation.getArgs();
            MappedStatement ms = (MappedStatement)args[0];
            Object parameter = args[1];
            RowBounds rowBounds = (RowBounds)args[2];
            ResultHandler resultHandler = (ResultHandler)args[3];
            Executor executor = (Executor)invocation.getTarget();
            CacheKey cacheKey;
            BoundSql boundSql;
            if (args.length == 4) {
                boundSql = ms.getBoundSql(parameter);
                cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
            } else {
            //二级缓存有数据会走到这里
                cacheKey = (CacheKey)args[4];
                boundSql = (BoundSql)args[5];
            }

            List resultList;
            //判断是否需要分页
            if (this.dialect.skip(ms, parameter, rowBounds)) {
            	//不需要分页走这里
                resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
            } else {
                String msId = ms.getId();
                Configuration configuration = ms.getConfiguration();
                Map<String, Object> additionalParameters = (Map)this.additionalParametersField.get(boundSql);
                //判断是否需要count
                if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
                    //需要count的上去了会拼上countSuffix 
                    String countMsId = msId + this.countSuffix;
                    MappedStatement countMs = this.getExistedMappedStatement(configuration, countMsId);
                    Long count;
                    if (countMs != null) {
                        count = this.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
                    } else {
                        countMs = (MappedStatement)this.msCountMap.get(countMsId);
                        if (countMs == null) {
                            countMs = MSUtils.newCountMappedStatement(ms, countMsId);
                            this.msCountMap.put(countMsId, countMs);
                        }
						//执行count的核心方法
                        count = this.executeAutoCount(executor, countMs, parameter, boundSql, rowBounds, resultHandler);
                    }

                    if (!this.dialect.afterCount(count, parameter, rowBounds)) {
                        Object var24 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds);
                        return var24;
                    }
                }

                if (!this.dialect.beforePage(ms, parameter, rowBounds)) {
                    resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql);
                } else {
                    parameter = this.dialect.processParameterObject(ms, parameter, boundSql, cacheKey);
                    //这个方法在处理sql
                    String pageSql = this.dialect.getPageSql(ms, boundSql, parameter, rowBounds, cacheKey);
                    BoundSql pageBoundSql = new BoundSql(configuration, pageSql, boundSql.getParameterMappings(), parameter);
                    Iterator var17 = additionalParameters.keySet().iterator();

                    while(true) {
                        if (!var17.hasNext()) {
                            resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql);
                            break;
                        }

                        String key = (String)var17.next();
                        pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
                    }
                }
            }
			//执行分页的核心方法
            var22 = this.dialect.afterPage(resultList, parameter, rowBounds);
        } finally {
            this.dialect.afterAll();
        }

        return var22;
    }

代码的核心语句我都加上了备注。

• debug代码
  我把线上报错日志的入参整到本地，想复现报错，用上了jmeter并发请求，发现不会出现任何问题（崩溃）
  跟踪insertSelective 正确执行过程 如下
  执行先执行 install into 然后执行LAST_INSERT_ID() 获取刚刚插入的id 放入到插入的对象中

错误就是下面这段源码抛出来的，所以这里重点看了一下

    private void processGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) {
        try {
            if (parameter != null && this.keyStatement != null && this.keyStatement.getKeyProperties() != null) {
                String[] keyProperties = this.keyStatement.getKeyProperties();
                Configuration configuration = ms.getConfiguration();
                MetaObject metaParam = configuration.newMetaObject(parameter);
                if (keyProperties != null) {
                    Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE);
                    //获取id值的核心方法
                    List<Object> values = keyExecutor.query(this.keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
                    if (values.size() == 0) {
                        throw new ExecutorException("SelectKey returned no data.");
                    }

                    if (values.size() > 1) {
                        throw new ExecutorException("SelectKey returned more than one value.");
                    }

                    MetaObject metaResult = configuration.newMetaObject(values.get(0));
                    //有结果的话下面方法把结果set到id中
                    if (keyProperties.length == 1) {
                        if (metaResult.hasGetter(keyProperties[0])) {
                            this.setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0]));
                        } else {
                            this.setValue(metaParam, keyProperties[0], values.get(0));
                        }
                    } else {
                        this.handleMultipleProperties(keyProperties, metaParam, metaResult);
                    }
                }
            }

        } catch (ExecutorException var10) {
            throw var10;
        } catch (Exception var11) {
        //日志中打印的异常， 抛出的地方
            throw new ExecutorException("Error selecting key or setting result to parameter object. Cause: " + var11, var11);
        }
    }
   

源码看到这里完全发现不了问题
我仔细对比了一下执行正确和执行错误的日志，发现出错都会出现这么一行日志，我觉得问题出现在这里。

com.legal.draft.dao.mapper.ContractDraftMapper.insertSelective!selectKey_COUNT

debug许久后，我根据日志中的 _COUNT 找到核心的代码块

		//判断是否分页
		if (this.dialect.skip(ms, parameter, rowBounds)) {
				//正常的话会在这个if
                resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
            } else {
            	//错误的都会走这个
                String msId = ms.getId();
                Configuration configuration = ms.getConfiguration();
                Map<String, Object> additionalParameters = (Map)this.additionalParametersField.get(boundSql);
                if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
                //那个出现错误都会拼上_COUNT ，一行奇怪的日志让我离真相越来越近
                    String countMsId = msId + this.countSuffix;

为什么进else里面呢 一个插入方法为啥会分页，skip（是否分页）的源码如下

public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
        if (ms.getId().endsWith("_COUNT")) {
            throw new RuntimeException("在系统中发现了多个分页插件，请检查系统配置!");
        } else {
        	//获取page
            Page page = this.pageParams.getPage(parameterObject, rowBounds);
            if (page == null) {
                return true;
            } else {
                if (StringUtil.isEmpty(page.getCountColumn())) {
                    page.setCountColumn(this.pageParams.getCountColumn());
                }

                this.autoDialect.initDelegateDialect(ms);
                return false;
            }
        }
    }

源码很简单，就是判断当前线程有没有page，就是下面这个东西

PageHelper.startPage(pageNum, pageSize);

肯定没有啊，我一个插入接口怎么会去分页，真是离了大普，于是我去查PageHelper插件的原理，发现了这么一句话。

PageHelper.startPage 方法调用后，后面必须有一个Mapper的查询方法，必须被消费掉。否则会由于ThreadLocal的原因，当该线程被其他方法调用时被分页。在文档中非常明确的写了分页插件的使用方法！！另外这不是ThreadLocal引起的问题，是使用不当！

我豁然大悟，于是写了个测试接口。

    /**
     * 变更
     * @param
     * @return
     */
    @PostMapping("modifyDraftTemp")
    @EPAroundLog
    public BaseResponse<ContractVO> modifyDraft() {

        //开启分页
        PageHelper.startPage(1,1);

        //返回结果
        return BaseResponse.ok();
    }

在掉了几次这个测试接口以后，在调用插入insertSelective方法插入，果然问题必复现。

到这里问题已经明了，原因是项目中某个位置调用了PageHelper.startPage，但是没有被消费，因为这个page对象是放在本地线程中，ThreadLocal刚好执行了insertSelective的方法，会走到分页方法中，
处理获取刚刚插入的id时，组装出来的sql长这样。

SELECT LAST_INSERT_ID() LIMIT ?

执行的话就会报上面的sql语法的错误

知道问题原因，就很好解决了。
方法 1 ： 找到项目中没有被消费的PageHelper.startPage的地方，优化掉代码。（我看了一下我们项目好几十处，太恐怖）
方法 2 ： 在插入语句前加 PageHelper.clearPage() （治标不治本，但是好使，我又懒，我用的2）

        //如果别的线程有未消费的分页，会导致这里插入报错
        PageHelper.clearPage();
        if (contractDraftMapper.insertSelective(contractDraft) != 1) {
            return null;
        }

搞定。