工作日常-SQL不能乱写

阅读原文时间：2021年10月15日阅读：1

前言：刚接手别人的项目没多久，在昨天的一次上线中无故躺坑，且该大兄弟已经离职，不得不帮他填坑，整完后，今天想搞一个总结，结论就是：SQL不能乱写。

搜索关键词：Cause: java.sql.SQLException: Unknown thread id: 1

参考文章：https://blog.csdn.net/weixin_42340670/article/details/88587118

问题背景，接手的一个项目中，有一个 service 类姑且叫 ServiceA ，在 ServiceA 中依赖一个类 @Autowired ServiceB，而 ServiceB 创建后有一个初始化方法 @PostConstruct init()，在这个方法中一路调用来到了 dao 层的一个方法中，该方法中执行了这样一句 sql：

SELECT
a.vin,
avg( a.kwh_100km ) AS kwh_100km
FROM
tb_tmp a
WHERE
kwh_100km > 0
AND EXISTS ( SELECT count( 1 ) FROM tb_tmp WHERE vin = a.vin AND kwh_100km > 0 AND dt > a.dt HAVING count(*) < 7 )
GROUP BY
a.vin

这句 sql 执行报错的栈信息如下：

[ERROR] [2021-10-13T17:20:04.774] Application startup failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'brainCarServiceImpl': Unsatisfied dependency expressed through field 'd1ScreenService'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'micMonitorD1ScreenServiceImpl': Injection of autowired dependencies failed; nested exception is org.springframework.jdbc.UncategorizedSQLException:

Error querying database. Cause: java.sql.SQLException: Unknown thread id: 1

The error may exist in class path resource [mybatis/mapper/mic/MicEvRunDataDailyMapper.xml]

The error may involve com.xiaojukeji.iov.mic.biz.dao.mic.MicEvRunDataDailyDao.selectD1CarRecord-Inline

The error occurred while setting parameters

SQL: select a.vin, avg(a.kwh_100km) as kwh_100km from tb_tmp a WHERE kwh_100km > 0 and exists ( select count(1) from tb_tmp where vin = a.vin and kwh_100km > 0 and dt > a.dt having count(*) < 7 ) group by a.vin

Cause: java.sql.SQLException: Unknown thread id: 1

; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1094]; Unknown thread id: 1; nested exception is java.sql.SQLException: Unknown thread id: 1
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:588)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)
at com.xiaojukeji.iov.mic.biz.IovMicBizApplication.main(IovMicBizApplication.java:28)
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.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'micMonitorD1ScreenServiceImpl': Injection of autowired dependencies failed; nested exception is org.springframework.jdbc.UncategorizedSQLException:

Error querying database. Cause: java.sql.SQLException: Unknown thread id: 1

The error may exist in class path resource [mybatis/mapper/mic/MicEvRunDataDailyMapper.xml]

The error may involve com.xiaojukeji.iov.mic.biz.dao.mic.MicEvRunDataDailyDao.selectD1CarRecord-Inline

The error occurred while setting parameters

SQL: select a.vin, avg(a.kwh_100km) as kwh_100km from mic_ev_run_data_daily a WHERE kwh_100km > 0 and exists ( select count(1) from mic_ev_run_data_daily where vin = a.vin and kwh_100km > 0 and dt > a.dt having count(*) < 7 ) group by a.vin

Cause: java.sql.SQLException: Unknown thread id: 1

Error querying database. Cause: java.sql.SQLException: Unknown thread id: 1

The error may exist in class path resource [mybatis/mapper/mic/MicEvRunDataDailyMapper.xml]

The error may involve com.xiaojukeji.iov.mic.biz.dao.mic.MicEvRunDataDailyDao.selectD1CarRecord-Inline

The error occurred while setting parameters

SQL: select a.vin, avg(a.kwh_100km) as kwh_100km from mic_ev_run_data_daily a WHERE kwh_100km > 0 and exists ( select count(1) from mic_ev_run_data_daily where vin = a.vin and kwh_100km > 0 and dt > a.dt having count(*) < 7 ) group by a.vin

Cause: java.sql.SQLException: Unknown thread id: 1

; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1094]; Unknown thread id: 1; nested exception is java.sql.SQLException: Unknown thread id: 1
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy112.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy159.selectD1CarRecord(Unknown Source)
at com.xiaojukeji.iov.mic.biz.task.MicD1LabelTask.queryEv(MicD1LabelTask.java:258)
at com.xiaojukeji.iov.mic.biz.task.MicD1LabelTask.setDataLabelDTOList(MicD1LabelTask.java:143)
at com.xiaojukeji.iov.mic.biz.task.MicD1LabelTask.getD1LableMap(MicD1LabelTask.java:71)
at com.xiaojukeji.iov.mic.biz.service.impl.MicMonitorD1ScreenServiceImpl.init(MicMonitorD1ScreenServiceImpl.java:311)
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.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:701)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366)
… 38 common frames omitted
Caused by: java.sql.SQLException: Unknown thread id: 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2455)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
at com.mysql.jdbc.StatementImpl$CancelTask$1.run(StatementImpl.java:117)　

直接带来的结果就是 ServiceB 初始化失败，然后 ServiceA 注入失败，程序启动失败。

按照日志中的语句 Cause: java.sql.SQLException: Unknown thread id: 1 搜索到上面的文章，然后去检查sql,测试环境没问题，然后拿到生产环境，对sql稍加修改如下，执行全量查询报错：

SELECT
COUNT(*)
FROM
(
SELECT
a.vin,
avg( a.kwh_100km ) AS kwh_100km
FROM
tb_tmp a
WHERE
kwh_100km > 0
AND EXISTS ( SELECT count( 1 ) FROM tb_tmp WHERE vin = a.vin AND kwh_100km > 0 AND dt > a.dt HAVING count(*) < 7 )
GROUP BY
a.vin
) AS tmp

生产环境执行结果：

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded