odd SQLException - Could not retrieve transation read-only status server,怎么处理?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (445)

我有一个每5分钟在MySQL数据库中执行一次存储过程的作业,由于某种原因,每3次执行中就有1次会失败,从而产生这个奇怪的异常。我一直在寻找这个异常的含义,但我找不到解决办法。下面是完整的堆栈跟踪:

java.sql.SQLException: Could not retrieve transation read-only status server
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
    at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3939)
    at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3910)
    at com.mysql.jdbc.PreparedStatement.checkReadOnlySafeStatement(PreparedStatement.java:1258)
    at com.mysql.jdbc.CallableStatement.checkReadOnlySafeStatement(CallableStatement.java:2656)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1278)
    at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:920)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3044)
    at org.deadmandungeons.website.tasks.RankUpdateTask.execute(RankUpdateTask.java:30)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 1,198,219 milliseconds ago.  The last packet sent successfully to the server was 950,420 milliseconds ago.
    at sun.reflect.GeneratedConstructorAccessor43.newInstance(Unknown Source)
    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:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3673)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4113)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2761)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
    at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3933)
    ... 9 more
Caused by: java.net.SocketException: Connection timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:150)
    at java.net.SocketInputStream.read(SocketInputStream.java:121)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3573)
    ... 17 more

所以我想它是超时了,因为它认为MySQL服务器处于只读状态?

提问于
用户回答回答于

在此MySQL服务器上有管理权限吗?如果你做了,你应该尝试设置

FLUSH TABLES WITH READ LOCK;
SET GLOBAL READ_ONLY=ON;

作为重现错误的测试。此命令使您的数据库不可写,因此在还原此配置之前,您将无法在其中添加数据,显然是使用

SET GLOBAL READ_ONLY=0;
UNLOCK TABLES;

如果这个测试的结果是肯定的(同样的错误被复制了),那么您应该尝试隔离存储在数据库中的数据的应用程序,以找出哪个应用程序与Quartz冲突。

用户回答回答于

堆栈跟踪显示

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

这意味着JDBC和DB之间的链接被破坏了,根据您的观察,您可以说每三个作业调用中就有一个失败。你可以试着设置unreturnedConnectionTimeoutdebugUnreturnedConnectionStackTraces...。这将使您更深入地了解连接泄漏或需要很长时间的db调用。

扫码关注云+社区