Solved MySQL problem

Discussion in 'Spigot Plugin Development' started by hund35, May 25, 2017.

  1. I am playing around with Mysql to learn the basic about mysql. It seems to work, but i have 2 problems.

    The first problem is the database keeps disconnect after around 6-12 hours with no mysql actions.
    I have already 2 solutions by myself maybe, but i not sure which of them is best.
    1: Use autoReconnect in the url.
    2: or make a async task that check if the connection is close or null.

    error:
    [09:38:43] [Craft Scheduler Thread - 704/WARN]: Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 49,344,147 milliseconds ago. The last packet sent successfully to the server was 49,344,183 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.
    [09:38:43] [Craft Scheduler Thread - 704/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    /

    The second problem is efter around 2/4 - 6 hours drops from 20Tps to around 5 before it crashs (Before it starts to drop, its runs smoothly even when i am updating/getting data in-game, and i am running it async) it seems to a problem in the method to get data at line 66

    Error log: https://hastebin.com/afaguhuver.php

    i admit that the way to return the data us pretty poorly coded, so advise to improve, would be great.
    Code (Text):
    public int GetInt(Player p, int r, String t){
             Bukkit.getScheduler().runTaskAsynchronously(main.instance, new Runnable() {

                    public void run() {
                        ResultSet res;
                        try {
                            res = main.connect.prepareStatement("SELECT * FROM players WHERE uuid='"+ p.getUniqueId() +"'").executeQuery(); <--- line 66
         
                        if(res.next()){
                     
                            IntData.put(p.getName() + "." + t, res.getInt(r));
                             res.close();                  
                        }
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }              
                    }
                    });

        if(IntData.get(p.getName() + "." + t) != null) {
             int iv  = IntData.get(p.getName() + "." + t);
            return iv;
        }
        return 0;
    }
     
    #1 hund35, May 25, 2017
    Last edited: May 26, 2017
  2. I hate to bump threads, but this performance issue has annoyed me for days now.
     
  3. java.lang.OutOfMemoryError .. I suppose you're not allowing enought RAM to your server..
     
  4. java.lang.OutOfMemoryError
    To fix this issue, perhaps you could add more space to your server so your plugin can actually function. You could always pay for a cheap host just to test this stuff out, try think of some solutions.
     
  5. Its seems to be a memory leak problem more than not enough ram allocated. i Didn't had this problem when i used YMAL's instead of MySQL.
     
  6. That's normal, MySQL uses more memory than YAML ^^
    YAML is used locally as MySQL needs to be connected to a network
     
  7. electronicboy

    IRC Staff

    You should use Hikari in order to handle the connection, should help with performance and keeping connections open

    as for sane programming, try-with-resources is amazing and allows you to ensure that connections and resources are closed properly, e.g. you only close the connection if you get any results, otherwise you leave it open, which leaks memory

    also, you can't really return data async, generally speaking, we go for several routes for this:
    1. Cache a copy locally as soon as possible, e.g. async login; - this works for a fair chunk of stuff, but ofc not viable for everything
    2. callbacks, e.g. you provide a runnable to run after the query has been performed in order to pass the results to the runnable
     
    • Informative Informative x 1
  8. I have tested for some hours, and the server has been online for almost 24 horus without a crash or tps drops. So it seems my problem is solved. Thank you very much :D.