17

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...

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Jon McPherson
  • 2,495
  • 4
  • 23
  • 35
  • 2
    What MySQL version are you using? See here: http://bugs.mysql.com/bug.php?id=70290 If you google the exact message, you'll find other pages too related to this message. – peter.petrov Feb 08 '14 at 21:38
  • @peter.petrov The MySQL version is 5.6.10-log which is relatively new. Also, in that thread, their problem was that a certain variable was not supported. This stack trace shows something a little different though. I have tried searching for the exact message. – Jon McPherson Feb 08 '14 at 21:50
  • OK, see also here: http://www.toadworld.com/products/toad-extension-for-eclipse/f/42/t/20417.aspx They just say directly 5.6 is not yet fully tested or supported. But this is from Oct 2013. I guess you should open a bug with MySQL and see what they say. – peter.petrov Feb 08 '14 at 21:52
  • have you checked this ? http://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysql – lababo Aug 07 '15 at 02:50
  • I think it's a duplicate of http://stackoverflow.com/questions/32717417/sqlexception-could-not-retrieve-transation-read-only-status-server . And it have accepted answer – Alex Yu Dec 22 '15 at 02:12
  • 2
    I've seen the communication wrote from Java before; it usually means the client is attempting to use a connection that the server has closed. It has been almost 1000 second since the successful packet, so maybe there's a 15 minute connection timeout on the MySQL server? I don't buy it as a version issue, might be, but the network protocols for this stuff aren't moving too quickly. – erik258 Dec 31 '15 at 17:33

2 Answers2

2

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.

Dhananjay
  • 3,903
  • 2
  • 29
  • 44
1

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...

Carlos Eduardo Santos
  • 83
  • 9