Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

mysql server gone away, pdo not reconnecting

eleitheleith New
edited March 2010 in Vanilla 2.0 - 2.8
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.
«1

Comments

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

  • ubuntu 8.04 server, running latest stable packages of mysql (5.0.51a) and php (5.2.4), and latest vanilla 2 from git
  • lucluc ✭✭
    fast-cgi via which web server?
    How many instances? Busy forum?

    Have you got some php cache somewhere?

  • 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
  • lucluc ✭✭
    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?
  • 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.

  • lucluc ✭✭
    I'll try first thing tomorrow morning.
  • 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).
  • i feel like something worse is happening.

    i have now been able to reproduce the error just by refreshing the website in rapid succession.
  • lucluc ✭✭
    I guess so.
    I've just tested your steps above, and it works fine.

    I've got php-xcache though.
  • MarkMark Vanilla Staff
    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?
  • MarkMark Vanilla Staff
    @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.
  • @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...
  • lucluc ✭✭
    @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.
  • lucluc ✭✭
    edited March 2010
    @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
  • lucluc ✭✭
    One more question, are you connecting via network or via the filesystem socket?
  • lucluc ✭✭
    edited March 2010
    One more question, are you connecting via network or via the filesystem socket?

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

    i'll try upgrading php on my end
  • lucluc ✭✭
    edited March 2010
    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.
  • lucluc ✭✭
    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...
Sign In or Register to comment.