Johannes Schlüter

Since PHP 7.0 has been released there's more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren't always as one eventually might initially expect.

For MySQL the type we see -- in the first approximation -- is defined by the network protocol. The MySQL network protocol by default converts all data into strings. So if we fetch an integer from the database and use PHP 7's typing feature we get an error:

<?php
declare(strict_types=1);

function getInteger() : int {
  $mysqli = new mysqli(...);
  return $mysqli->query("SELECT 1")->fetch_row()[0];
}

var_dump(getInteger());
?>

Fatal error: Uncaught TypeError: Return value of getInteger() must be of the type integer, string returned in t.php:6

Of course the solution is easy: Either we cast ourselves or we disable the strict mode and PHP will cast for us.

Now let's take a look at another case. Assume we have an application where we fetch an integer ID from the database. We know MySQL will send us a string and we treat the ID as opaque data anyways so we have the type check for a string. Now we refactor the code slightly and make use of prepared statements. What will the result be?

<?php
declare(strict_types=1);

function getId() : string {
  $mysqli = new mysqli(...);
  $stmt = $mysqli->prepare("SELECT 1");
  $stmt->execute();
  return $stmt->get_result()->fetch_row()[0];
}

var_dump(getId());
?>

Fatal error: Uncaught TypeError: Return value of getId() must be of the type string, integer returned in t.php:8

Wait! - What's up there!? -- Didn't I just say that the MySQL protocol will always send a string, thus we retrieve a string in PHP!? - Yes I did and that's true for "direct queries." It's not true for results from prepared statements. With prepared statements the MySQL protocol uses a binary encoding of the data and therefore mysqlnd and mysqli will try to find the matching PHP type. This isn't always possible, especially if we're going into the range of big values. So let's query for PHP_INT_MAX and PHP_INT_MAX+1 and look at the types:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT 9223372036854775807, 9223372036854775808");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  int(9223372036854775807)
  [1]=>
  string(19) "9223372036854775808"
}

Here 9223372036854775807 is the largest value a PHP integer can represent and thus is an integer. 9223372036854775808 however is to large and can't fit in a signed 64bit integer thus it is converted in a string, as this keeps all information and can be handled at least to some degree.

Similar things happens to other types which can't be properly represented in PHP:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT 1.23");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  string(4) "1.23"
}

Yay - yet another wtf! So what is going on this time? -- Well, a literal in SQL is treated as DECIMAL. A DECIMAL field is supposed to be precise. If this were to be converted into a PHP float aka. double we probably would loose the precision, thus treating it as string again makes sure we're not loosing information. If we had a FLOAT or DOUBLE field this could safely be represented as float in PHP:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT RAND()");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  float(0.16519711461402206)
}

So to summarize:

• For a direct query the MySQL server sends strings, PHP returns all data as string
• For prepared statements MySQL sends data in binary form and PHP will use a corresponding type
• If the value could only be represented with a potential data loss in PHP it is converted to a string by PHP, even with prepared statements

Now we might expect the same when using PDO. Let's check:

<?php
$pdo = new PDO("mysql:host=localhost", "...", "...");
$stmt = $pdo->prepare("SELECT 9223372036854775808, RAND()");
$stmt->execute();
var_dump($stmt->fetch(PDO::FETCH_NUM));
?>

array(2) {
  [0]=>
  string(1) "1"
  [1]=>
  string(18) "0.3217373297752229"
}

This example uses prepared statements, but returns strings!? The reason is that PDO by default doesn't use prepared statements on the network layer but an emulation within PHP. This means PHP will replace potential placeholders and then runs a direct query. As mentioned above with a direct query the MySQL server will send strings, thus PHP will represent all data as string. However we can easily ask PDO to disable the emulation:

<?php
$pdo = new PDO("mysql:host=localhost", "...", "...");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare("SELECT 1, RAND()");
$stmt->execute();
var_dump($stmt->fetch(PDO::FETCH_NUM));
?>

array(2) {
  [0]=>
  int(1)
  [1]=>
  float(0.24252333421495)
}

This leaves the question whether you should disable the emulation in order to get the correct types. Doing this has some impact on performance characteristics: With native prepared statements there will be a client-server round-trip during the prepare and another round-trip for the execute. With emulation only during the execute. The native prepared statements also require some server resources to store the handle. However if a single statement is executed multiple times there might be some savings. Also the type representation means that different type conversions happen and a different amount of data is transfered. For most cases this shouldn't have notable impact, but in the end only a benchmark will tell.

Hope this helps to give a better understanding, or more confusion

PHP users who attended any of my recent PHP&MySQL related talks or read Ulf's blog will know our mysqlnd_ms plugin. This plugin hooks into PHP's mysqlnd library and provides transparent support for replication and load-balancing features. Without changing your application you get transparent load-balancing and read-writ splitting so all your reading queries will be sent to a slave while the writes go to a master server. The exact strategies for that can be defined in mysqlnd_ms's configuration so quite often no, or only few application changes are needed. But we have one limitation: The MySQL servers have to be configured in each configuration on all your PHP servers, this can be annoying when you're changing your environment like adding a new slave or promoting a machine to master in case the original master fails. But there's help coming!

At this year's MySQL Connect conference we've announced the initial labs release for MySQL Fabric. MySQL Fabric aims to be "an integrated environment for managing a farm of MySQL server supporting high-availability and sharding." Part of this framework is an RPC interface to query available servers which are managed by MySQL Fabric which delivers us the missing piece for mysqlnd_ms.

As this release of Fabric put the focus on sharding, this is what I want to show here, too. A general introduction to MySQL Fabric and its sharding features can be found on VN's blog so I'll be quite fast in some areas, for details please refer to the documentation and the mentiond blogs.

The first thing we need is the MySQL Utilities package with Fabric support which is available from labs.mysql.com. After installing this package you have to locate the main.cfg configuration file and configure the storage instance.

[storage]
database = fabric
user = fabric
address = localhost:3300
connection_timeout = 6
password = i do no now

This is a MySQL server where Fabric will store its configuration and such. After that we can initialize Fabric and start the daemon.

$ mysqlfabric setup
$ mysqlfabric start

The setup step creates tables in the configured database and the start starts the daemon process. Now we can o and configure our server groups. A server group contains a master server where the group's data is being written to and a number of slaves to which MySQL will replicate data. For our sample sharding setup I plan to create two shards and a global group. The purpose of the global group is to hold table definitions and data which Fabric will make available on all systems in our environment. Each of these groups will, in this example, have one master and one slave. This means we need six MySQL server instances running. These six instances should all be running MySQL 5.6. an except from having binary logging enabled and having different server ids there is no replication configuration needed before running these commands. In my example setup I'm running all of those on one machine, obviously that's only useful for tests:

$ mysqlfabric group create global
$ mysqlfabric group add global 127.0.0.1:3301 root secret
$ mysqlfabric group promote global
$ mysqlfabric group add global 127.0.0.1:3302 root secret

$ mysqlfabric group create shard1
$ mysqlfabric group add shard1 127.0.0.1:3303 root secret
$ mysqlfabric group promote shard1
$ mysqlfabric group add shard1 127.0.0.1:3304 root secret

$ mysqlfabric group create shard2
$ mysqlfabric group add shard2 127.0.0.1:3305 root secret
$ mysqlfabric group promote shard2
$ mysqlfabric group add shard2 127.0.0.1:3306 root secret

So this creates the three groups and will configure the servers to replicate the servers as needed. With this setup the server on port 3301 will be the global master. 3302, 3303 and 3305 will e 3301's direct slaves and 304 will be configured to be a slave for 3303, as will 3306 to 3305.

Now we go to define our sharding rules. I'm going to use range based sharding with two shards. The first shard, which will be assigned to the server group shard1 created above will have shard id 1 to 9999 and the second shard, in group shard2 will have data for shard key values 10000+. We also define the table fabrictest in the test schema as our sharding tale and id as the shard column.

$ mysqlfabric sharding define RANGE global
$ mysqlfabric sharding add_mapping 1 test.fabrictest id

$ mysqlfabric sharding add_shard 1 shard1 ENABLED 1
$ mysqlfabric sharding add_shard 1 shard2 ENABLED 10000

Note that for range-based sharding we don't have to define the upper bound as that is defined by the lower bound of the next shard.

Now we have MySQL Fabric and our MySQL Servers configured and can go to PHP. As mentioned in the beginning we need mysqlnd_ms, to be precise the 1.6.0 alpha release which we can install using pecl:

$ sudo pecl install mysqlnd_ms-alpha

To configure PHP we firstly need a mysqlnd_ms configuration file. myslqnd_ms uses json and a simple confiuration using Fabric might look like this:

 fabric.json:

{
        "test" : {
                "fabric":{
                        "hosts": [ { "host": "localhost", "port": 8080 } ]
                }
        }
}

This configures the application test to use a MySQL Fabric based setup where MySQL Fabric's RPC daemon runs on the local machine. Again: We put all on one machine for a test, not what one would do on a production setup.

Next we locate our system's php.ini file and enable mysqlnd_ms to use our config.

php.ini:
extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/fabric.json

And now we are finally done and run a test script.

<?php
$c = new mysqli("test", "root", "", "test");

echo "Creating global table:\n";
mysqlnd_ms_fabric_select_global($c, "test.fabrictest");
var_dump($c->query("CREATE TABLE fabrictest (id INT NOT NULL)"));
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";

echo "Inserting with ID 10:\n";
mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10);
var_dump($c->query("INSERT INTO fabrictest VALUES (10)"));
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";

echo "Trying to read id 10 from 10010:\n";
mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10010);
$r = $c->query("SELECT * FROM fabrictest WHERE id = 10");
var_dump($r->fetch_row());
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";
?>

With this script we do a few things, first observation is the hostname test. The mysqlnd_ms plugin will recognize that as application name and will refer to its configuration. Second are the mysqlnd_ms_* functions. First we pick the global group and execute a CREATE TABLE operation there. mysqlnd_ms will detect that this is a write operation and therefore connect to the globals master. This should be 127.0.0.1:3301 which hopefully is printed by the echo call. Then we select the shard responsible for id 10 in the fabrictest table and insert data. mysqlnd_ms will, again, detect that this is a write operation and will therefore figure out where writes to that shard have to go to, which is 127.0.0.1:3303. Finally we do an operation which will not really succeed: We select the servers for shard 10010 which is shard2 from our setup and then query for id 10. the data we stored in shard1. This will query 127.0.0.1:3306 (slave of 3305 in shard2 group) which will return an empty result set.

I hope this is enough to get you started, you can now add shards or migrate them or take servers down and promote current slaves to masters etc. and see how the system reacts.

In a future post we will combine this with Doctrine stay tuned.

Note: This blog post features labs/alpha releases. Which aim at demonstrating functionality. They are not for production use. There might be stability issues, there certainly are performance restrictions we're working on. We'd like however to receive feedback.

One of the great things of mysqlnd as a base library for PHP's MySQL support are the collected statistics. mysqlnd collects about 160 different statistical values about all the things going on. When having such an amount of raw data it, obviously, is quite hard to draw conclusions out of it. Therefore I recently created a PHP library sitting on top of this feature to collect all data, run some analysis and then provide some guidance and made it available from the JSMysqlndAnalytics GitHub repo (see there also for instructions for using Composer).

Using the library is relatively simple as the short instructions show. The library consists of two main parts. On the one side the "Collector"  this is a wrapper around mysqli_get_client_stats() (even though this function is part of mysqli it will also work for applications using ext/mysql or PDO_mysql) which is creating the raw statistics which could be stored away or such and then the actual Analytics engine comparing the values to predefined rules. The current set of rules is a bit limited so I'm looking for input for ideas.

In case you're a Symfony user live is quite easy: Some time ago I already provided an Symfony bundle providing a Symfony Profiling Toolbar plugin showing the statistics. This JSMysqlndBundle has been extended  to make use of these Analytics. The screenshot might give a rough idea on how this looks.

Hope this helps creating better applications! Happy hacking!

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.