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

Question

I have a Quartz Job 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:

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?

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

Answer 1

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.

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

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

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

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

Answer 2

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

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.

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