"SQL Server has gone away."

[AcadiusLost]

We're using NWNx4 pretty heavily for our NWN2 persistent world, and most all facets of our core rules tie into a centralized MySQL database, to track everything from PC location, logging of experience gain, what time of day it should be on server, etc.

Occasionally, there will be a failure in connecting to the MySQL database for a write operation. This is recorded in the xp_mysql.txt log as something along the lines of "Error: The SQL Server has gone away." This error repeats on each subsequent attempt by the server scripts to SELECT or INSERT to tables, even long after the MySQL server is back up and otherwise responding normally. Seems like the XP_MySQL module needs to reestablish connection to the server, but doesn't know to do it on it's own.

A module reload (without ending the NWNx4 process) seems to sort things out reliably- so it doesn't seem to be a problem with NWNx4 itself- could perhaps be as simple as needing to re-fire the lines of code that normally run OnModuleLoad for the SQL DB.

I think can script the module to reset if it fails a few timestamp INSERT statements, but that's a fairly crude fix- I'd like to get it retrying behind the scenes if possible.

Any thoughts or experience that might be assistance?

I'm running:
NWN Extender 4 V.0.0.8
with added compatibility with 0.0.7 - Ilrilan

NWNX MySQL Plugin V.0.0.8

I've also got other plugins running, but it seems to be only the MySQL one that's stalling out- it's worrisome because the only way I know it's down is if players report persistency problems, or if I happen to look into the logfile. Appreciate the time, look forward to any thoughts you may have.
_________________
Technical Administrator
A Land Far Away, a Persistent World Project

[AcadiusLost]

A brief followup on this- a scripted attempt to StartNewModule() does not reestablish connection to the MySQL database, only when it is stopped and started again from the NWNx4 GUI.

Also, the MySQL interruption can be extremely brief and still lead to this dysfunctional state. Literally stopping the MySQL process, then restarting it immediately seems to preclude xp_sql function, even when no writes are attempted during the "outage" time.

Any thoughts on how this may be worked around? A way to tell NWNx4 to restart the module would be one solution, a more elegant one would still be having a way to tell xp_mysql.dll to retry the connection from xp_mysql.ini - any suggestions how either might be accomplished?
_________________
Technical Administrator
A Land Far Away, a Persistent World Project

[TroveLord]

It happens when the database is not queried for a while, use a ping function every 5 or 10 minutes and you'll be fine.

[AcadiusLost]

To clarify, this is not a timeout problem. Any failed SELECT or INSERT statements from even a 10-15 stop/restart of the MySQL process lead to the xp_mysql plugin locking itself into this state. I have duplicated this error by manipulating a connected local MySQL service manually. Subsequent attempts to write or read from the SQL database via scripts autofail with the same "SQL server has gone away" error- even across a StartNewModule() call.

Our core scripts already "ping" the database periodically to write timestamps, even when the nwn2server is otherwise inactive. These timestamps function correctly right up until the first failed read/write, then get stuck in the same "Gone Away" errorstate as all other reads/writes.

The only way to restore the xp_mysql plugin's database connection once it's in this state seems to be restarting the server from the NWNx4 GUI. Presumably, making NWNx4 do the restart also reinitializes the xp_mysql plugin.

What I'm looking for is either:

1. A command (or plugin) that will tell NWNx4 to reload the module, which I can call from inside when the scripts notice the DB isn't responding.

2. A command for xp_mysql.dll that will tell it to retry/reestablish link with the SQL database defined in xp_mysql.ini (or just a reinitialization command?).
_________________
Technical Administrator
A Land Far Away, a Persistent World Project

[Undertowe]

We had exactly the same thing happen on our PW, but only once in 7 months of operation. So far, the problem hasn't resurfaced. *crosses fingers*

I'd also be interested in a solution, in case the problem comes back.

[Papillon]

@AcadiusLost: Your database server is probably going away for some time and then restarting itself. Please have a look at your MySQL logs - maybe there is something in them.
_________________
Papillon

[AcadiusLost]

The cause is certainly interruption in the MySQL database service, no doubt there- we're linking multiple PW servers into a single, central MySQL database across the 'net, so occational interruptions are going to be inevitable in this arrangement. I can duplicate the cause with a localhost MySQL service by stopping and restarting MySQL while the server is up.

The xp_mysql.ini logs show the "SQL server has gone away" error on the first attempt to write after the MySQL service interruption, and then again on every subsequent one (even after the MySQL DB has been restored, and across scripted module restarts), until such time as the NWNx4 GUI is used to stop and restart nwn2server.exe.

It's sounding to me like the best solution available is configuring some kind of a NWNx4-Restart plugin (NWNx4 system, maybe?), and using that to force the reinitialization from the NWNx4 restart. Unless someone knows of a way to send a command to the xp_mysql.dll, to tell it to retry a connection?
_________________
Technical Administrator
A Land Far Away, a Persistent World Project

[Gryphyn]

NWNX is not tolerant of database disconnects (for whatever reason).
When the plugin starts it creates a connection to the database, this is expected to persist forever... (which we know is not the case)

Your only option at the moment is to restart the server to re-establish the connection to the database.

Rather than resetting the server, the plugin needs to become 'IsConnected()' aware, so if the connection goes down, it can be reconnected automatically. [It'll need some sort of timeout, to go with the reconnect attempts]

Cheers
Gryphyn

[AcadiusLost]

I got the NWNx4-System plugin installed and tied it into my timestamp functions, setting the server to warn players and then reboot (via NWNx4) to reinitialize the xp_mysql plugin to reconnect to the SQL database. This seems to be working for now, naturally I'd prefer a way to send a message to the plugin to retry the connection, without rebooting the whole server.

I'll probably also have to go into the module load events and put another check in there, in case the MySQL DB is still unavailable at time of module reload- will make sense to schedule another reboot 5 min or so later to give more chance for the MySQL server to come back up.

Is a reconnect option feasible for a future version of the xp_mysql plugin?
_________________
Technical Administrator
A Land Far Away, a Persistent World Project

[MontyThreeCard]

I was wondering the same thing. We have a MySQL server running for our world, and it seems to stop responding as well. I will add a ping, that's no biggie, but it really would be helpful to have a reconnect function for NWNx. I will also download the latest, perhaps that will help. Thanks!

Note to Acadius - How are you rebooting the NWN server? I'd love to know....

[Papillon]

Get the 1.09 preview version of the MySQL plugin. It adds a reconnect feature.
_________________
Papillon

[MontyThreeCard]

Sweet!

Also, I was thinking of Flushing the logs on Module load. Has that become a problem for anyone? The logs are up to 24Mb, so it's a priority for us to do something about this, to reduce lag.

M3C