Pool Exhausted with executeUpdate

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

Ber4ber43
Posts: 79
Joined: 30 Apr 2019, 14:35

Pool Exhausted with executeUpdate

Postby Ber4ber43 » 24 Apr 2021, 08:52

Hi,
I had read on the web that when updating with these instructions, there is no need to close the connection, but I got the "Pool Exhausted" error, I use this code:

Code: Select all

void Write_On_Database(String gifts)
   {
      IDBManager dbmanager = GameData.loginZone.getDBManager();

      try
      {
         dbmanager.executeUpdate("UPDATE events_current SET gifts = ? WHERE id = ? ", new Object[] //This is CurrentEvents.java:305
         { gifts, id });
      }
      catch (SQLException e)
      {
         System.out.println("Error SQL: " + e);
         e.printStackTrace();
      }
      catch (Exception e)
      {
         System.out.println("Error Generic: " + e);
         e.printStackTrace();
      }
   }


Error:

Code: Select all

Error SQL: java.sql.SQLException: Cannot get a connection, pool error: Pool exhausted
java.sql.SQLException: Cannot get a connection, pool error: Pool exhausted
        at org.apache.commons.dbcp.PoolingDriver.connect(PoolingDriver.java:188)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:270)
        at com.smartfoxserver.v2.db.SFSDBManager.getConnection(SFSDBManager.java:113)
        at com.smartfoxserver.v2.db.SFSDBManager.executeUpdate(SFSDBManager.java:194)
        at gamedata.CurrentEvents.Write_GiftsString_On_Database(CurrentEvents.java:305)
        at gamedata.CurrentEvents.<init>(CurrentEvents.java:156)
        at gamedata.GameData.Control_If_Add_Event_To_CurrentEvents(GameData.java:330)
        at registerserver.Developer.Set_New_Event(Developer.java:189)
        at registerserver.Developer.handleClientRequest(Developer.java:76)
        at com.smartfoxserver.v2.extensions.SFSExtension.handleClientRequest(SFSExtension.java:208)
        at com.smartfoxserver.v2.controllers.v290.ExtensionReqController.processRequest(ExtensionReqController.java:174)
        at com.smartfoxserver.v2.controllers.v290.ExtensionReqController$1.run(ExtensionReqController.java:68)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.util.NoSuchElementException: Pool exhausted
        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1096)
        at org.apache.commons.dbcp.PoolingDriver.connect(PoolingDriver.java:180)
        ... 15 more
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Pool Exhausted with executeUpdate

Postby Lapo » 24 Apr 2021, 14:31

Hi,
you can get that error when the connection pool is fully used.
The solution to this is to set the "Exhausted pool action" in your DB config to GROW (from AdminTool > Zone Configurator > Database Manager). This way the connection pool will grow as the database traffic grows.


Cheers
Lapo
--
gotoAndPlay()
...addicted to flash games
Ber4ber43
Posts: 79
Joined: 30 Apr 2019, 14:35

Re: Pool Exhausted with executeUpdate

Postby Ber4ber43 » 24 Apr 2021, 17:27

Lapo wrote:Hi,
you can get that error when the connection pool is fully used.
The solution to this is to set the "Exhausted pool action" in your DB config to GROW (from AdminTool > Zone Configurator > Database Manager). This way the connection pool will grow as the database traffic grows.


Cheers


It worked!

But the best solution in this case is to use code like this? Or is that okay too?

Code: Select all

Connection connection = null;
      PreparedStatement ps = null;
      System.out.println("Crea tabella events");

      try
      {
         //@formatter:off
         connection = GameData.loginZone.getDBManager().getConnection();
         ps = connection.prepareStatement("CREATE TABLE IF NOT EXISTS _archive_event_PROGRESS_" + id +
               " (id_user INT NOT NULL"
               + ", wins TINYINT NOT NULL DEFAULT 0"
               + ", total_gifts_taken TINYINT NOT NULL DEFAULT 0"
               + ", gifts_taken VARCHAR(255) NOT NULL DEFAULT ''"
               + ", defeats TINYINT NOT NULL DEFAULT 0"
               + ", num_access TINYINT NOT NULL DEFAULT 1"
               + ", rating TINYINT NOT NULL DEFAULT 0"
               + ", comment VARCHAR(3000) NOT NULL DEFAULT ''"
               + ", PRIMARY KEY(id_user))");
         //"CREATE TABLE IF NOT EXISTS _archive_event_" + id + " (id INT NOT NULL, id_user INT NOT NULL, wins TINYINT NOT NULL, total_gifts_taken TINYINT NOT NULL, gifts_taken VARCHAR(255) NOT NULL, defeats TINYINT NOT NULL, rating TINYINT NOT NULL, PRIMARY KEY(id, id_user))");
         ps.executeUpdate();
         //@formatter:on

         Matchmaker_Events_Manager.Add_Matchmaker_Event(id);
         //GameData.matchmaker_events.Add_Matchmaker_Event(id);
      }
      catch (SQLException e)
      {
         System.out.println("Error SQL: " + e);
         e.printStackTrace();
      }
      catch (Exception e)
      {
         System.out.println("Error Generic: " + e);
         e.printStackTrace();
      }
      finally
      {
         try
         {
            if (ps != null) ps.close();
            if (connection != null) connection.close();
         }
         catch (SQLException e)
         {
            System.out.println("Error Closing SQL: " + e);
            e.printStackTrace();
         }
         catch (Exception e)
         {
            System.out.println("Error Closing Generic: " + e);
            e.printStackTrace();
         }
      }
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Pool Exhausted with executeUpdate

Postby Lapo » 25 Apr 2021, 17:29

But the best solution in this case is to use code like this? Or is that okay too?

I am not sure what your question is exactly.
Best solution ... to solve what? The solution to the "exhausted pool" error is what I suggested in my previous post, which seems to have worked for you.

In any case the code you have posted looks fine, it handles exceptions as we recommend in the docs.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games
Ber4ber43
Posts: 79
Joined: 30 Apr 2019, 14:35

Re: Pool Exhausted with executeUpdate

Postby Ber4ber43 » 25 Apr 2021, 18:48

OK thanks

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 68 guests