本文探讨了在进行数据库批量操作时遇到的关于read-only status server的SQLException，揭示了其实际原因是连接超时。解决办法包括优化耗时业务、调整数据库连接参数或延长超时时间。
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:949) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3976) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3947) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.PreparedStatement.checkReadOnlySafeStatement(PreparedStatement.java:1215) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1235) ~[mysql-connector-java-5.1.30.jar:na]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) ~[druid-1.0.5.jar:1.0.5]
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.0.5.jar:1.0.5]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) ~[druid-1.0.5.jar:1.0.5]
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:118) ~[druid-1.0.5.jar:1.0.5]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) ~[druid-1.0.5.jar:1.0.5]
at sun.reflect.GeneratedMethodAccessor163.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55) ~[mybatis-3.2.4.jar:3.2.4]
at com.sun.proxy.$Proxy232.execute(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) ~[mybatis-3.2.4.jar:3.2.4]
at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) [mybatis-3.2.4.jar:3.2.4]
at com.sun.proxy.$Proxy230.update(Unknown Source) [na:na]
... 43 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 4,087,194 milliseconds ago. The last packet sent successfully to the server was 4,087,420 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_131]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_131]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_131]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3983) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2596) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569) ~[mysql-connector-java-5.1.30.jar:na]
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3970) ~[mysql-connector-java-5.1.30.jar:na]
... 66 common frames omitted
Caused by: java.net.SocketException: Broken pipe (Write failed)
at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.8.0_131]
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111) ~[na:1.8.0_131]
at java.net.SocketOutputStream.write(SocketOutputStream.java:155) ~[na:1.8.0_131]
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_131]
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.8.0_131]
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3964) ~[mysql-connector-java-5.1.30.jar:na]
... 72 common frames omitted
在使用mysql数据库时，一次批量操作出现上述问题：乍一看：Could not retrieve transation read-only status server，不知道是个什么东西，看看源码可以知道这东西只是在执行更新操作时检查连接是否是只读，只读情况下不允许更新操作(或插入)。
/**
* Tests to see if the connection is in Read Only Mode. Note that prior to 5.6,
* we cannot really put the database in read only mode, but we pretend we can by
* returning the value of the readOnly flag
*
* @param useSessionStatus in some cases, for example when restoring connection with autoReconnect=true,
* we can rely only on saved readOnly state, so use useSessionStatus=false in that case
*
* @return true if the connection is read only
* @exception SQLException if a database access error occurs
*/
public boolean isReadOnly(boolean useSessionStatus) throws SQLException {
if (useSessionStatus && !this.isClosed && versionMeetsMinimum(5, 6, 5) && !getUseLocalSessionState()) {
java.sql.Statement stmt = null;
java.sql.ResultSet rs = null;
try {
try {
stmt = getMetadataSafeStatement();
rs = stmt.executeQuery("select @@session.tx_read_only");
if (rs.next()) {
return rs.getInt(1) != 0; // mysql has a habit of tri+ state booleans
}
} catch (SQLException ex1) {
// 这里抛的错误，导致该错误原因很多，所以这个Could not retrieve transation read-only status server 说明不了根本问题，要看根源错误在这个ex1异常上
if (ex1.getErrorCode() != MysqlErrorNumbers.ER_MUST_CHANGE_PASSWORD || getDisconnectOnExpiredPasswords()) {
throw SQLError.createSQLException(
"Could not retrieve transation read-only status server",
SQLError.SQL_STATE_GENERAL_ERROR, ex1, getExceptionInterceptor());
}
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception ex) {
// ignore
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception ex) {
// ignore
}
stmt = null;
}
}
}
return this.readOnly;
}
然后，注意异常堆栈信息那个The last packet successfully received from the server was 4,087,194 milliseconds ago....这才是根源问题，导致：Could not retrieve transation read-only status server的问题很多，此处导致该问题的原因是：批量操作耗时太长并且该业务方法加了transaction事务，导致数据库连接会话开启后，许久未用(在处理其他业务逻辑)，然后连接关闭了或者废掉了不可用了，或者超出mysql服务器配置的超时时间了，当耗时业务处理完毕执行更新操作时，抛出上述异常，因为连接已经不可用所以在执行select @@session.tx_read_only时(检查连接是否只读)就报错了，抛出来一个异常，对原异常做了一层包装。所以根本问题是被包装的那个异常，连接超时了。解决方式就是耗时业务优化一下，或者使用参数testWhileIdle、testOnBorrow，或者增加mysql服务器超时时间。
扫一扫
1103
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
