mysql server gone away, pdo not reconnecting

eleith

i get the "mysql server has gone away" error on my forums.

this seems to usually happen overnight after mysqldumps are run. i can not get the forums back up and running till i restart spawn-fcgi/php5.

from what i can gather online, the issue could be solved by just having the PDO driver disconnect and reconnect when it encounters this mysql state. this would be much preferred for me, rather than restarting spawn-fcgi.

i wanted to ask if anyone had done this, or has found another way to avoid these errors, before i go and mess with the database functions in garden/vanilla.

luc

First you could state your version numbers for php and mysql.

eleith

ubuntu 8.04 server, running latest stable packages of mysql (5.0.51a) and php (5.2.4), and latest vanilla 2 from git

luc

fast-cgi via which web server?
How many instances? Busy forum?

Have you got some php cache somewhere?

eleith

spawn-fcgi (10 php5 cgi threads) proxied to by nginx

forum has none to little activity

i have not tinkered with php besides adding pdo, pdo_mysql extension to the php.ini file

luc

I've the got same here, just 6 php-fastcgi only.
And I've never noticed any such thing.

Though it's running php 5.3 now with mysql 5.1 with debian sid, but when I installed it first it was the same version as you or close to it anyway.

By default mysql is 100 connections, so you're safe on that normally.
And dumping mysql data doesn't shut it down, so there's no reason for it to close the long running php connection (if there is one, I don't know about that), and if there's none long running connection to mysql, there's no reason why it will not allow to connect.

Have you changed mysql conf?

About the php, do you have susohin installed? About the cache, do you have something like apc or xcache?

eleith

my.cnf is pretty standard, no changes from default

susohin: no
apc/xcache: no

maybe this is a more isolated example, that could give you a clue to where i should be looking:

1) i restart mysql
2) vanilla/garden won't come up, complaining of lost mysql connect, even though mysql is running now
3) restart spawn-fcgi
4) vanilla/garden now works

i don't have this experience with my other apps, where their mysql drivers seem to catch that the old mysql thread pool died, and it attempts to reconnect before complaining.

luc

I'll try first thing tomorrow morning.

eleith

thanks luc.

just tested it again myself, and can confirm that the "mysql server gone away" message also comes up in the scenario i outline above.

so my current theory is that something happens at night every now and then that restarts mysql, and vanilla/garden won't come back up till spawn-fcgi restarts the php threads.

if you don't have this problem, then something is missing in my environment.

if you do have this problem, then it might not be a bad idea to have vanilla/garden attempt a reconnect (assuming it doesn't already do it).

eleith

i feel like something worse is happening.

i have now been able to reproduce the error just by refreshing the website in rapid succession.

luc

I guess so.
I've just tested your steps above, and it works fine.

I've got php-xcache though.

Mark

I'm actually having this problem on our dev server, too. It's a very large database that we're connecting to - not doing any db dumps of any kind. After a varying # of hours it will give the "mysql server has gone away" message. I have to restart apache to get it back.

I'm wondering if there is a db connection parameter we need to change?

Mark

@eleith - I've found that when you refresh a bunch of times like that, it actually clears your cache - so it was down the whole time, but showing you a cached version. I don't think it is caused by the refreshes.

eleith

@mark you are correct

i've read some older bugs that suggest falling back to connectionless requests
http://bugs.php.net/bug.php?id=36602

but it's hard to believe that after four years the bug still exists and this would still be recommended

does vanilla/garden catch these sorts of errors and tries to reconnect once before failing? i've seen this suggested online for various "mysql server has gone away" handlers...

luc

@Mark: what are your versions? pdo driver and mysql server.

As per http://pecl.php.net/bugs/bug.php?id=16845 there seems to be issues depending on the versions.

luc

@eleith: it might be fixed in 5.3, as I can't reproduce the issue.

Even my debian testing box is already using php 5.3

luc

One more question, are you connecting via network or via the filesystem socket?

luc

One more question, are you connecting via network or via the filesystem socket?

Edit: Sorry for the double post, it got stuck.

eleith

socket

i'll try upgrading php on my end

luc

Me too, I've just tried via network (even though localhost, well 127.0.0.1) and I don't get any message either, it does reconnect fine.


Anyway, as the requirement is php 5.1, it should be managed by Garden too.
Maybe recreating an object when it finds that the connection has gone away.

@Mark, I've checked the php docs, it doesn't seem that there is a parameter to manage this behaviour. Maybe I didn't check properly though.

luc

Well, the bug is: http://bugs.php.net/38996 and it's closed since 5.2.0 release, so with 5.2.4, you should be alright...