Mysql Server Has Gone Away'

[evanmcd]

Hi all,

Hoping someone has experienced this before.  Honestly, I'm not sure the issue is PW specific - I'm able to login to the admin - but when I navigate to any front-end page, I get a timeout and the 'MySQL server has gone away' error message in the log.

Any help would be GREATLY appreciated.

Thanks.

Edited August 1, 2014 by horst
changed title because the initial issue isn't urgent anymore (URGENT issue - 'MySQL server has gone away' on live site)

[Pete]

Only thing I can think of is maybe mySQL has crashed - I suggest restarting that.

[evanmcd]

Thanks, I had thought of that, but the PW admin wouldn't work if that were the case.

[Pete]

Hmm, good point. Maybe some tables are in need of repair?

[evanmcd]

I guess we think alike... repaired all the tables as well.

[Adam Kiss]

Incidentally, there seems to be many reasons why 'MySQL has gone away': http://dev.mysql.com/doc/refman/5.0/en/gone-away.html'>http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

But then again, maybe you just treated it like it's six years old, and it didn't like it, so it ran away.

[arjen]

Perhaps really obvious: have you tried the backup of yesterday? Or the day before?

[apeisa]

There are lot's of things to try on link Adam provided. I would assume it is something gone bad on mysql update, server restart or something like that.

[evanmcd]

Thanks guys, for the help.  I thought it was MySQL as well, but it's turning out to be an issue with RSS: a feed that we're loading through the PW RSS module is not loading - not timing out - just never loading.

Looking into the fix now, but at least I've just been able to disable that feed and get the site back up.

[apeisa]

Ah, we had the same issue just few days ago. Still thinking about best fix. It does need some kind of background pull method and serving only from cache.

It was nasty one to debug because nothing came to logs. We just got site halting, but no mysql errors of any kind.

[Hari KT]

I am also getting an error when I need to do a cronjob say around 16000 pages.

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I want to check when a db connection failed whether the connection can be automatically connected again.

[ryan]

16k pages is a lot to deal with one one request (if you are using the API as opposed to hitting the DB directly). It looks like MySQL has some resource limiter placed upon it in your case, so re-connecting probably won't help you (though that's a guess). You probably need to reduce the quantity of pages you are working with there, perhaps setting a limit (5000?) and keeping to it for each run. But if you want to check for a DB failure, your best bet is to wrap your operations in a try/catch. But I think the solution here will involve reducing the amount you do in a single cron run. 

[Hari KT]

Hey @ryan

I have send a PR https://github.com/ryancramerdesign/ProcessWire/pull/366 which will fix this.

This have been run and is working fine.

Edit : If you are running on an older ones have a look into http://zaemis.blogspot.in/2014/02/fixing-mysql-server-has-gone-away.html post . That will help you to set the flag. But not for PDO.

Edited February 26, 2014 by Hari KT

[horst]

 I have send a PR https://github.com/ryancramerdesign/ProcessWire/pull/366 which will fix this.

This have been run and is working fine.

Many thanks. Have installed it.

[Hari KT]

@horst you are welcome .

Hope all is well at your end also.

[horst]

@horst you are welcome .

Hope all is well at your end also.

Yes it is working well. I have a site on a shared host where I have got sometimes this "2006 Server has gone away" error, especially when the client was working in the admin with large pagelists (he wants to have the default limit of 50 increased to 200). But this wasn't the only time when this happens.

Now after update it with your patch I think we have to wait a week or two to be really sure it is solved. But actually I think yes it will be!

[Hari KT]

@horst, so how is things working . I was forced to fix with a different fix again . It was in `DatabaseQuery` execute method.

    public function execute() {
        try {
            $database = $this->wire('database');
            $query = $database->prepare($this->getQuery());
            $query->execute();
        } catch (Exception $e) {
            $msg = $e->getMessage();
            if (strstr($msg, 'MySQL server has gone away')) {
                $database->closePdo();
            }
            throw new Exception($e->getMessage());
        }
        return $query;
    }

and in the `WireDatabasePdo` adding a new method

    public function closePdo()
    {
        $this->pdo = null;
    }

Hope this fix will help everyone .
 

One still problem is one query can be missed which got the failure. To execute that I think it is good to call `execute()` again with some flag set.

Your thoughts ?

[horst]

@Hari KT: In the whole time since your first patch (5 month until now), it occured max 2 or 3 times, not more, but also not zero.

Thanks for sharing your code!

[Hari KT]

@horst good to hear.

I have another patch which will more more awesome then . Don't forget to add `private static $mysql_gone = false;` in the `wire/core/DatabaseQuery.php`. And the execute changes to

public function execute() {
    try {
        $database = $this->wire('database');
        $query = $database->prepare($this->getQuery());
        $result = $query->execute();
        if (self::$mysql_gone) {
            self::$mysql_gone = false;
        }
    } catch (Exception $e) {
        if (self::$mysql_gone) {
            throw new Exception("It is better to exit than throwing error " . $e->getMessage());
        } else {
            // try once again                
            $msg = $e->getMessage();
            if (strstr($msg, 'MySQL server has gone away')) {
                self::$mysql_gone = true;
                $database->closePdo();
                sleep(20);
                $this->execute();
            } else {
                throw new Exception($e->getMessage());
            }
        }
    }
    return $query;
}

Discussion on github https://github.com/ryancramerdesign/ProcessWire/pull/563#issuecomment-50871518

[MuchDev]

Old issue I know, but I seem to be having some quirks with my current setup. I have been getting this error when I go to update a cache-field with a large amount of fields. Is this error just in reference to a timeout from my mySQL server?

[benbyf]

having the same issue: processwire Error: Exception: SQLSTATE[HY000]:

this is happening when I upload an image on an edit page. anyone able to shed any light?

[Beluga]

I get these in OVH shared hosting. Updated PHP from 5.6 to 7, but it didn't help (well, it really seems to be about the db itself..)

Error:
Exception: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (in /home/user/www/wire/core/Pages.php line 2103)

#0 /home/user/www/wire/core/Pages.php(2103): PDOStatement->execute()
#1 /home/user/www/wire/core/Pages.php(546): Pages->executeQuery(Object(PDOStatement))
#2 /home/user/www/wire/core/PagesType.php(260): Pages->getById(Array, Object(Template))
#3 /home/user/www/wire/core/Users.php(33): PagesType->get(41)
#4 /home/user/www/wire/core/Session.php(102): Users->get(41)
#5 /home/user/www/wire/core/ProcessWire.php(279): Session->__construct()
#6 /home/user/www/wire/core/ProcessWire.php(94): ProcessWire->load(Object(Config))
#7 /home/user/www/index.php(232): ProcessWire->__construct(Object(Config))
#8 {main}

I get this even when trying to access the front page which doesn't have much else than the navigation menu. Menu is done using this: https://processwire.com/talk/topic/10448-css-only-responsive-multi-level-menu/

Root has 4 children. One of them has 9 children and each of those 3 grandchildren and some of the grandchildren have children of their own. Could walking this structure really bring the DB down?

Is there anything I can do except switch to dedicated hosting (cheapest Kimsufi is only 6 euros per month)?

According to OVH support, the MySQL variable max_allowed_packet is probably 1M and it cannot be raised.

Edit: here is the other type of message I get:

Exception: SQLSTATE[42000] [1203] User user already has more than 'max_user_connections' active connections (in /home/user/www/wire/core/ProcessWire.php line 216)

#0 /home/user/www/wire/core/ProcessWire.php(94): ProcessWire->load(Object(Config))
#1 /home/user/www/index.php(232): ProcessWire->__construct(Object(Config))
#2 {main}

I might get over 200 error emails in one go.