See the log below. (Taken for brevity, unsnipped @ http://pastebin.com/k9sCM6Ee )
In short: somehow, the rows get the assigned identifier 0. When this happens, it blocks the inserts, even if these inserts do not conflict with the identifier 0 (although this really should not happen in the first place).
Although it is highly readable and very heavily inserted (up to ~ 300 thousand rows / min), this table is never updated. The only method that inserts one that results in INSERT INTO queries is as shown below. No foreign keys, etc.
a) WTF? b) How can I fix this?
Thank!
$ mysql --version
mysql Ver 14.14 Distrib 5.1.30, for apple-darwin9.4.0 (i386) using readline 5.1
$ mysql
mysql> SHOW CREATE TABLE visitations \G
*************************** 1. row ***************************
Table: visitations
Create Table: CREATE TABLE `visitations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`scraping_id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`visited` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_visitations_on_scraping_id_and_site_id` (`scraping_id`,`site_id`),
KEY `index_visitations_on_site_id` (`site_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23525407 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show triggers;
Empty set (0.04 sec)
mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> SELECT * FROM `visitations` WHERE (`scraping_id`,`site_id`,`visited`) IN ((647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip]);
Empty set (1 min 27.43 sec)
mysql> select * from visitations where id = 0;
+----+-------------+---------+---------+
| id | scraping_id | site_id | visited |
+----+-------------+---------+---------+
| 0 | 645 | 46177 | 0 |
+----+-------------+---------+---------+
1 row in set (0.00 sec)
mysql> delete from visitations where id < 363;
Query OK, 363 rows affected (0.11 sec)
mysql> select * from visitations where id = 0;
Empty set (0.00 sec)
mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];
Query OK, 500 rows affected (0.23 sec)
Records: 500 Duplicates: 0 Warnings: 0
mysql> select * from visitations where id = 0;
Empty set (0.00 sec)
mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];
ERROR 1062 (23000): Duplicate entry '647-196' for key 'index_visitations_on_scraping_id_and_site_id'