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!
Wednesday, November 5. 2008 at 13:22 (Link) (Reply)
is it already decided that at some point the stream will be exported into PHP userland? If yes, will this feature come with php 5.3 or will it arrive later, i.e. bound to mysqlnd's own release cycle (independend on php releases)?
Thanks,
Tuesday, November 25. 2008 at 10:50 (Link) (Reply)
Monday, December 8. 2008 at 18:36 (Reply)
Sunday, June 21. 2009 at 01:32 (Link) (Reply)
Specifically, if storing a large email message in a database, I'd like to be able to process it as a stream to parse out the mime parts, and maybe only keep the smaller text part while discarding the attached images. While doing so, it would also be nice to only have one copy in memory to minimize the memory used.
Sunday, June 21. 2009 at 03:53 (Reply)
Sunday, June 21. 2009 at 05:23 (Link) (Reply)