Cannot get a connection

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

Moderators: Lapo, Bax

NoN33d
Posts: 25
Joined: 23 Sep 2016, 12:07

Cannot get a connection

Postby NoN33d » 15 Jul 2017, 12:11

Hello,

We are using Smartfox Server 2X with Jelastic cloud to host our online games. From the first time we published our game we are having some problems with the server. It seems that when we reach about 150 - 200 ccu after a while the server stops working and starts working again in about 5 minutes.

When this happens an exception gets thrown (we assume that this causes the problem) "Cannot get a connection, pool error: Could not create a validated object, cause: ValidateObject failed".

We are not sure why this happen, that's why we are asking for your help.
This is the brief code that throws this exception ("//LOGIC" parts also contains update, import and select statements, I can send you the complete function to check):

Code: Select all


   Connection connection = null;

        ResultSet res_select_major = null;
        PreparedStatement stmt_select_major = null;

        try {

            connection = getParentExtension().getParentZone().getDBManager().getConnection();
            stmt_select_major = connection.prepareStatement("SELECT STATEMENT");
            res_select_major = stmt_select_major.executeQuery();

            if (res_select_major.next()) {
               
                // LOGIC
               
                stmt_select_major.close();
                res_select_major.close();
               
                // LOGIC
               
            }
           
            // LOGIC
           
            connection.close();

        } catch (SQLException ex) {

            Debugger.ShowDebug("ERROR ZONE UserHandler 1 : " + ex.getMessage() + " STACK TRACE: " + Arrays.toString(ex.getStackTrace()), ext);

        } finally {

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                }
            }

            if (res_select_major != null) {
                try {
                    res_select_major.close();
                } catch (SQLException ex) {
                }
            }

            if (stmt_select_major != null) {
                try {
                    stmt_select_major.close();
                } catch (SQLException ex) {
                }
            }

        }
       



Is there something wrong with this part of code?


Thank you for your time and effort!
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Cannot get a connection

Postby Lapo » 17 Jul 2017, 07:54

Hi,
it looks like an issue with the database.
Maybe too many concurrent connections towards the DB. Are you using the default config for the connection pool (form the SFS side)? Or is it custom?

Also it would help to see an excerpt of the server log files around the time of the problem. Including a few minutes before and a few minutes after.

If you want you can send them to our support@... email box with a reference to this discussion.
Thanks
Lapo
--
gotoAndPlay()
...addicted to flash games
NoN33d
Posts: 25
Joined: 23 Sep 2016, 12:07

Re: Cannot get a connection

Postby NoN33d » 17 Jul 2017, 10:10

Hello Lapo and thank you for your reply.


I set up the database connection through SFS from "Zone Configuration" -> "My Zone" -> "Database Manager".

These are our current settings:
Database driver class = com.mysql.jdbc.Driver
Connection string = jdbc:mysql://mysql...:port/database
Username = username
Password = password
Test SQL = SELECT * FROM table_name
Max active connections = 2000
Max idle connections = 200
Exhausted pool action = FAIL
Block time = 3000


I want to clear something out, is the code I sent you correct? Or am I handling the Connection, ResultSet and PreparedStatement in the wrong way?

Also where can I find the full email address in order to send you the log?


Thank you in advance.
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Cannot get a connection

Postby Lapo » 17 Jul 2017, 13:19

Hi,
the "Test SQL" is used to validate connections from the connection pool. I would recommend using a faster SQL code, because using a "SELECT * FROM..." could result in heavy work for the DB if the table has hundreds of entries.

I'd recommend adding a "LIMIT 1" at the end of the expression to make it as fast as possible. Also you could just read one field instead of "*". Something like: "SELECT name FROM someTable LIMIT 1".

For the rest the config looks fine, and yes your code looks okay, you're catching errors and deallocating objects correctly.

Hope it helps
Lapo

--

gotoAndPlay()

...addicted to flash games
NoN33d
Posts: 25
Joined: 23 Sep 2016, 12:07

Re: Cannot get a connection

Postby NoN33d » 17 Jul 2017, 13:57

Hello again,

Our table actually has about 1 million entries with 7 columns. The "select *" takes about 1 sec while the "SELECT name FROM someTable LIMIT 1" takes a few ms.
I changed it already but I can't understand why this can cause the issue, doesn't this "Test SQL" run once every time the zone gets started (once every time I restart the server) ? When does the "Test SQL" run ?

Thanks a lot!
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Cannot get a connection

Postby Lapo » 17 Jul 2017, 15:47

No, the Test SQL is run every time a connection is picked up from the connection pool to test that it is usable. It's a sanity check, which is necessary because the DB might have forcefully shut down the connection or it might have timed out.

So it's important that the SQL expression is something very simple that returns a single result.

Given you have 1mil entries in the table I think that was the cause of the issue.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games
NoN33d
Posts: 25
Joined: 23 Sep 2016, 12:07

Re: Cannot get a connection

Postby NoN33d » 18 Jul 2017, 08:42

Hello!

I think that this change on the "Test SQL" solved our issue, that's amazing!
The internal traffic dropped from 50 Gb to 1 Mb (we were searching a lot to solve this, we tried many... many approaches), the cloudlets dropped from 200 to 5 and the server is working smoothly (finally).

We will keep watching the server for this issue but I think that it wont happen again.

Thanks a lot Lapo for your support!
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Cannot get a connection

Postby Lapo » 18 Jul 2017, 08:48

Yeah, that SQL was killing the performance.
Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: Stevenor, Thomasea and 95 guests