Solved MySQL keeps disconnecting

Discussion in 'Spigot Plugin Development' started by mlgcraftnetwork, May 28, 2016.

  1. I'm having this problem were my mysql datatbase connection stops working after a while, I already added some code that should open the connection again if its closed but that doesn't work. This is the error I'm getting:
    Code (Text):
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 38.165.066 milliseconds ago.  The last packet sent successfully to the server was 38.165.067 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_91]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3644) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2452) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.Ranks.getRank(Ranks.java:40) ~[?:?]
        at mcgglobal.Ranks.checkDefaultRank(Ranks.java:26) ~[?:?]
        at mcgglobal.Listeners.onJoin(Listeners.java:51) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 4 more
    Caused by: java.net.SocketException: Software caused connection abort: socket write error
        at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:1.8.0_91]
        at java.net.SocketOutputStream.socketWrite(Unknown Source) ~[?:1.8.0_91]
        at java.net.SocketOutputStream.write(Unknown Source) ~[?:1.8.0_91]
        at java.io.BufferedOutputStream.flushBuffer(Unknown Source) ~[?:1.8.0_91]
        at java.io.BufferedOutputStream.flush(Unknown Source) ~[?:1.8.0_91]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3626) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2452) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.Ranks.getRank(Ranks.java:40) ~[?:?]
        at mcgglobal.Ranks.checkDefaultRank(Ranks.java:26) ~[?:?]
        at mcgglobal.Listeners.onJoin(Listeners.java:51) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 4 more
    Is this a problem with my code or my database?
     
  2. There your solve stands. It's the wrong configured Database
     
  3. Can you post your code please? If you are not able to configure your database server, a reconnect is the right way. I think you have a error in your reconnect method.

    Let me helo you there ;)
     
  4. This is what I made hoping this would solve the problem, I first used == null instead on .isClosed() so this may work but == null didn't.
    Code (Text):
            if(Main.c.isClosed()){
                try {
                    Main.c = Main.MySQL.openConnection();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
    But I think the problem is the MySQL servers "'wait_timeout'" setting, can I turn that off or something?
     
  5. I think it's not possible to disable the timeout of mysql. Maybe you can prevent the timeout by sending useless commands every hour for example like "use <database>;" but this is not a good way.

    I am not at home. I know that I have a full working mysql class with reconnect. But can't get this one here. I mean my solution was to check the mysql connection status before/while every use, if the status was bad (it was a integer, idk which, like "1" or something like this) it initialized a reconnect and tried again.

    If you want i can send you the class tomorrow, or you can get the solution by yourself.
     
  6. md_5

    Administrator Developer

    ==null certainly wont work. The above should.
     
  7. Tux

    Tux

    Use a connection pool
     
  8. Well this code isn't working for some weird reason, when a player joins the server after the connection has been broken basicly the whole plugin is broken because its not able to get the players data even though I pasted this code to check the connection in front of all my mysql interaction code. Any suggestions how to fix this? Thanks.

    UPDATE:
    I just joined my server for the second time after the connection lost and it seems like it DID reconnect, but it didn't reconnect in time to get the data for the first time joining. It kinda rude but I will add a kicking system that kicks the player who's data was not loaded xD.
     
  9. Btw do you know how I can get the location of all enderchests in a specific world? I created a thread for this but I have not found a way to do this yet. Thanks for your help.