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

本篇文章為大家展示了生產(chǎn)環(huán)境數(shù)據(jù)庫連接超時自動回收問題及解決方法，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

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

思路

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

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

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

具體信息如下：

java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1875) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:370) ~[HikariCP-3.2.0.jar!/:na]     at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) [HikariCP-3.2.0.jar!/:na]     at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:344) [spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:838) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:812) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.transaction.support.TransactionTemplate.rollbackOnException(TransactionTemplate.java:168) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:144) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at logwire.web.service.ActionContext$TransactionExecutor.doWithoutResult(ActionContext.java:716) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.ActionContext.doInNewTransactionWithoutResult(ActionContext.java:561) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTaskInner(DefaultTaskRunner.java:85) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTaskInnerWithMetric(DefaultTaskRunner.java:130) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.run.impl.DefaultTaskRunner.doTask(DefaultTaskRunner.java:176) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.execute.TaskExecuteService.doTask(TaskExecuteService.java:35) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.execute.redisson.impl.RedissonTaskImpl.doTask(RedissonTaskImpl.java:22) [logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_102]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_102]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_102]     at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_102]     at org.redisson.RedissonRemoteService.invokeMethod(RedissonRemoteService.java:360) [redisson-3.11.3.jar!/:3.11.3]     at org.redisson.RedissonRemoteService.lambda$executeMethod$4(RedissonRemoteService.java:329) [redisson-3.11.3.jar!/:3.11.3]     at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[na:1.8.0_102]     at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_102]     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_102]     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_102]     at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_102] Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.     at sun.reflect.GeneratedConstructorAccessor129.newInstance(Unknown Source) ~[na:na]     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_102]     at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_102]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:1259) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:575) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1829) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     ... 25 common frames omitted Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure  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.     at sun.reflect.GeneratedConstructorAccessor126.newInstance(Unknown Source) ~[na:na]     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_102]     at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_102]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:555) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:725) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:664) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:979) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1153) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:951) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar!/:na]     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar!/:na]     at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at logwire.web.service.query.sql.QueryDataSetManager.insertModelRow(QueryDataSetManager.java:419) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.query.sql.QueryDataSetManager.saveModelDataSet(QueryDataSetManager.java:140) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.query.sql.QueryDataSetManager.saveModelDataSet(QueryDataSetManager.java:104) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.query.sql.QueryDataSetManager.insert(QueryDataSetManager.java:272) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.query.QueryService.insert(QueryService.java:183) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.query.QueryService.insert(QueryService.java:163) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at jdk.nashorn.internal.scripts.Script$Recompilation$203$16161AAAAAAA$importHandlers.L:1$insertLog(config/custom_js/importHandlers.js:449) ~[na:na]     at jdk.nashorn.internal.scripts.Script$Recompilation$198$9472A$tasks.L:1$handleImport$L:256(config/custom_js/tasks.js:273) ~[na:na]     at jdk.nashorn.javaadapters.java.util.function.Consumer.accept(Unknown Source) ~[na:na]     at java.util.ArrayList.forEach(ArrayList.java:1249) ~[na:1.8.0_102]     at jdk.nashorn.internal.scripts.Script$Recompilation$196$9015AAA$tasks.L:1$handleImport(config/custom_js/tasks.js:256) ~[na:na]     at jdk.nashorn.internal.scripts.Script$Recompilation$195$8841AAA$tasks.L:1$partsImportDD(config/custom_js/tasks.js:239) ~[na:na]     at jdk.nashorn.internal.runtime.ScriptFunctionData.invoke(ScriptFunctionData.java:643) ~[nashorn.jar:na]     at jdk.nashorn.internal.runtime.ScriptFunction.invoke(ScriptFunction.java:494) ~[nashorn.jar:na]     at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:393) ~[nashorn.jar:na]     at jdk.nashorn.api.scripting.ScriptObjectMirror.callMember(ScriptObjectMirror.java:199) ~[nashorn.jar:na]     at logwire.core.meta.task.ScriptTask.doTask(ScriptTask.java:24) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at logwire.web.service.task.run.impl.DefaultTaskRunner$1.doInTransactionWithoutResult(DefaultTaskRunner.java:88) ~[logwire-web-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]     at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:36) ~[spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]     ... 18 common frames omitted Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.     at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:549) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]     ... 53 common frames omitted

2. 直連mysql測試

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

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

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

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

mycat日志如下：

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 ser=root,txIsolation=3, autocommit=true, schema=tms_prod, executeSql=null] 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)過查找資料和分析，得出該問題主要是由于連接時間過長，失效所致，既然程序獲取的連接已經(jīng)失效了，那么對于失效的連接就不應(yīng)該使用，而應(yīng)該再次獲取有效的連接。增加配置如下：

spring.datasource.validation-query=SELECT 1

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

總結(jié)

當(dāng)數(shù)據(jù)庫連接池中的連接被創(chuàng)建而長時間不使用的情況下，該連接會自動回收并失效，但客戶端并不知道，在進行數(shù)據(jù)庫操作時仍然使用的是無效的數(shù)據(jù)庫連接，這樣，就導(dǎo)致客戶端程序報“  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ù)庫連接來使用。

上述內(nèi)容就是生產(chǎn)環(huán)境數(shù)據(jù)庫連接超時自動回收問題及解決方法，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。