Johannes Schlüter

Previously I was writing about combining Symfony2 and mysqlnd to get more statistics on what is going on below the surface in the database communication when using a Symfony2 application via the Symfony2 profiler. Now that's not all that can be done and I gave some ideas for extending this. One idea was adding mysqlnd_qc support. mysqlnd_qc is the client side query cache plugin for mysqlnd. This provides a client-side cache for query results transparently without changing the application.

A nice thing about this plugin, for this context here, is the function mysqlnd_qc_get_query_trace_log() which provides information about each query being executed. Not only the query string but also some timing (execution time, result storage time) and a stack trace so you can see where in the code a query was executed. I've added this functionality to the JSMysqlndBundle as you can see in the screenshot. I won't show a screenshot about what happens if you click the stacktrace link as this currently breaks the layout a bit, but maybe somebody wants to make this nicer? - Or maybe even feels motivated to make it even better using mysqlnd_uh (which, as of today, has docs, thanks to Ulf) Feel free to contact me to talk about ideas!

In a previous blog posting I was mentioning that I'm working on a small hobby PHP project. As I'm using this project to update myself to current frameworks I've decided to use Symfony2. Symfony provides a nice feature, which is the Symfony Profilier, an extensive logging and reporting system for Symfony2 developers to understand what's going on. A part of it is the Doctrine query logger which lists all database queries executed by Doctrine and their execution time.

This is nice but when we're using mysqlnd in our PHP build we have more information available. "So why not use that information," I thought and built a new bundle for Symfony2 doing exactly that. The JSMysqlndBundle will take all the 150 or so statistic values collected, so they can be seen in the profiler (click screenshot for a larger view).

As this is the initial value, a quick Sunday morning hack, it has not all features I can imagine. Things one could do include

• Provide information on caching decisions and behavior when mysqlnd_qc is used
• Provide replication-related decisions when the new mysqlnd replication and load balancing plugin is used
• Take David's mysqlnd_uh-based query logging ideas and provide more information on any executed query
• ....

Any takers? - Code away!

Today we've released a few things for PHP and MySQL users: One is the first (and probably only) beta of the mysqlnd_ms plugin for MySQL Replication and Load Balancing support and the first GA version of a PHP plugin for the Query Analyzer of the MySQL Enterprise Monitor.

Ulf blogged a lot about mysqlnd_ms, so I don't have to repeat him here. what I want to talk about is the other one. So what is that about?

When running a PHP-based application with MySQL it is often quite interesting to see what actually happens on the database sever. Besides monitoring of the system load etc. it is often interesting to see what queries are actually executed and which of them are expensive. A part of MySQL Enterprise Monitor is the MySQL Query Analyzer which helps answering these questions.

Traditionally the MySQL Query Analyzer  was based on MySQL Proxy which is configured to sit between the application and the MySQL server and collects all relevant data.

Now in the new 2.3.7 release of the MySQL Enterprise Monitor we have enhanced this for PHP users: We now provide a plugin which can be loaded in PHP and which will provide data for the Query Analyzer directly from within PHP.

By that we don't only reduce the latency for the data collection but we can provide more information about the current environment.

In the query detail window you now don't only see general query statistics but also a stack trace from the application, so you can immediately identify the part of the application which should be improved. So above you can see a few screenshots I made from this server showing some insights of this blog where I was testing the plugin.

If you want to learn more checkout the documentation and product pages. Hope you like it!

Roughly three years ago I was writing about Direct MySQL Stream Access - a way to access the low-level stream PHP's mysqlnd library is using. Back then this had been a patch against PHP's mysqli extension. As such a feature is quite dangerous (you can easily mess with the connection state which confuses mysqlnd and/or the MySQL server) we didn't push it into the main PHP tree. Now three years later it's time to look at this again as we don't need to patch PHP anymore.

Since the mentioned patch was written mysqlnd got a plugin interface about which I was talking before. This plugin-interface, especially in the version of PHP 5.4, makes it trivial to implement this feature.

PHP_FUNCTION(mysqlnd_to_stream)
{
    zval *conn_zv;
    MYSQLND *conn;

    if (zend_parse_parameters(ZEND_NUM_ARGS() TSRMLS_CC, "z", &conn_zv) == FAILURE) {
        return;
    }

    if (!(conn = zval_to_mysqlnd(conn_zv))) {
        php_error_docref(NULL TSRMLS_CC, E_WARNING,
                    "Passed variable is no mysqlnd based connection");
        RETURN_FALSE;
    }

    php_stream_to_zval(conn->net->stream, return_value);
}
 

If you take a function like the one shown above and add some general PHP infrastructure you are done. The key function here is the function MYSQLND* zval_to_mysqlnd(zval *connection) which takes a PHP variable as parameter and in case it is a MySQL connection (ext/mysql, mysqli or pdo_mysql) will return the corresponding MYSQLND pointer which gives access to the stream which then has to be packed into a PHP variable, again. The nice thing, compared to the old version is not only that it is a plugin which can be loaded into PHP as shared extension via php.ini but also that it works with all MySQL extensions, not only mysqli as the one before.

You can download the complete source, but be warned: This is experimental stuff and not supported in any way, but I hope good enough to get a feeling what's possible with mysqlnd.

Some time ago I was already writing about the power included with mysqlnd plugins and how they can they can be used transparently to help you with your requirements without changing your code. But well, as mysqlnd plugins in fact are regular PHP extensions they can export functions to the PHP userland and providing complete new functionality.

In my spare time I'm currently writing a shiny Web 2.0 application where I'm heavily using AJAX-like things, so what I do quite often in this application is, basically this: Check some pre-conditions (permissions etc.) then select some data from the database, do a fetch_all to get the complete result set as an array and run it through json_encode; or to have it in code:

<?php
$m = new MySQLi(/*...*/);
check_whether_the_user_is_checked_in_and_allowed_to_see_this();
$result = $m->query("SELECT a,b,c,d FROM t WHERE e=23");
echo json_encode($result->fetch_all());
?>

Of course that example is simplified as I'm using the Symfony 2 framework for this project. When writing a similar function for the 5th time I wondered whether I really need to create the temporary array and all these temporary elements in it.

So I wrote a mysqlnd plugin.

The mysqlnd_query_to_json plugin (hey what a name!) provides a single function, mysqlnd_query_to_json(), which takes two parameters, a connection identifier and an SQL query, and returns a JSON string containing the result set. The connection identifier can be a mysql resource, a mysqli object or even a PDO object. The resulting JSON string will be created directly from the network buffer without the need of temporary complex structures. Using the above example would create code like this:

<?php
$m = new MySQLi(/*...*/);
check_whether_the_user_is_checked_in_and_allowed_to_see_this();
echo mysqlnd_query_to_json($m, "SELECT a,b,c,d FROM t WHERE e=23");
?>

The plugin, which you can find here, requires PHP 5.4 and has a few limitations as it knows nothing about MySQL bitfields or escaping of unicode characters for creating fully valid JSON data and Andrey called it, for good reasons, a hack. Neither did I benchmark it, yet as I merely share it to show what's possible and maybe start some discussion on what is actually needed.

If you want to learn more on these topics I also suggest to check the MySQL Webinar page frequently as Ulf is going to hold a Webinar on myslqnd plugins in October!

If you follow my blog or twitter stream you might know I've recently been at Barcelona to attend the PHP Barcelona conference. Conferences are great for exchanging ideas one of the ideas I discussed with Combell's Thijs Feryn: They are a hosting company providing managed MySQL instances to their customers, as such they run multiple MySQL servers and each server serves a few of their customers. Now they have to provide every customer with database credentials, including a host name to connect to. The issue there is that a fixed hostname takes flexibility out of the setup. Say you have db1.example.com and db2.example.com over time you figure out that there are two high load customers on db1 while db2 is mostly idle. You might want to move the data from one customer over to db2 to share the load. This means you have to ask the customer to change his application configuration at the time you're moving the data. Quite annoying task.

Now there's a solution: MySQL Proxy. The proxy is a daemon sitting in between of the application/web servers and MySQL something like in the picture below.

The proxy can be scripted using lua so it is not too hard to implement a feature which chooses the database server to actually connect to. The customer is then told to connect to the proxy and depending on the username given he is redirected to a specific system. All magic happens transparent in the background. This is nice but not without issues: There is one more daemon to monitor, the proxy sitting in between adds latency, and so on.

In case you attended a recent talk by Ulf or me you certainly learned about mysqlnd plugins. We always compare mysqlnd plugins with the MySQL Proxy, so let's take a closer look: The plugins are PHP extensions, usually written in C, hooking into mysqlnd, the native driver for PHP, overriding parts of mysqlnd's internals. mysqlnd, introduced in PHP 5.3, is the implementation of the MySQL Client-Server-Protocol sitting invisible below the PHP extensions ext/mysql, mysqli and PDO_mysql. This means any plugin to mysqlnd can transparently change the behavior without an changes to the actual application.

Now with this plugin facility we can move the code for the server selection from the proxy directly in PHP. By doing this we will have almost no overhead and due to the deep integration less work for monitoring and no additional fault component.

So let's look in the implementation of such a simple plugin: The goal is having an extension which overrides the server name given by the user by one set in  a special configuration file so the user is transparently redirected. The configuration file format used is a INI file. As said above a mysqlnd plugin is a regular PHP extension, even though we usually won't export functions to PHP userland. A quick note before we really start: I won't discuss all parts of the PHP API in detail, please see the resources linked below for more on that.

The first thing PHP looks at while loading an extension is a module entry. In our case there is one special thing: We add a dependency to mysqlnd, to make sure mysqlnd was initialised before this extension is initialised. You can also see that I have chosen the name mysqlnd_server_locator.

static const zend_module_dep mysqlnd_server_locator_deps[] = {
    ZEND_MOD_REQUIRED("mysqlnd")
    {NULL, NULL, NULL}
};

zend_module_entry mysqlnd_server_locator_module_entry = {
    STANDARD_MODULE_HEADER_EX,
    NULL,
    mysqlnd_server_locator_deps,
    "mysqlnd_server_locator",
    NULL,
    PHP_MINIT(mysqlnd_server_locator),
    PHP_MSHUTDOWN(mysqlnd_server_locator),
    NULL,
    NULL,
    NULL,
    "0.1",
    STANDARD_MODULE_PROPERTIES
};

On PHP startup the module initializer, MINIT, is being called. We want to override the connect method from mysqlnd's connection related functions. Additionally I initialize a HashTable which will hold the translation table.

static int plugin_id;
static func_mysqlnd_conn__connect orig_mysqlnd_conn_connect_method;

static HashTable server_list;
static int server_list_init = 0;

PHP_MINIT_FUNCTION(mysqlnd_server_locator)
{
    struct st_mysqlnd_conn_methods *conn_methods;

    plugin_id = mysqlnd_plugin_register();
    conn_methods = mysqlnd_conn_get_methods();

    orig_mysqlnd_conn_connect_method = conn_methods->connect;
    conn_methods->connect = MYSQLND_METHOD(mysqlnd_server_locator, connect);

    if (zend_hash_init(&server_list, 10, NULL, free, 1) == FAILURE) {
        php_error_docref(NULL TSRMLS_CC, E_WARNING, "Failed to init server_list table");
        return FAILURE;
    }

    return SUCCESS;
}

One thing to note here is that I don't actually load the translation table, yet. This is due to issues I had while using the ini scanner during PHP's initialization phase and having the mechanism to load it later has the benefit of being ale to update the table without having to restart PHP. Anyways the above function should be relatively clear. We tell mysqlnd that a plugin is around, store the connection method pointer in a safe place and set our own connection method and then init the HashTable.

During PHP shutdown we will free this table again:

PHP_MSHUTDOWN_FUNCTION(mysqlnd_server_locator)
{
    zend_hash_destroy(&server_list);
    return SUCCESS;
}

Now let's look at the implementation of the overridden connect method. At first this looks complex as it takes tons of parameters but we simply pass them through and don't have to care about them. All we care about are two things: Firstly we make sure the the translation table was initilised, then we look for the username in the table, if the user exists in the table we take the hostname given in the table, else we connect to the host requested by the user.

static enum_func_status MYSQLND_METHOD(mysqlnd_server_locator, connect)(MYSQLND * conn,
        const char *host, const char *user,
        const char *passwd, unsigned int passwd_len,
        const char *db, unsigned int db_len,
        unsigned int port,
        const char * socket_or_pipe,
        unsigned int mysql_flags
        TSRMLS_DC)
{
    char **new_host;
    char *actual_host = host;

    if (!server_list_init) {
        mysqlnd_server_locator_init_server_list(TSRMLS_C);
        server_list_init = 1;
    }

    if (zend_hash_find(&server_list, user, strlen(user) + 1, (void**)&new_host) == SUCCESS) {
        actual_host = *new_host;
    }

    return orig_mysqlnd_conn_connect_method(conn, actual_host, user, passwd, passwd_len, db, db_len, port, socket_or_pipe, mysql_flags TSRMLS_CC);
}

Please note that this method is not thread-safe and should, in this form, only be used in non-threaded environments. This is fixed in a version linked below, which also does one more thing: It will always check whether the ini file was modified since we read it, but let's keep it simple here. As said the configuration is a ini file which simply consists of username=host pairs:

johannes=db1.example.com
guybrush=db1.example.com
sam=db2.example.com
max=db2.example.com
bernard=db1.example.com

Such files can be parsed by PHP, I won't go into the details of the implementation here.

static void mysqlnd_server_locator_ini_parser_cb(zval *arg1, zval *arg2, zval *arg3, int callback_type, void *list_v TSRMLS_DC)
{
    HashTable *list = (HashTable*)list_v;
    char *hostname;
    
    if (!arg1 || !arg2) {
        return; 
    }
    
    switch (callback_type)
    {
        case ZEND_INI_PARSER_ENTRY:
            hostname = pestrndup(Z_STRVAL_P(arg2), Z_STRLEN_P(arg2), 1);
            zend_hash_update(list, Z_STRVAL_P(arg1), Z_STRLEN_P(arg1) + 1, &hostname, sizeof(char *), NULL);
            break;
        case ZEND_INI_PARSER_SECTION:
            break;
        case ZEND_INI_PARSER_POP_ENTRY:
            php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Array syntax not allowed in ini file");
            break;
        default:
            php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Unexpected callback_type while parsing server list ini file");
            break; 
    }   
}       

static int mysqlnd_server_locator_init_server_list(TSRMLS_D)
{
    zend_file_handle fh;

    memset(&fh, 0, sizeof(fh));
    fh.filename = "/tmp/server.ini";
    fh.type = ZEND_HANDLE_FILENAME;

    if (zend_parse_ini_file(&fh, 0, ZEND_INI_SCANNER_NORMAL, mysqlnd_server_locator_ini_parser_cb, &server_list TSRMLS_CC) == FAILURE) {
        php_error_docref(NULL TSRMLS_CC, E_WARNING, "Failed to parse server list ini file");
        return FAILURE;
    }

    return SUCCESS;
}

And that's it. Now let's have a look at some PHP code running while this extension is loaded:

$ php -r 'mysql_connect("loalhost", "johannes", "supersecretpasswordforthis");'
Warning: mysql_connect(): php_network_getaddresses: getaddrinfo failed: node name or
service name not known in Command line code on line 1

Warning: mysql_connect(): [2002] php_network_getaddresses: getaddrinfo failed: node
name or servi (trying to connect via tcp://db1.example.com:3306) in Command line code on line 1

Neat, isn't it? - I also packaged this code in an slightly improved version. This version uses a php.ini setting for configuring the location of the extension's ini file, solves the threading issue mentioned above and automatically reloads the configuration file in case it was changed. Note that this code comes for educational purpose as-is only and I take no responsibility of any form.

This won't solve all problem's in the case of Combell as they want to provide external access or access from other applications, too. But I could imagine a solution using such a plugin for PHP as the overhead is minimal (in the version above one hash lookup, in the download version one hash lookup and a, well cached, stat call during connect which both can be neglected) and a proxy-based solution for other systems.

Some more resources:

• PHP Internals Documentation
• mysqlnd plugin API
• Ulf's blog with lot's of related postings
• Ulf's presentations
  

Recently I gave a few public presentations and started to convert the slides for making them available online. Here's the first bunch with slides from two conferences which were held in October, the International PHP Conference in Germany and PHP Barcelona in Spain. As always: The spoken word is missing on the slides ...
Read More

Ever wondered what your PHP application and MySQL actually do? An experimental mysqlnd branch will give you full access to the network communication stream. Using a custom PHP stream filter you can then intercept the communication ... but let's start at the beginning:

When talking about mysqlnd - the mysql native driver for PHP - we always mention the fact it's native in a way that we're, when possible, using PHP infrastructure. The most common example here is the memory management. By directly using PHP's memory we can avoid unnecessary copies of data from the MySQL Client Library's memory into PHP memory.

<?php
$mysqli = mysqli_connect("localhost", "root", "", "test");
$stream = mysqli_conn_to_stream($mysqli);
stream_filter_append($stream, "mysql.server", STREAM_FILTER_READ);
?>

But there's more what we're doing. We're also using PHP's stream abstraction layer. From a development perspective the benefit is that we're using a tested abstraction from different stream implementations by different operating systems instead of writing our own. But, again, there's more to it: We can export the communication stream to PHP userland. We hesitated about exporting it for some time as it can be quite dangerous and you might easily corrupt the client-server- communication.

As Ulf mentioned during his IPC talk I recently pushed a mysqlnd branch to launchpad which adds a userspace function to mysqli which returns a PHP stream for a connection. Using that stream you can now send your own requests to the server and wait for the response. That might be nice in a way, but I guess you most likely won't have use for that. PHP streams allow you to do more: PHP streams give you the possibility to add filters to a stream. These filters allow you to intercept packages which are sent or received , read them, change them or do whatever you like. A very simple filter can be found on the launchpad site, mentioned above. That filter simply prints the information after replacing unprintable (binary) characters by dots.

Once again: Just a small step, the next one is to decode the MySQL protocol. For that I've written a simple decoder for the MySQL protocol, not complete, but enough to give an idea. The script, including the decoder and some sample code using it, is, as a sample, part of the branch. When running you will get some output like

Query:
-> 0 59: QUERY: SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES LIMIT 1
<- 1 1: DATA
<- 2 52: FIELD INFO
<- 3 5: EOF
<- 4 19: DATA
<- 5 5: EOF
Invalid Query:
-> 0 29: QUERY: ghdfjtgfdrs tztr ttgdszthtdr
<- 1 183: ERROR: 2000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ghdfjtgfdrs tztr ttgdszthtdr' at line 1
Prepare:
-> 0 61: PREPARE: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.STATISTICS LIMIT 2
<- 1 12: OK
<- 2 52: FIELD INFO
<- 3 5: EOF
Ping:
-> 0 1: PING
<- 1 7: OK
Execute:
-> 0 11: EXECUTE
<- 1 1: DATA
<- 2 52: FIELD INFO
<- 3 5: EOF
<- 4 15: OK
<- 5 15: OK
<- 6 5: EOF
EOF
-> 0 5: CLOSE_STMT
-> 0 1: QUIT

As one can see: The protocol isn't fully decoded yet so this all might be extended but for me it served the purpose well enough. For making real use out of this we're thinking about exporting the protocol decoder which exists within mysqlnd to PHP userland.

What are your ideas for such a feature? - Sending different queries to different servers? Rewriting queries? Sharding? Replication? Easy scaling of your application while refactoring your application? Let us know!

Since a few hours we're having, thanks to Edin, Windows snapshots for the upcoming PHP 5.3 release. In combination with the latest sources from CVS everybody should be able to test the current state.

There are quite a few new features already committed to the CVS tree, some of them are documented in the NEWS file and READMEs in CVS, most of them have been commented on Planet PHP so I suggest searching there for more information and keep this list short:

• Namespaces: A way to help you organizing your code.
• Late Static Binding: Gives you the class name used when calling a static method.
• Dynamic static calls: $c = "classname"; $c::someMetod();
• Improved ini-handling: .htaccess file like per-directory configuration and much more
• __callStatic magic method: similar to __call but for static calls
• mysqlnd: a replacement for libmysql for better PHP-MySQL-Integration
• getopt() on any platform (inclding windows)
• ...

As you can see there's lots of stuff which needs testing! The earlier bugs are found the earlier they can be fixed! In case you ever wondered how you could give back something to PHP (which you certainly should do) you might take a look at our test coverage reports to see which areas of PHP need more testing and provide additional tests. (See qa.php.net and Marcus's slides for an introduction to test writing) Having more tests is important for us - and you don't have to know much about PHP internals to write proper tests (sometimes it's even better to write black-box tests...)

But well, there's not only PHP 5.3: If you're interested in unicode you should certainly take a look at PHP 6 and if you're running older systems you should consider upgrading to the latest 5.2 release instead of waiting for PHP 5.3 - the stable release is not expected within this year and many things there can change till it's released.

And as a final note: Test the snaps as often as you can - generic tests can never cover all use-cases and - as said above - fixing regressions is harder after a release than during development. (And be warned: We won't take any complaints from people not testing in time)

Ilia recently published the probably final release candidate of the current stable tree: PHP 5.2.4RC2. The stable release can be expected soon. This release fixes quite some bugs from older versions and does a few minor adjustments. Please test the RC release so unexpected regressions can be detected and fixed before it's marked stable. If regressions and other problems aren't detected now it will take a few months till the next version will be released, so do your best to make sure it's worth being called stable! Please file found issues, after checking for other reports and the documentation, into the bug tracker. The recent trend to report bugs using blogs isn't working well!

In other news one could read that there were also some major improvements in the development of PHP 6 made: Next to all that Unicode related work which was made over the last months PHP got, due to Andrey, mysqlnd as PHP-optimized replacement for libmysql and, thanks to Dmitry's work, namespace support. So this weekend might be the perfect time for sending you're family visting other parts of the family, letting them sweat on some beach or having fun at some theme park - then you can sit down, relax, and, after testing PHP 5.2.4RC2, give that new stuff in PHP 6 a test.

For getting started with PHP 6, which you can get, as you're hopefully used to, from the snaps site, you should first read Andrei's slides about Unicode and PHP 6. The most important Unicde+PHP facts are also collected inside a README file. For namespaces there's, again, a README file, David Coallier has written a nice summary and Stanislav wrote a short FAQ. If you are curious what mysqlng is: simply read Ulf's blog postings or Andrey's slides.