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 < $this->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 function connect($conn, $host, $user, $passwd, $db, $port, $socket, $mysql_flags) {
$ret = parent::connect($conn, $host, $user, $passwd, $db, $port, $socket, $mysql_flags);
if (!$ret)
$ret = $this->connect($conn, "192.168.2.27", $user, $passwd, $db, $port, $socket, $mysql_flags);
return $ret;
}
public function query($conn, $query) {
$ret = parent::query($conn, $query);
if (!$ret) {
$this->connect($conn, "192.168.2.27", "root", "", "test", 3306, NULL, 0);
$ret = $this->query($conn, $query);
}
return $ret;
}
}
Mysqlnd has a fancy feature which allows us to do a re-connect pretty much at any time. Whenever the proxy code recognizes an error, it can open a new connection and try again. In the 17 lines demo, proper error handling is not done (requirement: "certain error on the connection") and the fail over server selection is basic. The opening example gets it right. However, to make this actually work, I had to fix PECL/mysqlnd_uh earlier today. Uh, uh… – you have been warned before.
Meanwhile, you know how to activate and use the proxy script from the previous two postings. Fast forward to test the proxy with a mysqli script.
printf("Connecting to 'blubb'...\n");
$mysqli = new mysqli("blubb", "root", "", "test");
printf("Host info: %s...\n", $mysqli->host_info);
$mysqli->query("DROP TABLE IF EXISTS test");
printf("Killing database connection...\n");
$mysqli->kill($mysqli->thread_id);
$mysqli->query("CREATE TABLE test(id INT)");
printf("Host info: %s...\n", $mysqli->host_info);
printf("Running INSERT...\n");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
printf("Running SELECT...\n");
$res = $mysqli->query("SELECT * FROM test");
var_dump($res->fetch_assoc());
printf("done!\n");
Connecting to 'blubb'...
...connect(host=blubb)
...connect() fail over to '127.0.0.1'
...connect(host=127.0.0.1)
Host info: 127.0.0.1 via TCP/IP...
...query(query=DROP TABLE IF EXISTS test)
Killing database connection...
...query(query=CREATE TABLE test(id INT))
...query() fail over to '192.168.2.27'
...connect(host=192.168.2.27)
...query(query=CREATE TABLE test(id INT))
Host info: 192.168.2.27 via TCP/IP...
Running INSERT...
...query(query=INSERT INTO test(id) VALUES (1), (2), (3))
Running SELECT...
...query(query=SELECT * FROM test)
array(1) {
["id"]=>
string(1) "1"
}
done!
Application based versus proxy/library/extension/plugin/C-based fail over
As wonderful as this little script is, it has one drawback: it forgets about the connection state when doing fail over. Every connection has a state. The replication and load balancing plugin (PECL/mysqlnd_ms) manual describes it in depth. Whenever a connection is manipulated without letting the application know, state information may be lost and cause follow-up errors. Think of MySQL session variables or character set settings bound to a connection. If, all of a sudden, a user connection handle points to a different MySQL connection, those settings are gone.
Tell us how… and we do fail over in the extensions
That said, maybe your connections don’t have much of a state? I do not know. I am a library hacker not an application developer. If you would like to see a fail over feature in any of the PHP MySQL extensions, please use PECL/mysqlnd_uh as a bridge between us to prototype what you need and ask us to implement it in C. Only you, the PHP user, can tell how the API shall be, which cases have to be considered, what limits are acceptable. If such a wonderful description existed – a description in the language (code) spoken by all hackers – there is a fair chance…
Happy hacking!
PS: You can "abuse" PECL/mysqlnd_ms to do nothing but fail over… more on that another day.