Solved MySQL losing connection after 15 or so minutes?

Everything works fine in my plugin for about 15 or so minutes but all of a sudden I will get this error message when a player joins

Code:

[12:16:11] [User Authenticator #16/INFO]: UUID of player samuelh176 is 4c0c4f88-6bb5-454b-9863-701ed05d0f51
[12:16:11] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
[12:16:11] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[12:16:11] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
[12:16:11] [Server thread/WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[12:16:11] [Server thread/WARN]: at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:382)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1213)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1200)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2485)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2467)
[12:16:11] [Server thread/WARN]: at me.beanonaboard.Leveling.MySQLManager.getPlayerScore(MySQLManager.java:53)
[12:16:11] [Server thread/WARN]: at me.beanonaboard.Leveling.LevelingSystem.newSetScoreboard(LevelingSystem.java:75)
[12:16:11] [Server thread/WARN]: at me.beanonaboard.Leveling.playerJoinEvent.onPlayerJoinEvent(playerJoinEvent.java:21)
[12:16:11] [Server thread/WARN]: at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[12:16:11] [Server thread/WARN]: at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
[12:16:11] [Server thread/WARN]: at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[12:16:11] [Server thread/WARN]: at java.lang.reflect.Method.invoke(Method.java:606)
[12:16:11] [Server thread/WARN]: at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:292)
[12:16:11] [Server thread/WARN]: at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
[12:16:11] [Server thread/WARN]: at org.bukkit.plugin.TimedRegisteredListener.callEvent(TimedRegisteredListener.java:30)
[12:16:11] [Server thread/WARN]: at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
[12:16:11] [Server thread/WARN]: at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.PlayerList.c(PlayerList.java:265)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.PlayerList.a(PlayerList.java:152)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.LoginListener.c(LoginListener.java:105)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.LoginListener.a(LoginListener.java:43)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.NetworkManager.a(NetworkManager.java:183)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.ServerConnection.c(ServerConnection.java:81)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.MinecraftServer.v(MinecraftServer.java:713)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.DedicatedServer.v(DedicatedServer.java:283)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.MinecraftServer.u(MinecraftServer.java:576)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.MinecraftServer.run(MinecraftServer.java:482)
[12:16:11] [Server thread/WARN]: at net.minecraft.server.v1_7_R3.ThreadServerApplication.run(SourceFile:628)
[12:16:11] [Server thread/WARN]: Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 6,974,983 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
[12:16:11] [Server thread/WARN]: at sun.reflect.GeneratedConstructorAccessor143.newInstance(Unknown Source)
[12:16:11] [Server thread/WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[12:16:11] [Server thread/WARN]: at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1474)
[12:16:11] [Server thread/WARN]: at me.beanonaboard.Leveling.MySQLManager.getPlayerScore(MySQLManager.java:54)
[12:16:11] [Server thread/WARN]: ... 22 more
[12:16:11] [Server thread/WARN]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2529)
[12:16:11] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
[12:16:11] [Server thread/WARN]: ... 30 more

I am not sure of the best way to solve it, I have tried checking if the connection to the database is open or not first (and then opening it if it is not)
But the code that is causing the error is this one below

Code:java

    public int getPlayerScore(Player p) throws SQLException {
        String pname = p.getUniqueId().toString();
        if(!this.db.checkConnection())
            this.db.openConnection();
        Statement statement = this.db.getConnection().createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM PlayerStats WHERE UUID = '" + pname + "';");
 
        if(!rs.next()) return 0;
 
        if(rs.getString("Name") != p.getName())
        {
            this.updateName(p);       
            }
 
        return rs.getInt("LevelXP");
 
    }

Thank you for your help in advance!

 

i dont see issue, mysql drop connection due inactivity by default, this is expected behaiviour.

you shoud fix you mysql.

 

Ok thank you, so is there a way to prevent the connection from being closed/timing out (in phpmyadmin)?

dw got it thank you anyway

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

How did you do it? Maybe nice to know for others with the same problem

 

there is configuration for mysql.
read documentation and everything will go smooth

 

RawCode
Nop it turns out what I did, did not solve my issue (I changed the login timout) but still no luck

I think it might have something to do with the configuration time-out settings not saving (will test and let you know)?

 

Don't keep and rely on a constantly open connection. You should have an instantiable MySQL class that you can use for your connections.

 

Ah, I remember having this problem a while back. Are you by any chance using a shared host?

Is there any real point in closing the connection if it's constantly being reopened?

 

Wizehh "Is there any real point in closing the connection if it's constantly being reopened?"

That's not what he's suggesting, he's suggesting that the OP have a class that keeps track of all this data.

According to this post on stack overflow, your best bet would be to run a dummy query on the database before serving it, and if it fails, to open a new connection.

 

Slightly different solution, you should either use a connection pool or close your connection after you have finished. An example (using my own class with AutoCloseable):

Code:java

try (MySQL db = new MySQL()) {
    db.open();
    PreparedStatement stmt = db.prepare("SELECT * FROM `example` WHERE `id` = ?");
    stmt.setInt(1, 42);
    ResultSet set = stmt.executeQuery();
    // work with set
} catch (SQLException ex) {
    // error querying database
}

This is one of the two "preferred" solutions, with the other being a connection pool.

 

Yes it is on a shared host

 

I recommend using a connection pool, but a less attractive way of keeping a single connection open would be to use an asynchronous task timer to either pass a query or close and re-open the connection.

 

RoGue @mythbusterma

Thank you for all your help in the end to solve the issue (the easiest way for me), was to use a repeating task to pass a query. As I realised that I was already establishing a connection using a repeating task (updating signs), so I just decreased the time of the repeating task in the end (from every hour to 15 minutes). Which worked!

But again thank you for all your help!!!