A Little Noise

Category: MySQL

Things related to http://www.mysql.com

  • Messing with LAST_INSERT_ID()

    Time for another MySQL Community Quiz:

    Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

    INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

    The INSERT inserts ’42’ into the table, and the SELECT returns 42.

    So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

    CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();


    You get the auto_increment field, at least in this case:

    mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

    And for a follow up, does it matter if you swap the insertion around?

    INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();


    Nope.

    mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

    The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

  • Access Control Quiz

    First, the setup:

    CREATE TABLE `user` (
  `user` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
);

INSERT INTO `user` 
    (`user`, `host`, `sort`) 
VALUES 
    ('','%',8),
    ('testUser','%',7),
    ('','%localhost',9),
    ('testUser','%localhost',5),
    ('','%localhost%',10),
    ('testUser','%localhost%',6),
    ('','localhost',2),
    ('testUser','localhost',1),
    ('','localhost%',4),
    ('testUser','localhost%',3);

    Now, the quiz:

    SELECT * FROM user ORDER BY ___

    Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.

    +----------+-------------+------+
| user     | host        | sort |
+----------+-------------+------+
| testUser | localhost   |    1 |
|          | localhost   |    2 |
| testUser | localhost%  |    3 |
|          | localhost%  |    4 |
| testUser | %localhost  |    5 |
| testUser | %localhost% |    6 |
| testUser | %           |    7 |
|          | %           |    8 |
|          | %localhost  |    9 |
|          | %localhost% |   10 |
+----------+-------------+------+

  • Capital. Just capital.

    Culled from the Certification Study Guide:

    mysql> SELECT * FROM test;
+----------------+
| data           |
+----------------+
| This is a test |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(data) FROM test;
+----------------+
| UPPER(data)    |
+----------------+
| This is a test |
+----------------+
1 row in set (0.03 sec)

    How’s that work?

     CREATE TABLE `test` (
   `data` varbinary(255) default NULL
 );

    Binary strings are just a list of bytes. They aren’t characters anymore, so there is no upper or lower case. Remember that when choosing between char/varchar/text and binary/varbinary/blob.

  • Octets from INET_ATON

    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.

  • Comparing version output

    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.

  • Drop all tables

    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…)

  • Audit Trail

    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?

  • Here Be Dragons

    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


    Note the definition of map.mapType. In particular, the order of the values.

    Then take a gander at section 3.6 of the manual.

    SELECT 
    locationId,
    m.mapName
FROM 
    locations l
    JOIN map m ON (
        l.continent = m.continent
        OR l.region = m.region
        OR l.country = m.country
    )
    LEFT JOIN map m2 ON (
        (l.continent = m2.continent
        OR l.region = m2.region
        OR l.country = m2.country)
        AND CAST(m.mapType AS unsigned) < CAST(m2.mapType AS unsigned)
    )
WHERE
    m2.mapType IS NULL
GROUP BY 
    locationId;

  • Empty Set Equality

    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?
    MySQL says no.

    +----------+--------+
| equality | result |
+----------+--------+
| 1 = 1    |      1 |
| 1 = 2    |      0 |
| 1 = NULL |   NULL |
+----------+--------+

  • Single-query UNION with LIMIT and ORDER

    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…)