doc:todo:mysqlnd_vs_libmysql

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 will not read my.cnf server configuration files

mysqlnd only features:

  • asynchronous queries
  • 100+ statistics
  • security: mysqlnd can inspect LOAD LOCAL INFILE and check open_basedir setting, libmysql won't care about open_basedir. libmysql allows accessing files which other PHP functions cannot access, if open_basedir is set

in general

  • On debugging: memory usage of the MySQL Client Library and libmysql cannot be compared using memory_get_usage(). memory_get_usage() does not take memory into account allocated by the MySQL Client Library - see “Internet Super Hero” blog post.

API differences

general

ini settings

Only available with mysqlnd:

  • mysqlnd.debug
    • reason: mysqlnd only feature
    • test: mysqli_debug_ini.phpt
    • note - records everything from all extensions using mysqlnd
    • note (general, not mysqlnd specific): flag “O” vs. “o” - flush after every [disk] write yes/no -> performance
    • note - only available with a debug build of PHP
  • mysqlnd.collect_statistics
    • type: boolean
    • default: “1”
    • changeable: PHP_INI_SYSTEM
    • changelog: Available since 5.3.0

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.

  • mysqlnd.collect_memory_statistics
    • internal type: boolean
    • default: “0”
    • changeable: PHP_INI_SYSTEM
    • changelog: Available since 5.3.0

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.

  • mysqlnd.net_cmd_buffer_size
    • internal type: long
    • default: 5.3.0 - “2048”, 5.3.1 - “4096”
    • changelog: Available since 5.3.0

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)

  • mysqlnd.net_read_buffer_size
    • internal type: long
    • default: “32768”
    • changelog: Available since 5.3.0

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”.

  • mysqlnd.log_mask
    • internal type: long
    • default: “0”
    • changelog: Available since 5.3.0

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:

  • MYSQLI_OPT_INT_AND_FLOAT_NATIVE
  • MYSQLI_REFRESH_BACKUP_LOG
  • MYSQLI_OPT_NET_CMD_BUFFER_SIZE
    • see also mysqli_options()
    • see also php.ini settings
  • MYSQLI_OPT_NET_READ_BUFFER_SIZE
    • see also mysqli_options()
    • see also php.ini settings
  • MYSQLI_ASYNC
  • MYSQLI_OPT_NUMERIC_AND_DATETIME_AS_UNICODE
    • PHP 6.0+

Not available with mysqlnd:

  • MYSQLI_RPL_MASTER
    • reason: deprecated, may not come with every MySQL Client Library
  • MYSQLI_RPL_SLAVE
    • reason: deprecated, may not come with every MySQL Client Library
  • MYSQLI_RPL_ADMIN
    • reason: deprecated, may not come with every MySQL Client Library
Functions

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

Only available with mysqlnd:

  • mysqli->get_connection_stats()
    • proto: array mysqli_get_connection_stats(void)
    • reason: the MySQL Client Library does not collect any statistics, mysqlnd does collect many
    • test: mysqli_class_mysqli_interface.phpt, mysqli_get_connection_stats.phpt, mysqli_connection_stats_off.phpt
  • mysqli_poll() -
    • proto: int mysqli_poll(array read, array write, array error, long sec [, long usec])
    • reason: no asynchronous queries supported by the MySQL Client Library
    • test: mysqli_class_mysqli_interface.phpt, mysqli_poll.phpt, mysqli_poll_kill.phpt, mysqli_poll_reference.phpt, mysqli_poll_mixing_select_insert.phpt
  • mysqli->reap_async_query()
    • proto: int mysqli_reap_async_query(object link)
    • reason: no asynchronous queries supported by the MySQL Client Library
    • test: mysqli_class_mysqli_interface.phpt, mysqli_class_mysqli_reflection.phpt, *poll*.phpt
  • mysqli_result->fetch_all()
    • proto: mixed mysqli_fetch_all (object result [,int resulttype])
    • reason: no MySQL C API call, though could be emulated, if we want... Opinions? Andrey? Johannes?
    • test: mysqli_class_mysqli_result_interface.phpt, mysqli_class_mysqli_result_reflection.phpt, mysqli_fetch_all.phpt, mysqli_fetch_all_oo.phpt
  • mysqli_stmt->get_result()
    • proto: object mysqli_stmt_get_result(object link)
    • reason: no MySQL C API call, may be possible to emulate, if we want... Opinions? Andrey? Johannes?
    • mysqli_class_mysql_statement_interface.phpt, mysqli_stmt_get_result*.phpt (some 10)
  • mysqli_stmt->more_results()
  • proto: bool mysqli_stmt_next_result(object link)
  • reason: no corresponding MySQL C API call yet, may come, laaaater. mysqlnd has a more powerful implementation of the PS protocol.
  • test: mysqli_class_mysql_statement_interface.phpt - heck, no further test?!
  • mysqli_stmt->next_result()
    • proto: bool mysqli_stmt_next_result(object link)
    • reason: no corresponding MySQL C API call yet, may come, laaaater. mysqlnd has a more powerful implementation of the PS protocol.
    • test: mysqli_class_mysql_statement_interface.phpt - heck, no further test?!

Different feature set with mysqlnd:

  • mysqli->connect(), mysqli->real_connect()
    • proto: no change, host parameter checked if it starts with “p:”
    • reason: supports persistent connections.
    • test: mysqli_pconn_kill.phpt, mysqli_pconn_limits.phpt, mysqli_pconn_max_links.phpt, mysqli_pconn_reuse.phpt
    • php.ini -> mysqli.allow_persistent, mysqli.max_persistent
  • mysqli->debug():
    • proto: no change but reacts to different flags
    • reason: mysqlnd only and partly only relevant for mysqlnd
    • test: mysqli_debug.phpt, mysqli_debug_append.phpt, mysqli_debug_control_string.phpt, mysqli_debug_ini.phpt, mysqli_debug_control_string.phpt, mysqli_debug_mysqlnd_only.phpt
    • missing over libmysql: Ddgp
    • exclusive to mysqlnd: m - trace memory function
  • mysqli->options()
    • proto: no change but new flags allowed
    • reason: mysqlnd only flags
    • test: mysqli_options.phpt
    • MYSQLI_OPT_NET_CMD_BUFFER_SIZE, MYSQLI_OPT_NET_READ_BUFFER_SIZE, MYSQLI_OPT_NET_READ_BUFFER_SIZE, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, MYSQLI_OPT_NUMERIC_AND_DATETIME_AS_UNICODE
    • you may be able to fool the function and set options which are not exported to PHP as a constant by passing the integer value of the corresponding MySQL C API option to the function. The MySQL Client Library will accept it, mysqlnd will reject it because mysqlnd does not feature all options. mysqlnd takes away an undocumented (not recommended!) feature but makes the function also more secure.
  • phpinfo()
    • some additional fields... 'client statistics', 'bytes_sent', 'bytes_received', 'packets_sent', 'packets_received', 'protocol_overhead_in', 'protocol_overhead_out', 'result_set_queries','non_result_set_queries', 'no_index_used', 'bad_index_used', 'buffered_sets', 'unbuffered_sets', 'ps_buffered_sets', 'ps_unbuffered_sets','flushed_normal_sets', 'flushed_ps_sets', 'rows_fetched_from_server','rows_fetched_from_client', 'rows_skipped', 'copy_on_write_saved', 'copy_on_write_performed', 'command_buffer_too_small', 'connect_success','connect_failure', 'connection_reused', 'explicit_close', 'implicit_close','disconnect_close', 'in_middle_of_command_close', 'explicit_free_result', 'implicit_free_result', 'explicit_stmt_close', 'implicit_stmt_close','put_hits', 'put_misses', 'get_hits', 'get_misses','size', 'free_items', 'references', 'mysqli.cache_size','mysqli.allow_local_infile','mysqli.allow_persistent', 'mysqli.max_persistent'

Not available with mysqlnd:

  • mysql_driver->embedded_server_start()
    • reason: mysqlnd != MySQL embedded server, also not available when using regular MySQL Client Library
    • test: mysqli_class_mysqli_driver_interface.phpt
  • mysql_driver->embedded_server_end()
    • reason: mysqlnd != MySQL embedded server, also not available when using regular MySQL Client Library
    • test: mysqli_class_mysqli_driver_interface.phpt
  • mysql->ssl_set()
    • reason: no SSL support in mysqlnd
    • test: mysqli_class_mysqli_driver_interface.phpt
  • mysql->set_local_infile_default()
    • reason: not needed, you can workaround using PHP Streams. Also, MySQL Client Library call can be abused by users to crash PHP
    • test: mysqli_class_mysqli_driver_interface.phpt
  • mysql->set_local_infile_handler()
    • reason: not needed, you can workaround using PHP Streams. Also, MySQL Client Library call can be abused by users to crash PHP
    • test: mysqli_class_mysqli_driver_interface.phpt

different behaviour:

  • mysql_stmt->set_attr() - MYSQLI_CURSOR_TYPE_NO_CURSOR, MYSQLI_CURSOR_TYPE_READ_ONLY, MYSQLI_CURSOR_TYPE_FOR_UPDATE, MYSQLI_CURSOR_TYPE_SCROLLABLE
    • reason: some of those constants are not documented by MySQL, they worked only in temporary MySQL 5.0 versions which never became GA
    • test: ext/mysqli/tests/mysqli_stmt_attr_set.phpt
    • mysqlnd only accepts MYSQLI_CURSOR_TYPE_NO_CURSOR and MYSQLI_CURSOR_TYPE_READ_ONLY
    • libmysql may accept more/other ones, return value may even change among libmysql versions - I don't know - undocumented, undefined
  • mysql_stmt->set_attr() - detection of invalid attributes
  • reason: feature request to libmysql
  • test: ext/mysqli/tests/mysqli_stmt_attr_set.phpt
  • mysqlnd detects invalid attributes and returns false when trying to set, libmysql won't

Statistics

  • mysqli_get_client_stats() and mysqli_get_connection_stats() return the the same data fields. Even their values can be identical, if there is only one connection and it is the first connection that gets opened.
  • affected_rows patch from Andrey
  • not working before PHP 5.3.1: no_index_used, bad_index_used, slow_queries

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:

  • GREET packet n bytes shorter than expected
  • OK packet n bytes shorter than expected
  • EOF packet n bytes shorter than expected
  • PREPARE packet n bytes shorter than expected
  • CHANGE_USER packet n bytes shorter than expected
  • Result set field packet n bytes shorter than expected
  • Wrong COM_STMT_PREPARE response size. Received n
  • Empty row packet body
  • Protocol error. Server sent NULL_LENGTH
  • Packets out of order. Expected n received n. [..]
  • Error while reading greeting packet. PID=n
  • Error while sending <name> packet. PID=n

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.

doc/todo/mysqlnd_vs_libmysql.txt · Last modified: 2017/09/22 13:28 by 127.0.0.1

Page Tools



Table of Contents