Johannes Schlüter

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!

The MySQL Server has a quite simple interface for plugins to create tables inside INFORMATION_SCHEMA. A minimal plugin for creating a table with nothing but a counter might look like this:

static int counter_fill_table(THD *thd, TABLE_LIST *tables, Item *cond)
{
  ulonglong value= 0;
  
  while (1)
  {
    table->field[0]->store(value++, true);
  }
  
  return 0;
}

static ST_FIELD_INFO counter_table_fields[]=
{
  {"COUNT", 20, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0},
  {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

static int counter_table_init(void *ptr)
{
  ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;

  schema_table->fields_info= counter_table_fields;
  schema_table->fill_table= counter_fill_table;
  return 0;
}

static struct st_mysql_information_schema counter_table_info =
{ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

mysql_declare_plugin(counter)
{
  MYSQL_INFORMATION_SCHEMA_PLUGIN,
  &counter_table_info,          /* type-specific descriptor */
  "COUNTER",                    /* plugin and table name */
  "My Name",                    /* author */
  "An I_S table with a counter",/* description */
  PLUGIN_LICENSE_GPL,           /* license type */
  counter_table_init,           /* init function */
  NULL,                         /* deinit function */
  0x10,                         /* version */
  NULL,                         /* no status variables */
  NULL,                         /* no system variables */
  NULL,                         /* no reserved information */
  0                             /* no flags */
}
mysql_declare_plugin_end;

This is quite straight forward and documented inside the MySQL documentation. It also has an obvious issue: It will run forever (at least if we assume we don't run in an out of memory situation). Luckily we might have a user who foresees this issue and added a WHERE clause like here:

SELECT COUNT FROM INFORMATION_SCHEMA.COUNTER WHERE COUNT < 10

Read More

Some time ago I was writing about the InnoDB Memcache Daemon plugin already for the MySQL server. Back then we had a labs release with a little preview only. Meanwhile quite some time passed and new developments were made - just in time for the MySQL 5.6 RC announced this weekend by Tomas.

The innodb_memcache daemon plugin is a plugin for the MySQL Server end contains an embedded memcached. This embedded memcached is configured to use MySQL's InnoDB engine as storage backend. By using this data stored inside an InnoDB table can be accessed using memcache's key-value protocol. Back in the times of the previous blog post this was limited to data from a single table, which maps easily to the key-value nature of memcache but is a clear limitation. The InnoDB obviously knows that and improvd it:

A user may now define multiple configurations at the same time and therefore access different tables at the same time - or the same table using different key-columns as memcache key, for accessing the data the memcache key names will then be prefixed by specially formatted configuration names.

Let's take a look at an simple example. Assume we have this configuration inside innodb_memcache.containers:

      Name: prefix_test
      schema: test
      table: test
      key column: id
      key name: PRIMARY KEY
      value columns: lastname

We can then use the memcache configuration using a key like this:

      set @@prefix_test.1 Schlüter
      get @@prefix_test.1

The first call will store my last name with id=1 in the test.test table. For accessing multiple configurtions we simply add entries to the containers list.

Of course we can still access miltiple columns, as in the previous version:

   
      Name: test_first_last
      schema: test
      table: test
      key columns: id
      key name: PRIMARY
      value columns: firstname,lastname

And then we add my firstname:

 
      set @@test_first_last.1 Johannes,Schlüter
      get @@test_first_last.1

The configurations above are, obviously, just a short introduction. For full information please check the documentation.

Now this blog entry is tagged a s PHP. Hs is that coming into play? - Well, on the one side we have this fast memcache interface, which allows to access almost arbitrarry data from the database. On the other side we have our PHP mysqlnd plugin interface where we can add special features, like query caching or load balancing, transparently to any PHP application. Why not combine those two things? - Good question. That's what we have done in the PECL mysqlnd_memcache PHP extension. This PHP extension is a plugin to mysqlnd intercepting queries sent to the server. In a quick analysis it tries to identfy whether an SQL statement can - transparently - be converted into memcache requests. We therefore exchange some computing power on the PHP server and gain more performance from the MySQL server. As SQL is a rather complex language and memcache is a quite limited key-value protocol this will only work for a limited subset of common queries though.

So let's take a look at some PHP code:

 
      <?php
      $mysqli = new mysqli("localhost", "usr", "pass", "test");
      $memcache = new memcached();
      $memcache->addServer("localhost", 11211);
     
      mysqlnd_memcache_set($mysqli, $memcache);
      ?>

Here we ceate a MySQL connection using mysqli as well as a memcache connection using the pecl/memcached extension. Instead of mysqli we could, as with any mysqlnd plugin, use ext/mysql or the MySQL PDO driver. We then associate the MySQL connection with the memcache connection. As a consequence of this code the mysqlnd_memcache plugin will query the MySQL server for the current memcache configuration. Subsequently it will analyse SQL queries sent to the server:

  
      <?php
      $q1 = $mysqli->query("SELECT firstname, lastname FROM test WHERE id = 1");
      
      $q2 = $mysqli->query("SELECT * FROM test");
      ?>

These are two normal queries and nothing special on first sight. In case there's no error $q1 and $q2 will hold mysqli_result instances where rows can be read using fetch_row() or in other provided ways. But there are things going on in the back: The PHP extension will see that the first one can be translated to a memcche request and then fetch the data using this shortcut. The second call tries to read all data from the table. The memcache protocol provides no way for doing that so this query will use the "classic" way of sending the SQL to the MySQL server.

In order to be fast and limit the overhead - mind: we have to check any query - we didn't add a full SQL parser to this plugin but the check is done using a regular expression which will be enriched using data collected from the MySQL Server. In case this reguar expression causes trouble it can be overriden when the inital association is established. There are a few other caveats in the initial 1.0.0-beta reease available from PECL therefore we'd love to hear from you to see what you need and how we can improve your MySQL experience!

Over the last few weeks I had been quite silent, but that's about to change: Over the next few weeks I'll give a few presentations. Feel free to join any of those.

• January, 18th: Erstellung hochperformanter PHP-Anwendungen mit MySQL (German)
  MySQL Webinar, Online
• February, 9th: MySQL Konnectoren (German)
  OTN Developer Day: MySQL, Frankfurt, Germany
• February 24th/25th: PHP under the hood (English)
  PHP UK Conference, London, UK

One of the great things about the HTTP protocol, besides status code 418, is that it's stateless. A web server therefore is not required to store any information on the user or allocate resources for a user after the individual request is done. By that a single web server can handle many many many different users easily, and well if it can't anymore one can add a new server, put a simple load balancer in front and scale out. Each of those web servers then handles its requests without the need for communication which leads to linear scaling (assuming network provides enough bandwidth etc.).

Now the Web isn't used for serving static documents only anymore but we have all these fancy web apps. And those applications often have the need for a state. The most trivial information they need is the current user. HTTP is a great protocol and provides a way to do authentication which works well with its stateless nature - unfortunately this authentication is implemented badly in current clients. Ugly popups, no logout button, ... I don't have to tell more I think. For having nicer login systems people want web forms. Now the stateless nature of HTTP is a problem: The user may login and then browse around. On later requests it should still be known who that user is - with a custom HTML form based login alone this is not possible. A solution might be cookies. At least one might think so for a second. But setting a cookie "this is an authorized user" alone doesn't make sense as it could easily be faked. Better is to simply store a random identifier in a cookie and then keep a state information on the server. Then all session data is protected and only the user who knows this random identifier is authenticated. If this identifier is wisely chosen and hard to guess this works quite well. Luckily this is a mostly PHP- and MySQL-focused blog and as PHP is a system for building web applications this functionality is part of the core language: The PHP session module.

The session module, which was introduced in PHP 4, partly based on work on the famous phplib library, is quite a fascinating piece of code. It is open and extendable in so many directions but still so simple to use that everybody uses it, often newcomers learn about it on their first day in PHP land. Of course you can not only store the information whether the user is logged in but cache some user-specific data or keep the state on some transactions by the user, like multi-page forms or such.

In its default configuration session state will be stored on the web server's file system. Each session's data in its own file in serialized form. If the filesystem does some caching or one uses a ramdisk or something this can be quite efficient. But as we suddenly have a state on the web server we can't scale as easily as before anymore: If we add a new server and then route a user with an existing session to the new server all the session data won't be there. That is bad. This is often solved by a configuration of the load balancer to route all requests from the same user to the same web server. In some cases this works quite ok, but it is often seen that this might cause problems. Let's assume you want to take a machine down for maintenance. All sessions there will die. Or imagine there's a bunch of users who do complex and expensive tasks - then one of your servers will have a hard time, giving these users bad response times which feels like bad service, even though your other systems are mostly idle.

A nice solution for this would be to store the sessions in a central repository which can be accessed from all web servers.

Read More

For the last few months I had quite a few MySQL blog posts and didn't have anything from my "new features in PHP [trunk|5.4]" series. This article is a bridge between those two. The PHP 5.4 NEWS file has a small entry:

MySQLi: Added iterator support in MySQLi. mysqli_result implements Traversable. (Andrey, Johannes)

From the outside it is a really small change and easy to miss. The mentioned class, mysqli_result, implements an interface which adds no new methods. What once can't see is that this relates to some internal C-level functions which can be called by the engine for doing a foreach iteration on objects of this class. So with PHP 5.4 you don't have to use an "ugly" while construct anymore to fetch rows from a mysqli result but can simply do a foreach:

mysqli_report(MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli(/* ... */);
    foreach ($myslqi->query("SELECT a, b, c FROM t") as $row) {
        /* Process $row which is an associative array */
    }
} catch (mysqli_sql_exception $e) {
    /* an error happened ... */
}

I'm configuring mysqli in a way to throw exceptions on error. This is useful in this case as mysqli::query() might return false in the case of an error. Passing false to a foreach will give a fatal error, so I'd need a temporary variable and a check in front of the foreach loop, with exceptions I simply do the error handling in the catch block.

One thing to note is that mysqli is using buffered results ("store result") by default. If you want to use unbuffered result sets ("use result") you can easily do that by setting the flag accordingly:

foreach ($myslqi->query("SELECT a, b, c FROM t", MYSQLI_USE_RESULT) as $row) {
    /* ... */
}

People who are advanced with iterators in PHP might ask "Why did you implement Traversable only, not Iterator?" - the main reason is that we simply didn't want to. The mysqli_result class already has quite a few methods and we didn't want to make the interface confusing. If you need an Iterator class for some purpose you can simply wrap mysqli_result in an IteratoIterator.

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!

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!

This year I'll attend OSCON for the first time. I'll give two talks:

• PHP and MySQL - Recent Developments
  PHP’s MySQL support recently received many changes under the hood. PHP 5.3 introduced mysqlnd – the MySQL native driver which is a replacement for libmysql deeply bound into PHP. In this presentation you will learn what the PHP and MySQL development teams were up to. After starting with an introduction of the PHP-stack, demystifying things like mysqli, mysqlnd or PDO, this presentation will show you how to build mysqlnd plugins as PHP C extension and hooking into mysqlnd from PHP userland. It will also discuss existing plugins like a client side query cache or a module for doing read-write-splitting, both working transparently, without changes to your application.
• PHP Under the hood
  The beauty of PHP is that everybody can read the code and see the inner workings of software. But understanding concepts from reading code isn’t often helpful. Especially if you are no pro in that language. This presentation will take apart many parts of the PHP runtime, describe the concepts behind so attendees understand the inner workings without actually reading C code. Concepts covered include HashTables, the foundation for PHP arrays and many other internal data structures, the reference counting mechanism, which is important for writing efficient code as well as the overall executor.

In case you can't make it to these talks but want to talk to me you'll probably find me at the Oracle booth where I'll also try to give some short talks on some topics to be defined (any wishes?)

 In case you're not interested in me and my talks but MySQL there are a few sessions by other MySQL Engineers:

• MySQL Replication Update
• InnoDB: Performance and Scalability Features
• Python Utilities for Managing MySQL Databases
• The MySQL Time Machine

In case you want to attend you can use the code os11fos which should give you a 20% discount.

One of the issues Web Developers face is making their application robust to prevent SQL injection attacks. Different approaches exist which help. Sometimes people use large abstraction layers (which, sometimes, don't make anything safe ...) and sometimes people use prepared statements as a way to secure queries. Now prepared statements were a nice invention some 30 years ago abut they weren't meant for making things secure and so they do have some shortcomings: One issue is that preparing and executing a query adds a round-trip to the server where it then requires resources. In a classic application this is no issue. The users starts the application up early in the morning and processes data multiple times so the prepared statement handle is re-used quite some time. The system benefits from early optimisations.  In a typical PHP Web application this isn't the case. A request and therefore a database connection with its associated statement handles lives way less than a second before being thrown away. The PDO MySQL driver, by default, tries to improve that by emulating the prepared statement on the client side. This emulation faces issues as it is lacking the knowledge of what's valid SQL which can lead to strange behaviour (The simple example is $pdo->prepare("SELECT * FROM t LIMIT ?")->execute(array($_GET['count'])); which will emit an SQL syntax error) and inherits limitations from prepared statements. A second issue with prepared statements is that queries are being built dynamically. A common case which is hard to do with prepared statements is the IN() clause with a dynamic amount of values. With prepared statements you first have to build the list of place holders (the exact amount of place holders (?) separated by a comma, without trailing comma)  and then bind the values and mind the offsets when having other values - this typically becomes ugly code.

So why not take a step back. - Let's not try to emulate prepared statements but try to make it simpler to construct queries while escaping data?

An API for doing this might follow the sprintf() semantics and look like this;

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE f1 = %s AND f2 = %i", "foobar", 23);

which would return a string

SELECT * FROM t WHERE fi = 'foobar' AND f2 = 23

which can safely be send to the database. As said the IN clause should work. as we're in PHP we might simply extend it to do this:

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE f1 IN (%s)", array("foobar", 23));

SELECT * FROM t WHERE f1 IN ('foobar', '23')

Well doesn't look fancy? - But there's more: By not pretending to emulate prepared statements we can easily work with more dynamic queries. Something along the lines of

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE uid = %i", $_SESSION['uid']);
if (isset($option['option1']) {
    $sql .= mysqli_format_query($mysqli, "AND option1 = %s", $option['option1']);
}
if (isset($option['option2']) {
    $sql .= mysqli_format_query($mysqli, "OR option2 = %s", $option['option2']);
}

Doing such a thing using prepared statements  or in some classic way becomes way harder to maintain. For playing with this approach I quickly cooked up a simple implementation of that logic which should work well with PHP 5.3 and mysqli:

Read More

This week there are two big events for the MySQL community: The O'Reilly MySQL Conference and Oracle Collaborate run by the IOUG. At these events our Engineering VP, Tomas Ulin, announced the latest milestone releases for our main products. MySQL 5.6 and MySQL Cluster 7.1 as well as our new Windows Installer. There's lots of cool stuff in there but one feature really excited me: MySQL 5.6 contains a memcache interface for accessing InnoDB tables. This means you can access data stored in MySQL not only using SQL statements but also by using a well established and known noSQL protocol.

This works by having the memcache daemon running as plugin as part of the MySQL server. This daemon can then be configured in three ways: Either

• to do what memcached always did - use an in memory hash table to store its data - or
• to access an InnoDB table to store and read data from or
• to use its own hash table in memory and fall back to InnoDB if data is not found directly in memcache.

This combines the power of MySQL and InnoDB's persistent storage with the lightweight protocol memcache uses, which has faster connecting times (no authorization handshake etc.) and faster data access (no SQL parsing, optimization etc.) while you're still able to query the data using SQL when you're doing more complex operations.

Of course I had to give it a run with PHP.

First step for using this is fetching the MySQL preview release and configuring it accordingly. My colleague Jimmy Yang from the InnoDB team has a nice blog posting showing these first steps. After that we have to configure PHP where we have two choices: We can use the a bit older memcache module or the newer memcached module. I've chosen the first one as that was already configured on my system. On most systems the installation should be as easy as querying your package manager or using PECL:

# pecl install memcache
or
# pecl install memcached

And then adding the corresponding entry (extension=memcache[d].so) to your php.ini file.

So let's do a first test from command line:

$ php -r '$m = memcache_connect("localhost", 11211); ' \
         '$m->add("key", "value"); var_dump($m->get("key"));'
string(5) "value"

So we store a value in memcache and then load it again to see if it was stored properly. Now we verify the results directly in MySQL:

mysql> SELECT * FROM demo_test WHERE c1 = 'key';
Empty set (0.00 sec)

Uh, what's wrong? - O simple: We didn't read Jimmy's article properly:

If you would like to take a look at what’s in the “demo_test” table, please remember we had batched the commits (32 ops by default) by default. So you will need to do “read uncommitted” select to find the just inserted rows

So we can apply that knowledge and query again:

mysql> set session TRANSACTION ISOLATION LEVEL read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM demo_test WHERE c1 = 'key';
+------+------+------+------+-------+------+------+------+------+------+------+
| cx   | cy   | c1   | cz   | c2    | ca   | CB   | c3   | cu   | c4   | C5   |
+------+------+------+------+-------+------+------+------+------+------+------+
| NULL | NULL | key  | NULL | value | NULL | NULL |    0 | NULL |    1 | NULL |
+------+------+------+------+-------+------+------+------+------+------+------+
1 row in set (0.00 sec)

And yay! - We see our value in between the other columns for meta-data and other things.

Both PHP modules provide a session handler so you can store your session data easily in memcacheInnoDB. For configuring this we first need to add two entries to our php.ini file:

; when using the "memcache" extension:
session.save_handler=memcache
; when using the "memcached" extension:
; session.save_handler=memcached

session.save_path="tcp://localhost:11211"

After restarting the web server, so it reads the new configuration we can test it with a simple script:

<?php
session_start();
echo "<pre>Session ID: ".session_id()."\n";
var_dump($_SESSION);
$_SESSION['foo'] = 'bar';
?>

When first requesting this we will receive an output like

m1h4iqmp6hc7e4l85qlld0gtd
array(0) {
}

Then we reload the page and see:

m1h4iqmp6hc7e4l85qlld0gtd1
array(1) {
  ["foo"]=>
  string(3) "bar"
}

After that we can, again, look directly into MySQL:

mysql> select * from demo_test where c1 = 'm1h4iqmp6hc7e4l85qlld0gtd1';
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
| cx   | cy   | c1                         | cz   | c2             | ca   | CB   | c3   | cu   | c4   | C5   |
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
| NULL | NULL | m1h4iqmp6hc7e4l85qlld0gtd1 | NULL | foo|s:3:"bar"; | NULL | NULL |    0 | NULL |    4 | NULL |
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
1 row in set (0.00 sec)

I hope this helps you to get started. If you'd like to learn more about MySQL 5.6, MySQL Cluster 7.1 (which btw, also can be access using memcache!) our new installer or such you can watch a recording of Tomas' keynote or visit dev.mysql.com.

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

Oh time is flying! - This weekend it is already time for FrOSCon, the Free and Open source Conference in St. Augustin close to Western Germany's former capitol Bonn. The conference consists out of a main track and different side tracks, like the PHP developer room and the OpenSQL sub-conference.

In the PHP developer room I will give an overview over things that happened at MySQL, especially in regards to PHP in recent times. My colleague Ulf Wendel will then go and talk about plugins to mysqlnd - the MySQL native driver for PHP -  in detail.

In the OpenSQL Camp track you can find other interesting MySQL related talks which will, unfortunately, not leave you with enough time to watch all the interesting talks of the other tracks. And that all for an entrance fee of just 5€! So if you have a chance: Go there and say hi!

PHP 5.3 is released and after the release stress is over my mind is open for new ideas. While relaxing yesterday I thought about many things, among them was the Resultset iterator I recently discussed.

Now I wondered where to go next with this and had the idea that an individual Resultset is a child of the whole result and this might be wrapped in an Recursive Iterator. For doing so we don't implement the Iterator interface but RecursiveIterator. RecursiveIterator extends a typical Iterator with two methods: hasChildren() and getChildren(). But now we have a problem: The Iterator returned by getChildren() has to be a RecursiveIterator, too, which makes sense, in general. But I want to return a MySQLi Resultset which isn't recursive - so making this a RecursiveIterator is wrong. My solution now is to introduce yet another Iterator which goes by the name of MySQLi_PseudoRecursiveResultIterator and is implemented by extending IteratorIterator which will wrap the MySQLi_Result and implements RecursiveIterator telling the caller that there are no children.

As a sidenote: In our experimental tree Andrey made MySQLi_Result an iterator but that's not yet in php.net's CVS (might need some more testing, and probably we might change the design there...) so I'm emulating this with MySQLi_Result::fetch_all() combined with an ArrayIterator, using the experimental code the constructor can be dropped.

So let's finally look at the code of these two classes:

<?php
class MySQLi_ResultsetIterator implements RecursiveIterator
{
    private $mysqli;
    private $counter = 0;
    private $current = null;
    private $rewinded = false;

    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }
    private function freeCurrent() {
        if ($this->current) {
            $this->current->free();
            $this->current = null;
        }
    }
    public function rewind() {
        if ($this->rewinded) {
            throw new Exception("Already rewinded");
        }
        $this->freeCurrent();
        $this->counter = 0;
        $this->rewinded = true;
    }
    public function valid() {
        $this->current = $this->mysqli->store_result();
        return (bool)$this->current;
    }
    public function next() {
        $this->freeCurrent();
        $this->counter++;
        $this->mysqli->next_result();
    }
    public function key() {
        return $this->counter;
    }
    public function current() {
        if (!$this->current) {
            throw new Exception("valid() not called");
        }
        return $this->current;
    }

    public function hasChildren() {
        return true;
    }
    public function getChildren() {
        return new MySQLi_PseudoRecursiveResultIterator($this->current);
    }
}

class MySQLi_PseudoRecursiveResultIterator
     extends IteratorIterator
     implements RecursiveIterator
{
    public function __construct(MySQLi_Result $result) {
        // This ctor can be dropped with the experimental bzr sources
        // as IteratorIterator::__construct() directly works with
        // MySQLi_Result
        parent::__construct(new ArrayIterator($result->fetch_all()));
    }
    public function hasChildren() {
        return false;
    }
    public function getChildren() {
        throw new Exception("This should never be called");
    }    
}
?>

Now we can use this code. For properly using a RecursiveIterator one should use a RecursiveIteratorIterator (RII). To get some nice labels I'm extending the RII and then have a single foreach:

<?php
class MyRecursive_IteratorIterator
     extends RecursiveIteratorIterator
{
    public function __construct(MySQLi $mysqli, $flags = 0) {
        parent::__construct(
                new Mysqli_ResultSetIterator($mysqli),
                $flags | RecursiveIteratorIterator::LEAVES_ONLY);
    }

    public function beginChildren() {
        echo "Next ResultSet:\n";
    }
}

$mysqli = new MySQLi("localhost", "root", "", "test");

$query  = "SELECT 1,2 UNION SELECT 3, 4;".
          "SELECT 'hi world' UNION SELECT 'foobar'";
if ($mysqli->multi_query($query)) {
    foreach (new MyRecursive_IteratorIterator($mysqli) as $key => $row) {
        printf("    %s\n", $row[0]);
    }
}
?>

Now calling this code gives us a result similar to the following:

Next ResultSet:
    1
    3
Next ResultSet:
    hi world
    foobar

Isn't that nice? - I think that's a cool API! What do you think? Do you have use cases for such an API? Should we implement this in C and bundle it with PHP? Any feedback welcome!