Solved Async MySQL

Discussion in 'Spigot Plugin Development' started by Willim, Jul 24, 2018.

  1. For some reason when the plugin first starts up everything works fine but after awhile of the server being on my plugin starts throwing off errors related to MySQL -

    The Error
    Code (Text):

    > [20:34:40 WARN]: Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    The last packet successfully received from the server was 1,286,749 milliseconds ago. The last packet sent successfully to the server was 16 milliseconds ago.
    > [20:34:40 WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    > [20:34:40 WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    > [20:34:40 WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    > [20:34:40 WARN]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    > [20:34:40 WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    > [20:34:40 WARN]: at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
    > [20:34:40 WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
    > [20:34:40 WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462)
    > [20:34:40 WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3903)
    > [20:34:40 WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    > [20:34:40 WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    > [20:34:40 WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    > [20:34:40 WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    > [20:34:40 WARN]: at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
    > [20:34:40 WARN]: at me.willis.permissions.configuration.SQLConfig.getGroup(SQLConfig.java:116)
    > [20:34:40 WARN]: at me.willis.permissions.util.GroupManager.getPrefix(GroupManager.java:221)
    > [20:34:40 WARN]: at me.willis.permissions.listeners.PlayerChat.onPlayerChat(PlayerChat.java:36)
    > [20:34:40 WARN]: ... 16 more
    > [20:34:40 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.
     

    The error leads to the following method -
    Code (Text):

    public String getGroup(UUID uuid) {
        if (!isConnected()) {
            connect();
        }
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT RANK FROM players WHERE UUID = ?");
            preparedStatement.setString(1, uuid.toString());

            ResultSet resultSet = preparedStatement.executeQuery();
         
            if (resultSet.next()) {
                return resultSet.getString("RANK").toLowerCase();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return plugin.getConfig().getString("DefaultGroup");
    }
     
     
  2. Mind posting the full class in a pastebin so I can see line numbers please?
    Rather, which line is 116?
     
  3. Line 116 is

    Code (Text):

    ResultSet resultSet = preparedStatement.executeQuery();
     
     
  4. So, I believe the problem is that your "connection" is getting cut after a while due to several possible things.
    I see that you're making an SQL request every time a player chats, don't do this. Some firewall settings only allow a certain number of requests in a short period of time. Making this many requests is very inefficient. You'll want to cache the player's group when they login and store it. (If you don't have a player wrapper class then I recommend storing it in a Map<Player, *GroupClass or String*>. If their rank changes, simply update the map, then push the SQL change.)
    Try making this change, and if the problem still persists, you can look more in depth about this error here.

    Hope I could help!
     
  5. Ah I see, also good suggestion with storing it within a Map and grabbing it, that won't be a problem I'll just have to grab it when a player joins
     
  6. Furthermore, create a new connection for every request. Also, are you doing this in async? If you want, use my lib (in signature). It allows you to do database calls, with return values, in async.
     
  7. I might need it, it would most likely be the best idea when it comes to needing to connect to a data base constantly. If you'd like to check out the plugin https://github.com/Willim7/Permissions and see what the best way about doing MySQL would be I'd appreciate it. Because I need to have it so when a player joins it can grab their rank without maxing out a MySQL server's connections or get a communications link failure
     
  8. @Willim RLib would work well. I am also implementing HikariCP or some other type of pool system in the next version. It will help your async database calls and manage them nicely.
     
  9. Iv'e been looking more into Async MySQL and Callbacks not sure if I am doing this correctly though -

    https://hastebin.com/finicazono.cs
     
  10. Not commenting on your async handling, but regarding your original issue: I assume your original issue was (and still seems to be) that the connection gets closed by the database after a certain time of inactivity. Your isConnected-check doesn't actually check if the connection is still valid. To check that you might have to send a quick query (many connection pools use something like 'select 1;' for this) (Alternatively, there might also be a connection.isValid function, but it's not guranteed that it is supported by all db drivers.. and often times they simple use a 'select 1;' query as well internally).
    Alternatively/Additionally, since there are principally other (temporal limited) reasons why the db might 'deny' your query, you could also add logic to catch the exceptions and automatically retry your query X times, before actually giving up and logging an error.
     
  11. Oh, so basically async isn't required but my check when it comes to seeing if the data-base is still up might be causing the issue? So I'd need to send a query to check if it goes through and if it doesn't re-connect it
     
  12. Async is still required. But also, your connection is obviously closing after a certain timeout. To avoid this, do what I do in my lib & make a new connection each time or use a connection pool. Also, your async method is ok, but not the best. Bukkit runnables are, iirc, deprecated.

    Edit: I recommend CompletableFuture from java 8. It allows async callbacks.
     
    • Like Like x 1
  13. But am I not making a new connection every time by disconnecting when the method runs then re-checking if the plugin is connected with the database if not then connect?
     
  14. Hm, yes you are. The async is still not the best. Try using the library and see if u still get the error?
     
  15. Use a connection pool using some kind of framework to prevent using one big connection all the time (which, eventually after a certain amount in inactivity, gets closed by the server). Also you should be doing things async. Either a callback or a simple runnable would do, depending on the situation you're in.
     
  16. Yes. I said this too. To prevent confusion - in conclusion:

    • Use CompletableFutures for async, or Runnables, or some other method. You’re alredy doing this, but it’s not the best.
    • Use a connection pool, although I don’t think this is causing the issue since you’re creating a new connection each time.