自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

記一次生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)連接超時(shí)自動(dòng)回收問(wèn)題及解決方法

作者：波波說(shuō)運(yùn)維
運(yùn)維 數(shù)據(jù)庫(kù)運(yùn)維
最近在前臺(tái)導(dǎo)入3000條記錄時(shí)，經(jīng)常發(fā)現(xiàn)只導(dǎo)入大概500條記錄，然后就會(huì)報(bào)ERROR TransactionInterceptor - Application exception overridden by rollback exception的問(wèn)題。下面記錄一下解決過(guò)程，以作備忘!

最近在前臺(tái)導(dǎo)入3000條記錄時(shí)，經(jīng)常發(fā)現(xiàn)只導(dǎo)入大概500條記錄，然后就會(huì)報(bào)ERROR TransactionInterceptor - Application exception overridden by rollback exception的問(wèn)題。下面記錄一下解決過(guò)程，以作備忘!

思路

這里主要是由于連接時(shí)間過(guò)長(zhǎng)，失效所致。即，程序獲取到的連接已經(jīng)失效了，而拿一個(gè)失效的連接再次請(qǐng)求就會(huì)報(bào)那樣的異常。

1. 查看應(yīng)用日志

可以看到都是在跟后端數(shù)據(jù)庫(kù)連接時(shí)斷開導(dǎo)致。

記一次生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)連接超時(shí)自動(dòng)回收問(wèn)題及解決方法

具體信息如下：

  1. java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed. 
  2.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  3.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  4.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  5.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  6.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  7.     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  8.     at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1875) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  9.     at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:370) ~[HikariCP-3.2.0.jar!/:na] 
  10.     at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) [HikariCP-3.2.0.jar!/:na] 
  11.     at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:344) [spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  12.     at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:838) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  13.     at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:812) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  14.     at org.springframework.transaction.support.TransactionTemplate.rollbackOnException(TransactionTemplate.java:168) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  15.     at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:144) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  16.     at logwire.web.service.ActionContext$TransactionExecutor.doWithoutResult(ActionContext.java:716) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  17.     at logwire.web.service.ActionContext.doInNewTransactionWithoutResult(ActionContext.java:561) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  18.     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTaskInner(DefaultTaskRunner.java:85) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  19.     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTaskInnerWithMetric(DefaultTaskRunner.java:130) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  20.     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTask(DefaultTaskRunner.java:176) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  21.     at logwire.web.service.task.execute.TaskExecuteService.doTask(TaskExecuteService.java:35) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  22.     at logwire.web.service.task.execute.redisson.impl.RedissonTaskImpl.doTask(RedissonTaskImpl.java:22) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  23.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_102] 
  24.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_102] 
  25.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_102] 
  26.     at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_102] 
  27.     at org.redisson.RedissonRemoteService.invokeMethod(RedissonRemoteService.java:360) [redisson-3.11.3.jar!/:3.11.3] 
  28.     at org.redisson.RedissonRemoteService.lambda$executeMethod$4(RedissonRemoteService.java:329) [redisson-3.11.3.jar!/:3.11.3] 
  29.     at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[na:1.8.0_102] 
  30.     at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_102] 
  31.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_102] 
  32.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_102] 
  33.     at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_102] 
  34. Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed. 
  35.     at sun.reflect.GeneratedConstructorAccessor129.newInstance(Unknown Source) ~[na:na] 
  36.     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_102] 
  37.     at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_102] 
  38.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  39.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  40.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  41.     at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:1259) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  42.     at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:575) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  43.     at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1829) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  44.     ... 25 common frames omitted 
  45. Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure 
  46.  
  47. The last packet successfully received from the server was 35,661 milliseconds ago. The last packet sent successfully to the server was 35,662 milliseconds ago. 
  48.     at sun.reflect.GeneratedConstructorAccessor126.newInstance(Unknown Source) ~[na:na] 
  49.     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_102] 
  50.     at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_102] 
  51.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  52.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  53.     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  54.     at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  55.     at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:555) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  56.     at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:725) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  57.     at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:664) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  58.     at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:979) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  59.     at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1153) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  60.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:951) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  61.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  62.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  63.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  64.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  65.     at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar!/:na] 
  66.     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar!/:na] 
  67.     at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  68.     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  69.     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  70.     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  71.     at logwire.web.service.query.sql.QueryDataSetManager.insertModelRow(QueryDataSetManager.java:419) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  72.     at logwire.web.service.query.sql.QueryDataSetManager.saveModelDataSet(QueryDataSetManager.java:140) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  73.     at logwire.web.service.query.sql.QueryDataSetManager.saveModelDataSet(QueryDataSetManager.java:104) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  74.     at logwire.web.service.query.sql.QueryDataSetManager.insert(QueryDataSetManager.java:272) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  75.     at logwire.web.service.query.QueryService.insert(QueryService.java:183) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  76.     at logwire.web.service.query.QueryService.insert(QueryService.java:163) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  77.     at jdk.nashorn.internal.scripts.Script$Recompilation$203$16161AAAAAAA$importHandlers.L:1$insertLog(config/custom_js/importHandlers.js:449) ~[na:na] 
  78.     at jdk.nashorn.internal.scripts.Script$Recompilation$198$9472A$tasks.L:1$handleImport$L:256(config/custom_js/tasks.js:273) ~[na:na] 
  79.     at jdk.nashorn.javaadapters.java.util.function.Consumer.accept(Unknown Source) ~[na:na] 
  80.     at java.util.ArrayList.forEach(ArrayList.java:1249) ~[na:1.8.0_102] 
  81.     at jdk.nashorn.internal.scripts.Script$Recompilation$196$9015AAA$tasks.L:1$handleImport(config/custom_js/tasks.js:256) ~[na:na] 
  82.     at jdk.nashorn.internal.scripts.Script$Recompilation$195$8841AAA$tasks.L:1$partsImportDD(config/custom_js/tasks.js:239) ~[na:na] 
  83.     at jdk.nashorn.internal.runtime.ScriptFunctionData.invoke(ScriptFunctionData.java:643) ~[nashorn.jar:na] 
  84.     at jdk.nashorn.internal.runtime.ScriptFunction.invoke(ScriptFunction.java:494) ~[nashorn.jar:na] 
  85.     at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:393) ~[nashorn.jar:na] 
  86.     at jdk.nashorn.api.scripting.ScriptObjectMirror.callMember(ScriptObjectMirror.java:199) ~[nashorn.jar:na] 
  87.     at logwire.core.meta.task.ScriptTask.doTask(ScriptTask.java:24) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  88.     at logwire.web.service.task.run.impl.DefaultTaskRunner$1.doInTransactionWithoutResult(DefaultTaskRunner.java:88) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT] 
  89.     at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:36) ~[spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  90.     at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE] 
  91.     ... 18 common frames omitted 
  92. Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. 
  93.     at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  94.     at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  95.     at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  96.     at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  97.     at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  98.     at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  99.     at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  100.     at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:549) ~[mysql-connector-java-8.0.13.jar!/:8.0.13] 
  101.     ... 53 common frames omitted 

2. 直連mysql測(cè)試

由于后端數(shù)據(jù)庫(kù)是用mycat+mysql主從做的讀寫分離，影響因素比較多，所以需先去掉影響的一些因素，這里先直連mysql數(shù)據(jù)庫(kù)測(cè)試，繞過(guò)mycat。

經(jīng)過(guò)業(yè)務(wù)測(cè)試發(fā)現(xiàn)可以導(dǎo)入成功，這樣問(wèn)題就在mycat上了。

記一次生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)連接超時(shí)自動(dòng)回收問(wèn)題及解決方法

3. mycat關(guān)閉讀寫分離

關(guān)閉mycat的讀寫分離后導(dǎo)入3000條可以導(dǎo)入2700條，但還有部分失敗。

mycat日志如下：

  1. INFO [$_NIOREACTOR-1-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:520)) - close connection,reason:stream closed ,ServerConnection [id=55, schema=tms_prod, host=, u 
  2. ser=root,txIsolation=3, autocommit=true, schema=tms_prod, executeSql=null] 
  3. INFO [$_NIOREACTOR-2-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:520)) - close connection,reason:stream closed ,ServerConnection [id=56, schema=tms_prod, host=, u 

4、. 改應(yīng)用配置

經(jīng)過(guò)查找資料和分析，得出該問(wèn)題主要是由于連接時(shí)間過(guò)長(zhǎng)，失效所致，既然程序獲取的連接已經(jīng)失效了，那么對(duì)于失效的連接就不應(yīng)該使用，而應(yīng)該再次獲取有效的連接。增加配置如下：

記一次生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)連接超時(shí)自動(dòng)回收問(wèn)題及解決方法

  1. spring.datasource.validation-query=SELECT 1 

修改后讓業(yè)務(wù)重新測(cè)試，導(dǎo)入正常，問(wèn)題解決。

總結(jié)

當(dāng)數(shù)據(jù)庫(kù)連接池中的連接被創(chuàng)建而長(zhǎng)時(shí)間不使用的情況下，該連接會(huì)自動(dòng)回收并失效，但客戶端并不知道，在進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)仍然使用的是無(wú)效的數(shù)據(jù)庫(kù)連接，這樣，就導(dǎo)致客戶端程序報(bào)“ java.sql.SQLException: Io 異常: Connection reset by peer”或“java.sql.SQLException 關(guān)閉的連接”異常，加上spring.datasource.validation-query=SELECT 1配置后，客戶端在使用一個(gè)無(wú)效的連接時(shí)會(huì)先對(duì)該連接進(jìn)行測(cè)試，如果發(fā)現(xiàn)該連接已經(jīng)無(wú)效，則重新從連接池獲取有效數(shù)據(jù)庫(kù)連接來(lái)使用。

 

責(zé)任編輯：趙寧寧 來(lái)源： 今日頭條
境數(shù)據(jù)庫(kù)連接超時(shí)自動(dòng)回收
相關(guān)推薦

2021-03-01 06:14:50

環(huán)境高并發(fā)延遲

2018-12-06 16:25:39

數(shù)據(jù)庫(kù)服務(wù)器線程池

2019-08-19 01:34:38

數(shù)據(jù)庫(kù)SQL數(shù)據(jù)庫(kù)優(yōu)化

2019-11-18 13:42:55

MySQL數(shù)據(jù)庫(kù)遷移

2019-11-22 08:05:01

數(shù)據(jù)庫(kù)mysql分區(qū)

2019-12-12 10:38:10

mysql數(shù)據(jù)庫(kù)nnodb

2021-10-14 10:53:20

數(shù)據(jù)庫(kù)查詢超時(shí)

2019-09-08 17:52:10

數(shù)據(jù)庫(kù)log file sy等待事件

2019-12-27 10:43:48

磁盤數(shù)據(jù)庫(kù)死鎖

2019-07-25 08:30:58

數(shù)據(jù)庫(kù)服務(wù)器故障

2019-09-27 17:24:26

數(shù)據(jù)庫(kù)優(yōu)化sql

2019-09-05 09:17:37

MySQL數(shù)據(jù)庫(kù)線程

2019-12-16 07:18:42

數(shù)據(jù)庫(kù)SQL代碼

2019-09-24 07:00:01

SQL Server服務(wù)器卡頓內(nèi)存分配

2018-07-18 15:37:24

數(shù)據(jù)庫(kù)DB2故障處理

2019-08-15 11:30:06

SQL數(shù)據(jù)庫(kù)ASH

2020-09-25 07:57:42

生產(chǎn)事故系統(tǒng)

2020-11-03 07:34:12

Kafka后端工程師

2022-06-01 06:17:42

微服務(wù)Kafka

2020-08-24 07:34:39

網(wǎng)絡(luò)超時(shí)請(qǐng)求
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)