What cause SpringJDBC socket write error with MariaDB server?

Question

My tomcat application server has a Spring JDBC Template service to connect to local MariaDB database.

I'm using Spring-Jdbc library version 4.2.3, Mariadb-java-client library version 2.0.1 and MariaDB version 10.1.18.

Sometimes, I can say randomly, I got this error during query operations between application server and database:

java.net.SocketException: Software caused connection abort: socket write error

this is the full stack

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

This is the DataSource object properties:

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;
 }

Consider that database machine and application machine are the same, this is the connection string:

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

What could be the cause? I experienced this problem both in windows (Win 10) and linux (Ubuntu server 16.04.2 LTS) machines.

score 0 · Accepted Answer · answered Sep 11 '18 at 11:25

This smells like a stale connection in the pool, closed by the server. Impossible to say for certain as this is only a symptom that can have many root causes. It is however a common problem to run into.

There is the glaring "autoReconnect" property that has been enabled on the jdbc url, which sounds like it should solve all problems right? The original MySQL description of autoReconnect is

If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction

(source)

Emphasis mine. So that property probably doesn't do what you are thinking it does. This existing Stack Overflow question further backs that up. I don't know if anything changed there in MariaDB though, I can't really find any specific documentation about that so quickly.

The best way to guard against stale connections in the pool is to let the connection pool do it for you, for which you can define the datasource properties testOnBorrow, testOnConnect, testOnIdle, as demonstrated in this existing answer. testOnBorrow is probably enough. I'll quote it just in case the link happens to break:

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

That assumes a moderately recent version of Spring Boot, it shouldn't be hard to translate that into however you are using Spring.

Thank you for your answer, I'm trying this solution with testOnBorrow. — Tobia, Sep 11 '18 at 13:41
I tried to set wait_timeout to a very low value and I could easily reproduce the problem, I tried to set testOnBorrow option and I worked! Thank you. — Tobia, Sep 13 '18 at 15:10

What cause SpringJDBC socket write error with MariaDB server?

1 Answers1