Author Topic: Database connection timing out (Read 5227 times)

kardsen · « **on:** July 16, 2008, 02:13:32 am »

Feels like Deja Vu

right vry?

MySQL error (# 0) on query: SELECT * FROM craftingclass WHERE name = 'Sinjin
MySQL server has gone away
MySQL error (# 0) on query: Database not found or insufficient priviledges!
MySQL server has gone away

Does the new bot version use a new mysql connection string?
Cuz it looks like my remote DB connection is timing out..

kardsen · « **Reply #1 on:** July 16, 2008, 02:19:34 am »

seems to time out here

Quote

Guildcrier [LOGIN] [STATUS] Connecting
Guildcrier [LOGIN] [STATUS] Authenticating
Guildcrier [LOGIN] [STATUS] Logging in
Guildcrier [LOGIN] [RESULT] OK
Guildcrier [SETTINGS] [SAVED] orggov for module Security set to Unknown as datatype string
Guildcrier [BUDDY] [LOG] Jeffthegirl logged [on] (MEMBER)
Guildcrier [CORE] [INC_GANNOUNCE] Detected org name as: ~Guild
Guildcrier [BUDDY] [LOG] Kardsen logged [on] (OWNER)
Guildcrier [BUDDY] [LOG] Rainmaker logged [on] (SUPERADMIN)
Guildcrier [BUDDY] [LOG] Amaes logged [on] (MEMBER)
Guildcrier [BUDDY] [LOG] Gaderus logged [on] (MEMBER)
Guildcrier [BUDDY] [LOG] Ashli logged [on] (not on notify)
Guildcrier [BUDDY] [LOG] Heck logged [on] (not on notify)
Guildcrier [BUDDY] [LOG] Brockus logged [on] (not on notify)
Guildcrier [SETTINGS] [SAVED] connected for module irc set to TRUE as datatype bool
Guildcrier [SETTINGS] [SAVED] orggov for module Security set to Unknown as datatype string
Guildcrier [SETTINGS] [SAVED] orggov for module Security set to Unknown as datatype string
Guildcrier [SETTINGS] [LOAD] Loaded settings from database.
MySQL error (# 0) on query: Database not found or insufficient priviledges!
Lost connection to MySQL server during query
MySQL error (# 0) on query: SELECT code FROM colors WHERE name = 'tan'
MySQL server has gone away
MySQL error (# 0) on query: Database not found or insufficient priviledges!
MySQL server has gone away
MySQL error (# 0) on query: SELECT code FROM colors WHERE name = 'forestgreen'
MySQL server has gone away
MySQL error (# 0) on query: Database not found or insufficient priviledges!
MySQL server has gone away
MySQL error (# 0) on query: SELECT name, code FROM colors
MySQL server has gone away
MySQL error (# 0) on query: Database not found or insufficient priviledges!
MySQL server has gone away

Vrykolas · « **Reply #2 on:** July 16, 2008, 03:30:59 pm »

The database is re-connected to every time it is used so I suspect something else is going on...

I'll look at the problem when I get back home tonight Kard

kardsen · « **Reply #3 on:** July 16, 2008, 04:52:26 pm »

Yeah, I was looking through the mysql code and the connection is reestablished each time it's called updonas you stated. So im not sure why after it fails I cannot get it to run any queries.. even queries that ran before it failed don't work. Though a reboot of the bot fixes the issue temporarly (10min max)

R24-26 works fine. I might, on occasion get a "unable to connect to mysql server"
But the next time it needs the database it connects fine.

I'll let the r46 bot run a couple times today and see if I can find out where its failing at.

kardsen · « **Reply #4 on:** July 16, 2008, 07:04:51 pm »

Anyone else reading this..

I have tried starting with a new database, verified the mysql.conf was corect.
The error only occurs when connecting to a remote database. Local databases are fine.

None of this was an issue with the old release. I am not using any modified code.

kardsen · « **Reply #5 on:** July 17, 2008, 02:59:16 am »

Thanks to Vrykolas for the fix..

if your reading this and have a similar problem with r48 losing the connection to our database that is hosted remotely..
Back up your ./Sources/MySQL.php

and replace with the file listed below. Remember to rename it from .phps to .php

*updated file to the 0.5.2 release

Khalem · « **Reply #6 on:** July 18, 2008, 06:55:35 pm »

I ran into the same problem myself last night. Unfortunately i have not had the time to make a proper fix, however i do have some theories.

The commit introducing the problem as far as BeBot trunk goes is http://svn.shadow-realm.org/index.py/BeBot/trunk/Sources/MySQL.php?r1=1531&r2=1541

What seems to be occuring is that in the time between the bot connecting to the server, and the cronjobs ensuing, the database connection times out on the server end, but the client does not realize it, meaning that the $conn = mysql_connect($this -> SERVER, $this -> USER, $this -> PASS); returns a seemingly valid connection that is actually no longer valid. And the bot keeps trying to use this connection until a fatal function (security system) fails to load data from MySQL.

I haven't had the time to look at the above provided file, but i'm sure theres a proper way to detect if the link is still alive and well.

On a side note, the problem can also exist on local connections. It depends on the server timeout settings.

Vrykolas · « **Reply #7 on:** July 18, 2008, 09:03:20 pm »

All my fix does is modify the connect function so that it is aware and and respects the timeout value and clears and retries connecting once on failure.