MySQL crashes

[TroveLord]

Lately on my test box I'm experiencing many MySQL crashes.

From the mysql.txt log I get this

[Papillon]

NWNX does nothing special to the DB, so it would likely crash with every other programm accessing it as well.

You might want to
a) check the MySQL documentation how to find the root of the problem
b) reinstall MySQL
c) check database consistency
d) maybe check that you have enough free RAM.

I have to admit I have never seen MySQL crashing, so I can not give more detailed advice.
_________________
Papillon

[TroveLord]

I'll do so, thanks for the quick reply.

[TroveLord]

I'm starting to think that's an issue with NWNX4. All out of a sudden it loses the connection with the daemon claiming that it's down but I can fully access the data in it with the mysql client. From the xp_mysql.txt log I can't see anything suspicious, yet if I look at the nwnx4 GUI it seems that the mysql server crashes happens when the gamespy query fails. Should I turn off the gamespy watchdog?

TIA.

[Papillon]

If the connection is broken, NWNX won't try to reconnect... even if it's only for a short time.

I'm not sure I understand what you mean with the reference to the gamespy watchdog in this context ...?
_________________
Papillon

[TroveLord]

[Papillon]

Well, if the gamespy watchdog prints out the message about how the query failed, your nwn server is not responding. This usually happens when the server crashes or runs in an endless loop (at 100% CPU).

I am not sure how this might be related to your MySQL server crashing. This seems a bit fishy. Your PC is ok, otherwise ? No sudden crashes in other areas ?
_________________
Papillon

[TroveLord]

[JohnB]

I have the same problem.

However I am using NWNx2 along with MySQL.

The game starts up fine, finds the DB and reads in persistent times, CNR data etc. It will also store persistent locations and CNR data.

Then suddenly I get

! SQL Error: [MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]MySQL server has gone away

Although I can still access the server and the DB from SQLyog.

Any thoughts? The PC is up and running, doesn't appear to have any other problems? Could it be an issue with ODBC drivers? Or perhaps I should look at adding a connection test and (if needed) a DC connect before every read or write?

[JohnB]

I realised that I didn't have the latest version of MySQL. I have now installed 5.0.41 - now just waiting to see if it still happens.

[JohnB]

That appears to have resolved the problem

[TroveLord]

I don't mean to jinx but I've experienced these issues with 5.0.41, much less lately but you can't ever tell.

[JohnB]

Yep - It happened again. This time however it lasted longer than it has done before.

From what has been said before - it seems that the server is fine and if we were to force a reconnect when the server is 'Gone away' - that would over come the problem.

Any thoughts on the best way to do it? Not sure I klnow how to code all of these variants - but it seems like there are three ways to handle this ...

1) Restart the module regularly. More frequently that the average time until the 'Server Gone away' message. That would force a new connection ....

2) Have a script run rugularly that checks to see if the server is there, and then tries a reconnect if it isn't. Once an hour should have minimum impact opn the server. I suppose we could even check a second time and if the reconnect doesn't work - we could reload the module.

3) Tweak the script that actually writes to the DB to check for a 'Server Gone Away' error message. (is that even possible?). We could use that to trigger a reconnect or a reboot. I wonder if its possible to save the data and try and write it again after we reconnect to the database?

[Papillon]

How often does the "MySQL server has gone away" message appear ? Does it appear only after prolonged periods of inactivity or does it also happen when players are on and exercise the DB ?
_________________
Papillon

[JohnB]

It looks to be after a period of inactivity - however I haven't managed to work out what the time limits are.

I am working with a new server, thats still heavily under development - it can go for a long time without a player connecting to it.

Generally it is OK overnight (about 6-7 hours) without any use. It is more likely to 'Go Away' while I am out at work whis is, perhaps, a slightly longer period with no activity. However, that doesn't appear to be consistent.