Writing Custom Plugins for PHP’s MySQLnd

While I have previously written custom hooks for specific MySQLnd plugins, like custom routing for mysqlnd_ms, and a custom cache handler for mysqlnd_qc, this time we will be looking at mysqlnd_uh (or user handler) which allows us to write an entire plugin ourselves.

Mysqlnd_uh is the least stable of the mysqlnd plugins, only being available as an alpha. You will most likely need to compile from source.

Instalation and Setup

First, grab the source code and install it:

svn co http://svn.php.net/repository/pecl/mysqlnd_uh/trunk mysqlnd_uh
cd mysqlnd_uh
phpize
./configure --enable-mysqlnd-uh
make
sudo make install

Then add the following to your php.ini (or similar):

extension=mysqlnd_uh.so

Note: you must load the mysqlnd_uh extension after mysqlnd.

MySQLnd_uh Proxies

A mysqlnd_uh plugin can be considered an application-level proxy between your application code and the MySQL server.

There are two types of proxies: connection proxies and prepared statement proxies. To implement these, you must extend MysqlndUhConnection and MysqlndUhPreparedStatement respectively in your code.

It’s important to understand that both of these classes are from the mysqlnd driver (written in C) and are exposed directly to PHP userland. This means that if you fail to call the parent method it may cause PHP to crash, or leak memory.

They also have a number of methods that are not mapped directly to userland functionality (e.g. in the mysqli or pdo/mysql extension APIs) but are instead called by the internal underlying implementations.

Registering a Proxy

The mysqlnd_uh extension exposes two functions to register proxies:

• mysqlnd_uh_set_connection_proxy() for connection proxies (those extending MysqlndUhConnection)
• mysqlnd_uh_set_statement_proxy() for statement proxies (those extending MysqlndUhPreparedStatement)

Each of these accepts an instance of the proxy class. This means that you must be careful about maintaining object state as it is shared globally.

Creating a Proxy

To create a proxy we simply extend the appropriate class. For example if we want to create a proxy that simply records every time a MySQL connection is made we can create a connection proxy by extending MysqlndUhConnection like so:

class ConnectionRecorderProxy extends MysqlndUhConnection {
	public function connect($connection , $host , $user , $password , $database , $port , $socket , $mysql_flags) {
		$time = time();
		file_put_contents("/tmp/connection-log", "Connection to $host on port $port for user $user at " .date('r', $time) . PHP_EOL);
		return parent::connect($connection , $host , $user , $password , $database , $port , $socket , $mysql_flags);
	}
}

Here we override the connect() method, add a line to our log, and return the result of parent::connect().

Note: You may only register one proxy of each type (connection or statement) at any given time. It would be fairly trivial to write a single proxy that would allow you to register other callbacks for each of the methods, however this is beyond the scope of this article.

Tying It All Together

As we saw in earlier articles in this series, many features of the the MySQLnd plugins can be controlled using SQL hints, such as the mysqlnd_ms read/write routing, or query caching. These hints are available as constants that can be injected inside of the SQL comments.

However, injecting those hints (either by printf/sprintf or concatenation) is inelegant and messy. We can instead write simple proxies to automatically transform the queries to replace the string constant names with their values.

To do this we need to add both a connection proxy to catch queries issued with mysqli->query() or pdo->query(), and a statement proxy, to catch prepared queries created with mysqli->prepare() or pdo->prepare().

Here’s what that might look like:

// A utility class for replacing SQL hints
class MySQLndPluginQueryTransformer {
	static public transform($sql)
	{
		// A list of constants to replace
		$constants = [
			'MYSQLND_MS_MASTER_SWITCH',
			'MYSQLND_MS_SLAVE_SWITCH',
			'MYSQLND_MS_LAST_USED_SWITCH',
			'MYSQLND_QC_ENABLE_SWITCH',
			'MYSQLND_QC_DISABLE_SWITCH',
			'MYSQLND_QC_TTL_SWITCH',
		];
		
		// Replace all the hints
		foreach ($constants as $constant) {
			$sql = str_replace($constant, constant($constant), $sql);
		}

		// Handle MYSQLND_QC_TTL_SWITCH= which would double up the =
		$sql = str_replace(MYSQLND_QC_TTL_SWITCH . '=', MYSQLND_QC_TTL_SWITCH, $sql);

		return $sql;
	}
}

// Connection proxy class
class MySQLndPluginConnectionProxy extends MysqlndUhConnection {
	public function query($connection, $query)
	{
		// Transform the query
		$query = MySQLndPluginQueryTransformer::transform($query);
		
		// Call & return the parent function with the transformed query
		return parent::query($connection, $query);
	}
}

// Statement proxy class
class MySQLndPluginStatementProxy extends MysqlndUhPreparedStatement {
	public function prepare($connection, $query)
	{
		// Transform the query
		$query = MySQLndPluginQueryTransformer::transform($query);

		// Call & return the parent function with the transformed query
		return parent::prepare($connection, $query);
	}
}

// Set the proxies
mysqlnd_uh_set_connection_proxy(new MySQLndPluginConnectionProxy());
mysqlnd_uh_set_statement_proxy(new MySQLndPluginStatementProxy());

This works by first creating a single simple class, MySQLndPluginQueryTransformer, to perform the replacements. We first iterate over each of the constants as strings, and replace the string with the value of the constant using the constant() function.

Additionally, because the MYSQLND_QC_TTL_SWITCH constant contains the equals sign, it should be left out of the hint, like so:

/*MYSQLND_QC_TTL_SWITCH10*/ SELECT * FROM user;

For readability, we want to also allow the following:

/*MYSQLND_QC_TTL_SWITCH=10*/ SELECT * FROM user;

To do this we must check and ensure we remove the extraneous = sign.

We then create our proxy classes, and override just the MysqlndUhConnection->query() and MysqlndUhPreparedStatement->prepare() methods. We then pass the SQL query through our MySQLndPluginQueryTransformer::transform() method and call the parent with our new SQL.

Finally we register both our proxies.

Now any queries will be transparently transformed with no changes to our existing code.

Now if you issue a query using one of the constants literally like so:

/*MYSQLND_MS_MASTER_SWITCH*/ SELECT * FROM user;

Whether it’s a prepared statement or not, the literal MYSQLND_MS_MASTER_SWITCH will be replaced automatically so it automatically becomes:

/*ms=master*/ SELECT * FROM user;

Conclusion

Mysqlnd_uh, while very powerful, is also very simple: it only exposes one thing, and does nothing unless you tell it to.

Given that you can hook into almost any part of the connection and query process, this extension has unlimited potential—from monitoring, statistics, backwards compatibility layers, testing… you name it! It’s all potentially possible.

However, given this extension’s alpha state, it is not recommended for production use at the moment. That doesn’t mean it’s useless, however. Things like being able to detect and report bad queries during dev (e.g. those issued against non-existant or renamed tables) can be very handy.

Your next step is to read the PHP manual for the MysqlndUhConnection and MysqlndUhPreparedStatement classes. This will give you an idea of what you can hook in to for your own purposes.

P.S. Have you used mysqlnd_uh before? What ideas do you have for using it? We’d love to hear your ideas. Throw us a comment below.