how to enable database auto-reconnect in PHP?

Question

and the database is MYSQL

Answer 1

Sometimes when your MySQL connection opened for too long, the connection to the DB will be dropped when the time without a query exceeds the wait_timeout value in my.cnf. You will get "MySQL server has gone away" timeout error.

This is how I implement auto reconnect in my code:

class databaseClass {
    var $conn;
    var $db;

    public function __construct() {
        $this->connect();
    }

    public function connect() {
        $this->conn = mysql_connect(DB_HOST, DB_USER, DB_PASS);
        $this->db = mysql_select_db(DB_NAME, $this->conn);
    }

    public function disconnect() {
        mysql_close($this->conn);
    }

    public function reconnect() {
        $this->disconnect();
        $this->connect();
    }

    public function queryCompanyExist($company) {
        //auto reconnect if MySQL server has gone away
        if (!mysql_ping($this->conn)) $this->reconnect();

        $query =  "SELECT name FROM company WHERE name='$company'";
        $result = mysql_query($query);
        if (!$result) print mysql_error() . "\r\n";
        return mysql_fetch_assoc($result);
    }
}

Refer here for more infomation about mysql_ping

Answer 2

From the C mysql API:

my_bool reconnect = 0; mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect); See http://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html

From PHP mysqli in php.ini set the global: mysqli.reconnect = On See http://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.reconnect

For the PHP PDO mysql driver it should be available through PDO::setAttribute but I am unable to find documentation indicating that it is implemented. The code appears to be attempting to respect the MYSQL_OPT_RECONNECT which is now required by mysql but to have failed to do so in the initial implementation (https://bugs.php.net/bug.php?id=58863). The intent of the patch in 58863 is to allow: new PDO('mysql:host=xxx', 'xx', 'xx',array(PDO::MYSQL_OPT_RECONNECT=>true));

The reasons why the reconnect occurs are various but commonly because a pooled set of connections contains a mature connection which has timed out due to a lack of use. By default mysql connections time out after 8 hours. See: http://dev.mysql.com/doc/refman/5.6/en/gone-away.html

For side effects of a reconnection see: http://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html

Answer 3

You can use the mysql_ping() function to test the status of the connection and reconnect when it returns false. mysql_ping() will even reconnect for you if you're using a MySQL before 5.0.13 as mentioned on that documentation page; "Since MySQL 5.0.13, automatic reconnection feature is disabled.".

Answer 4

You can write a function which will ping the database via connection and in case it down, reconnect it again and then proceed the query or whatever you want, also take a look on mysqli php library, it can be useful for you.

PS. Also could be useful to implement Singleton design pattern in order to maintain the database connection, once created it will connect to database and then you can implement the method called getConnection which each time will proceed with the check I've described above.

PPS. You can use an exception, try you query, whenever it fails catch an exception, reconnect and try again.

Answer 5

You should be checking if the query fails anyway. By checking the error code you can tell if it failed because of no connection and reconnect. Just make sure you keep track of reconnection attempts so you don't get stuck in a loop.

Answer 6

Why do you need to "reconnect" in the first place? How/Why are you getting disconnected? In PHP you connect at the beginning of the script and the connection is closed automatically at the end of the script.

There's no need to call mysql_close in your script at all (and it would be silly to "automatically reconnect" if you explicitly closed the connection).

You can try mysql_pconnect is that's what you're looking for. The connection will then stay open permanently, and other scripts that connect can use the connection.