A Little Noise

1Oct/0812

A Parting Shot

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.

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

  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.

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

  4. Using MySQL 5.1

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

  5. I think I got it:

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

  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)

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

  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)

  9. A compressed MyISAM table?

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

  11. hrm, a federated table perhaps?


Leave a comment