dbManager returning expired connections

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

Moderators: Lapo, Bax

jurekf
Posts: 69
Joined: 08 Dec 2010, 23:46

dbManager returning expired connections

Postby jurekf » 04 Aug 2011, 04:08

Hi,

We're still getting "Last packet sent was xxxxxxx milliseconds ago" exceptions in spite of the fact that we send periodic SELECT statements to keep the connection alive.

It seems that when calling getConnection, the connections returned are from a pool, and there's no guarantee that the connection you're using for the keepalive SELECT statements will be the same ones you'll get from the login handler.

What's the workaround for this? Setting MySQL's connection timeout to a really high number is undesirable, as this would mean we would need to restart mysql every time we restart sfs2x or risk running out of connections. We use mysql for other functions on the back end that are independent of sfs2x.
User avatar
Lapo
Site Admin
Posts: 21647
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 04 Aug 2011, 07:34

Quite weird, the "ping" solution has been working for all our clients without issues. What's the frequency of the keepalive SQL? Few minutes? And what's the mysql timeout?
The problem might be related to a too-fast mysql timeout compared to the keepalive activity. Try playing with those settings.
Lapo
--
gotoAndPlay()
...addicted to flash games
jurekf
Posts: 69
Joined: 08 Dec 2010, 23:46

Postby jurekf » 04 Aug 2011, 08:51

My bad, we're using this:

Code: Select all

_dbManager.executeUpdate("select 1");


which might be different from us calling getConnection().
We log a "PING" command right after, and we're using default timeouts now, so we only really see this if we leave a server running alone overnight.

I'm going to change it right now to getConnection() and see if that makes a difference. Is there a recommended query to use, just in case "select 1" gets preoptimized and discarded somewhere (since it doesn't really fetch data from the database)?
tchen
Posts: 191
Joined: 11 Dec 2010, 14:14

Postby tchen » 07 Aug 2011, 19:31

SELECT 1 should be fine.

Most Tomcat DBCP users use exactly the same thing. Although it would be nice if SFS2X implemented a testOnBorrow/validationQuery parameters to the DatabaseManager xml config section.

*hint, hint* :lol:

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 29 guests