PHP connection to MySql through Transient disconnection and automatic reconnection, phpmysql

PHP connection to MySql through Transient disconnection and automatic reconnection, phpmysql

Use php as the background running program (for example, text message sending) and execute php in cli mode. php needs to connect to mysql to execute database processing cyclically.

When the mysql connection is transient disconnected, loop execution will fail.

We need to design a method. when mysql is disconnected, it can be automatically reconnected so that subsequent programs can be executed 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. php files running in the background

Db. php

<? Php // database operation class DB {// save database connection private static $ _ instance = null; // connect database public static function get_conn ($ config) {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; dbname = $ 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 parameter} 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;} // run the public static function query ($ dbconn, $ sqlstr, $ Condparam) {$ things = $ dbconn-> prepare ($ sqlstr); try {$ things-> execute ($ condparam);} catch (PDOException $ e) {echo $ e-> getMessage (). PHP_EOL;} $ result = $ th-> fetchAll (PDO: FETCH_ASSOC); return $ result ;}}?>

Test. php

<? Phprequire 'db. php '; // database settings $ config = array ('host' => 'localhost', 'dbname' => 'user', 'user' => 'root ', 'Password' => '', 'pconnect '=> 0, 'charset' =>''); // execute while (true) cyclically) {// create a data connection $ dbconn = DB: get_conn ($ config); // run the query $ sqlstr = 'select * from user where id =? '; $ Condparam = array (mt_rand (1, 3); $ data = DB: query ($ dbconn, $ sqlstr, $ condparam); print_r ($ data ); // 10 seconds delayed echo 'sleep 10 '. PHP_EOL.PHP_EOL; sleep (10) ;}?>

4. Perform steps

Run test. php in php cli mode, and immediately execute mysql. server stop and mysql. server start to simulate transient disconnection.

mysql.server stopShutting down MySQL.. SUCCESS! mysql.server startStarting MySQLSUCCESS!

We can see that the database cannot be reconnected after transient disconnection, and subsequent programs cannot be executed.

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

5. added the reconnection mechanism.

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

After transient disconnection, because the value of self: $ _ instance exists, calling get_conn does not reconnect, but uses the saved connection for processing.

In fact, when a connection exists, you do not need to create a new mysql connection to reduce the number of mysql connections.

Therefore, you need to clear the self :: _ instance value after transient disconnection so that you can re-obtain the connection next time without using the created but invalid database connection.

The improvement method is as follows:

Added the reset_connect method, which is called when an error occurs. If it is determined that the error is MySQL server has gone away, the existing database connection will be cleared, and mysql will be reconnected next time.

The modified PHP file is as follows:

Db. php

<? Php // database operation class DB {// save database connection private static $ _ instance = null; // connect database public static function get_conn ($ config) {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; dbname = $ 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 parameter} 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;} // run the public static function query ($ dbconn, $ sqlstr, $ Condparam) {$ things = $ dbconn-> prepare ($ sqlstr); try {$ things-> execute ($ condparam);} catch (PDOException $ e) {echo $ e-> getMessage (). PHP_EOL; self: reset_connect ($ e-> getMessage (); // call reset connection when an error occurs} $ result = $……-> fetchAll (PDO: FETCH_ASSOC ); return $ result;} // reset the connection public static function reset_connect ($ err_msg) {if (strpos ($ err_msg, 'mysql server has gone away ')! = False) {self ::$ _ instance = null ;}}?>

6. Execute transient disconnection again

You can see the improved results. after transient disconnection, the current execution will fail, but you can re-create a new connection to continue the execution.

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

The above section describes how to use PHP to connect to MySql through Transient disconnection and automatic reconnection. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!