1.16.5 MySQL Connections problems

Discussion in 'Spigot Plugin Development' started by xMaikiYT, Jun 3, 2021.

  1. Hiii,

    I made a plugin thats used mysql for storing coins.. but I have this problem when, i am trying to get the placeholder or inserting in the database:


    Code (Text):
    [06:21:06] [Server thread/WARN]: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1664)
    [06:21:06] [Server thread/WARN]:     at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1580)
    [06:21:06] [Server thread/WARN]:     at net.maiky.tokens.engine.SQLGetter.getTokens(SQLGetter.java:92)
    [06:21:06] [Server thread/WARN]:     at net.maiky.tokens.engine.utils.checkPlayer(utils.java:81)
    [06:21:06] [Server thread/WARN]:     at net.maiky.tokens.PapiExpansion.onPlaceholderRequest(PapiExpansion.java:47)
    [06:21:06] [Server thread/WARN]:     at me.clip.placeholderapi.PlaceholderHook.onRequest(PlaceholderHook.java:31)
    [06:21:06] [Server thread/WARN]:     at me.clip.placeholderapi.replacer.CharsReplacer.apply(CharsReplacer.java:156)
    [06:21:06] [Server thread/WARN]:     at me.clip.placeholderapi.PlaceholderAPI.setPlaceholders(PlaceholderAPI.java:69)
    [06:21:06] [Server thread/WARN]:     at me.clip.placeholderapi.PlaceholderAPI.setPlaceholders(PlaceholderAPI.java:97)
    From where the problem can be? And what can i do?
     
  2. You are closing the connection before executing stuff...
    Unfortunately, without a snippet of code I am afraid I cannot help you a lot cause I cannot tell you where the errors are
     
  3. Thank you. I analyzed the code but as hard as I tried to understand where the problem is I think I'm not that experienced yet, and I didn't find a solution. Sorry. I hope someone will come and help you out as I'm really curious!
     
  4. No problem :) thanks for your time!
     
  5. are you calling the disconnect method somewhere?
     
  6. Try adding ?autoReconnect=true at the end of jdbc:...
     
  7. i will try.. but it its not working anymore:
    connection = DriverManager.getConnection("jdbc:mysql://" +
    host + ":" + port + "/" + database + "?useSSL=false",
    username, password + " autoReconnect=true");
     
  8. not there... thats password string....try
    DriverManager.getConnection("jdbc:mysql://" +host + ":" + port + "/" + database + "?useSSL=false?autoReconnect=true", username, password);
    tho i still dont know if that would work...
     
  9. Yes, one connection is only supposed to last until the current operations are done. For example, player executes /tokens, so you make the connection, then execute the query and then close the connection.
     
  10. how? Give me instractions I am noobi
     
  11. Documentation is key. Also, there are plenty of good tutorials out there that explain what you have to do and why you do it. Reading this forum, I feel like you don't really have a clue what you are doing. So if you face problems, you are stuck pretty easily.
     
    • Funny Funny x 2
  12. Replace:
    Code (Java):
    public Connection getConnection() {
            return connection;
        }
    with:
    Code (Java):
    public Connection getConnection()  throws SQLException {
            return DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database + "?useSSL=false", username, password); // Note: You had double ; here (;;)
        }
    remove (not necessary, but you won't need it, but probably keep it, until you are sure, everything works):
    Code (Java):
    // Your connection variable.
    private Connection connection;

    // isConnected method
    public boolean isConnected() {
            return (connection == null ? false : true);
        }

    // Your disconnect method.
    public void disconnect() {
            if(isConnected()) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    //e.printStackTrace();
                }
            }
    Now, all of your "update" operations with MySQL should look like this:
    Code (Java):
        public void addTokens (UUID uuid, int tokens)
        {
            try (Connection connection = plugin.MySQL.getConnection(); // try-with-resources
                 PreparedStatement statement = connection.prepareStatement("UPDATE atokens SET TOKENS=? WHERE UUID=?"))
            {
                statement.setInt(1, getTokens(uuid) + tokens);
                statement.setString(2, uuid.toString());
                statement.executeUpdate();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    And all of your "queries" like this:
    Code (Java):
        public int getTokens (UUID uuid)
        {
            int result = 0;

            try(Connection connection = plugin.MySQL.getConnection(); // try-with-resources
                PreparedStatement statement = connection.prepareStatement("SELECT TOKENS FROM atokens WHERE UUID=?"))
            {
                statement.setString(1, uuid.toString());

                try (ResultSet resultSet = statement.executeQuery()) // try-with-resources again
                {
                    if (resultSet.next())
                        result = resultSet.getInt("TOKENS");
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }

            return result;
        }
    If you don't understand what I did there, it is called try-with-resources (read here)

    Important notes:
    • In your main, create getMySQL() getter and make the field private.
    • Name your classes with first capital letter, specifically your tokens class (tokens -> Tokens)
    If you have any questions, go ahead, also, it is likely, that I have some spelling or something mistakes there.
     
  13. I dont recommend getting spoonfed. Looking up your things and understanding what you are doing makes it so you learn on how to solve problems. Not only will that apply to this problem, but also to all problems you will be facing in the future.
     
  14. He is asking a question, I am giving an answer. In this scenario, there is not much new. You are right. But I don't see any problem in this scenario. Also if he's not dumb, he will try to understand what I did there, as anyone should.
     
  15. Also, you should work with MySQL async.