A Little Noise

October 1, 2008

A Parting Shot

Filed under: MySQL Gotchas — snoyes @ 11:37 am
mysql> INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM trickyTable;
Empty set (0.00 sec)

That’s odd – what happened to the row?

I have the necessary privileges:

mysql> SHOW GRANTS;
+---------------------------------------------------+
| Grants for root@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

The table exists:

mysql> SHOW TABLES;
+------------------------+
| Tables_in_example      |
+------------------------+
| trickytable            |
+------------------------+
1 row in set (0.00 sec)

The field is there and adequately typed for the value:

mysql> DESCRIBE trickyTable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

Not using BLACKHOLE, and no transaction tricks:

mysql> SELECT engine FROM information_schema.tables WHERE table_name = 'trickyTable';
+--------+
| engine |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)

No triggers to make things tricky:

mysql> SELECT * FROM information_schema.triggers WHERE event_object_table = 'trickyTable';
Empty set (0.00 sec)

Just take my word for it that I’m not using Proxy, and this has nothing to do with replication.

13 Comments »

  1. must have something to do with case.
    insert is to trickyTable
    show tables is trickytable
    or they are two different tables.

    Comment by jd — October 1, 2008 @ 12:30 pm

  2. Nothing to do with identifier case. This happens to be on a Windows box, but on Linux I’d just turn on lower case table names.

    Comment by snoyes — October 1, 2008 @ 12:33 pm

  3. IGNORE is the trick, I think, which causes errors to be treated as warnings. Do ‘SHOW WARNINGS;’ and display the results, please.

    Comment by Don McArthur — October 1, 2008 @ 12:56 pm

  4. Using MySQL 5.1

    set global event_scheduler=1;
    create event e1 on schedule every 1 second do truncate trickytable;

    Comment by Giuseppe Maxia — October 1, 2008 @ 1:06 pm

  5. I think I got it:

    create table trickytable (id int) partition by list (id) (partition p1 values in (2));

    Comment by Giuseppe Maxia — October 1, 2008 @ 1:10 pm

  6. Don: Sure.

    mysql> INSERT IGNORE INTO trickyTable (id) VALUES (1);
    Query OK, 0 rows affected (0.00 sec)

    mysql> SHOW WARNINGs;
    Empty set (0.00 sec)

    Comment by snoyes — October 1, 2008 @ 1:12 pm

  7. Giuseppe: Event scheduler wouldn’t do it – the INSERT statement reported 0 rows affected.

    Comment by snoyes — October 1, 2008 @ 1:15 pm

  8. A blackhole temporary table comes close, but shows 1 row inserted instead of the zero your example shows.

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

    mysql> create temporary table trickyTable (id int) engine=blackhole;
    Query OK, 0 rows affected (0.00 sec)

    mysql> INSERT IGNORE INTO trickyTable (id) VALUES (1);
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT * FROM trickyTable;
    Empty set (0.00 sec)

    (and then your I_S queries pick up the myisam table)

    Comment by ryan — October 1, 2008 @ 2:16 pm

  9. A compressed MyISAM table?

    Comment by XL — October 1, 2008 @ 3:52 pm

  10. Hmm. No. Compressed (=readonly) table gives an error.

    Comment by XL — October 1, 2008 @ 3:57 pm

  11. hrm, a federated table perhaps?

    Comment by Sheeri K. Cabral — October 1, 2008 @ 4:15 pm

  12. Well done, Scott!
    The trick is explained here:
    http://datacharmer.blogspot.com/2008/10/using-partitions-to-create-constraints.html

    Comment by Giuseppe Maxia — October 2, 2008 @ 1:56 am

  13. […] partitions to create constraints A devilish quiz by Scott Noyes has made me thinking about a side effect of partitioning.The quiz Given a table […]

    Pingback by Using partitions to create constraints | Integribase.com — October 2, 2008 @ 3:53 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress