Table of Contents

Raw bin collection of differences between mysqlnd and the MySQL Client Library (AKA libmysql)

This is a “raw bin” collection of differences between the MySQL native driver for PHP (mysqlnd) and the MySQL Client Library (AKA libmysql). If you want to do MySQL marketing a favour don't use the term libmysql in the documentation.

A new sibling of the MySQL Client Library is the MySQL Connector/C (alternative MySQL naming: MySQL driver for C). The MySQL Connector/C is kind of a standalone version of the MySQL Client Library. Remember that the MySQL Client Library comes with the MySQL Server, unless you use your OS distribution packages... The MySQL driver for C is a spin-off from the MySQL Client Library that was shipped with MySQL 6.0.

That means user can choose between three different libraries for PHP: mysqlnd, MySQL Client Library and Connector/C. However, there are no major differences between Connector/C and the MySQL Client Library. For the sake of PHP, Connector/C = standalone version of MySQL Client Library.

mysqlnd is part of the PHP source code as of PHP 5.3. Therefore users do not need to install any MySQL libraries on their PHP build host.

Windows builds downloaded from php.net use mysqlnd as their default MySQL client library as of PHP 5.3. With PHP 5.4 the default library used with mysql, mysqli and PDO_MySQL is mysqlnd on all platforms. Using the MySQL Client Library (AKA libmysql) is still supported. There are no plans to remove libmysql support.

Major features added after PHP 5.3.0

Feature and extension dependencies

Mysqlnd is tightly integrated into PHP. It is using PHP infrastructure, for example, PHP Streams. Some mysqlnd features depend on other PHP extensions:

Please make sure that you are using a PHP build with Zlib and OpenSSL support when switching from libmysql to mysqlnd. This will ensure that mysqlnd offers the same functionality as libmysql does.

Major feature differences

mysqlnd does not support:

mysqlnd only features:

in general

API differences

general

ini settings

Only available with mysqlnd:

Enable the collection of various client statistics which can be accessed through mysqli_get_client_stats(), mysqli_get_connection_stats(), mysqli_get_cache_stats() and are shown in mysqlnd section of the output of the phpinfo() function as well. NOTE: Statistics are aggregated among all extensions that use mysqlnd. For example, when compiling both ext/mysql and ext/mysqli against mysqlnd, both function calls of ext/mysql and ext/mysqli will change the statistics. There is no way to find out to find out how much a certain API call from any extension that uses mysqlnd has impacted a certain statistic. You can configure the MySQL driver for PDO, ext/mysql and ext/mysqli to optionally use mysqlnd. When doing so, all three extensions will change the statistics. Note also the difference between global and connection statistics explained in the blog postings.

Enable the collection of various memory statistics which can be accessed through mysqli_get_client_stats(), mysqli_get_connection_stats(), mysqli_get_cache_stats() and are shown in mysqlnd section of the output of the phpinfo() function as well.

From http://blog.ulf-wendel.de/?p=268 -> command_buffer_too_small

mysqlnd allocates an internal command/network buffer of mysqlnd.net_cmd_buffer_size (php.ini) bytes for every connection. If a MySQL Client Server protocol command, for example, COM_QUERY (“normal” query), does not fit into the buffer, mysqlnd will grow the buffer to what is needed for sending the command. Whenever the buffer gets extended for one connection command_buffer_too_small will be incremented by one.

If mysqlnd has to grow the buffer beyond its initial size of mysqlnd.net_cmd_buffer_size (php.ini) bytes for almost every connection, you should consider to increase the default size to avoid re-allocations.

The default buffer size is 2048 bytes in PHP 5.3.0. In future versions the default will be 4kB or larger. The default can changed either through the php.ini setting mysqlnd.net_cmd_buffer_size or using mysqli_options(MYSQLI_OPT_NET_CMD_BUFFER_SIZE, int size).

It is recommended to set the buffer size to no less than 4096 bytes because mysqlnd also uses it when reading certain communication packet from MySQL. In PHP 5.3.0, mysqlnd will not grow the buffer if MySQL sends a packet that is larger than the current size of the buffer. As a consequence mysqlnd is unable to decode the packet and the client application will get an error. There are only two situations when the packet can be larger than the 2048 bytes default of mysqlnd.net_cmd_buffer_size in PHP 5.3.0: the packet transports a very long error message or the packet holds column meta data from COM_LIST_FIELD (mysql_list_fields() and the meta data comes from a string column with a very long default value (>1900 bytes). No bug report on this exists - it should happen rarely.

As of PHP 5.3.2 mysqlnd does not allow setting buffers smaller than 4096 bytes.

(The default change may/should make it into 5.31 - lets wait and see what the release manager decides)

The value can also be set using mysqli_option(link, MYSQLI_OPT_NET_CMD_BUFFER_SIZE, size)

Maximum read chunk size in bytes when reading the body of a MySQL command packet. The MySQL client server protocol encapsulated all its commands in packets. The packets consist of a small header and a body with the actual payload. The size of the body is encoded in the header. mysqlnd reads the body in chunks of MIN(header.size, mysqlnd.net_read_buffer_size) bytes. If a packet body is larger than mysqlnd.net_read_buffer_size bytes, mysqlnd has to call read() multiple times.

The value can also be set using mysqli_optionS(link, MYSQLI_OPT_NET_READ_BUFFER_SIZE, size)

... we have never done any proper performance testing around this. Larger values force PHP streams to allocate larger buffers and we have to doo less read calls. On the other hand might the transport protocol (TCP or Unix Sockets) in use work even better with smaller buffers... but it should also depend on your SQL queries: do you have queries that generate result sets larger than mysqlnd.net_read_buffer_size ... No idea for a recommendation but “run your own tests and don't be disappointed if it makes no big difference”.

No meaning for a standard PHP binary, see http://blog.ulf-wendel.de/?p=272 . Bug in PHP 5.3.0: the default of 0 has disabled the update of the mysqlnd statistics “no_index_used”, “bad_index_used”, “slow_query”. As of PHP 5.3.1 - if the patch makes it into 5.3.1 - the log mask does no longer impact collecting statistics.

ext/mysqli

Constants

In general mysqlnd exports everything exported by ext/mysqli when using MySQL Client Library 5.0.8 or newer.

Only available with mysqlnd:

Not available with mysqlnd:

Functions

Why do I mention the tests? Because its the best documentation apart from the .c[omment] files ;-)

Only available with mysqlnd:

Different feature set with mysqlnd:

Not available with mysqlnd:

different behaviour:

Statistics

Warnings

In general mysqlnd is more verbose than libmysql when it comes to low-level network errors and protocol parsing problems. We need those warnings as an aid to detect MySQL client server protocol differences between different MySQL versions. Not each and every Mysql client server protocol detail is documented on the MySQL wiki. Although we tested mysqlnd against some 40 different MySQL server versions there may be still “buggy” MySQL servers which we have missed. Examples of warnings that may come whenever the client communicates with MySQL:

I suggest users shall report those warnings on bugs.php.net together with the MySQL server version, the PHP version and - important - a reproducible short (<20 lines) script.