前言:剛接手別人的專案沒多久,在昨天的一次上線中無故躺坑,且該大兄弟已經離職,不得不幫他填坑,整完後,今天想搞一個總結,結論就是: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框架封裝的異常資訊也是坑,吞沒了真正有用的異常資訊,給出一堆不相關的資訊。