PHP connection Mysql Flash Break Automatic reconnection method _mysql

Using PHP as a background run program (such as bulk SMS), executing php,php in CLI mode requires a MySQL loop to perform database processing.

When the MySQL connection flashes, the execution of the loop will fail.

We need to design a method that, when MySQL flashes, can automatically reconnect, so that the following program can be performed normally.

1. Create Test data table

CREATE TABLE ' user ' (
' id ' int ' unsigned NOT null auto_increment,
' name ' varchar ' is 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. php files running in the background

db.php

<?php//Database Operations class db{//Save database connection private static $_instance = NULL;//Connect database public static function Get_conn ($CONFI g) {if (Isset (self::$_instance) &&!empty (self::$_instance)) {return self::$_instance;} $dbhost = $config ['
Host '];
$dbname = $config [' dbname '];
$dbuser = $config [' user '];
$DBPASSWD = $config [' Password '];
$pconnect = $config [' Pconnect '];
$charset = $config [' CharSet '];
$DSN = "mysql:host= $dbhost;d bname= $dbname;"; try {$h _param = array (pdo::attr_errmode => pdo::errmode_exception,); if ($charset!= ') {$h _param[pdo::mysql_attr_ Init_command] = ' SET NAMES '. $charset;
Set the default password} if ($pconnect) {$h _param[pdo::attr_persistent] = true;} $conn = new PDO ($DSN, $dbuser, $dbpasswd, $h _param); The catch (Pdoexception $e) {throw new errorexception (' Unable to connect to DB server. Error: '.
$e->getmessage (), 31);
} self::$_instance = $conn;
return $conn; //execute queries 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 ' db.php ';
Database settings
$config = Array (
' host ' => ' localhost ', '
dbname ' => ' user ',
' user ' => ' root ',
' Password ' => ',
' Pconnect ' => 0,
' charset ' => '
);
Loop execution while
(true) {
//Create data connection
$dbconn = Db::get_conn ($config);
Execute query
$sqlstr = ' SELECT * from user where id=? ';
$condparam = Array (Mt_rand (1,3));
$data = Db::query ($dbconn, $sqlstr, $condparam);
Print_r ($data);
Delay 10 seconds of
Echo ' sleep 10 '. Php_eol. Php_eol;
Sleep (ten);
>

4. Implementation steps

Executes the test.php in PHP CLI mode and immediately executes the Mysql.server stop and mysql.server start simulation flash break

Mysql.server stop
shutting down MySQL
. Success! 
Mysql.server start
starting MySQL
success!

You can see that the back of the flash cannot reconnect the database, the following program cannot proceed.

Array
(
[0] => array
(
[id] => 3
[name] => Terry
)
) sleep
sqlstate[ HY000]: General error:2006 MySQL Server has gone away
Array
(
) sleep
sqlstate[hy000]: General error:2006 MySQL Server has gone away
Array
(
)
...

5. Increasing the interconnection mechanism

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

After the flash, because the value of the self::$_instance exists, the call to Get_conn does not reconnect, but instead uses the saved connection for processing.

This is actually when the connection exists, you do not need to create a MySQL connection again, reducing the number of MySQL connections.

So you need to clear the Self::$_instance value in the Flash, so the next time you get the connection again, instead of using the database connection that you created but failed.

The improvement methods are as follows:

Add the Reset_connect method, which is invoked when an error occurs. If the error is a MySQL server has gone away empty the existing database connection, the next time you empty it will reconnect MySQL.

The modified PHP file is as follows:

db.php

<?php//Database Operations class db{//Save database connection private static $_instance = NULL;//Connect database public static function Get_conn ($CONFI g) {if (Isset (self::$_instance) &&!empty (self::$_instance)) {return self::$_instance;} $dbhost = $config ['
Host '];
$dbname = $config [' dbname '];
$dbuser = $config [' user '];
$DBPASSWD = $config [' Password '];
$pconnect = $config [' Pconnect '];
$charset = $config [' CharSet '];
$DSN = "mysql:host= $dbhost;d bname= $dbname;"; try {$h _param = array (pdo::attr_errmode => pdo::errmode_exception,); if ($charset!= ') {$h _param[pdo::mysql_attr_ Init_command] = ' SET NAMES '. $charset;
Set the default password} if ($pconnect) {$h _param[pdo::attr_persistent] = true;} $conn = new PDO ($DSN, $dbuser, $dbpasswd, $h _param); The catch (Pdoexception $e) {throw new errorexception (' Unable to connect to DB server. Error: '.
$e->getmessage (), 31);
} self::$_instance = $conn;
return $conn; //execute queries 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 ());
The reset connection is called when an error occurs $result = $sth->fetchall (PDO::FETCH_ASSOC);
return $result; //Reset connection public static function Reset_connect ($err _msg) {if Strpos ($err _msg, ' MySQL server has gone away ')!==false) {Self
:: $_instance = null; }}}?>

6. Perform flash break again

You can see the improved effect, after the flash, the current execution fails, but then the new connection can be recreated to continue.

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

The above is a small series to introduce the PHP connection to the MySQL Flash Automatic reconnection method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!