Johannes Schlüter

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

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

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:

Modular software can be pain - you end up installing tons of add-opns. I recently configured a new desktop box and had to reconfigure Firefox. Here's the lsit of extensions I installed, I'd be interested in comments about better or missing add-ins

•  Adblock Plus
  This is a tricky one. I get content for free for looking at some ads. Might be a fair deal, but often it's too much. On some sites I allow ads, on others not.
• BetterPrivacy
  Prevent Flash Cookies from tracking me
• Brief
  An RSS/Atom/... Feed reader
• Certificate Patrol
  Notifies me when sites change their SSL certifactes, might prevent fraud.
• Change Rerer Button
  I don't like being tracked. At least not always
• CookieSafe
  Firefox's cookie handling is damn limited. With this add-in I can edit/delete single cookies way simpler and easily change the per site settings
• Firebug
  Good for fixing broken sites
• FoxyProxy Standard
  When switching between company VPN and my local net I need different proxy configuration. With this extension I don'T have to go through the settings
• Greasemonkey
  Improve websites according to my interest
• JSON View
  Renders json more nicely. Good when working with JSON-based protocols
• NoScript
  per-site configuration of javascript and stuff. Some websites have nice JS free versions.
• Open in Browser
  Adds a new option to the download dialog to open in Browser as web page or plain text. Useful for files a server sends with a "wrong" header.
• Tab Kit
  The wider the screen the more I want to have the tabs on the side. This add-in shows tabs in a sidebar with a tree structure and different colors and such
• Tamper Data
  Edit HTTP Request before the browser sends them, initially I've got this for security research, nowadays I use it to work-around issues in bad WEb-UIs
• User Agent Switcher
  Maybe switching the User agent every now and then makes tracking a bit harder, certainly it can be nice to get a different view. Many sites have a mobile version which is focussed on the content way mor than the regular site.
• Web Developer
  Nice tool for working with HTML stuff
  

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.

File uploads via HTTP are an annoyance. Web Browser know quite a lot but still the give little feedback to the users. Some a bit more, most close to no feedback. Now over the years this led to man unhappy users. Over the last year, with all these AJAX things, solutions emerged so that one can periodically poll the web server on a second connection for the status. For implementing this we have one architectural problem: PHP implements, for very good reasons, a shared nothing architecture. So one request from connection has no insight into another request/connection - but this is needed for the upload progress. Different people thought about this and implemented solutions. One of the first things was implemented in APC, another one by a special uploadprogress extension. They are nice and found quite some adoption but they have two problems. For one they are not fully native to PHP, so they have to be installed additionally, and the use a local storage to transport the status. APC uses the system's shared memory. upload_progress the filesystem (yes, file systems could be shared, I do know that). not very satisfying for having PHP as the language for solving the web problem.

The obvious solution, of course, would be to use PHP's session handling system for this. The PHP session system is an integral part of PHP and can be configured to use different storage handlers, like the local file system or memcache, which can be useful to share session data in a load-balanced cluster. Now there were some technical issues why this wasn't done at first ... but then Arnaud Le Blanc sat down and created a proper implementation of an upload progress storage handler which has been commit to PHP trunk.

Long story short: In the next version of PHP (5.4?) you will, mot likely, have an Upload Progress mechanism built-in.

Arnaud wrote a nice RFC explaining this functionality. So we configure our PHP to enable this feature, by making sure we have the default values:

session.upload_progress.enabled = 1 
session.upload_progress.prefix = upload_progress_
session.upload_progress.name = PHP_SESSION_UPLOAD_PROGRESS

And we are set to go. Then we obviously need an HTML file upload form:

<form action="upload.php" method="POST" enctype="multipart/form-data">
 <input type="hidden" name="<?php echo ini_get("session.upload_progress.name"); ?>" value="johannesupload" />
 <input type="file" name="file1" />
 <input type="file" name="file2" />
 <input type="submit" />
</form>

And we can upload a file. If the file is big enough (and the connection slow enough) we can then periodically poll the server and read and read data about the progress from the $_SESSION["upload_progress_johannesupload"] variable. The full contents is mentioned in the RFC, so I won't quote it here.

So far so good. But how to poll? Well, take the sample by Rasmus about the above mentioned APC-based solution and adopt it. I let this to the experienced reader as exercise

Disclaimer: This article is describing features in not released software. Things may change without notice till it is release. Feel free to read other blog postings from my series of new features!

P.S. I still think the browser should give better feedback by itself. As should it offer better integration of HTTP-Auth, like a simple logout button ...