<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title>Error handling</title> </head> <body><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.transaction.html">Transaction handling</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.transient_errors.html">Transient errors</a></div> <div class="up"><a href="mysqlnd-ms.concepts.html">Concepts</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div><hr /><div id="mysqlnd-ms.errorhandling" class="section"> <h2 class="title">Error handling</h2> <p class="para"> 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. </p> <p class="para"> 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. </p> <p class="para"> <div class="example" id="example-1792"> <p><strong>Example #1 Provoking a connection error</strong></p> <div class="example-contents"> <div class="inicode"><pre class="inicode">{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "invalid_host_name", } }, "lazy_connections": 1 } }</pre> </div> </div> </div> </p> <p class="para"> The explicit activation of lazy connections is for demonstration purpose only. </p> <p class="para"> <div class="example" id="example-1793"> <p><strong>Example #2 Connection error on query execution</strong></p> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">())<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SET @myrole='master'"</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">/* Connection 2, run on slave because SELECT, provoke connection error */<br /></span><span style="color: #007700">if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT @myrole AS _role"</span><span style="color: #007700">))) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />} else {<br /> </span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"@myrole = '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_role'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above example will output something similar to:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> 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 </pre></div> </div> </div> </p> <p class="para"> Applications are expected to handle possible connection errors by implementing proper error handling. </p> <p class="para"> Depending on the use case, applications may want to handle connection errors differently from other errors. Typical connection errors are <em>2002 (CR_CONNECTION_ERROR) - Can't connect to local MySQL server through socket '%s' (%d)</em>, <em>2003 (CR_CONN_HOST_ERROR) - Can't connect to MySQL server on '%s' (%d)</em> and <em>2005 (CR_UNKNOWN_HOST) - Unknown MySQL server host '%s' (%d)</em>. 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. </p> <p class="para"> <div class="example" id="example-1794"> <p><strong>Example #3 Provoking a connection error</strong></p> <div class="example-contents"> <div class="inicode"><pre class="inicode">{ "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": [ ] } } }</pre> </div> </div> </div> </p> <p class="para"> Explicitly activating lazy connections is done for demonstration purposes, as is round robin load balancing as opposed to the default <em>random once</em> type. </p> <p class="para"> <div class="example" id="example-1795"> <p><strong>Example #4 Most basic failover</strong></p> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">())<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SET @myrole='master'"</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">/* Connection 2, first slave */<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT VERSION() AS _version"</span><span style="color: #007700">);<br /></span><span style="color: #FF8000">/* Hackish manual fail over */<br /></span><span style="color: #007700">if (</span><span style="color: #0000BB">2002 </span><span style="color: #007700">== </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">|| </span><span style="color: #0000BB">2003 </span><span style="color: #007700">== </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">|| </span><span style="color: #0000BB">2004 </span><span style="color: #007700">== </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">) {<br /> </span><span style="color: #FF8000">/* Connection 3, first slave connection failed, trying next slave */<br /> </span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT VERSION() AS _version"</span><span style="color: #007700">);<br />}<br /><br />if (!</span><span style="color: #0000BB">$res</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"ERROR, [%d] '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />} else {<br /> </span><span style="color: #FF8000">/* Error messages are taken from connection 3, thus no error */<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"SUCCESS, [%d] '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"version = %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_version'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above example will output something similar to:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> [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 </pre></div> </div> </div> </p> <p class="para"> 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 <span class="function"><a href="mysqli.select-db.html" class="function">mysqli_select_db()</a></span>, 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. </p> <p class="para"> Such cases can be debugged by either checking for errors of the type <em>E_WARNING</em> (see above) or, if no other option, investigation of the <a href="mysqlnd-ms.debugging.html" class="link">mysqlnd_ms debug and trace log</a>. </p> </div><hr /><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.transaction.html">Transaction handling</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.transient_errors.html">Transient errors</a></div> <div class="up"><a href="mysqlnd-ms.concepts.html">Concepts</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div></body></html>