SQL/JAVA Communications link error occasionally happens JDBC

Question

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

experiencing this error, If someone could use the code I provided to fix it in their answer that would be great. I'm not good with SQL I'm just helping out a friend.

Here is the code:

public static Connection con = null;
public static Statement stmt;
public static boolean connectionMade;
public static void createConnection() {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://localhost/highscores","root","root");
        stmt = con.createStatement();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
public static ResultSet query(String s) throws SQLException {
    try {
        if (s.toLowerCase().startsWith("select")) {
            ResultSet rs = stmt.executeQuery(s);
            return rs;
        } else {
            stmt.executeUpdate(s);
        }
        return null;
    } catch (Exception e) {
        destroyConnection();
        createConnection();
        e.printStackTrace();
    }
    return null;
}
public static void destroyConnection() {
    try {
        if(stmt != null)
        stmt.close();
        if(con!= null)
        con.close();
        connectionMade = false;
    } catch (Exception e) {
        e.printStackTrace();
    }
}

full stack trace:

[9/07/15 19:23]: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 10 ms ago.
[9/07/15 19:23]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[9/07/15 19:23]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[9/07/15 19:23]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[9/07/15 19:23]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[9/07/15 19:23]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
[9/07/15 19:23]:        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
[9/07/15 19:23]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
[9/07/15 19:23]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
[9/07/15 19:23]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
[9/07/15 19:23]:        at server.util.SQL.query(SQL.java:29)
[9/07/15 19:23]:        at server.util.SQL.saveHighScore(SQL.java:55)
[9/07/15 19:23]:        at server.model.mobile.players.Client.logout(Client.java:581)
[9/07/15 19:23]:        at server.model.mobile.players.packets.Clicking.ClickingButtons.processPacket(ClickingButtons.java:2190)
[9/07/15 19:23]:        at server.model.mobile.players.PacketHandler.processPacket(PacketHandler.java:118)
[9/07/15 19:23]:        at server.model.mobile.players.Client.processQueuedPackets(Client.java:769)
[9/07/15 19:23]:        at server.model.mobile.players.PlayerHandler.process(PlayerHandler.java:191)
[9/07/15 19:23]:        at server.Server$1.execute(Server.java:103)
[9/07/15 19:23]:        at server.task.Task2.tick(Task2.java:105)
[9/07/15 19:23]:        at server.event.TaskScheduler.run(TaskScheduler.java:100)
[9/07/15 19:23]:        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[9/07/15 19:23]:        at java.lang.Thread.run(Unknown Source)
[9/07/15 19:23]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2431)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882)
[9/07/15 19:23]:        ... 24 more
[9/07/15 19:23]: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 10 ms ago.
[9/07/15 19:23]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[9/07/15 19:23]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[9/07/15 19:23]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[9/07/15 19:23]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[9/07/15 19:23]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
[9/07/15 19:23]:        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
[9/07/15 19:23]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
[9/07/15 19:23]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
[9/07/15 19:23]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
[9/07/15 19:23]:        at server.util.SQL.query(SQL.java:29)
[9/07/15 19:23]:        at server.util.SQL.saveHighScore(SQL.java:55)
[9/07/15 19:23]:        at server.model.mobile.players.Client.logout(Client.java:581)
[9/07/15 19:23]:        at server.model.mobile.players.packets.Clicking.ClickingButtons.processPacket(ClickingButtons.java:2190)
[9/07/15 19:23]:        at server.model.mobile.players.PacketHandler.processPacket(PacketHandler.java:118)
[9/07/15 19:23]:        at server.model.mobile.players.Client.processQueuedPackets(Client.java:769)
[9/07/15 19:23]:        at server.model.mobile.players.PlayerHandler.process(PlayerHandler.java:191)
[9/07/15 19:23]:        at server.Server$1.execute(Server.java:103)
[9/07/15 19:23]:        at server.task.Task2.tick(Task2.java:105)
[9/07/15 19:23]:        at server.event.TaskScheduler.run(TaskScheduler.java:100)
[9/07/15 19:23]:        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[9/07/15 19:23]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[9/07/15 19:23]:        at java.lang.Thread.run(Unknown Source)
[9/07/15 19:23]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2431)
[9/07/15 19:23]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882)
[9/07/15 19:23]:        ... 24 more

The root cause seems to `java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.`. Maybe [this can help](http://stackoverflow.com/questions/13950496/what-is-java-io-eofexception-message-can-not-read-response-from-server-expect) — MadProgrammer, Jul 13 '15 at 00:54
Since `createConnection` doesn't appear in the stack trace, it's possible that you're using a stale `Connection` which has already been closed... — MadProgrammer, Jul 13 '15 at 00:55
Rather then doing `stmt = con.createStatement();` this in `createConnection`, create a `stmt` each time you need it from active connection (and make you close it when you're done). Also you can use `Connection#isClosed` and `Connection#isValid` to test the connection and recreate it as required. You might also consider using a `ConnectionPool` of some kind to take care of this automatically for you — MadProgrammer, Jul 13 '15 at 01:05
Can you show that with the code I provided? Sorry i'm not great at this just doing it for my friend. It is for an automatic system. @MadProgrammer — Joliquine4, Jul 13 '15 at 01:18
Not really, the code you've provided is only part of the problem — MadProgrammer, Jul 13 '15 at 01:19
I added the rest of the connection code just now. @MadProgrammer — Joliquine4, Jul 13 '15 at 01:23

score 1 · Accepted Answer · answered Jul 13 '15 at 01:28

Generally speaking, it appears that the Connection and Statement which you are relying has been closed, either explicitly be yourself or implicitly by the server.

As a general good practice, you should be creating a new Statement only when you need it and closing it when you're doing, for example...

public Connection createConnection() throws SQLException {
    Connection con = null;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://localhost/highscores", "root", "root");
    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException ex) {
        throw new SQLException("Unable to load JDBC driver", ex);
    }
    return con;
}

public void makeQuery() throws SQLException {

    try (Connection con = createConnection()) {
        try (PreparedStatement stmt = con.prepareStatement("...")) {
            // Fill in parameters to the statement
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    // Get the results
                }
            }
        }
    }

}

Good practice would also suggest that you make use of PreparedStatements to ensure that you code doesn't fall victim to SQL inject attacks

Have a look at The try-with-resources Statement and Using Prepared Statements for more details.

I would also encourage you to do some research into connection pools, as it will help save you time when creating multiple connections and help manage their life cycle.

I'd also beware of using static, as this raises the risk of threading issues, having one thread close the connection while another thread is trying to use it.

Thanks a lot. My code is now in line with your suggestions (however messy) and it seems to be working well. @MadProgrammer — Joliquine4, Jul 13 '15 at 05:03

SQL/JAVA Communications link error occasionally happens JDBC

1 Answers1