A Little Noise

March 2, 2007

Octets from INET_ATON

Filed under: MySQL — snoyes @ 6:02 pm

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.

November 28, 2006

Comparing version output

Filed under: MySQL — snoyes @ 6:46 pm

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.

November 10, 2006

Drop all tables

Filed under: MySQL FAQ — snoyes @ 10:06 pm

Since there exists neither DROP TABLE * nor DROP TABLE WHERE name LIKE ‘something’, here are two ways to achieve the same:

For version 5.0+:

mysql -N -e "SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') FROM information_schema.tables WHERE table_schema = 'dbName'" | mysql dbName

(more…)

November 9, 2006

Audit Trail

Filed under: MySQL — snoyes @ 8:14 pm

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?

October 6, 2006

Here Be Dragons

Filed under: MySQL Gotchas — snoyes @ 8:09 pm

I have a table of maps:

CREATE TABLE map (
    mapName varchar(255),
    mapType ENUM('continent', 'region', 'country'),
    continent varchar(255) NULL,
    region varchar(255) NULL,
    country varchar(255) NULL
);
INSERT INTO map VALUES 
    ('Europe', 'continent', 'Europe', NULL, NULL),
    ('North America', 'continent', 'North America', NULL, NULL),
    ('Northern Hemisphere', 'region', NULL, 'Northern Hemisphere', NULL),
    ('Sweden', 'country', NULL, NULL, 'Sweden'),
    ('Mexico', 'country', NULL, NULL, 'Mexico');

And a table of locations:

CREATE TABLE locations (
    locationId int auto_increment primary key,
    continent varchar(255),
    region varchar(255),
    country varchar(255)
);

INSERT INTO locations VALUES 
    (NULL, 'Europe', 'Northern Hemisphere', 'Sweden'),
    (NULL, 'North America',  'Northern Hemisphere', 'Canada'),
    (NULL, 'North America',  'Pacific Northwest', 'USA');

Obviously, this example is denormalized – in the real world, I’d use a surrogate key and store continentId, regionId, and countryId. Even then, it’s not the ideal layout for this application. But that’s just part of the challenge.

Here’s the goal: find the most specific map for each location. By most specific, I mean use a country map if available. If not, use a region map. Use a continent map as a last resort.

So, the result set should be:

+------------+---------------------+
| locationId | mapName             |
+------------+---------------------+
|          1 | Sweden              |
|          2 | Northern Hemisphere |
|          3 | North America       |
+------------+---------------------+

Bonus points if it works pre-4.1

Hint ▼

Show Answer ▼

September 27, 2006

Empty Set Equality

Filed under: MySQL Gotchas — snoyes @ 3:48 pm 
SELECT 1 LIMIT 0;
Empty set (0.00 sec)

SELECT 2 LIMIT 0;
Empty set (0.00 sec)

SELECT NULL LIMIT 0;
Empty set (0.00 sec)

Exactly what I’d expect. So here’s the question:

SELECT '1 = 1' AS equality, (SELECT 1 LIMIT 0) = (SELECT 1 LIMIT 0) AS result
UNION SELECT '1 = 2', (SELECT 1 LIMIT 0) = (SELECT 2 LIMIT 0)
UNION SELECT '1 = NULL', (SELECT 1 LIMIT 0) = (SELECT NULL LIMIT 0);

What do you think? Are all empty sets created identical?
Show Answer ▼

September 12, 2006

Single-query UNION with LIMIT and ORDER

Filed under: MySQL — snoyes @ 9:20 pm

I have a query that behaves differently in every version of MySQL I’ve tried. Could you do me a favor? Run the following four lines and post a comment with your results. And if you know of a particular bug fix that addresses this issue, let me know about that too.

CREATE TABLE `test` (`id` tinyint);
INSERT INTO test VALUES (1), (2), (3), (4), (5);
SHOW VARIABLES LIKE 'version%';
(SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;

(more…)

August 31, 2006

Auto_increment Indexing

Filed under: MySQL FAQ — snoyes @ 5:15 pm

It’s so common to set the auto_increment column to primary key, it has become common belief that it’s required. Not true; an auto_increment column can use any index type. It doesn’t even have to be unique:

CREATE TABLE testTable (
  id INT AUTO_INCREMENT,
  INDEX(id)
) ENGINE=MyISAM; -- Works for InnoDB too.

INSERT INTO testTable VALUES (1), (1), (NULL);

SELECT * FROM testTable;

+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
+----+

August 23, 2006

Order By Puzzle

Filed under: MySQL Gotchas — snoyes @ 2:30 pm

From a question on Freenode.

SELECT * FROM theTable;
+----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

Fair enough. Let’s order it by `data`.

SELECT * FROM theTable ORDER BY data;
+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

What??? Maybe the server didn’t quite understand.

SELECT * FROM theTable ORDER BY data ASC;
+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

Show Answer ▼

August 22, 2006

LAST_INSERT_ID and multi-row inserts

Filed under: MySQL Gotchas — snoyes @ 8:43 pm

A bit of a quiz to see if you’re paying attention:

First, we’ll set up a couple tables with some auto increment fields:

CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));

Next, we’ll insert a row to one table:

INSERT INTO t1 VALUES (NULL);

Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():

INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);

What happens here? Did the insert go ok, or did we get an error? What happens if we now issue

SELECT * FROM t2;

Show Answer ▼

« Newer Posts — Older Posts »

Powered by WordPress