A Little Noise

August 19, 2008

Two for the price of one

Filed under: MySQL Gotchas — snoyes @ 11:20 am

Haven’t done any sneaky puzzles in a while. How would you accomplish this?

mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT(*) FROM t1;
| COUNT(*) |
|        2 |
1 row in set (0.00 sec)


  1. Using Proxy?


    Comment by Giuseppe Maxia — August 19, 2008 @ 12:01 pm

  2. Maybe the size of int (type) is 2. :)

    Comment by cett — August 19, 2008 @ 12:07 pm

  3. Giuseppe: Proxy could do it, but I’m not using it.

    cett: It isn’t, and that wouldn’t give this output anyway.

    Comment by snoyes — August 19, 2008 @ 12:20 pm

  4. Ooh, ooh Stored function or UDF


    RETURN 2;

    which would work for

    SELECT COUNT(‘*’) FROM t1;

    But I think you might need a real UDF to get it to work without the single quotes.

    Comment by Bill Newton — August 19, 2008 @ 12:58 pm

  5. Create a trigger on insert that inserts another row.

    Comment by James Bathgate — August 19, 2008 @ 2:02 pm

  6. Before you issue those commands, create a temporary table t1 (doens’t matter what format) which contains 2 rows.

    Comment by ryan — August 19, 2008 @ 2:07 pm

  7. Trigger?

    Comment by Jeremy — August 19, 2008 @ 2:21 pm

  8. using a trigger? :)

    Comment by Tommaso — August 19, 2008 @ 2:50 pm

  9. Damn, this one has me puzzled. Does the default storage engine have anything to do with it? You’re not doing anything with funny combinations of lowecase L and 1 typographical confusion as far as I can see…

    I’ve rather missed this series, and this one is really good.

    Comment by Xaprb — August 19, 2008 @ 2:55 pm

  10. CREATE TRIGGER myTrigger

    Comment by bob — August 19, 2008 @ 5:11 pm

  11. Having a temporary table ‘t1’ already?

    Comment by Jedy — August 19, 2008 @ 7:03 pm

  12. A temporary table named t1 that already contained one row could do it. It’s not what I used, but it would work, so I’ll give full marks for that answer.

    A trigger would not work – you cannot use a trigger to insert into the table that launched it.

    Overriding the COUNT function would not work – if you create a function with the same name as a built-in function, you must call it with a space between COUNT and (, and there isn’t one in the test case. I imagine a UDF could handle the unquoted *, but I think you’d still need the space. I guess you could go edit the source, but I think we’ll all agree that’s cheating.

    Storage engine doesn’t matter (except it’s not BLACKHOLE). I have not used any typographical tricks.

    Comment by snoyes — August 19, 2008 @ 8:03 pm

  13. Something along the lines of this ??

    mysql> create table t2(id int) engine=myisam;
    Query OK, 0 rows affected (0.24 sec)

    mysql> create table t1(id int) engine=merge union(t2) insert_method=last;;
    Query OK, 0 rows affected (0.07 sec)

    mysql> delimiter //
    mysql> create trigger tricky after insert on t1
    -> for each row
    -> insert into t2 values (1)//
    Query OK, 0 rows affected (0.01 sec)

    mysql> select count(*) from t1;
    -> //
    | count(*) |
    | 0 |
    1 row in set (0.00 sec)

    mysql> delimiter ;
    mysql> insert into t1 values (1);
    Query OK, 1 row affected (0.00 sec)

    mysql> select count(*) from t1;
    | count(*) |
    | 2 |
    1 row in set (0.00 sec)

    Comment by Tom Hanlon — August 20, 2008 @ 8:16 pm

  14. Tom: Very clever. Not what I did, but it works. In the comments I mentioned that storage engine doesn’t matter, but that wasn’t part of the original puzzle – so full marks to you too. Though your solution makes me wonder if it ought to behave that way – should a trigger on the base table of a MERGE fire when a row is inserted in this fashion?

    To repeat what I did, you must become master of your own destiny.

    Comment by snoyes — August 20, 2008 @ 8:52 pm

  15. Elementary, my dear, though your clue makes it too easy.

    Your database is either set to slave off of itself, or it is slaving off of a slave (with log-slave-updates enabled) of itself (or some combination thereof)… in any case, you set replicate-same-server-id, so your db is re-executing the statement it just wrote to its own binlog :)

    Comment by Mike Baehr — August 20, 2008 @ 9:03 pm

  16. Mike: That is what I did. You also have to instruct the slave to skip errors, so you don’t have a problem when creating the table.

    Comment by snoyes — August 20, 2008 @ 10:51 pm

  17. Snoyes,

    You said.. “it makes me wonder if it should work that way ” in regards to the trigger on a base table in a merge.

    In my opinion.. it depends :-)

    Somedays I feel like this is a bug, some days I feel like the insert_method feature of merge tables is the bug, sometimes I just don’t worry about it :-)

    Comment by Tom Hanlon — August 21, 2008 @ 9:01 pm

  18. While peeling an apple, the answer came to me. So I rushed to my computer and came back to post the answer. And then I saw that someone had already gotten it (slave of self with shoot-my-foot-off settings). Woe is me, I am not the frist psoter. Will I ever recover? If I don’t, it’s your fault.

    Back to my apple, now.

    Comment by Xaprb — August 27, 2008 @ 12:21 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress