HikariCP doesn't prevent too many connections SQL error.

Discussion in 'Spigot Plugin Development' started by Nukreeper64, Jun 12, 2017.

  1. My setup seemed to be working fine, but now I'm getting an SQL exception with the message "Too many connections". As far as I can tell, I am only opening one connection in my code. So I am almost wondering if the connections persist through a server reload? I thought hikariCP was supposed to handle that stuff for you and you could just call dataSource.getConnection() and the library would make it easy. If someone could give me some idea of what is going on that would be great.

    Code (Java):

    public SQL(Main plugin)
       {
         this.plugin = plugin;
         dataSource = new HikariDataSource();
         /*
         dataSource.setJdbcUrl("jdbc:mysql://" + Database.HOST + ":" + Database.PORT + "/" + Database.NAME);
         dataSource.setUsername(Database.USERNAME);
         dataSource.setPassword(Database.PASSWORD);
         */

         System.out.println(Database.HOST);
         
         dataSource.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
      dataSource.addDataSourceProperty("serverName", Database.HOST);
      dataSource.addDataSourceProperty("port", Database.PORT);
      dataSource.addDataSourceProperty("databaseName", Database.NAME);
      dataSource.addDataSourceProperty("user", Database.USERNAME);
      dataSource.addDataSourceProperty("password", Database.PASSWORD);
       
       
      try
         {
         Connection conn = dataSource.getConnection();
         
           PreparedStatement statementCreatePlayerData = conn.prepareStatement("CREATE TABLE IF NOT EXISTS player_data(faction_id TEXT, player_id TEXT, balance INTEGER, xp INTEGER, name TEXT)");
           statementCreatePlayerData.execute();
           
           PreparedStatement statementCreateShopData = conn.prepareStatement("CREATE TABLE IF NOT EXISTS player_data(shop_id TEXT, played_id TEXT)");
           statementCreateShopData.execute();
           
           PreparedStatement statementCreateFactionDiplomacy = conn.prepareStatement("CREATE TABLE IF NOT EXISTS faction_diplomacy(faction_id TEXT, enemies TEXT, allies TEXT)");
           statementCreateFactionDiplomacy.execute();
           
           PreparedStatement statementCreateFactionData = conn.prepareStatement("CREATE TABLE IF NOT EXISTS faction_data(faction_id TEXT, leader TEXT, name TEXT)");
           statementCreateFactionData.execute();
         }
      catch (SQLException e)
         {
           plugin.log().severe("SQL exception while attempting to initialize tables: " + e.getMessage());
         }
       }
     
     
  2. You must close the connection after using it.
     
  3. Ok. I must have missed that part when read about how to use hikari. I added close statements after I use each connection. Does it persist after reload though? because it is still giving me the error. Do I also have to call dataSource.close in my onDisable()?
     
  4. Yes, I think the best thing you should be doing is closing the pool onDisable.
    Also, what pool size are you using? And what connection limit? Maybe you should increase also the connection limit.