Solved MySQL - No operations allowed after connection closed error

Hi there, I got a strange error, while I was coding my plugin. My code gets some data from MySQL table, nothing else. But when I want to get data, it says "no operations allowed..."... Here's my code:

Code:

public synchronized static String getLastRowInTable(){
     try{
       try {
         if(connection.isClosed()){
           openConnection();
           Bukkit.broadcastMessage(ChatColor.YELLOW + "[IRC] Opening connection... {at getLastRowInTable()}");
         }
       } catch (SQLException e1) {
         e1.printStackTrace();
       }
       Bukkit.broadcastMessage(ChatColor.YELLOW + "[IRC] Opened connection: "+!connection.isClosed());
       int rows = countRowsInTable();
       PreparedStatement sql = connection.prepareStatement("SELECT player,message,f FROM `ircchat_mc` WHERE id="+rows+";");
       ResultSet rs = sql.executeQuery();
       rs.first();
       String name = rs.getString(1);
       String message = rs.getString(2);
       String from = rs.getString(3);
       String row;
       if(from.equalsIgnoreCase("web")){
         row = ChatColor.GRAY + "[IRC] " +ChatColor.AQUA + name + ChatColor.WHITE + ": " + message;
       }else{
         row = "Error";
       }
       
       return row;
     }catch (Exception e){
       e.printStackTrace();
       return "Error";
     }finally{
       try {
         if(!connection.isClosed()){
           closeConnection();
         }
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }

And here's the error:

Code:

[15:34:52 INFO]: [IRC] Opening connection... {at getLastRowInTable()}
[15:34:52 INFO]: [IRC] Opened connection: true
[15:34:52 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
[15:34:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[15:34:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[15:34:52 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[15:34:52 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[15:34:52 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[15:34:52 WARN]:        at com.mysql.jdbc.Util.getInstance(Util.java:382)
[15:34:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
[15:34:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
[15:34:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
[15:34:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
[15:34:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1213)
[15:34:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1200)
[15:34:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4203)
[15:34:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4169)
[15:34:52 WARN]:        at me.PDKnight.main.IRC.getLastRowInTable(IRC.java:132)
[15:34:52 WARN]:        at me.PDKnight.main.IRC$1.run(IRC.java:168)
[15:34:52 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftTask.run(CraftTask.java:53)
[15:34:52 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53)
[15:34:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[15:34:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[15:34:52 WARN]:        at java.lang.Thread.run(Unknown Source)

Can you help me? What did I do wrong?
Thanks for any solution

 

Maybe you are running this method at least twice at the same time (in separate threads / async tasks). And then you close the connection at the end of the method while in an other thread the method hasn't finished yet.

 

Good idea, I'll test it

 

@PDKnight
Does countRowsInTable() close the connection after it is finished? In that case you have to make sure you leave it open. Or you can use a subselect to get the latest entry:

Code:

SELECT player,message,f 
FROM `ircchat_mc` 
WHERE id= IN (
   SELECT MAX(id) 
   FROM `ircchat_mc`);

 

@hexaan Thank you for your idea! Oh, and one thing: after your code my server got an error:

Code:

[18:27:52 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'IN (   SELECT MAX(id)   FROM `ircchat_mc`)' at line 1
[18:27:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[18:27:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[18:27:52 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[18:27:52 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[18:27:52 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[18:27:52 WARN]:        at com.mysql.jdbc.Util.getInstance(Util.java:382)
[18:27:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
[18:27:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
[18:27:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
[18:27:52 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[18:27:52 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[18:27:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
[18:27:52 WARN]:        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
[18:27:52 WARN]:        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)
[18:27:52 WARN]:        at me.PDKnight.main.IRC.getLastRowInTable(IRC.java:117)
[18:27:52 WARN]:        at me.PDKnight.main.IRC$1.run(IRC.java:146)
[18:27:52 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftTask.run(CraftTask.java:53)
[18:27:52 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53)
[18:27:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[18:27:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[18:27:52 WARN]:        at java.lang.Thread.run(Unknown Source)

Code:

Code:

PreparedStatement sql = connection
                    .prepareStatement("SELECT player,message,f FROM `ircchat_mc` WHERE id= IN (   SELECT MAX(id)   FROM `ircchat_mc`);");

 

@PDKnight

Woops it should be without the =

Code:

SELECT player,message,f
FROM `ircchat_mc`
WHERE id IN (
   SELECT MAX(id)
   FROM `ircchat_mc`);

Code:

PreparedStatement sql = connection
                    .prepareStatement("SELECT player,message,f FROM `ircchat_mc` WHERE id IN (SELECT MAX(id)   FROM `ircchat_mc`);");

 

@hexaan Thanks! Guys, this topic is now solved! Thanks for any solutions!