reconnect on MySQL server has gone away

[razvanphp]

We are using this library for a long time, thank you for that!

Until now, we tried different approaches of making sphinx HA, first by using a local instance with distributed index, than with a TCP load balancer, but all methods have drawbacks.

Recently, this got worse with moving to AWS, because it seems that for long running jobs, TCP connections don't hang in too long, so we get this generic error, even tho the sphinx server did not crush.

Would it be possible to extend the connection object in a way that it is possible to retry (at least once, ideally configurable) connecting again to the configured endpoint? It seems that $silence_connection_warning only does this at the first attempt to connect & ping() method just checks if the connection object is there, not if the TCP connection is still alive.

If you can provide some guidelines how you would want this implemented elegantly, I would happily provide a PR.

[oohnoitz]

ping() calls mysqli::ping which should return a true or false value when if checking the connection to the server is still alive, but it won't do anything more than that, unless you have mysqli.reconnect enabled I believe.

My current gut feeling would be to implement a public reconnect method that can be used to forcibly reconnect to Sphinx. This could then be called whenever you run into a case where your initial connection was idle for too long before you execute the search query. But I'm not entirely sure this is necessary since it shouldn't take long even on the smallest EC2 instance to generate and execute a query against Sphinx.

[razvanphp]

mysqli implementation with mysql_nd disabled the ping function for security reason, the bug here is marked as won't fix: https://bugs.php.net/bug.php?id=52561

so indeed it looks like this has to be optional, but reconnect would be pretty useless since we can just do close() & connect() with the same effect.

problem is, we do not know when this happens to do it, so we would have to catch all errors in all places we use SphinxQL builder.

a better approach imho would be to have a configuration attribute in the connection class called reconnect or similar that can be set when it is instantiated and have the library do this automagically.

[oohnoitz]

Right, I forgot we had the close method available on the Connection classes for each driver, which would make the reconnect method somewhat redundant.

problem is, we do not know when this happens to do it, so we would have to catch all errors in all places we use SphinxQL builder.

Agreed. That would just end up being a workaround and not an actual solution to the problem.

a better approach imho would be to have a configuration attribute in the connection class called reconnect or similar that can be set when it is instantiated and have the library do this automagically.

I have mix feelings about doing this. At this point, it makes me wonder if having a connection manager/pool would make sense. Want to chime in on this @woxxy? Also, I'm a bit curious as to why you are encountering this at all. Are you generating/running queries throughout your long running job and using the same connection instantiated at the beginning on the job?

[razvanphp]

Yes, exactly.

The sitemap job for example does some other things in between for some minutes, and when it tries to reuse the connection, ping() is called in multiQuery() but this is too dumb, only checks if the Connection object was instantiated, not if it's still actually connected on the TCP stack, so that would be a fatal exception from PDO on $this->connection->query();.

The "proper" way suggested in all questions regarding this is to make a SELECT 1 to really check the connection, but this feels really hacky, even tho it works in sphinx.

Maybe you guys have a more elegant idea...

[TwoHeadedBoy001]

with the Mysqli driver, changing the ping function from this

    public function ping() {
        $this->ensureConnection();
        return $this->getConnection()->ping();
    }

to this

    public function ping() {
        $this->ensureConnection();
        if($this->getConnection()->ping() === false) {
            $this->connect();
        }
        return true;
    }

works with long-running processes failing to reconnect.