PHP MYSQLND
Contents
|
[edit] MySQL Native Driver for PHP (mysqlnd)
[edit] What is mysqlnd?
mysqlnd is the MySQL Native Driver for PHP. What does this mean? For those who are already very familiar with PHP’s MySQL support, a single sentence should explain it: mysqlnd is a replacement for libmysql, distributed under the terms of the PHP license and tightly integrated into PHP at the C level. This means you no longer need to have MySQL installed on a machine if you merely want to use PHP to access a MySQL server that’s on a different machine. Those of you who would not call yourselves C-level PHP hackers should read on.
- “native”: This doesn’t mean “written in PHP”; that would be too slow. PHP itself is a program written in C. Therefore “native” means this driver is written in C and tightly integrated into PHP at the C level. This allows the driver to achieve maximum performance (just like libmysql).
- “driver”: This is not a new programming API or PHP extension; there are already three APIs (ext/mysql, ext/mysqli, PDO/MySQL) and three extensions, so there is no need for more. Rather, mysqlnd is a library that implements the MySQL low-level communication protocol. This library can be used by the existing extensions. Currently ext/mysql and ext/mysqli have been modified so they can optionally use mysqlnd instead of libmysql, and PDO/MySQL will be modified later.
- “for PHP”: This is nothing new in the sense that libmysql is also for PHP. But mysqlnd is published under the terms of the PHP license whereas libmysql was not. Therefore, unlike with libmysql, there is no need for the FLOSS License Exception to make the license compatible with that of PHP. Those who recall the (partly heated) discussions about the exception might be especially happy to hear about this.
[edit] Terminology: PHP extension, API
Kristian Köhntopp made an observation which is just so spot-on that it deserves to be repeated: PHP is like the Borg: assimilate everything and take the best out of it to strengthen your own position. PHP assimilates C libraries. At its heart, PHP is a small language core which can be extended to do anything by embedding specialized C libraries. Typically, embedding a C library into PHP means writing a new PHP extension and exporting some library functions to PHP userland to create a new API (application programming interface).
Being able to assimilate is one of the key factors in the success and adoption of PHP. It makes PHP very flexible and ready for all kinds of different and future tasks. It also allows advanced users to break out of the (speed) limitations of PHP and go down to the C level if necessary.
Quite often a new extension goes hand in hand with a new API. Why would you want to write a new PHP extension, if it does not export any functions to the PHP userland? Most of the time this is not desired. If your PHP extension is based on a C library, you typically implement some wrapper functions (PHP functions) to be able to use the functionality of the C library from within your PHP scripts.
Browse the function reference in the PHP manual and you will see how many PHP functions are based on a C library. The libxml functions even have the abbreviation “lib” (for “library”) in their name: the libxml functions belong to the libxml extension which exports C library functionality from the libxml library to PHP userland.
[edit] A brief history of PHP’s MySQL APIs
At some point in the history of PHP, a very important point in the history of MySQL as well, someone decided to hack a PHP extension that exposes some libmysql (MySQL Client Library) C-level functions to PHP userland: ext/mysql was born.
With the birth of ext/mysql a new API (application programming interface) was born as well. The mysql_*-functions got introduced to PHP. The API is very “phpish” and was somewhat abstracted from the C-level functions found in libmysql. Until today this first extension supporting MySQL seems to be one of the most popular one, although using ext/mysqli is recommended, because ext/mysqli features all functionality offered by MySQL 4.1+, unlike ext/mysql does.
A few years later, with the introduction of PHP 5, ext/mysqli got developed. It is a second PHP extension using the libmysql. And it is a new API which provides the mysqli_*-functions. The reasons for developing yet another PHP extension - ext/mysqli - using libmysql are still listed in a historical article:
- ext/mysql was difficult to maintain due to many #ifdef in the C code
- ext/mysqli supports all MySQL Server features, unlike ext/mysql
- ext/mysqli has a procedural and OO interface - new OO language features have been one of the hottest topics during the development of PHP 4
However, this is not the end of the story. With PHP 5 a third PHP extension using libmysql has been introduced: PDO/MySQL. PDO is a reaction to the fact that PHP does not have a unified API to connect to databases, unlike, for example, Java has with JDBC. Prior to the introduction of PDO, it has been common practice to use database abstractions written in PHP which tends to result in rather slow code. This itself is not necessarily much of a problem, but like with template engines there has never been kind of a “standard” database abstraction. The existing database abstractions did not only have different APIs but also very different feature sets. Having the choice is great on the one hand. On the other hand it was annoying to deal with so many “standards”. As a database API abstraction on the C-level is faster than one implemented in PHP and there was so much confusion about a lacking standard, PDO got developed. Lessons to learn
Let’s recap. There are three APIs in PHP that you can use to connect to MySQL:
- PHP 3+ - ext/mysql: the oldest extension and API - please don’t use it any more, it does not support all MySQL features.
- PHP 5+ - ext/mysqli: the “current” extension and API - supports all features of MySQL
- PHP 5+ - PDO/MySQL: an extension and database API abstraction layer introduced with PHP 5
[edit] mysqlnd is not a new extension! mysqlnd is it not a new API!
Mysqlnd is neither a new PHP extension nor a new API! mysqlnd is new C-level library code. The mysqlnd library provides almost the same functionality as libmysql does. Both C-libraries implement the MySQL communication protocol and can be used to connect to the MySQL Server.
However, libmysql is a generic C-library with Dual-Licensing. Any C-based program can use it. As PHP is based on C, PHP is using it. mysqlnd is not a generic C-library. mysqlnd is licensed under the PHP license and it is tightly integrated into PHP on the C-level. For example, mysqlnd is using the PHP memory management functions and network streams. Due to the close integration, it is difficult for other C programs but PHP to use the library. Any other C program that tries to use mysqlnd would need to link against large parts of PHP. Maybe this explains what “native” and “for PHP” means.
mysqlnd has been designed as a drop-in replacement for libmysql. PHP extensions that use libmysql to connect to MySQL, can be modified to support both libmysql and mysqlnd. This adoption has been finished for ext/mysql and ext/mysqli. Of course, an extension can only use one C-library at a time: either libmysql or mysqlnd. Which one gets used is decided at compile time, see my other blog posting on how to compile mysqlnd.
[edit] mysqlnd performance
First benchmarks showed that mysqlnd seems to be roughly as fast as libmysql. Sometimes it is a little faster, sometimes libmysql seems to be slightly ahead of mysqlnd. There are a few differences between mysqlnd and libmysql. For example, mysqlnd needs to hold no extra copies of rows when fetching data. Therefore it can be a little bit more efficient using memory. However, you must never forget that the database layer is only a small piece in the whole game, if you look at PHP from a higher view and consider the whole web request which includes accepting the HTTP request, starting PHP, parsing PHP code, running PHP, spending a few milliseconds in the database layer itself, spending time transferring data from the database server via the network to PHP, processing the data in PHP and finally serving a response to the web client. Don’t raise that high expectations.
[edit] Different libraries offer different functionality.
libmysql and mysqlnd offer slightly different features. We list them in short on http://dev.mysql.com/downloads/connector/php-mysqlnd/:
- improved persistent connections
- mysqli_fetch_all()
- performance statistics call: mysqli_get_cache_stats(), mysqli_get_client_stats(), mysqli_get_connection_stats()
Some of those features have a certain impact on performance. For example, if you compile ext/mysqli with mysqlnd, Persistent Connections can be made. This feature is not there if you compile ext/mysqli with libmysql. Persistent Connections can give you a little performance boost. When comparing apples and oranges - ext/mysqli @ libmysql without persistent connections and ext/mysqli @ mysqlnd with persistent connections - We found the Dell DVD Store to run some 5% faster, depending on the configuration a little less or a little more than 5%. So much about performance.
You are asking about security. It is not a task of the database layer to make your applications more secure! You are responsible for filtering data! Applications are state machines. It must not be possible to get from one state to another using the wrong data. It must not be possible to get from “ask user for data” to “store data”, if data contains anything that might cause harm. Sorry, but it is your fault if getting from one of these states to the other is possible using bogus input. If this is possible, your state machine is broken.
What database layers can do is provide you with mechanisms that are robust and prevent faulty input to be executed or stored. By their nature those mechanisms can be only of a very generic type. Database layers do know nothing about your state machine, database layers do not know if they are used in the web or on the CLI, database layers know nothing about your business rules. Therefore any such mechanism does not free you from the task to define clear rules for state transitions and implement those rules, that is to filter input! Any “security” feature of the database layer can only be seen as a last-level backup that tries to prevent the worst. In the first line, your application is responsible for input filtering and validations.
To answer your question: Prepared Statements are available both with ext/mysqli and PDO/MySQL. No matter how you compile them - using mysqlnd or using libmysql. Though, once again, it makes little sense to move the task of input filtering to the database layer. That’s a poor application design. There can be exceptions to this rule, for example when doing rapid prototyping, but in general it is bad style. BTW, ext/mysql does not support Prepared Statements - as said, stop using it, if you still do and go for ext/mysqli.
[edit] Advantages of mysqlnd
- Easier to compile: no more linking against libmysql
- Easier to compile: no need to have libmysql on the PHP build host
- License: PHP license, no need for FLOSS Exception any more
- Native: uses PHP memory management, supports PHP memory limit
- Native: keeps every row only once in memory, with libmysql you have it twice in memory
- New: keeps a long list of performance related statistics for bottle-neck analysis
- New: persistent connections for ext/mysqli
- Performance: can be faster than libmysql in certain cases
- Future performance: we consider a client-side result set cache (pre-alpha design study in Bazaar)
- … and much more
[edit] Installation
mysqlnd is part of the official PHP 5.3 and PHP 6 branches. This makes compiling PHP with mysqlnd support easy.
For building PHP 5.3 or PHP 6 with mysqlnd support on Unix/Linux systems you can decide for all three MySQL extensions (ext/mysql, ext/mysqli, PDO_mysql) whether they should be built using mysqlnd or libmysql. When choosing mysqlnd use "mysqlnd" as path to the mysql client library:
./configure --with-mysql=mysqlnd \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd
If you don't specify "mysqlnd" as library location PHP will try to build using libmysql from the location you provided or, if none is given, the default location like /usr. You can combine the configure line with other configure options from PHP, as you're used to.
When using official Windows builds from php.net mysqlnd is activated to be used by all three mysql extensions - no special configuration is needed there.
Please refer to the PHP documentation for further instruction on building and installing PHP.
[edit] Access the latest source code
mysqlnd and the three extensions are kept in PHP's CVS repository on cvs.php.net. You can fetch tarballs and regular Windows builds from PHP's snapshot site. For experimental features we're keeping a Bazaar code repository. When using the sources from Bazaar please keep in mind that the code is highly experimental and might easily break.
[edit] Known/reported Bugs
If you discovered and reported a bug in the new functionality, please add it to the list below, to avoid duplicate reports by other users.
[edit] Community improvement requests
Here we'll collect community improvement requests, please mind the page about PHP_PDO_MYSQLND, too. For general PHP topics please refer to PHP's bug tracker and wiki.
[edit] Contact
To discuss mysqlnd, please use our PHP Forum. You may also contribute to the MySQL PHP mailing list.