PDO_MYSQLND is in the PHP CVS repository at php.net: PDO_MYSQL has been patched (PHP 5.3, PHP 6.0). Try out PDO_MYSQL with the MySQL native driver for PHP (mysqlnd). Its has new features.

Since Kaj’s announcement of the MySQL native driver for PHP (mysqlnd), we promised to to patch all three PHP MySQL extensions (ext/mysql, ext/mysqli and PDO_MYSQL) optionally support it. Almost exactly a year ago, on July 27th 2007, mysqlnd and updated versions of ext/mysql and ext/mysqli have been checked into the PHP 6 (HEAD) branch of the PHP CVS repository. PDO_MYSQL was the last extension to be upgraded. We have choosen PDO_MYSQLND as a working title for the upgrade PDO_MYSQL. On Monday, July 21st 2008, Johannes has committed the patch into the CVS with a short comment.

 Log:
  - Add mysqlnd support for PDO_mysql, fixes at least bug#41997,#42499,  pecl#12794, pecl#12401

Short recap: what is mysqlnd?

JIT – just in time for the PHP 5.3 feature code freeze on Thursday, July 24th 2008. Lets recap what the MySQL native driver for PHP is before checking going through its advantages in the context of PDO. Under the hood of PHP, on the C-level, all three PHP MySQL extensions make use of MySQL Client Library to connect to MySQL. The MySQL Client Library implements the MySQL Client Server protocol. The MySQL Client Library is a general purpose C-library which is not particulary optimized for PHP.

Since about a year you can optionally compile ext/mysqli and ext/mysql either against the MySQL Client Library, like ever since, or against mysqlnd. Since a few days the same is true for PDO_MYSQL: you can choose between the MySQL Client Library and mysqlnd. PHP 5.3 will use mysqlnd as a default for the PHP MySQL extensions, because:

• mysqlnd is easier to compile: its part of the PHP source tree
• mysqlnd is optimized for PHP due to its tight integration into the PHP internals
• mysqlnd can save memory and uses the same amount of memory at the worst
• mysqlnd can be faster than the MySQL Client Library and is equally fast at the worst
• mysqlnd provides a rich set of performance statistics
• mysqlnd is licensed under the PHP license to avoid any license and/or copyright hassles

Note that mysqlnd will not connect to any MySQL Server older than 4.1. MySQL 4.0 and PHP 4 go hand in hand, they are from the same generation. PHP 4 is in maintenance only mode. Active Support for MySQL 4.0 has ended in 2006. The Extended Support will end at the end of 2008, see the MySQL Lifecycle Policy. New development, such as mysqlnd, reflect this. If you still run MySQL 4.0 or earlier you have to use the MySQL Client Library, like you did in the past. Everybody is requested to try out mysqlnd and move over to it on the long run.

Advantages of using mysqlnd for PDO

PDO_MYSQL was the last extension to be upgraded. PDO_MYSQL has been a community-driven development in the past. MySQL did not actively contribute to is but has been available at any time for technical questions. Most of the coding has been done by George Schlossnagle,Wez Furlong and Ilia Alshanetsky – thanks guys (and all others I forgot to mention)! Prior to the CVS check-in of the PDO_MYSQLND patch Johannes presented the patch to Wez and Ilia. And we stayed to our earlier promise of offering that we would like to take over responsibilities for PDO_MYSQL. The first advantage which mysqlnd has brought to PDO is that Sun/MySQL actively contributes code to PDO_MYSQL.

The list of technical advantages of using PDO_MYSQL with mysqlnd is not as long and exciting as the advantages of using ext/mysqli with mysqlnd. A major reason behind is the overall PDO design. PDO consists of a core module (ext/pdo) and database specific drivers. The PDO core sets the overall structure and performs callback operations. The mapping from the callbacks to the native database API is not always lucky because it has to be generic. Every driver is treated in the same way. PDO might have chosen this design to ensure uniformity among the drivers because there is no PDO compliance test suite to ensure that every driver behaves in the same way. For example, PDO features a SQL parser in the core which has to be used to all Prepared Statement Emulation tasks. As its a generic parser its far from being perfect. Another limitation is on memory management: ext/mysqli and mysqlnd can theoretically save up to 40% memory. Although we have patched PDO to support the new technique in general, PDO is quite resistant against it when binding results. Read below for details. The PDO specific advantages of mysqlnd are:

• mysqlnd can save memory and CPU-cycles using a “read-only” variable trick when using PDO::FETCH_ASSOC
• mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.
• mysqlnd can handle Prepared Statements returning different numbers of result set columns upon each invocation, the mysqlclient library cannot
• Assorted bugs have been closed, check the bug overview posting

This reads like a short list. But don’t miss the above long list of general advantages of mysqlnd which all PHP MySQL extensions, including PDO_MYSQL, profit from.

Memory tricks partially supported by PDO

Typical PHP database extensions make use of the general purpose C library of the database vendor to connect to a database server. The generic library implements the communication protocol of the database server. When reading data, the mysqlclient library (AKA libmysql) does fetch data from the wire into internal network buffers. The buffers are managed by the mysqlclient library.

On the C-level PHP uses a data type called zval to represent variables. Every PHP variable is bound to one zval. The zval itself points to the memory areas holding the PHP variables value. When using the mysqlclient library all database results are copied from the library internal network buffers to extra memory areas managed by the user variables, the zvals. Every (buffered) database result is hold twice in the memory: 1x inside the library internal network buffers and 1x inside your user variable.

Things are different with mysqlnd. The network buffers of mysqlnd consist of zvals. User variables can point directly to those zvals as long as the value of the user variable does not get changed (“read-only variables”). Therefore, mysqlnd keeps database results only once in memory. In the worst case the user modifies a variable returned by mysqlnd and a so-called zval-separation happens: a new zval gets created and the data gets copied. In the worst case you have the data twice in memory, just like with the mysqlclient library.

We have patched PDO to allow drivers to return zvals. If a driver returns a zval, PDO does not create a new zval and does not copy data from a network buffer into a second place to which the zval can point to. You get the “read-only variables” benefit. However, this is only true when you do not use result set binding. Using the following syntax you can save memory and CPU cycles.

$db = new PDO("mysql:dbname=phptest;unix_socket=/tmp/mysql.sock", "user", "pass");
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
$stmt = $db->query("SELECT 1");
var_dump($stmt->fetch(PDO::FETCH_ASSOC));
array(1) {
  [1]=>
  int(1)
}

Result set binding (PDO::bindColumn()) cannot make use of this mysqlnd feature. The problem is that PDO causes a zval-separation due to a type cast to string. The cast is requested by the PDO core. Deactivating it would potentially affect all PDO drivers. And of course, you should not introduce optimizations into a shared core component if there is only one single driver (PDO_MYSQL) that would profit from it.

$db = new PDO("mysql:dbname=phptest;unix_socket=/tmp/mysql.sock", "user", "pass");
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
$stmt = $db->query("SELECT 1");
$stmt->bindColumn(1, $value, PDO::PARAM_INT);
$stmt->fetch(PDO::FETCH_BOUND)
array(1) {
  [1]=>
  int(1)
}

Although the user explicitly asks for an integer value ($stmt->bindColumn(1, $value, PDO::PARAM_INT);) and mysqlnd has a zval to offer that represents an integer value, PDO will always ask the driver for a string. At this point PDO_MYSQL cannot return a pointer to the zval it has to offer. The zval comes from mysqlnd and it is read-only. PDO_MYSQL must not cast it to string because this would change the read-only zval. PDO_MYSQL is forced to create a new zval and copy the database results into it. The advantage of holding database results only once in memory is gone. Shortly after, PDO will cast the string value it has recieved from the driver to an integer value.

The above is the worst case scenario. When fetching only string columns (VARCHAR, CHAR, …) all is fine and mysqlnd is able to delay or totally avoid extra memory copies.

Native data types with Server-side Prepared Statements

Did you notice that SELECT 1 has returned an integer value in the above PDO::FETCH_ASSOC code example? This is the new default with PDO_MYSQL when compiled against mysqlnd, turning off the PDO prepared statement emulation and disabling PDO::ATTR_STRINGIFY_FETCHES. MySQL Prepared Statements use different binary communication protocol but non-prepared statements. The binary protocol avoids unecessary type casts to strings. Type casts consume CPU cycles. With mysqlnd the benefit of the binary protocol is finally available to you not only through ext/mysqli but also through PDO_MYSQL.

However, there is a “but”. The mysqlclient library will always return strings with PDO_MYSQL. As you know from ext/mysqli it is in general capable of returning native types, but the PDO core design cannot make use of this feature. PDO will always cast data to string by default and the PDO_MYSQL driver can only work around this PDO default by returning zvals. This in turn is a mysqlnd specific feature.

Note that sometimes even mysqlnd will return a string for an INTEGER UNSIGNED column if the value of the database column cannot be represented in a PHP number. For example, you will get a string '4294967295' on a 32bit machine for an INTEGER UNSIGNED column holding the value of 4294967295. Check the test ext/pdo_mysql/tests/pdo_mysql_types.phpt for more edge cases.

Nasty Stored Procedures

Although mysqlnd cannot lift Prepared Statements limitations of the MySQL Server the new version of PDO_MYSQL should feel more solid. Not only should calling Stored Procedures work fine with mysqlnd, you can also execute stored procedures that return a different number of result set columns upon each invocation using prepared statements . However, this can only be done with mysqlnd. It will not work when compiling PDO_MYSQL against the mysqlclient library.

CREATE PROCEDURE p() BEGIN 
  DECLARE cols INT; 
  SELECT @numcols INTO cols; 
  IF cols < 2 THEN 
    SET @numcols = 2; 
    SELECT cols AS "one";
  ELSE SET 
    @numcols = 1; 
    SELECT 1 AS "one", cols AS "two"; 
  END IF; 
END;

See ext/pdo_mysql/tests/pdo_mysql_stmt_variable_columncount.phpt for an example how to call such a stored procedure using PDO_MYSQL.

PDO_MYSQLND, the patch to add mysqlnd support to PDO_MYSQL, is no revolution. Its an evolution that comes with PHP 5.3 . Given that, maybe Johannes was right to make it a short commit message? Anyway, go and give PHP 5.3 a try. PHP 5.3 alpha 1 is around the door. It includes an improved version of PDO_MYSQL.