A Parting Shot

Written by

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

13 responses to “A Parting Shot”

October 1, 2008

jd

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

snoyes

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.
October 1, 2008

Don McArthur

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

Giuseppe Maxia

Using MySQL 5.1

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

Giuseppe Maxia

I think I got it:

create table trickytable (id int) partition by list (id) (partition p1 values in (2));
October 1, 2008

snoyes

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)
October 1, 2008

snoyes

Giuseppe: Event scheduler wouldn’t do it – the INSERT statement reported 0 rows affected.
October 1, 2008

ryan

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)
October 1, 2008

XL

A compressed MyISAM table?
October 1, 2008

XL

Hmm. No. Compressed (=readonly) table gives an error.
October 1, 2008

Sheeri K. Cabral

hrm, a federated table perhaps?
October 2, 2008

Giuseppe Maxia

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

Using partitions to create constraints | Integribase.com

[…] 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 […]