search
HomeBackend DevelopmentPHP TutorialHow to automatically reconnect when mysql connection is interrupted

Use php as a background running program (such as bulk SMS), 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 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 file running in the background

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 != '') {                $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;
        }        $result = $sth->fetchAll(PDO::FETCH_ASSOC);        return $result;
    }
}?>

test.php

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

4. Execute steps

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

mysql.server stopShutting down MySQL
.. SUCCESS! 
mysql.server start
Starting MySQL
 SUCCESS!

It can be seen that the database cannot be reconnected after the flash interruption, and subsequent programs cannot be executed.

Array
(
    [0] => Array
        (
            [id] => 3
            [name] => terry
        )
)
sleep 10SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10SQLSTATE[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 judgment error is 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

<?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 != '') {                $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 flash execution again

You can see the improved effect. After the interruption, the current execution will fail, but subsequent executions can be re-created and a new connection continued.

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...

This article explains the relevant knowledge about the method of automatic reconnection when mysql connection is interrupted. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

php A class for converting HTML entity numbers and non-ASCII strings to and from

php According to the auto-increment id creates a unique number class

About mysql The method of viewing the currently used configuration file my.cnf is explained

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

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain how load balancing affects session management and how to address it.Explain how load balancing affects session management and how to address it.Apr 29, 2025 am 12:42 AM

Load balancing affects session management, but can be resolved with session replication, session stickiness, and centralized session storage. 1. Session Replication Copy session data between servers. 2. Session stickiness directs user requests to the same server. 3. Centralized session storage uses independent servers such as Redis to store session data to ensure data sharing.

Explain the concept of session locking.Explain the concept of session locking.Apr 29, 2025 am 12:39 AM

Sessionlockingisatechniqueusedtoensureauser'ssessionremainsexclusivetooneuseratatime.Itiscrucialforpreventingdatacorruptionandsecuritybreachesinmulti-userapplications.Sessionlockingisimplementedusingserver-sidelockingmechanisms,suchasReentrantLockinJ

Are there any alternatives to PHP sessions?Are there any alternatives to PHP sessions?Apr 29, 2025 am 12:36 AM

Alternatives to PHP sessions include Cookies, Token-based Authentication, Database-based Sessions, and Redis/Memcached. 1.Cookies manage sessions by storing data on the client, which is simple but low in security. 2.Token-based Authentication uses tokens to verify users, which is highly secure but requires additional logic. 3.Database-basedSessions stores data in the database, which has good scalability but may affect performance. 4. Redis/Memcached uses distributed cache to improve performance and scalability, but requires additional matching

Define the term 'session hijacking' in the context of PHP.Define the term 'session hijacking' in the context of PHP.Apr 29, 2025 am 12:33 AM

Sessionhijacking refers to an attacker impersonating a user by obtaining the user's sessionID. Prevention methods include: 1) encrypting communication using HTTPS; 2) verifying the source of the sessionID; 3) using a secure sessionID generation algorithm; 4) regularly updating the sessionID.

What is the full form of PHP?What is the full form of PHP?Apr 28, 2025 pm 04:58 PM

The article discusses PHP, detailing its full form, main uses in web development, comparison with Python and Java, and its ease of learning for beginners.

How does PHP handle form data?How does PHP handle form data?Apr 28, 2025 pm 04:57 PM

PHP handles form data using $\_POST and $\_GET superglobals, with security ensured through validation, sanitization, and secure database interactions.

What is the difference between PHP and ASP.NET?What is the difference between PHP and ASP.NET?Apr 28, 2025 pm 04:56 PM

The article compares PHP and ASP.NET, focusing on their suitability for large-scale web applications, performance differences, and security features. Both are viable for large projects, but PHP is open-source and platform-independent, while ASP.NET,

Is PHP a case-sensitive language?Is PHP a case-sensitive language?Apr 28, 2025 pm 04:55 PM

PHP's case sensitivity varies: functions are insensitive, while variables and classes are sensitive. Best practices include consistent naming and using case-insensitive functions for comparisons.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools