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.
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
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
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
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
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
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
Giuseppe: Event scheduler wouldn’t do it – the INSERT statement reported 0 rows affected.
Comment by snoyes — October 1, 2008 @ 1:15 pm
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
A compressed MyISAM table?
Comment by XL — October 1, 2008 @ 3:52 pm
Hmm. No. Compressed (=readonly) table gives an error.
Comment by XL — October 1, 2008 @ 3:57 pm
hrm, a federated table perhaps?
Comment by Sheeri K. Cabral — October 1, 2008 @ 4:15 pm
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
[…] 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