How to build $errno-based error retry logic

M66 2025-05-17

The stability of database operations is crucial when developing PHP applications. Especially for high-concurrency applications, how to deal with database connection failures or query errors and implement an efficient error retry mechanism has become a problem faced by many developers. In this article, we will explore how to use mysqli::$errno to implement efficient error retry logic to improve the stability of database operations.

What is mysqli::$errno ?

In PHP's MySQLi extension, the $errno attribute stores the error code of the last MySQL operation. When we perform database operations, if an error occurs, MySQL will return an error code, and this error code can be obtained through the mysqli::$errno property. Depending on the error code, we can decide whether to retry.

Error retry scenario

Common errors in database operations include:

Temporary network outage
The number of database connections reaches the upper limit
MySQL service is temporarily unavailable, etc.

These errors are usually temporary and we can improve the stability of the application through appropriate retry strategies. If you can quickly determine the error type and retry at the right time, it can greatly reduce the chance of database operation failure caused by occasional errors.

Build error retry logic

Next, we will implement an error retry logic based on mysqli::$errno with an example. We will try to connect to the database and try again if the connection fails.

 <?php
// Set database connection parameters
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'test_db';

// Set the maximum number of retry times
$maxRetries = 3;
$retryInterval = 2; // Retry interval time（Second）

// Create a database connection
function createConnection($host, $username, $password, $database) {
    $connection = new mysqli($host, $username, $password, $database);
    
    // Check if the connection is successful
    if ($connection->connect_error) {
        echo "Connection failed: " . $connection->connect_error . "\n";
        return false;
    }
    return $connection;
}

// Try connecting and try again
function tryConnection($host, $username, $password, $database, $maxRetries, $retryInterval) {
    $retries = 0;
    $connection = null;
    
    while ($retries < $maxRetries) {
        $connection = createConnection($host, $username, $password, $database);
        if ($connection) {
            // Connection successfully，Return to the connection object
            return $connection;
        }
        
        // Get the error code
        $errno = $connection->connect_errno;
        
        // Determine whether it is a temporary error based on the error code
        if ($errno == 2002 || $errno == 1040) {
            // For example：2002Indicates that the database server cannot be connected，1040Indicates that there are too many database connections
            echo "数据库Connection failed，Trying again...（Error code：$errno）\n";
            $retries++;
            sleep($retryInterval); // Wait for a while before trying again
        } else {
            // If the error is another type，Terminate retry
            echo "Connection failed，Error code：$errno\n";
            break;
        }
    }
    
    if ($retries == $maxRetries) {
        echo "Maximum number of retryes reached，Unable to connect to the database。\n";
        return null;
    }
    
    return $connection;
}

// Call the retry function
$connection = tryConnection($host, $username, $password, $database, $maxRetries, $retryInterval);
if ($connection) {
    echo "数据库Connection successfully！\n";
    // Perform other database operations...
} else {
    echo "Unable to connect to the database，Please try again later。\n";
}
?>

Explain the code

Connection function createConnection() : This function is used to create a MySQL database connection and return the connection object. If the connection fails, false is returned.
Retry function tryConnection() : In this function, we implement the retry mechanism. It will try to connect to the database, and if the connection fails, it will decide whether to retry based on the error code. We use connect_errno to get the error code and check whether it is a temporary connection error (such as network problems, too many database connections, etc.). If so, we will wait for a while before trying again until the maximum number of retries is reached.
Judgment of error code :
- Error code 2002 indicates that the database server cannot be connected.
- Error code 1040 indicates that there are too many database connections.

Further optimization

We can make judgments through more error codes, so as to make smarter decisions about different errors. For example:

Error code 2003 : Indicates that the connection to the MySQL server is not accessible or the server is not started.
Error code 1045 : indicates that the user name or password is wrong. This is an explicit configuration error and should not be retryed.

For each error code, we can set a different retry interval, or terminate the retry directly.

Summarize

By using mysqli::$errno to determine the error type in the database operation, we can implement an efficient error retry mechanism to avoid database connection failures caused by network interruption or temporary errors. Reasonably setting the maximum number of retry times and retry interval can effectively improve the stability of database operations, especially in high concurrency environments. With these skills mastered, you can better control the interaction between the application and the database and ensure the reliability of the system.

mysqli::$errno