MySql connection over connection pool can not be restored

Question

I have a BasicDataSource instance and a jdbctemplate uses that.

So after some idle time it always says:

Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:949)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3976)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3947)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2295)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
        ... 39 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,457,725 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3970)
        ... 48 more
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.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
        ... 56 more

And the bean definition for datasource is:

<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://1.2.3.4:3306/?autoReconnectForPools=true"/>
    <property name="username" value="root"/>
    <property name="password" value="password"/>
    <property name="initialSize" value="${datasourceInitialPoolSize}"/>
</bean>

Any ideas?

this value seems to be a strange 1.2.3.4:3306 and check http://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai — kuhajeyan, Nov 03 '16 at 18:19

score 0 · Answer 1 · answered Nov 03 '16 at 17:47

0

Check if the mysql server is up. Try running "mysql", the mysql client, on the server machine. Doesn't matter how often you try to connect if the server cannot be connected to.

answered Nov 03 '16 at 17:47

Sean F

4,344
16
30

score 0 · Answer 2 · answered Nov 03 '16 at 18:14

DB connections do not last forever. Connection pools generally allow for testing the validity of a pooled connection. Your BasicDataSource supports a couple of relevant properties that you might consider using: testOnBorrow, testOnReturn, testWhileIdle, validationQueryTimeout and validationQuery.

If you want to be as sure as possible that the connection you get from the pool is good, use testOnBorrow. Your validation query can be something simple, as long as it returns at least one row. For example, "select 1".

MySql connection over connection pool can not be restored

2 Answers2