MySQL server has gone away - but no connect error

Question

Consider the following scenario: We have an AWS worker instance (SQS based) which opens persistent MySQL connections to our RDS.

$this->connectRegistry[ $host ][ 'connect' ] = mysqli_connect(
    "p:" . $host ,
    $this->hostCredentials[ $host ][ 'username' ] ,
    $this->hostCredentials[ $host ][ 'password' ]
);

Now sometimes - not on a regular basis - this call genertes the following warning:

PHP Warning: mysqli_connect(): MySQL server has gone away

As AWS worker instances are handling requests with a web-server, the setup basically identical to a usual instance which serves a website. We use the same class in multiple projects but in not a single website this error has even occurred once.

To get an idea where these warnings originates from we have tried to print a stack trace if a connection error occurs but - and now the awkward thing starts to come in - there is no connect error. Please take a look at the whole connect function:

private function connect ( $host )
{
    $this->connectRegistry[ $host ][ 'connect' ] = mysqli_connect(
        "p:" . $host ,
        $this->hostCredentials[ $host ][ 'username' ] ,
        $this->hostCredentials[ $host ][ 'password' ]
    );
    $error = mysqli_connect_error();
    if( $error ) {
        $this->raiseError( "Connect (" . mysqli_connect_errno() . ") " . $error );
        return false;
    }
    return true;
}

The method raiseError builds a complete error message with a stack trace and so on and then calls trigger_error. However, this method is not called if the above-mentioned warning is triggered.

Our first goal is to know in which cronjobs these warnings are triggered - maybe there are some bad performing queries.

I don't think `mysqli_connect_error()` returns a bool as a result?! — Naruto, Aug 26 '20 at 07:31
@Naruto If you say that because of `if( $error )`, that's the idiomatic way to test if a string is not null. — Álvaro González, Aug 26 '20 at 07:36
I do not have experience with AWS worker instance, but from the info I've got from the web it is possible that it creates some kind of PHP Daemon, is that right? — Sergio Rinaudo, Aug 26 '20 at 07:41
@SergioRinaudo Yes, kind of. It is just an apache/nginx backed php application where certain paths are called periodically as you specify it in a yaml file. So, it basically invokes cron jobs on a web server. — viamuli, Aug 26 '20 at 08:00
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Aug 26 '20 at 10:01

Dharman · Accepted Answer · 2020-08-26T10:24:09.357

1

There would not be any connection error if you get

mysqli_connect(): MySQL server has gone away

This message means that the existing connection is not usable anymore. Something must have happened and the connection was closed by MySQL server. There could be many reasons for this. The most common one is calling mysqli_close() somewhere else in your code, but as you are using persistent connections here the root cause could be something different altogether. You need to debug what is causing the connection to be dropped.

However, I would strongly recommend to stop using persistent connections altogether. It is highly unlikely that you have a valid technical reason to use them and debugging them can be very problematic.

edited Aug 26 '20 at 10:24

answered Aug 26 '20 at 10:15

Dharman

30,962
25
85
135

Thank your for your answer. I will try out what happens without persistent connections. I tried to convince my superior to not use persistent connections but he said we definitely should use them. Thankfully he's on vacation right now. – viamuli Aug 27 '20 at 08:52
For the last few days at least we haven't had this error. I don't trust it yet but it looks solved. Thank you! – viamuli Aug 31 '20 at 09:12

score 0 · Answer 2 · answered Aug 26 '20 at 09:00

I had this kind of problem with php daemons: when you start your daemon, the php connection is made and if there are no query for some time, the DB connection actually dies.

In the past I've found two solution

To keep the connection alive, make a simple fast query every n seconds
Create the connection at runtime, not when the daemon start but when the daemon does the work.

I advice the second choice.

The "persistent" connection doesn't mean that the connection will live forever, it is just reused if use the same username, password etc ( doc ), this can help if you use the method 2)

MySQL server has gone away - but no connect error

2 Answers2