How reconnect and retry a MySQL query in PHP when the database connection has been lost?

Question

I have a PHP file which sometimes has a long execution and the MySQL connection (a mysqli object) goes stale. If I check the connection with mysqli_ping (and reconnect if mysqli_ping returns false), then I never have a problem. However, pinging before every query seems highly inefficient, so I'd like to only perform the ping check (and reconnect) upon the query failing. My problem is that I cannot seem to get my code to throw an exception upon the failure of the mysqli_query in order to trigger a reconnect.

Here is my "basic" code:

<?php

function query($query) {
   $connection = $GLOBALS['global_connection'];
   if(empty($connection) || mysqli_connect_errno()) $connection = connect();

   try {
        $result = mysqli_query($connection, $query);
   } catch(Exception $e) {
        if(!mysqli_ping($connection)) {
           $connection = connect(); // reestablish the connection
           $GLOBALS['global_connection'] = $connection; // update global connection value
           $result = mysqli_query($connection, $query); // requery using the new connection
        }
   }

   return $result;
}

The problem is that a bad connection in the mysqli_query does not cause an exception to be thrown, but it does cause an error to show up in my error log.

I have tried mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); (as suggested in other questions) before the mysqli_query but it does not change the outcome. I also have mysqli.reconnect enabled in my PHP config, but that doesn't seem to stop this.

So my question is either:

How do I throw an exception when a mysqli_query fails (due to a timed out connection)?

or

How do I automatically reconnect database in an efficient manner?

The mysqli functions don't throw exceptions, they just return `FALSE` or `NULL` when they fail. You need to check the reason with `mysqli_errno()`. — Barmar, May 26 '17 at 19:02
The documentation says that `mysqli.reconnect` is ignored if you have the `MYSQLND` driver. — Barmar, May 26 '17 at 19:04
You should probably check `myqli_errno()` to see if the reason for the error was that the connection went away. If it's because of something like a syntax error there's no point in retrying. — Barmar, May 26 '17 at 19:34
When you reconnect you should assign `$GLOBALS['global_connection'] = $connection;` — Barmar, May 26 '17 at 19:37
I do reset the `GLOBALS` value already. Would the pre-`mysqli_ping` check just be `if(mysqli_connect_errno() == 2006) { /* mysqli_ping code here */ }` then? — Bing, May 26 '17 at 19:40
And is there some way to simulate the connection going away for testing purposes? `mysqli_close` doesn't seem to cause that error, but a "cannot connect (at all)" one.... — Bing, May 26 '17 at 19:41
You could probably change the timeout to something very short, then run a script that uses a `sleep()` call to cause it to disconnect. — Barmar, May 26 '17 at 19:43
Another possibility. Use `SHOW PROCESSLIST` to find the connection belonging to your application, then `KILL ` to kill the ID of that connection. — Barmar, May 26 '17 at 19:46
See https://www.pythian.com/blog/connection-timeout-parameters-mysql/ — Barmar, May 26 '17 at 19:46

score 3 · Accepted Answer · answered May 31 '17 at 21:58

Per Barmar's help in the comments I realized all I had to do was this:

<?php
$result = mysqli_query($connection, $query);
if(empty($result)) {
    if(!mysqli_ping($connection)) {
        $connection = connect(); // re-establish the connection 
        $GLOBALS['global_connection'] = $connection; // update global connection value
        $result = mysqli_query($connection, $query); // re-query using the new connection
    }
}

No try{}catch needed at all! Clean and simple.

score 1 · Answer 2 · answered May 26 '17 at 19:54

You will need to manage (throw) your own custom exceptions for this to work the way you need it too. I would consider making a database abstraction class to help handle this. The database class would have a member variable that is the connection reference and could use the mysqli built in error handling to throw custom exceptions relating to that instance of the mysqli connection object.

If you only need one database connection I might try a singleton pattern.

How reconnect and retry a MySQL query in PHP when the database connection has been lost?

2 Answers2