java程序运行中突然报如下错误:
### Error querying database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server
### The error may exist in file [F:\duijie\ATEA\change_0606\shopweb-plugin-abm-atea_0619\classes\com\hanshows\plugin\dao\IntegrationTaskMapper.xml]
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
### The error may exist in file [F:\duijie\ATEA\change_0606\shopweb-plugin-abm-atea_0619\classes\com\hanshows\plugin\dao\IntegrationTaskMapper.xml]
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 611,444 milliseconds ago. The last packet sent successfully to the server was 23 milliseconds ago.
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:3166)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3620)
... 35 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:3166)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3620)
... 35 more
报错如上。网上查询很多说是mysql-connector-java-5.1.28与数据库不匹配的问题,但是我这个不是,程序正常运行没有问题,但是一旦手动重启MYSQL服务就会报这个错误。
查了下网上,在Spring配置DBCP连接池的时候,在定义datasource增加属性
validationQuery
和
testOnBorrow就可以了。重新运行程序,手动重启mysql上面错误不见了。
bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.user.account}"/>
<property name="password" value="${db.user.password}"/>
<property name="maxActive" value="5"/>
<property name="maxIdle" value="5"/>
<property name="maxWait" value="60000"/>
<property name="defaultAutoCommit" value="true"/>
<property name="logAbandoned" value="true"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="600"/>
<property name="validationQuery" value="SELECT 1" />
<property name="testOnBorrow" value="true"/>
</bean>
如果是c3p0连接池则在定义datasource的时候,添加属性testConnectionOnCheckin
和testConnectionOnCheckout
,如:
<bean name="cacheCloudDB" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${cache.url}"/>
<property name="user" value="${cache.user}"/>
<property name="password" value="${cache.password}"/>
<property name="initialPoolSize" value="10"/>
<property name="maxPoolSize" value="${cache.maxPoolSize}"/>
<property name="testConnectionOnCheckin" value="false"/>
<property name="testConnectionOnCheckout" value="true"/>
<property name="preferredTestQuery" value="SELECT 1"/>
</bean>
如果使用的是JDBC,在JDBC URL上添加
?autoReconnect=true
,如:jdbc:mysql://10.10.10.10:3306/mydb?autoReconnect=true