logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
Server disconnects from MySQL after a period of time

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
HelmedRaven



Joined: 23 Oct 2012
Posts: 1
PostPosted: Tue Oct 23, 2012 12:13    Post subject: Server disconnects from MySQL after a period of time Reply with quote
Hello Everyone,

I've got a situation that has been frustrating for me, and my players. For the past while, my server will seemingly disconnect from MySQL after a certain amount of time. This is sometimes after 24 hours, but I've seen it happen even earlier, which makes me suggest this is more than just the server being on too long.

The logs report that the there has been an error in SQL, and returns as an empty set, shown from the logs below.

Code:
o Logfile maximum size limit is: 524288 bytes
o Log level: Everything will be logged.
o Using MySQL connection.
o Hooking SCO....hooked at 5d5830
o Hooking RCO....hooked at 5d5710
o Connect successful.
o Got request: UPDATE chardata SET online = '0' where online = '1'
o Got request: UPDATE accounts SET onlineCount = '0' where onlineCount != '0'
////Several hours later, Alexander tries to access the DB
o Got request: SELECT cdKey FROM chardata WHERE charName='Alexander Hector Thade'
! SQL Error:
o Empty set


As you can see from above, the server is successful in talking to MySQL by resetting tables chardata and accounts, and players are able to talk to MySQL before it suddenly disconnects without any issue. This does not seem to be an issue with MySQL itself due to players being able to connect successfully after a server restart with MySQL remaining untouched.

I am currently running my server on a Windows 7 Home Premium machine. The server was once on a Linux server (of which I have no details about) and it did not display this issue, making me wonder if the issue may be tied to Windows. I am not however, currently able to host the server on Linux, so I must solve the issue if it is related to Windows 7.

On a possibly related note, despite reading that the SCO and RCO are hooked, Object Storage properly saves to the Database, but does not recall the stored objects. While this is not a pressing concern for me at this moment, I offer this tidbit as possibly a clue as to what is going on between the connection of my server and MySQL.

Thank you for any help any of you can give, it's been a frustrating issue and my experience with the outside workings of NWN1 and MySQL is very, very minimal. My Persistent World Players and I are very grateful for any assistance you can give.

Thank you!
Back to top
View user's profile Send private message
PlasmaJohn



Joined: 04 Mar 2005
Posts: 70
Location: The Garage
PostPosted: Tue Nov 06, 2012 17:13    Post subject: Reply with quote
This is a long standing bug

Elsewhere I wrote:
    [FIXED]
  • (20:40:10) PlasmaJohn: oh swell... I see that the NWNX (code is still broken)
    (20:40:45) PlasmaJohn: nuLysia's log is spewing: "MySQL server has gone away"
    (20:41:01) PlasmaJohn: Gee, don't you think you should, like maybe, try to reconnect?

    Live Avlis is running against MySQL 3.23.58, Avlis Pending is running against MySQL 5.0.51a. Somewhere between those two versions, the MySQL developers added an idle timeout that cannot be turned off. The default is 8 hours. I've adjusted it to 365 days.

    If that doesn't work, there's an option that can be set so that the connection will automatically reconnect. I seem to remember MadK@ talking about that since A2 ran into the same issue.


In /etc/mysql/my.conf add or modify the two _timeout values in the [mysqld] section and restart the server.
Code:
[mysqld]
#
# * Basic Settings
#

interactive_timeout     = 31536000
wait_timeout    = 31536000
Back to top
View user's profile Send private message
Lazarus Magni



Joined: 15 Jun 2011
Posts: 30
PostPosted: Mon Feb 10, 2014 0:48    Post subject: Reply with quote
I believe we also experience this problem from time to time. This config file is not present in our ect folder however. Is it ok to just make one using wordpad? And if so what should the file extension be? ini?
Thanks,
Laz
Back to top
View user's profile Send private message
Ravine



Joined: 26 Jul 2006
Posts: 105
PostPosted: Mon Feb 10, 2014 10:36    Post subject: Reply with quote
We found this timeout problem too yeaaars ago, simply put a SELECT query to module heartbeat solved the keepalive (not running every hb, but every 3 mins iirc).
But if the mysql server dies and reloads later, it wont reconnect to it. Just ran into this, when i had to restart mysql because of the webserver, and forgot about nwn... Smile
Back to top
View user's profile Send private message
virusman



Joined: 30 Jan 2005
Posts: 1020
Location: Russia
PostPosted: Mon Feb 10, 2014 17:17    Post subject: Reply with quote
If you're running the latest nwnx_odbc on Linux, add reconnectonerror=true to nwnx.ini and it'll try to reconnect automatically when the server goes away.
_________________
In Soviet Russia, NWN plays you!
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
Ravine



Joined: 26 Jul 2006
Posts: 105
PostPosted: Mon Feb 10, 2014 18:11    Post subject: Reply with quote
Laff...right. The default value is false as i see Smile
Great to know thx Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group