PHP mysqli reconnect problem

Question

I am having trouble using the mysqli class in PHP and I haven't been able to find the answer anywhere.

In my script a class creates a mysqli connection that it uses throughout it's functions. Afterward, this script forks. The connection is used by the children as well, but I'm running into the problem of the connection being closed (MYSQL Server Has Gone Away) in the parent when the children die.

Before I switched to mysqli (was just using mysql) I simply called mysql_ping to assure that the db connection was there before performing the query in the parent process. Mysqli has a similar ping function BUT it doesn't actually reconnect if the connection is gone. I tried using the mysqli.reconnect=ON global setting with no luck (using php.ini and ini_set).

The php mysql_connect function allows you to grab an already-existing connection, so if I was using mysql instead of mysqli, I could simply reuse the connection in the child right after the process forked. BUT mysqli doesn't seem to have any such functionality...

The only thing I was able to do was call mysqli->ping() and if that returned false then reconnect to the database in the parent. This is terribly inefficient, and I would much rather figure out how to do it correctly with mysqli (and no need for manual reconnects) that having to change back to mysql..

Any suggestions?

Answer 1

I think you need to set mysqli.reconnect in my.cng, which is the mysql config, rather than php.ini, I couldn't manage to change reconnect via ini_set, but my sys admin did it in my.cnf.

So, tad confused that others have done it within php.ini....

Answer 2

u can try something a bit different .. instead of ping .. try to send a really simple low intensity query like "select now()" and see if you get any better results.

Answer 3

Can't you use persistent connections? Also, is that really necessary to fork() ?

Answer 4

The doc for mysqli_ping() says that if you set the global option mysqli.reconnect to 1 (in your php.ini) then mysqli_ping() will reconnect when it detects the connection has gone away.

Update: Since I answered this question in 2009, PHP has mostly moved on to use the mysqlnd driver by default instead of libmysql. As mentioned in the comment below, mysqlnd does not support the mysqli_ping() function, and the PHP developers did this intentionally, according to their "Won't Fix" answer in https://bugs.php.net/bug.php?id=52561

So there appears to be no solution for automatic reconnect in PHP anymore.

Answer 5

function IsConnected() {
    if (empty($this->_connectionID) === FALSE && mysqli_ping($this->_connectionID) === TRUE) {
        return true; // still have connect
    }
    $this->_connectionID = false;
    return false; // lose connection
}

Answer 6

Use http://www.php.net/manual/en/mysqli.real-connect.php ... reinstall php and check your php.ini settings