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.
October 1st, 2008 - 12:30
must have something to do with case.
insert is to trickyTable
show tables is trickytable
or they are two different tables.
October 1st, 2008 - 12:33
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 1st, 2008 - 12:56
IGNORE is the trick, I think, which causes errors to be treated as warnings. Do ‘SHOW WARNINGS;’ and display the results, please.
October 1st, 2008 - 13:06
Using MySQL 5.1
set global event_scheduler=1;
create event e1 on schedule every 1 second do truncate trickytable;
October 1st, 2008 - 13:10
I think I got it:
create table trickytable (id int) partition by list (id) (partition p1 values in (2));
October 1st, 2008 - 13:12
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 1st, 2008 - 13:15
Giuseppe: Event scheduler wouldn’t do it – the INSERT statement reported 0 rows affected.
October 1st, 2008 - 14:16
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 1st, 2008 - 15:52
A compressed MyISAM table?
October 1st, 2008 - 15:57
Hmm. No. Compressed (=readonly) table gives an error.
October 1st, 2008 - 16:15
hrm, a federated table perhaps?
October 2nd, 2008 - 01:56
Well done, Scott!
The trick is explained here:
http://datacharmer.blogspot.com/2008/10/using-partitions-to-create-constraints.html