Solved Connection problem in MySQL

Discussion in 'Spigot Plugin Development' started by OscarDali, Jun 1, 2018.

Thread Status:
Not open for further replies.
  1. Hello, I have a problem, and that is that I open many connections but I close them, but the problem is that by using many in a single instant the server (spigot) stops and stops working, does anyone know how I can solve this? That's why, this error appears to me.

    Code (YAML):
    Could not create connection to database server. Attempted reconnect 3 times. Giving up.
     
  2. Is your SQL server running?
     
  3. Yes, everything works fine, except that.

    Delete, add and modify data very well, only to run a new query I open a connection and close it, but as I do many in a short time I think I use the maximum number of connections.
     
  4. You said you're opening many connections? Might be hitting some sort of limit you have set. Hard to tell why you'd be getting that error.
     
  5. I'm getting stats from MySQL and I show them to the player, and for each stat the values of MySQL are obtained, how can I increase the number of connections without having to do it manually?, this is my connection code.

    Code (Java):
        public void openConnection(){
            FileConfiguration config = ConfigManager.get().getConfig();
            host = config.getString("MySQL.Host");
            port = config.getInt("MySQL.Port");
            database = config.getString("MySQL.DataBase");
            username = config.getString("MySQL.UserName");
            password = config.getString("MySQL.Password");
            try {
                if (connection != null && !connection.isClosed()) {
                    return;
                }
                synchronized (this) {
                    if (connection != null && !connection.isClosed()) {
                        return;
                    }
                    Class.forName("com.mysql.jdbc.Driver");
                    connection = DriverManager.getConnection("jdbc:mysql://" + this.host+ ":" + this.port + "/" + this.database + "?autoReconnect=true", this.username, this.password);
                }
            } catch (SQLException | ClassNotFoundException e) {
    Bukkit.getLogger().log(Level.WARNING, e.getMessage().toString());
            }
        }
     
  6. I wouldn't make a new connection every time you need to show stats to a player. Are they the player's stats? If so, load them on server start and cache them in a wrapper or hashmap, whatever works. Save every now and then, as well as onDisable. I'm somewhat inexperienced with SQL so I honestly won't be the best help as I hardly understand the issue. But you should definitely only be opening a connection as little as you need to, certainly not every single time you need to show a player a stat. Again- my best guess is that you're hitting a limit with your connections, can't see another reason why it would stop working after you've opened a lot. I load all of my player data to my PlayerWrapper's once the user has completely connected, and cache it inside of the PlayerWrapper until the player leaves for more than 5 minutes.
     
    • Friendly Friendly x 1
  7. I found the problem, it seems that the connections I make do not close and remain, how could I close them? I think my way of doing it does not work
     
  8. electronicboy

    IRC Staff

    If you want to reuse connections, use a connection pool such as hikari CP and get that to handle and manage your connections, you shouldn't be using connections across threads.
    You can close connections by actually calling close on the connection, but ideally ^
     
  9. I tried to use only one connection, but that generated another problem, opening new connections was the solution.
     
  10. electronicboy

    IRC Staff

    Yes, but you need to close these connections when you're finished when them, especially if you're running queries async, but really: opening connections is slow and you *should* be using a connection pool, you'll still need to "close" the connections to return them back to the pool, but you'll save yourself from reinventing the wheel and improve performance of your application (especially when it comes down to stuff like prepared statements being cached per connection)
     
    • Friendly Friendly x 1
  11. but I'm supposed to close the connection, how would you close it?
     
  12. electronicboy

    IRC Staff

    You call close on the Connection object that you created, just like you do for your Statement objects
     
  13. I did it again now using a single connection and a single Statement, but it gives me a problem when executing the Query of select, knows why this happens?, the connection works perfectly.

    By the way, thanks for the help, but I already closed the connection in that way and even then it did not work, but in this way I think it would work.

    Code (Java):
        public boolean playerExistInDB(Player p) {
            try {
                resulSet = statement.executeQuery("SELECT * FROM Players WHERE UUID = '" + p.getUniqueId() + "'");
                if (resulSet.next()) {
                    return true;
                }
                return false;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
     
  14. @electronicboy I think the problem is that the connection returns it as null, this has some explanation and / or solution?.

    nevertheless the connection if you create it, in mysql it recognizes it (I check with "SHOW PROCESSLIST;")
     
  15. After a certain amount of time the client may automatically disconnect, just check if the connection instance is closed. Then if it is reopen it. @OscarDali
     
  16. I'm trying but I always recognize it as null.

    I really do not know the cause ... I have a small program with mysql and it works perfect !, with only one connection, however, here it is not the same.
     
  17. Send me the class you're doing all of this in and I will attempt to correct it for you. (Please use Hatebin).
     
    • Friendly Friendly x 1
  18. Thanks for taking the trouble, but "it's already working", just... well look at this...

    [​IMG]
     
    • Like Like x 1
  19. Those connections cause the server to stop when the limit passes, how can I avoid that? In addition to increasing the number of connections, I can not do this with a single connection? Does the statement create a new connection of the ones that appear there?
     
  20. Woooaaahhh!!!, I could fix it xD, thanks to everyone! it was thanks to you that I could do it!

    In case you suffer, suffer from the same as me:

    • Verify the connection and if it does not exist reconnect it.
      Code (Java):
                  if(connection == null || connection.isClosed()) {
                      //Open Conection
                  }
    • If you create many connections just close them before closing a statement.

      Code (Java):
      stmt.close();
      connection.close();
     
Thread Status:
Not open for further replies.