Johannes Schlüter

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!

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!