代码之家 › 专栏 › 技术社区 › Tobia

Mariadb服务器出现SpringJDBC套接字写入错误的原因是什么?

spring-jdbc mariadb sockets java

Tobia · 技术社区 · 6 年前

我的Tomcat应用服务器有一个SpringJDBC模板服务来连接到本地Mariadb数据库。

我使用的是Spring JDBC库版本4.2.3、MiRADB Java客户机库版本2.0.1和MariaDB版本101.18。

有时,我可以随机地说,我在应用服务器和数据库之间的查询操作中遇到了这个错误:

java.net.socketException:软件导致连接中止:套接字写入错误

这是全叠

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [select exists (select 1 from smartcard where status>=?)]; (conn:13) Could not send query: Software caused connection abort: socket write error; nested exception is java.sql.SQLNonTransientConnectionException: (conn:13) Could not send query: Software caused connection abort: socket write error
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:79) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:790) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:814) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at com.mypackage.dao.SmartcardDao.hasPronte(SmartcardDao.java:83) ~[classes/:?]
    at com.mypackage.smartcard.SmartcardService.isReady(SmartcardService.java:215) ~[classes/:?]
    at com.mypackage.check.CheckService.isReady(CheckService.java:362) ~[classes/:?]
    at com.mypackage.check.CheckService.getPing(CheckService.java:398) ~[classes/:?]
    at com.mypackage.check.CheckService.getPingAuth(CheckService.java:408) ~[classes/:?]
    at com.mypackage.remote.update.UpdateService.sendPing(UpdateService.java:48) ~[classes/:?]
    at com.mypackage.check.CheckService$2.run(CheckService.java:170) [classes/:?]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_131]
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [?:1.8.0_131]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_131]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [?:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
    at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]
Caused by: java.sql.SQLNonTransientConnectionException: (conn:13) Could not send query: Software caused connection abort: socket write error
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:156) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:118) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:229) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:208) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:147) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:161) ~[mariadb-java-client-2.0.1.jar:?]
    at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_131]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc.jar:?]
    at com.sun.proxy.$Proxy47.executeQuery(Unknown Source) ~[?:?]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    ... 19 more
Caused by: java.sql.SQLException: Could not send query: Software caused connection abort: socket write error
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:1428) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:217) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:203) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:147) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:161) ~[mariadb-java-client-2.0.1.jar:?]
    at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_131]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc.jar:?]
    at com.sun.proxy.$Proxy47.executeQuery(Unknown Source) ~[?:?]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    ... 19 more
Caused by: java.net.SocketException: Software caused connection abort: socket write error
    at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:1.8.0_131]
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111) ~[?:1.8.0_131]
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155) ~[?:1.8.0_131]
    at org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer(StandardPacketOutputStream.java:101) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush(AbstractPacketOutputStream.java:157) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:210) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:203) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:147) ~[mariadb-java-client-2.0.1.jar:?]
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:161) ~[mariadb-java-client-2.0.1.jar:?]
    at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_131]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc.jar:?]
    at com.sun.proxy.$Proxy47.executeQuery(Unknown Source) ~[?:?]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    ... 19 more

这是数据源对象属性:

org.apache.tomcat.jdbc.pool.DataSource@10a3e2b{ConnectionPool[
 defaultAutoCommit=null;
 defaultReadOnly=null;
 defaultTransactionIsolation=-1;
 defaultCatalog=null;
 driverClassName=org.mariadb.jdbc.Driver;
 maxActive=10;
 maxIdle=5;
 minIdle=2;
 initialSize=5;
 maxWait=30000;
 testOnBorrow=false;
 testOnReturn=false;
 timeBetweenEvictionRunsMillis=5000;
 numTestsPerEvictionRun=0;
 minEvictableIdleTimeMillis=60000;
 testWhileIdle=false;
 testOnConnect=false;
 password=********;
 url=jdbc:mariadb://localhost/mydb?autoReconnect=true&useSSL=false;
 username=myuser;
 validationQuery=null;
 validationQueryTimeout=-1;
 validatorClassName=null;
 validationInterval=3000;
 accessToUnderlyingConnectionAllowed=true;
 removeAbandoned=true;
 removeAbandonedTimeout=60;
 logAbandoned=true;
 connectionProperties=null;
 initSQL=null;
 jdbcInterceptors=null;
 jmxEnabled=true;
 fairQueue=true;
 useEquals=true;
 abandonWhenPercentageFull=0;
 maxAge=0;
 useLock=false;
 dataSource=null;
 dataSourceJNDI=null;
 suspectTimeout=0;
 alternateUsernameAllowed=false;
 commitOnReturn=false;
 rollbackOnReturn=false;
 useDisposableConnectionFacade=true;
 logValidationErrors=false;
 propagateInterruptState=false;
 ignoreExceptionOnPreLoad=false;
 }

请考虑数据库计算机和应用程序计算机相同,这是连接字符串:

jdbc:mariadb://localhost/mydb?autoReconnect=true&useSSL=false

原因可能是什么? 我在Windows(Win 10)和Linux(Ubuntu Server 16.04.2 LTS)机器中都遇到了这个问题。

1 回复 | 直到 6 年前

Gimby 6 年前

这闻起来像是池中由服务器关闭的陈旧连接。不可能确切地说,因为这只是一个症状,可以有许多根本原因。然而,这是一个常见的问题。

jdbc url上有一个“autoconnect”属性,听起来应该可以解决所有问题,对吧?autoreconnect最初的mysql描述是

如果启用,驱动程序将对在上发出的查询引发异常属于当前事务的过时或死连接, 但会尝试重新连接 在发出下一个查询之前 上新事务中的连接

( source )

强调我的。所以这个属性可能不会像你想象的那样。这个 existing Stack Overflow question 进一步证实这一点。不过,我不知道Mariadb是否有什么变化,我真的找不到任何具体的文件。

防止池中过时连接的最佳方法是让连接池为您执行此操作,您可以为此定义数据源属性 试验借用 , 测试连接 , 测试设备 ,如本文所示 existing answer . testonborrow可能足够了。我会引用它以防链接中断:

spring.datasource.tomcat.testOnBorrow=true 
spring.datasource.tomcat.validationQuery=SELECT 1

这假设是一个较新版本的SpringBoot,不管您使用的是什么Spring,都不难将其转换成这个版本。