How to automatically reconnect when PHP connects to MySql

When the mysql connection is interrupted, subsequent execution of the loop will fail. This article mainly introduces the method of PHP connecting to MySql and automatically reconnecting after interruption. It is very good and has reference value. Friends who are interested should learn together

Use PHP as a background running program (such as group text messaging). To execute php in cli mode, php needs to connect to mysql to perform database processing in a loop.

When the mysql connection is interrupted, subsequent execution of the loop will fail.

We need to design a method that can automatically reconnect when mysql is disconnected, so that subsequent programs can execute normally.

1. Create a test data table

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Insert test data

insert into user(name) values('fdipzone'),('xfdipzone'),('terry');
mysql> select * from user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | fdipzone |
| 2 | xfdipzone |
| 3 | terry |
+----+-----------+

3.Backend Running php file

db.php

##

<?php
// 数据库操作类
class DB{
// 保存数据库连接
private static $_instance = null;
// 连接数据库
public static function get_conn($config){
if(isset(self::$_instance) && !empty(self::$_instance)){
return self::$_instance;
}
$dbhost = $config[&#39;host&#39;];
$dbname = $config[&#39;dbname&#39;];
$dbuser = $config[&#39;user&#39;];
$dbpasswd = $config[&#39;password&#39;];
$pconnect = $config[&#39;pconnect&#39;];
$charset = $config[&#39;charset&#39;];
$dsn = "mysql:host=$dbhost;dbname=$dbname;";
try {
$h_param = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);
if ($charset != &#39;&#39;) {
$h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = &#39;SET NAMES &#39; . $charset; //設置默認編碼
}
if ($pconnect) {
$h_param[PDO::ATTR_PERSISTENT] = true;
}
$conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);
} catch (PDOException $e) {
throw new ErrorException(&#39;Unable to connect to db server. Error:&#39; . $e->getMessage(), 31);
}
self::$_instance = $conn;
return $conn;
}
// 执行查询
public static function query($dbconn, $sqlstr, $condparam){
$sth = $dbconn->prepare($sqlstr);
try{
$sth->execute($condparam);
} catch (PDOException $e) {
echo $e->getMessage().PHP_EOL;
}
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
}
?>

test.php

<?php
require &#39;db.php&#39;;
// 数据库设定
$config = array(
&#39;host&#39; => &#39;localhost&#39;,
&#39;dbname&#39; => &#39;user&#39;,
&#39;user&#39; => &#39;root&#39;,
&#39;password&#39; => &#39;&#39;,
&#39;pconnect&#39; => 0,
&#39;charset&#39; => &#39;&#39;
);
// 循环执行
while(true){
// 创建数据连接
$dbconn = DB::get_conn($config);
// 执行查询
$sqlstr = &#39;select * from user where id=?&#39;;
$condparam = array(mt_rand(1,3));
$data = DB::query($dbconn, $sqlstr, $condparam);
print_r($data);
// 延时10秒
echo &#39;sleep 10&#39;.PHP_EOL.PHP_EOL;
sleep(10);
}
?>

##4. Perform steps Execute test.php in php cli mode, and then immediately execute mysql.server stop and mysql.server start to simulate a flash interruption

mysql.server stop
Shutting down MySQL
.. SUCCESS! 
mysql.server start
Starting MySQL
SUCCESS!

As you can see, the database cannot be reconnected after the interruption, and subsequent programs cannot be executed.

Array
(
[0] => Array
(
[id] => 3
[name] => terry
)
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
...

5. Add reconnection mechanism

if(isset(self::$_instance) && !empty(self::$_instance)){
return self::$_instance;
}

After the interruption, because the value of self::$_instance exists, calling get_conn will not reconnect, but will use the saved connection for processing.

This actually means that when the connection exists, there is no need to create a mysql connection again and reduce the number of mysql connections.

So you need to clear the value of self::$_instance after the interruption so that you can re-obtain the connection next time without using the database connection that has been created but failed.

The improvement method is as follows:

Add the reset_connect method, which is called when an error occurs. If the error is judged to be MySQL server has gone away, the existing database connection will be cleared. After clearing, MySQL will be reconnected next time.

The modified php file is as follows:

db.php

 PDO::ERRMODE_EXCEPTION,
);
if ($charset != '') {
$h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset; //設置默認編碼
}
if ($pconnect) {
$h_param[PDO::ATTR_PERSISTENT] = true;
}
$conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);
} catch (PDOException $e) {
throw new ErrorException('Unable to connect to db server. Error:' . $e->getMessage(), 31);
}
self::$_instance = $conn;
return $conn;
}
// 执行查询
public static function query($dbconn, $sqlstr, $condparam){
$sth = $dbconn->prepare($sqlstr);
try{
$sth->execute($condparam);
} catch (PDOException $e) {
echo $e->getMessage().PHP_EOL;
self::reset_connect($e->getMessage()); // 出错时调用重置连接
}
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
// 重置连接
public static function reset_connect($err_msg){
if(strpos($err_msg, 'MySQL server has gone away')!==false){
self::$_instance = null;
}
}
}
?>

6. Perform the flash execution againYou can see the improved effect. After the flash interruption, the current execution will fail, but subsequent executions can be re-created and a new connection will continue. .

Array
(
[0] => Array
(
[id] => 2
[name] => xfdipzone
)
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
Array
(
[0] => Array
(
[id] => 1
[name] => fdipzone
)
)
sleep 10
...

Related recommendations:

Garbled characters occur after PHP connects to the mysql database

How to connect PHP to mysql-mysqli and PDO

##

The above is the detailed content of How to automatically reconnect when PHP connects to MySql. For more information, please follow other related articles on the PHP Chinese website!