Error handling
Error handling
Error handling
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 #1 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 #2 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 #3 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 #4 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.