Java 奇怪的 SQLException - 无法检索事务只读状态服务器

odd SQLException - Could not retrieve transation read-only status server

提问by Jon McPherson

I have a QuartzJob that executes a Stored Procedure in my MySQL database once every 5 minutes, and for some reason, 1 out of 3 executions fails and gives this weird exception. I have searched and searched for what this exception means, but I could not find a solution. Here is the full stack trace:

我有一个Quartz作业，它每 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

So I figured it is timing out because it thinks the MySQL server is in read-only status? This only happens for this quartz job, and not any other time when I communicate with the database. This execution is of course happening in another thread, but I don't think that would have anything to do with it. Why would it think the server was in read-only mode?

所以我认为它超时是因为它认为 MySQL 服务器处于只读状态？这仅发生在这个石英作业中，而在我与数据库通信时不会发生。这种执行当然发生在另一个线程中，但我认为这与它无关。为什么它会认为服务器处于只读模式？

Also, I don't think "transation" is a word, so there's that...

另外，我不认为“翻译”是一个词，所以...

回答by Carlos Eduardo Santos

Research takes nowhere, as you guys said, but the error shows what seems to be a Database being populated by two applications at the same time.

正如你们所说，研究无济于事，但错误显示似乎是同时由两个应用程序填充的数据库。

Do you have admin privileges on this MySQL server? If you do, you should try setting

你在这个 MySQL 服务器上有管理员权限吗？如果你这样做，你应该尝试设置

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

as a test to reproduce the error. Just to warn you, this command makes your database unwritable, so you will not be able to add data in it until you revert this configuration, obviously with

作为重现错误的测试。只是为了警告您，此命令使您的数据库不可写，因此您将无法在其中添加数据，直到您恢复此配置，显然是

SET GLOBAL READ_ONLY=0;
UNLOCK TABLES;

If the result of this test is positive (same error had been reproduced), you should try isolating applications that are storing data on your database, to find out which one is conflicting with Quartz.

如果此测试的结果是肯定的（已重现相同的错误），您应该尝试隔离在您的数据库中存储数据的应用程序，以找出与 Quartz 冲突的应用程序。

I'm sorry for being vague, but I hope it gives you some help...

我很抱歉含糊不清，但我希望它能给你一些帮助......

回答by blob

Sorry for posting on old thread,

很抱歉在旧线程上发帖，

As stack trace says

正如堆栈跟踪所说

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

This implies the link between JDBC and DB is broken.As per your observation you say 1 out of 3 job invocations fails.

这意味着 JDBC 和 DB 之间的链接已断开。根据您的观察，您说 3 次作业调用中有 1 次失败。

You have these jobs scheduled every 5 minutes and as per trace the last successful message sent to server is ~15 minutes before.

您每 5 分钟安排一次这些作业，并且根据跟踪发送到服务器的最后一条成功消息是大约 15 分钟之前。

Hence I suspect either

因此我怀疑

1. You are procedure is not returning (waiting on something)
2. The JDBC connection has been invalidated by the firewall/ proxy

1. 你是程序没有返回（等待某事）
2. JDBC 连接已被防火墙/代理失效

It will interesting to see the how connections are managed, As per logs I see you are using c3p0.

看看如何管理连接会很有趣，根据日志，我看到您正在使用 c3p0。

You can try setting unreturnedConnectionTimeoutand debugUnreturnedConnectionStackTraces. This will give you more insight into connection leaks or db calls which are taking long.

您可以尝试设置unreturnedConnectionTimeout和debugUnreturnedConnectionStackTraces。这将使您更深入地了解需要很长时间的连接泄漏或数据库调用。