A Little Noise

So you’ve follow Arjen’s suggestion and are storing all your IP addresses in a nice integer format, having converted them with INET_ATON and selecting them with INET_NTOA.

But now you want to use one of the octets, maybe to find out how many connections you get from 192.x.x.x.

One way is to turn the integer back into a string, and use one of the substring functions to grab the parts you need. Let’s try that out for speed (I’ll use 192.168.1.1 in my examples, which translates to 3232235777):

mysql> SELECT BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192');
+-----------------------------------------------------------------------------+
| BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192') |
+-----------------------------------------------------------------------------+
|                                                                           0 |
+-----------------------------------------------------------------------------+
1 row in set (6.28 sec)

Is there a better way? Of course; I’d hardly be writing this blog entry if there weren’t. According to the manual, the calculation for INET_ATON is
192x256^3 + 168x256^2 + 1x256^1 + 1x256^0
Notice all those 256s. 256 is 2^8, so all we have to do is shift the bits to the right by the appropriate multiple of 8, and then throw away anything higher than the 8th bit. We’ll check real quick that gives us the right number:

mysql> SELECT
    ->   (3232235777 >> 24) & 255 AS oct1,
    ->   (3232235777 >> 16) & 255 AS oct2,
    ->   (3232235777 >> 8) & 255 AS oct3,
    ->   (3232235777 >> 0) & 255 AS oct4;
+------+------+------+------+
| oct1 | oct2 | oct3 | oct4 |
+------+------+------+------+
|  192 |  168 |    1 |    1 |
+------+------+------+------+

Looks like the right answer. Let’s see how it compares.

mysql> SELECT BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192);
+-----------------------------------------------------+
| BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192) |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (1.61 sec)

Noticeably faster, and you get to use those bitwise operators that you’ve always wondered about but never had a reason to use.

Features and bugs come and go with each release of MySQL, making it tricky for us hobbyists to help each other out when we don’t have the same version installed and observed behavior is a little different for each.

So I installed a dozen versions of MySQL, ranging from 3.23.58 to the current beta, and wrote a script (download below) to send one stream of SQL to each and compare output. Now I can show how identical SQL produces different results.

There’s no reason they have to be different versions; I might run this script against a bunch of identical slaves to check replication status.

I found some unexpected things along the way (who’d have thought the ON clause was so late to join the syntax?) and some silly trivia that just might win me a book someday (table formatted output differs in justification and whitespace between a few of the versions – can you find where?)

MySQL Demux

Requires the PEAR package Console_Getopt.

More an exercise in stored procedures, triggers, and building queries culled from the information_schema than anything else, here’s an audit trail setup.

Audit trails let you see how data has changed over the course of time, so you know who to blame when something goes wrong.

This particular setup works by adding an AFTER UPDATE trigger to each table to be audited. That trigger looks at the OLD and NEW values of each field, and if there is a difference, inserts a record into the auditLog.

• auditTrail.sql – Creates the table to hold the audit data and the stored procedure which is called to add a trigger to each table you wish to audit.
• setupTest.sql – Creates an example data table such as you might want to audit, and calls the stored procedure to build the trigger for it. The output from that stored procedure should be piped back in to MySQL to actually create the trigger. Why doesn’t it do this automatically? Because prepared statements don’t support CREATE TRIGGER statements as of 5.0.27.
• runTest.sql – Inserts a few rows of data into the example data table, updates them, and displays the changes recorded in the audit table.

Here’s the Windows command line to run the test in the `test` database:
mysql test < auditTrail.sql && mysql -N test < setupTest.sql | mysql --delimiter=$ test && mysql -t test < runTest.sql

For my purposes, watching updates was sufficient. You could extend this to cover inserts and deletes too.

There are a number of drawbacks to this particular method:

• It's a bit unwieldy, since the SQL output has to be piped back in to create the triggers. But hey, if the Wizard can do it, it's ok for us mere mortals too. This could be wrapped in a script. Or maybe it's a good thing, because then you can go edit the output and exclude fields you don't want audited.
• The tigger has to be created for each table separately. Again, handy if there are only a couple tables you want to audit, but a real pain if you wanted to audit all 297 tables in your database.
• If the table structure changes, you'll need to drop and recreate the trigger.
• The stored procedure assumes the current database. Again, something trivial to change.
• Hope you didn't want to use your AFTER UPDATE trigger for something else.

My next trick will be to add something so that we know which user made the change. Not which MySQL user, mind you, since these changes are issued from PHP scripts which all run as the same MySQL user. Somehow I need to pass in my application user id. Any suggestions?