Is having a active MySQL Connection for long periods of time bad?

Question

I'm developing an MMORPG, it's pretty far along.

However, I'm noticing now that the MySQL Server has a automatic connection terminate time limit. So after x time, a connection is terminated. So what's happening with my server is after about 8 hours of run time, users can't login or update their characters on logout.

A simple fix would be to make the time out value very very large, but I want to know if this is a bad idea?

Another solution would be, to check if the connection is still valid before making a SQL Query, and if not start a new connection. Although I prefer the first option if it has no bad side-effects.

Answer 1

In case you're not already doing that I'd like to say that the best option would be to use a proper connection pool on the server instead of reusing a single connection.

Now, increasing the timeout SHOULD be safe, but MySQL might have memory leaks (of sorts) that are tied to the connection, so dropping the connection from time to time might be much safer.

For example, if you're using dynamically generated prepared queries (some APIs do that to make the queries safe from the SQL injection attacks) then MySQL might have a problem caching all the prepared queries in memory.

You might have to implement such eviction yourself, unfortunately.