How to automatically recover from an MySQL SERVER HAS GONE AWAY error






The error message is very self explaining – the program lost connection to the server. The reason for this error is quite often simply that the script took longer time to execute than the time-out settings in the connection.

In PHP there is a simple command to check if the database connection still is open: mysqli_ping(mysqli $link) or just ping() when using object oriented programming. With a simple ping check you can easily see if you need to reconnect before sending your query.

// Reconnect to server if needed
if(!$link->ping()) {
  reconnect();
}

// Now we know the link is up
$result = $link->query($sql);

However, it’s not convenient having to write this code hundreds of times in your scripts before every query. One way to get around it is to create your own mysqli class extending the original one. Based upon a real_connect example from the PHP website we can continue and also extend the query function to always make a ping check before sending the query. To make the code work we need to store the database credentials in the class and also create an internal reconnect function to be called every time we’ve lost connection.

class extMysqli extends mysqli {
  private $host;
  private $user;
  private $pass;
  private $db;
  private $port;
	
  public function __construct($host, $user, $pass, $db, $port = null) {
    parent::init();
    $this->host = $host;
    $this->user = $user;
    $this->pass = $pass;
    $this->db = $db;
    $this->port = $port;
		
    if (!parent::options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
      die('Setting MYSQLI_INIT_COMMAND failed');
    }

    if (!parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, 30)) {
      die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
    }
		
    $this->intConnect();
  }
  function query($sql)
  {
    // Reconnect to server if needed
    if(!parent::ping()) {
      $this->intConnect();
    }

    return parent::query($sql);
  }
  private function intConnect()
  {
    if (!parent::real_connect($this->host, $this->user, $this->pass, $this->db)) {
      die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
    }
  } 
}

To use the class just create an instance of it and call the query function. A ping will automatically be made, with a following re-connect if needed.

$db = new extMysqli('localhost', 'my_user', 'my_password', 'my_db');
$result = $db->query('select * from customers');






So, if your error message was due to the fact that the script took too long time to execute then this little code snippet will take care of that.