PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Uh, uha PHP MySQL client fail over

Note: This article was originally published at Planet PHP on 21 April 4020.
Planet PHP

It is the third day I try to find mysqlnd plugin use cases for the Succeed with Plugins webinar on October, 26th. Not being innovative or creative today, I looked into a classic: client fail over. As a trained and talented reader, you won't be shocked to see 54 lines of PECL/mysqlnd_uh hacking today.

class __mysqlnd_conn_failover extends MysqlndUhConnection { private $fail_over_errno = array(2002 = "Can't connect to local MySQL server through socket '%s' (%d)", 2003 = "Can't connect to MySQL server on '%s' (%d)", 2004 = "Unknown MySQL server host '%s' (%d)", 2006 = "MySQL server has gone away", 2013 = "Lost connection to MySQL server during query"); private $fail_over_servers = array(array("host" = "192.168.2.27"), array("host" = "i_dont_exist"), array("host" = "127.0.0.1"),); private $max_retry = 2; public function connect($conn, $host, $user, $passwd, $db, $port, $socket, $mysql_flags, $retry = 0) { printf("\t...connect(host=%s)\n", $host); $ret = parent::connect($conn, $host, $user, $passwd, $db, $port, $socket, $mysql_flags); if (($errno = $this-getErrorNumber($conn)) && (isset($this-fail_over_errno[$errno])) && ($retry max_retry)) { $retry++; $idx = mt_rand(1, count($this-fail_over_servers)) - 1; printf("\t...connect() fail over to '%s'\n", $this-fail_over_servers[$idx]["host"]); $ret = $this-connect($conn, $this-fail_over_servers[$idx]["host"], $user, $passwd, $db, $port, $socket, $mysql_flags, $retry); } return $ret; } public function query($conn, $query, $retry = 0) { printf("\t...query(query=%s)\n", $query); $ret = parent::query($conn, $query); if (($errno = $this-getErrorNumber($conn)) && (isset($this-fail_over_errno[$errno]))) { $idx = mt_rand(1, count($this-fail_over_servers)) - 1; printf("\t...query() fail over to '%s'\n", $this-fail_over_servers[$idx]["host"]); $this-connect($conn, $this-fail_over_servers[$idx]["host"], "root", "", "test", 3306, NULL, 0); $ret = $this-query($conn, $query, $retry); } return $ret; } }

As before, selected mysqlnd library functions are replaced with PHP functions using PECL/mysqlnd_uh . All three PHP MySQL extensions (mysql, mysqli, PDO_MySQL) can be compiled to use the mysqlnd library. Whenever a PHP user invokes any of API calls provided by the extension, the extensions in turn call zero, one or more calls in the mysqlnd library, depending on the extensions API call in question. By manipulating the behaviour of the mysqlnd library functions, one can make the API calls behave different. No C skills required. Everything is done with PHP. Remember the warning from my first post: this is hackish. But do you care if it does the trick and you cannot change or do not want to the application?

If, for example, your application does not do proper error handling and if it does not implement fail over at the application-level, why not hack it into the library? No application change required. There are good reasons not to do fail over at the library and/or C extension level, but let's have some hacking fun first, let's give the elePHPant a swiss army knife!

Step by step

The task: implicit and transparent client fail over. Whenever a mysqli_connect() or a mysqli_query() user API call causes a certain error on the connection, the proxy code shall open a new connection to a random fail over server. A certain number of fail over attempts shall not be exceeded.

The first sentence, the core requirement, takes 17 lines of code. The rest from above is sugar. The mysqli_connect() and mysqli_query() functions map more or less directly to corresponding mysqlnd C library functions. The C library functions are made accessible by PECL/mysqlnd_uh through the built-in class MysqlndUhConnection. By subclassing the connect() and query() methods, we can check for errors and do an implicit connect to a fail over server, if needed.

class __mysqlnd_conn_failover extends MysqlndUhConnection { public functi

Truncated by Planet PHP, read more at the original (another 4458 bytes)