Solved Mysql Communication faillure

so my plugin makes connection to the mysql database of the server. here is the problem. on the minecraft server (not my local hosted) the mysql connection times out after some seconds and I can't reconnect. the only way to reconnect is by reloading the server. now when I local host it the error doesn't appear and everything works just fine. does someone have any idea's how to fix this?

the error

Code:

Communications link failure
 
The last packet successfully received from the server was 76,814 milliseconds ago.  The last packet sent successfully to the server was 11 milliseconds ago.

the connection code

Code:java

 MySQL MySQl = database.MySQl;
        int poging = 0;
      while(con == null){
        con = MySQl.openConnection();
        if(con == null){
            poging++;
            getLogger().info("Database connection lost. Reconection will be started");
 
        }
        if(poging == 2){
            getLogger().info("No Connection to Database. Plugin is deactivating. Reload server for database Connection");
            Bukkit.getPluginManager().disablePlugin(plugin);
            break;
        }
 
      }
      if(con != null){
      database.setcon(con);
      getLogger().info("Databse connection has succeed");
      database.TableCreate();

database class methods

Code:java

 public void intvar(){
          mysqlpass = plugin.getConfig().getString("mysqlpass");
            mysqluser = plugin.getConfig().getString("mysqluser");
            mysqldb = plugin.getConfig().getString("mysqldb");
            mysqlpot = plugin.getConfig().getString("mysqlport");
            mysqlhost = plugin.getConfig().getString("mysqlhost");
              MySQl = new MySQL(plugin, mysqlhost, mysqlpot, mysqldb, mysqluser, mysqlpass);
      }
 
      public DbStuff(Main instance){
          plugin = instance;
      }
      public void setcon(Connection connect){
          con = connect;
      }

and final I use Husky's mysql database library if you need to know

 

guitargun

This is an inherent part of MySQL and one of the reasons people recommend using a connection pool. You either need to send dummy SQL as sort of a "keep-alive" or you need to recreate the connection each time you use it.

 

The database you are using on the minecraft server is closing the connection after an idle timer expires (in this case, I'd guess maybe it's set at 60 seconds? That would seem to be sane inside of 76 seconds between your plugin last querying the db).

So, you have two ways to deal with this, I recommend implementing both.

1. Have a keep alive query running every x seconds, where x is < the connection timeout period. This would be simply querying the database with something like "SELECT1;" on a repeating task. Do not go for something stupid like 1-5 seconds, that's just dumb and will chew up resources.
2. Catch the connection failure, and reopen/rebuild related connection objects. I have no clue how you do that in that library, you might have to create a new instance of whatever class you use to connect to the database (in plain old JDBC, you have to make new connection and statement objects.)

 

mythbusterma, tehbeard
I think I solved it for now. with some trials and errors I found that the connection closing is between 20 and 30 seconds, 25 seoncds just works.

I did this. these 2 methods are called everytime I do something with the databse (that is only one method below and in one other class)

Code:java

 private int scheduler;
public void closecon(){
int timeout = 25;
 
 
int id = Bukkit.getScheduler().scheduleSyncRepeatingTask(plugin, new Runnable(){
public void run(){
PreparedStatement pst;
try{
pst = con.prepareStatement("SELECT id FROM `User_Table` LIMIT 1;");
pst.execute();
}catch(SQLException ex){
System.out.println(ex.getMessage());
}
}
}, 0L, timeout *20L);
scheduler = id;
}
public void opencon(){
Bukkit.getScheduler().cancelTask(scheduler);
}