A Little Noise

Related to that last quiz. Here’s the SHOW CREATE TABLE output – I’ve only cleaned up the whitespace.

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (100) 
    ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN MAXVALUE 
    DATA DIRECTORY = '~/mysql/data/' 
    ENGINE = MyISAM
) */

Now, pay close attention to the rows inserted:

mysql> INSERT INTO t1 VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Ok, here’s the tricky bit:

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

No triggers, no BLACKHOLE, no Proxy, no events or other scheduled tasks, no other connections deleting rows, no temporary tables, and I have all the necessary privileges (including to the data directory shown, which exists and isn’t full). It’s all MyISAM, so surely no transactions. No errors or warnings have been reported.

When you figure it out, don’t do it in your production environment. Bad things happen.

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.