工作日常-SQL不能亂寫

小白先生哦發表於2021-10-14

前言：剛接手別人的專案沒多久，在昨天的一次上線中無故躺坑，且該大兄弟已經離職，不得不幫他填坑，整完後，今天想搞一個總結，結論就是：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
; 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.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:372)
	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:202)
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:585)
	... 27 common frames omitted
Caused by: 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
; 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

由此可判斷是sql執行超時所致。

解決方案：try-catch ，避免該異常影響初始化動作。(後面改寫 sql ,這樣只是為了儘快恢復線上服務)

問題難點：框架異常封裝掩蓋了原始的sql異常資訊，真實的sql異常資訊是執行超時後sql執行任務取消

最後，去線上看一下對應資料量：8392727

最開始的時候資料量小，上面的 sql 執行沒問題，中間經過一個多月資料量上來後問題就出來了，而我就是要去幫別人填坑的人，坑啊。

總結兩個坑：

1、SQL亂寫，不考慮資料量起來後的問題。

2、Mybatis框架封裝的異常資訊也是坑，吞沒了真正有用的異常資訊，給出一堆不相關的資訊。

DBA日常維護SQL指令碼_自己編寫的
2018-05-11
SQL指令碼
【BUG系列】multiDexEnabled true 不能亂寫啊，ClassNotFoundException: Didn't find class "***
2018-12-29
IDEException
MySQL從庫卡主了--讀寫分離也不能亂讀
2023-03-08
MySql
DBA的日常工作
2018-06-27
運維日常工作
2019-12-25
運維
SQL SERVER日常運維（一）
2024-09-05
SQLServer運維
奉啟玉：話可以亂說，藥不能亂吃！
2022-06-02
用C#寫個PDF批次合併工具簡化日常工作
2024-09-23
C#
Git 的日常工作用法
2019-08-09
Git
專職DBA的日常工作
2019-07-04
工作經驗日常總結===20241105
2024-11-05
不能摸魚的工作不是好工作？
2021-10-09
在運維日常工作，"awk"的日常使用規範有哪些？
2024-09-12
運維
Docker日常工作常用命令
2021-11-14
Docker
MyBatis-Plus日常工作學習
2021-04-14
MyBatis
工作常用SQL
2024-07-26
SQL
OceanBase 4.X 日常運維常用SQL
2024-08-23
運維SQL
日常工作筆記(持續更新中。。)
2018-06-04
筆記
Druid.io SQL亂碼問題
2018-05-15
UISQL
MySQL寫sql的21個好習慣，學習工作效率翻倍
2020-11-24
MySql
日常工作知識點集合之vue(一)
2019-03-02
Vue
伺服器維護日常需做哪些工作?
2022-02-26
伺服器
日常積累——彙編檔案編寫
2020-11-15
MySQL寫sql的21個好習慣，讓你的工作效率翻倍
2020-11-17
MySql
【工作總結】工作為什麼總是手忙腳亂
2024-04-05
匯入sql檔案出現亂碼
2018-03-17
SQL
什麼是內容運營?日常涉及哪些工作?
2020-07-21
日常工作必備之DHCP入門與安全
2020-04-06
軟體測試的日常工作是什麼
2021-10-18
IT職場：如何在日常工作中使用PDCA？
2022-08-12
sql devloper 用法的和SQL 編寫
2022-07-27
SQLdev
日常小 Bug 之資料寫到哪去了
2020-02-29
SQL SERVER日常運維巡檢系列——結構設計
2024-11-26
SQLServer運維
SQL優化工作記錄
2020-11-18
SQL優化
阿里小二的日常工作要被TA們“接管”了！
2019-04-09
阿里
Shell在日常工作中的應用實踐
2023-04-20
ES6 在日常工作中的運用
2018-03-24
工作發狂：Mybatis 中$和#千萬不要亂用！
2019-05-09
MyBatis

工作日常-SQL不能亂寫

相關文章