Entries by Johannes Schlüter

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

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!

As PHP has moved to git recently everybody who works on the PHP source has to recreate his work environment. When working on PHP I have a few requirements for my working dirs. For one I want to be able to use different branches (like 5.3, 5.4 and master) at the same time and I want to quickly test different PHP configurations, like builds using thread-safety or debug mode on or off.

A simple approach for this is to use out-of-tree builds, something like that:

$ git clone ....php-src.git
$ (cd php-src && ./buildconf)
$ mkdir build-master-minimal
$ cd build-master-minimal
$ ../php-src/configure --disable-all
$ make

This allows having both requirements full-filled as you can have build dirs for each branch and each configuration. Nice, but in the long run quite confusing as you always have to make sure php-src has the correct branch checked out, matching the build dir you're currently building in, else you will create a mess.

Thankfully there is a nice solution to have multiple checkouts using git-new-workdir. So one can easily setup the branches and build dirs. Now it's still quite some repetitive work to create a structure using different branches and a set of different build dirs for each branch. Therefore I've created a simple shell script to do this quickly on my different machines and pushed the script to github in case anybody wants to have a similar structure, and maybe improve the script. But be warned: The script is really an ad hoc thing for me to get started.

Over the years a few videos of my presentations and some interviews were published. I've collected the ones I found and put them on a single web page. The oldest is from 2009 the latests just from February this year. Enjoy.

If you have another video which I missed: Please let me know!

By default PHP provides shared-nothing environments to ensure that whatever happens to PHP's state in one request has no effect on other requests, so all function tables are cleaned up, all file handles are closed etc. In a few rare cases this is not what people like, for that PHP introduced "persistent connections" of different kinds. Testing those is a bit annoying as you have to configure a webserver and ensure to hit the same instance over the course of a test and then use a load generator, probably one which can detect a failure. Additionally by having a webserver in the game there is more code being executed, which might mean an additional source for trouble while debugging. An alternative might be using FastCGI, while that adds it's own issues for such a test.

To solve this for myself I, some time ago, wrote a PHP SAPI module called pconn and pushed it to github. (A SAPI is the component in PHP which implements the communication with the web server or whatever triggers PHP requests) The general idea was to have a lightweight SAPI which does nothing but emulate a bunch of requests. I had it some where on my list of things to blog about, but well, low prio.

Now some time later it seems like Derick was doing some stuff with persistent connections, too, and figured that the new embedded web server is a good thing for such tests, too. While he didn't know about my solution, as one could see in a short discussion on twitter we had:

In other news, the new CLI web server in PHP 5.4 is brilliant for debugging issues with extensions that span more than one request.

— Derick Rethans (@derickr) March 15, 2012

@derickr for that you could also use github.com/johannes/pconn… which also does multithreading

— Johannes Schlüter (@phperror) March 15, 2012

@phperror: You need to write about that stuff :-þ

— Derick Rethans (@derickr) March 15, 2012

Now I've contradicted myself: Above I was proudly writing about this being lightweight and easy to debug, but in the tweet I mentioned threading. And well threading always includes lots of trouble to code. But yeah, over time I figured out that this was a good foundation to solve a second issue which has has to be done for PHP: PHP can be run in threaded environments, which in general is not advised. When doing that the old party rule applies: What happens in a thread stays in thread. Different threads should not impact the requests handled in other threads. Now testing for race conditions is even harder than testing persistent connections and additional web server code hurts even more. So my little SAPI became a lot bigger and can now be compiled in two modes. Either simple and short in non threaded mode or with all the extra stuff in threaded-mode which will allow running PHP requests in parallel threads in loops.

In case you find yourself working on some PHP extensions where this might help: Check the github repository and the README and drop me a line if anything is unclear.

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

So I wrote a mysqlnd plugin.

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

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

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

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

I was writing about PHP.next before, many things improved there meanwhile. Most notably we have a committed version number: The next PHP release will be called PHP 5.4. The topic I want to talk about today is "Improved application portability" which covers multiple small changes which aim at making it simpler for developers to write applications working on any PHP setup.

Separating <?= from short_open_tags

PHP knows quite a few ways to separate PHP code from surrounding text (usually HTML), most applications use <?php as that works on every system. There is a short form of this, <?, which can be disabled using php.ini's short_open_tags setting. Being able to disable this is important when embedding PHP code into XML documents containing XML processing instructions. Now we also have <?= which, basically, is a shortcut for <?php echo. This tag is useful when using PHP as templating language as it prevents cluttered code. The issue in current version of PHP is that this is bound to short_open_tags, so portable applications can't rely on it. But PHP 5.4 will bring the solution: <?= will always be there, independently from short_open_tags. Yay!

No more magic_quotes

In the old times it was easy to write code using PHP.

<?php
$q = mysql_query("SELECT * FROM t WHERE name = '$name' ");
?>

And you had, thanks to register_globals, some data to work on and this was mostly secure as PHP automatically escaped request data. But well this escaping worked only in a few cases acceptable good. Besides not knowing anything about other encodings or DBMS-specific escape sequences it also failed for non-string values as in

<?php
$q = mysql_query("SELECT * FROM t WHERE id = $id ");
?>

Where the external value wasn't escaped. So portable applications, which aim at being secure nowadays have to check whether magic_quote_gpc is enabled, then remove the "bad" quotes and then finally escape again using the appropriate way. That's quite  an annoyance and doing this the wrong way can cause bad bugs (like forcing such a replacement logic in an endless recursion by providing arrays) So nobody really likes magic_quotes. So with PHP 5.4 they are gone. No more need to worry about them. Use the proper escaping and you're done. Wonderful. Only issue: Legacy applications might rely on magic_quotes so when upgrading PHP make sure the application does the required escaping itself so almost-secure applications won't become insecure.

Dropped explicit --enable-zend-multibyte compile-time option

Especially in Asia people use multi-byte encodings which aren't ASCII-compatible so mixing them with PHP code might be hard. In current versions of PHP there is a compile-time option to enable a special multibyte mode for the engine which will handle this in the engine so PHP code can be provided using these encodings. By this portable applications had a hard time due to this conversion (not) being done. Thanks to the work by Dmitry and Moriyoshi this mode is now always enabled whithout penalty for people not depending on it and the extended functionality from mbstring can be provided as a shared module. By this distributors can provide a single build which will work for everybody.

Closing remarks

As always in this series: Be aware that things discussed here might change. Please try out the current snapshot of PHP 5.4 and test it with your applications. No we can still fix backwards compatibility breaks. fixing them after a release will possibly break it for people depending on the new behavior. Happy coding!

Recently PHP 5.4.0 Alpha 1 was released and the PHP development team is asking every PHP user to test it. In this blog I have some articles about upcoming features in that version. Now is a good time to test 5.4 in combination with your applications spot mistakes (complain now if we break compatibility, now we could fix it ...) and a good time to prepare your knowledge.

These are the articles I published here:

• Mind the encoding
• Improved interactive shell
• Jason, let me help you
• Array dereferencing
• No more extension for SQLite 2
• Upload progress

I also have articles on a feature which does not make that release:

• Scalar type hints in PHP trunk
• More on scalar type hints in PHP