8.7.5.4 Error handling

Copyright 1997-2018 the PHP Documentation Group.

Applications using PECL/mysqlnd_ms should implement proper error handling for all user API calls. And because the plugin changes the semantics of a connection handle, API calls may return unexpected errors. If using the plugin on a connection handle that no longer represents an individual network connection, but a connection pool, an error code and error message will be set on the connection handle whenever an error occurs on any of the network connections behind.

If using lazy connections, which is the default, connections are not opened until they are needed for query execution. Therefore, an API call for a statement execution may return a connection error. In the example below, an error is provoked when trying to run a statement on a slave. Opening a slave connection fails because the plugin configuration file lists an invalid host name for the slave.

Example 8.248 Provoking a connection error

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "invalid_host_name",
            }
        },
        "lazy_connections": 1
    }
}

The explicit activation of lazy connections is for demonstration purpose only.

Example 8.249 Connection error on query execution

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
if (!$mysqli->query("SET @myrole='master'")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* Connection 2, run on slave because SELECT, provoke connection error */
if (!($res = $mysqli->query("SELECT @myrole AS _role"))) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc();
 $res->close();
 printf("@myrole = '%s'\n", $row['_role']);
}
$mysqli->close();
?>

The above example will output something similar to:

PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %d
PHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306) in %s on line %d
[2002] php_network_getaddresses: getaddrinfo failed: Name or service not known

Applications are expected to handle possible connection errors by implementing proper error handling.

Depending on the use case, applications may want to handle connection errors differently from other errors. Typical connection errors are 2002 (CR_CONNECTION_ERROR) - Can't connect to local MySQL server through socket '%s' (%d), 2003 (CR_CONN_HOST_ERROR) - Can't connect to MySQL server on '%s' (%d) and 2005 (CR_UNKNOWN_HOST) - Unknown MySQL server host '%s' (%d). For example, the application may test for the error codes and manually perform a fail over. The plugins philosophy is not to offer automatic fail over, beyond master fail over, because fail over is not a transparent operation.

Example 8.250 Provoking a connection error

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "invalid_host_name"
            },
            "slave_1": {
                "host": "192.168.78.136"
            }
        },
        "lazy_connections": 1,
        "filters": {
            "roundrobin": [
            ]
        }
    }
}

Explicitly activating lazy connections is done for demonstration purposes, as is round robin load balancing as opposed to the default random once type.

Example 8.251 Most basic failover

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
if (!$mysqli->query("SET @myrole='master'")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* Connection 2, first slave */
$res = $mysqli->query("SELECT VERSION() AS _version");
/* Hackish manual fail over */
if (2002 == $mysqli->errno || 2003 == $mysqli->errno || 2004 == $mysqli->errno) {
  /* Connection 3, first slave connection failed, trying next slave */
  $res = $mysqli->query("SELECT VERSION() AS _version");
}
if (!$res) {
  printf("ERROR, [%d] '%s'\n", $mysqli->errno, $mysqli->error);
} else {
 /* Error messages are taken from connection 3, thus no error */
 printf("SUCCESS, [%d] '%s'\n", $mysqli->errno, $mysqli->error);
 $row = $res->fetch_assoc();
 $res->close();
 printf("version = %s\n", $row['_version']);
}
$mysqli->close();
?>

The above example will output something similar to:

[1045] Access denied for user 'username'@'localhost' (using password: YES)
PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %d
PHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306) in %s on line %d
SUCCESS, [0] ''
version = 5.6.2-m5-log

In some cases, it may not be easily possible to retrieve all errors that occur on all network connections through a connection handle. For example, let's assume a connection handle represents a pool of three open connections. One connection to a master and two connections to the slaves. The application changes the current database using the user API call mysqli_select_db, which then calls the mysqlnd library function to change the schemata. mysqlnd_ms monitors the function, and tries to change the current database on all connections to harmonize their state. Now, assume the master succeeds in changing the database, and both slaves fail. Upon the initial error from the first slave, the plugin will set an appropriate error on the connection handle. The same is done when the second slave fails to change the database. The error message from the first slave is lost.

Such cases can be debugged by either checking for errors of the type E_WARNING (see above) or, if no other option, investigation of the mysqlnd_ms debug and trace log.