Johannes Schlüter

Sometimes one has weird ideas, or am I the only one? - This specific one is at least a year old, now, during the Christmas days, waiting for New Year's Eve I had the time and mood to finally try it out: MySQL 5.1 has a plugin interface to easily add storage engines. PHP can easily embedded into other applications.  So why not combine these two things? - Writing a MySQL Storage Engine which reads data by calling a PHP script.

Let's start with a simple example first:

<?phpfunction create_table($table, $data) {
    return true;
}

function open_table($table) {
    return new ArrayIterator(array(
        array('id' => 1, 'dat' => 'foo'),
        array('id' => 2, 'a' => 'bar')
    ));
}
?>

This is the bare minimum storage engine my plugin supports. create_table() is called for creating the table, open_table() to access it, the later one then returns an iterator which is used for a full table scan. This example uses an ArrayIterator, which implements the SeekableIterator and the Countable interfaces, the first one provides a seek() method, which is called to read specific rows after sorting for instance, the later provides a method count() which gives the optimizer a hint.

Let's use this table:

mysql> CREATE TABLE php_test (id int, val CHAR(3)) ENGINE=PHP;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM php_test;
+------+------+
| id   | val  |
+------+------+
|    1 | foo  | 
|    2 | bar  | 
+------+------+
2 rows in set (0.00 sec)

Ok, of course that's nice and shiny but well, it's read only. To solve that you can implement a few interfaces provided by the plugin to handle writes:

<?php
class Test extends ArrayIterator 
implements MySQLStorage_Writable, MySQLStorage_Updatable, MySQLStorage_Deletable {
    public function write($data) {
        $this[] = $data;
    }

    public function update($data) {
        $this[$this->key()] = $data;
    }

    public function delete() {
        unset($this[$this->key()]);
    }
}

function create_table($table, $data) {
    return true;
}

function open_table($table) {
    return new Test(array(
        array('id' => 1, 'dat' => 'foo'),
        array('id' => 2, 'a' => 'bar')
    ));
}
?>

Again, we can test it:

mysql> UPDATE php_test SET val = 'baz' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DELETE FROM php_test WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO php_test VALUES(3, 'bar');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM php_write;
+------+------+
| id   | val  |
+------+------+
|    1 | baz  | 
|    3 | bar  | 
+------+------+
2 rows in set (0.00 sec)

As a reminder: This is calling PHP for all these operations.

So what might real life use cases be, once the major issues in the code are fixed? I have a few ideas like

• A live-logfile query tool, not sure that's really need, see the primitive Apache httpd access_log parser which is provided with the code as an example
• Combine it with the embedded MySQL server and use this storage engine for your unit tests, "mock tables" ...

Any other ideas? - Leave a comment

Oh, and like most MySQL stuff nowadays: There's a launchpad project for this plugin.

NetBeans 6.5 is soon to be released. After 10 years of NetBeans that's the first version of Sun's OpenSource IDE featuring PHP support. While 6.5 is waiting to be packaged the development didn't stop and the first features for the successor, NetBeans.next, are already being developed. David Van Couvering just showed a preview of a cool new feature: SQL completion in PHP strings, if it does what the screenshot promises that's a damn great addition in my opinion....

Ever wondered what your PHP application and MySQL actually do? An experimental mysqlnd branch will give you full access to the network communication stream. Using a custom PHP stream filter you can then intercept the communication ... but let's start at the beginning:

When talking about mysqlnd - the mysql native driver for PHP - we always mention the fact it's native in a way that we're, when possible, using PHP infrastructure. The most common example here is the memory management. By directly using PHP's memory we can avoid unnecessary copies of data from the MySQL Client Library's memory into PHP memory.

<?php
$mysqli = mysqli_connect("localhost", "root", "", "test");
$stream = mysqli_conn_to_stream($mysqli);
stream_filter_append($stream, "mysql.server", STREAM_FILTER_READ);
?>

But there's more what we're doing. We're also using PHP's stream abstraction layer. From a development perspective the benefit is that we're using a tested abstraction from different stream implementations by different operating systems instead of writing our own. But, again, there's more to it: We can export the communication stream to PHP userland. We hesitated about exporting it for some time as it can be quite dangerous and you might easily corrupt the client-server- communication.

As Ulf mentioned during his IPC talk I recently pushed a mysqlnd branch to launchpad which adds a userspace function to mysqli which returns a PHP stream for a connection. Using that stream you can now send your own requests to the server and wait for the response. That might be nice in a way, but I guess you most likely won't have use for that. PHP streams allow you to do more: PHP streams give you the possibility to add filters to a stream. These filters allow you to intercept packages which are sent or received , read them, change them or do whatever you like. A very simple filter can be found on the launchpad site, mentioned above. That filter simply prints the information after replacing unprintable (binary) characters by dots.

Once again: Just a small step, the next one is to decode the MySQL protocol. For that I've written a simple decoder for the MySQL protocol, not complete, but enough to give an idea. The script, including the decoder and some sample code using it, is, as a sample, part of the branch. When running you will get some output like

Query:
-> 0 59: QUERY: SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES LIMIT 1
<- 1 1: DATA
<- 2 52: FIELD INFO
<- 3 5: EOF
<- 4 19: DATA
<- 5 5: EOF
Invalid Query:
-> 0 29: QUERY: ghdfjtgfdrs tztr ttgdszthtdr
<- 1 183: ERROR: 2000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ghdfjtgfdrs tztr ttgdszthtdr' at line 1
Prepare:
-> 0 61: PREPARE: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.STATISTICS LIMIT 2
<- 1 12: OK
<- 2 52: FIELD INFO
<- 3 5: EOF
Ping:
-> 0 1: PING
<- 1 7: OK
Execute:
-> 0 11: EXECUTE
<- 1 1: DATA
<- 2 52: FIELD INFO
<- 3 5: EOF
<- 4 15: OK
<- 5 15: OK
<- 6 5: EOF
EOF
-> 0 5: CLOSE_STMT
-> 0 1: QUIT

As one can see: The protocol isn't fully decoded yet so this all might be extended but for me it served the purpose well enough. For making real use out of this we're thinking about exporting the protocol decoder which exists within mysqlnd to PHP userland.

What are your ideas for such a feature? - Sending different queries to different servers? Rewriting queries? Sharding? Replication? Easy scaling of your application while refactoring your application? Let us know!

Angelo recently showed an easy way to dump SQL queries using DTrace, while reading the articles I felt that some important information is missing: The name of the user executing the query and the selected database. So I sat down a few minutes and tried to collect that data.

For the database name I found a quite simple solution: It is passed as parameter to the check_user() function to MySQL so we can easily add a thread-local variable to keep that name. Simple script for that:

#!/usr/sbin/dtrace -s

#pragma D option quiet

pid$1::*check_user*:entry
{
    self->db = arg4 ? copyinstr(arg4) : "(no schema)";
}

pid$1::*dispatch_command*:entry
{
    printf("%s: %s\n", self->db, copyinstr(arg2));
}

Getting the username is a bit harder, for the PID-provider we need a function where it is passed as parameter, best would be if it was passed as char*, but there's no such function reliably called. (For instance there are functions for checking max connections per user where all we need is passed in a proper way, but it's only called when a limit was set) The only way would be to take the user_connect property of the THD object which is passed to dispatch_command and then access the username (and hostname). But getting that working from within DTrace is quite some work. I prepared some scripts doing this with simple C structures for the second part of my DTrace article series, which is ready in my head and is waiting to be typed, so in theory it should be possible, anybody wants to try?

The best solution, of course, would be to add proper probes into the MySQL server code which provide all that information.

End October - time for the traditional meeting of the German and International "PHP family" - end of October? - Yes, the International PHP Conference is a bit earlier this year. Additonally the organizers moved the conference away from the industrial area of Mörfelden to the center of Mainz which sounds quite promising. Although I'll spend only around 24hrs at Mainz I'm looking really forward to the conference next week.

On Thursday morning I'll give a presentation about PHP 5.3, which will be quite interesting as one of the biggest features, namespaces, is still undergoing heavy discussions and the final syntax probably won't be clear when presenting - fortunately PHP 5.3 is much more than namespaces!

Sun will also be present at the conference, so if you're looking for an open source PHP IDE you might talk to Petr and Wen about the upcoming NetBeans 6.5 release which will feature PHP support, if you're running a startup company you might talk to Stefan Schneider who will represent the Startup Essential Program which has interesting discounts on Sun products.

Unfortunately I won't be able to attend Brian Aker's keynote about Drizzle or Ulf's session about new and hot stuff in mysqlnd, one of the new feature in PHP 5.3.

In January Sun announced to buy MySQL, today the deal was closed and MySQL is now part of probably the worlds biggest Open Source company. For a full coverage visit mysql.com, Kaj's Blog or Sun's new product site. I'll continue with my tasks now.

mit fast einem Monat Verspätung habe ich gestern mal mein Profil bei Xing aktualisiert:

Since a few hours we're having, thanks to Edin, Windows snapshots for the upcoming PHP 5.3 release. In combination with the latest sources from CVS everybody should be able to test the current state.

There are quite a few new features already committed to the CVS tree, some of them are documented in the NEWS file and READMEs in CVS, most of them have been commented on Planet PHP so I suggest searching there for more information and keep this list short:

• Namespaces: A way to help you organizing your code.
• Late Static Binding: Gives you the class name used when calling a static method.
• Dynamic static calls: $c = "classname"; $c::someMetod();
• Improved ini-handling: .htaccess file like per-directory configuration and much more
• __callStatic magic method: similar to __call but for static calls
• mysqlnd: a replacement for libmysql for better PHP-MySQL-Integration
• getopt() on any platform (inclding windows)
• ...

As you can see there's lots of stuff which needs testing! The earlier bugs are found the earlier they can be fixed! In case you ever wondered how you could give back something to PHP (which you certainly should do) you might take a look at our test coverage reports to see which areas of PHP need more testing and provide additional tests. (See qa.php.net and Marcus's slides for an introduction to test writing) Having more tests is important for us - and you don't have to know much about PHP internals to write proper tests (sometimes it's even better to write black-box tests...)

But well, there's not only PHP 5.3: If you're interested in unicode you should certainly take a look at PHP 6 and if you're running older systems you should consider upgrading to the latest 5.2 release instead of waiting for PHP 5.3 - the stable release is not expected within this year and many things there can change till it's released.

And as a final note: Test the snaps as often as you can - generic tests can never cover all use-cases and - as said above - fixing regressions is harder after a release than during development. (And be warned: We won't take any complaints from people not testing in time)

Ilia recently published the probably final release candidate of the current stable tree: PHP 5.2.4RC2. The stable release can be expected soon. This release fixes quite some bugs from older versions and does a few minor adjustments. Please test the RC release so unexpected regressions can be detected and fixed before it's marked stable. If regressions and other problems aren't detected now it will take a few months till the next version will be released, so do your best to make sure it's worth being called stable! Please file found issues, after checking for other reports and the documentation, into the bug tracker. The recent trend to report bugs using blogs isn't working well!

In other news one could read that there were also some major improvements in the development of PHP 6 made: Next to all that Unicode related work which was made over the last months PHP got, due to Andrey, mysqlnd as PHP-optimized replacement for libmysql and, thanks to Dmitry's work, namespace support. So this weekend might be the perfect time for sending you're family visting other parts of the family, letting them sweat on some beach or having fun at some theme park - then you can sit down, relax, and, after testing PHP 5.2.4RC2, give that new stuff in PHP 6 a test.

For getting started with PHP 6, which you can get, as you're hopefully used to, from the snaps site, you should first read Andrei's slides about Unicode and PHP 6. The most important Unicde+PHP facts are also collected inside a README file. For namespaces there's, again, a README file, David Coallier has written a nice summary and Stanislav wrote a short FAQ. If you are curious what mysqlng is: simply read Ulf's blog postings or Andrey's slides.