dbManager returning expired connections

[jurekf]

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.

[Lapo]

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.

[jurekf]

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]

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*