[SQL] MySQL server has gone away

[MemoryThief]

I've recently started using an external SQL server for the DB. It's easy enough to NavCat into and apart from taking a bit longer to update to, I figured it would be more convenient to store the Server info decentralized. This is what I'm experiencing...

The Game Server: So I changed the SQL info from local SQL to external DB and I'm having some issues with keeping the connection to the SQL server alive. Everything is fine as soon as the servers initiate start-up and for a good 15-20mins or so after that... Then I get an error stating "[SQL] DB error - Lost connection to MySQL server during query" on the Game server followed by several "[SQL] DB error - MySQL server has gone away" messages with the occasional "[Info] Pinging SQL server to keep connection alive" in between the 'gone away' errors. The Game server lived for about 20 mins idle, and then fails to re-connect to the SQL database.

The Connect server is also subject to this as it will also give the "[SQL] DB error - MySQL server has gone away" message and not allow anyone to log in to the server after the server has been idle, however seeing the log from the connect server, the connect server was able to Ping to keep the connection alive for about 6 hours until the 'gone away' error pops up about 2 hours after the last ping. So the Connect server lives for about 6-8 hours with external DB, but then fails to re-connect.

The Search server seems unaffected.

I was wondering if there was any setting I'm missing to help keep connection alive between the servers, I looked in the map_darkstar.conf file and I didn't see anything that would specifically help, but I did run into 'max_time_lastupdate' but i'm not sure if that's to the SQL server or to the client, so I assumed it was for the client and didn't change it.


Possible solutions?

Not sure how SQL is supposed to be handled but perhaps if when server has 'gone away' then the server may attempt to close then reopen the connection as if it were a new connection and restarting. I read that it is common practice to keep the connection to the SQL server open until done, which I assume would be forever unless you needed to update the server files. But what to do in the event the SQL server is no longer responding to that first connection request? The server becomes dysfunctional after 30 mins of idle without SySop intervention.

Any insight or help would be appreciated!

Game Server after Idle.

[bluekirby0]

This is probably an issue with your SQL server configuration. You may want to extend the connection timeout and make sure it is on a reliable connection as the overhead required to continually disconnect and reconnect to the sql server will cause significant lag. Also, if your sql server isn't even on the same LAN as your game server, I'm going to go ahead and say you need to reconsider your setup.

[Metalfiiish]

tl;dr

I get that error on my Virtual Machine if I save the VM state and then try to restore. Restarting the gameserver EXe usually fixes it. If it is on a VM then it's technically expected behavior.

[MemoryThief]

I suppose the external SQL idea is botch. I'll stick with a local server then, I was somewhat out of control with this.

Oddly enough it is on a Virtual Machine, however the state is never saved, the exe is always restarted... This happens on my rig as well, as long as you're connecting to an external SQL server it will eventually disconnect... Making the SQL local will solve the issue.

Thanks for the responces!