偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

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

作者：波波說運維
運維 數(shù)據(jù)庫運維
最近在前臺導入3000條記錄時，經(jīng)常發(fā)現(xiàn)只導入大概500條記錄，然后就會報ERROR TransactionInterceptor - Application exception overridden by rollback exception的問題。下面記錄一下解決過程，以作備忘!

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

思路

這里主要是由于連接時間過長，失效所致。即，程序獲取到的連接已經(jīng)失效了，而拿一個失效的連接再次請求就會報那樣的異常。

1. 查看應用日志

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

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

具體信息如下：

  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測試

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

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

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

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

關(guān)閉mycat的讀寫分離后導入3000條可以導入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、. 改應用配置

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

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

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

修改后讓業(yè)務重新測試，導入正常，問題解決。

總結(jié)

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

 

責任編輯：趙寧寧 來源： 今日頭條
境數(shù)據(jù)庫連接超時自動回收
相關(guān)推薦

2018-12-06 16:25:39

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

2021-03-01 06:14:50

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

2019-11-18 13:42:55

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

2019-11-22 08:05:01

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

2019-08-19 01:34:38

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

2019-12-12 10:38:10

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

2021-10-14 10:53:20

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

2019-12-27 10:43:48

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

2019-09-08 17:52:10

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

2019-09-27 17:24:26

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

2019-07-25 08:30:58

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

2019-09-24 07:00:01

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

2019-09-05 09:17:37

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

2019-12-16 07:18:42

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

2019-08-15 11:30:06

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

2020-09-25 07:57:42

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

2018-07-18 15:37:24

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

2020-11-03 07:34:12

Kafka后端工程師

2022-06-01 06:17:42

微服務Kafka

2023-04-06 07:53:56

Redis連接問題K8s
點贊
收藏

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