Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

2006 MySQL server has gone away

Hello,

Im getting this error "General error: 2006 MySQL server has gone away" when saving a object.

Im not going to paste the code since it way too complicated and I can explain with this example, but first a bit of context:

Im executing a function via Command line using Phalcon tasks, this task creates a Object from a Model class and that object calls a casperjs script that performs some actions in web page, when it finishes its save some data, here's where sometimes I get mysql server has gone away, only when the casperjs takes a bit more.

Task.php

 function doSomeAction(){
     $object = Class::findFirstByName("test");
       $object->performActionOnWebPage();
 }

In Class.php

function performActionOnWebPage(){
    $anotherObject = AnotherClass::findFirstById($anId);
     $result = exec ("timeout 30s casperjs somescript.js");
    if($result){
        $anotherObject->value = $result->value;
         $anotherObject->save();
    }
 }

It seems like the save method is affected by the time exec ("timeout 30s casperjs somescript.js"); takes to get an answer, when it shouldnt.

Any explanation to this behaviour?

edited Jun '16

I'm having the same problem. I found some answers which advised to increase connection timeout and max_allowed_packet, but it didn't help.

I ended up with a hacky solution, implementing the ping method of procedural mysql api:

class MyDb extends PhalconDb {
    public function ping() {
        try {
            $this->fetchOne('SELECT 1');
        } catch (\PDOException $e) {
            if (strpos($e->getMessage(), 'gone away') !== false) {
                $this->connect();
            }
            else {
                throw $e;
            }
        }
    }
}

I call it before codes which have this problem...

edited Jun '16

You should consider to increase MySQL

waittimeout and interactivetimeout and maybe netreadtimeout

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar%5wait%5timeout http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar%5interactive%5timeout

because your script connection times out



1.1k

Yes, but I can't change the timeout since it will affect to the whole project.

I would like to understand what is going on. It seems like the *$anotherObject->save(); *method is affected by the time *exec ("timeout 30s casperjs somescript.js"); *takes to get an answer, when it shouldn't. Its not a matter of the data saved since it fails and saves succesfully with the same input, the only difference I see is the time casperjs takes to return a value. It seems like if for some reason phalcon opens the MySQL conection during the whole execution of the "Class.php" function, provoking the timeout when casperjs takes too long, does this make any sense?



6.9k
Accepted
answer
edited Jun '16

First time you use some model or something related to it, Phalcon will open connection to DB. Then when the execution is taking too long, it will exceeds one of the MySQL timeouts and DB server will close this connection even if script is still running.

@davihu : I don't think that's the actual problem. No single query takes more than 1-2 seconds for me. I guess it has to do with some sort of "connection keep-alive"



1.1k

So @davihu you are saying that when I find an object, that conection is open and it keeps that way until I save, which provoques the error because of the timeout.

That makes sense but, how to fix it or to work around it.

function performActionOnWebPage(){
    $anotherObject = AnotherClass::findFirstById($anId);
     $result = exec ("timeout 30s casperjs somescript.js");
    if($result){
        $anotherObject->value = $result->value;
         $anotherObject->save();
    }
 }
edited Jun '16

I had similar issues. I have solved it easy with:

    //this is also possible via try/catch block instead of forcing connect every time
        $this->db->connect(); //This will reconnect even active connection pipe

Since I knew my DB task is very fast, the issue was when there's no interaction between app and DB for a long time, the RDBMS will just close the connection if you're not using persistent connections. For my use case, it was enough to just connect every time before inserting stuff into database since this was a persistent CLI task (running in memory 24/7) but events are not that often during night, and obiously this issue was present only during night when there are few events to catch etc.

But if you have that long queries running, you should really try to optimize as much as possible since the connection / read limits by default should be enough.



1.1k

How can I do that from a Model Class?

edited Jun '16

Models does not interact directly(note) with DB component / serivce which handles low level task of connecting to the remote RDBMS. You can inject via php $di and resolve the db service from there if you really want, but usually this should be done on controller logic.

note -> sure they do, but not in terms of taking care of direct connection etc.

Try to use php $this->db->connect(); from your base model, if it doesn't work you need to inject $di.



1.1k

$this->db->connect(); didnt work, How could I inject $di?

edited Jun '16

@subharb You can resolve it then to a local variable like:

$db = $this->getDI()->getShared('db');

And here's an example of handling this 'server has gone away' error from another application.

It can be useful for such scenarios in Phalcon too.

 if(!DB::Execute($query,$query_args)) { //if not - probably server gone away - retry every 10 seconds for 1h
            for($i=0; $i<360; $i++) {
                sleep(10);
                $connection = null;
                try {
                    $connection = DB::Connect(); //reconnect database as new connection
                } catch(Exception $e) {
                    continue; //no connection - wait
                }
                if($connection->Execute($query,$query_args)) {  //if ok then break and exit
                    $connection->Close();
                    break;
                }
                $connection->Close();
            }
        }