mysql: Duplicate entry '0' for key 'PRIMARY' and bizarre ID behavior

Question

See log below. (Snipped just for brevity; unsnipped @ http://pastebin.com/k9sCM6Ee)

In short: somehow rows are getting assigned ID 0. When this happens, it blocks inserts, even when those inserts aren't actually conflicting with ID 0 (although that really shouldn't happen in the first place).

Although it is heavily read and very heavily inserted (up to ~300k rows/min), this table is never updated. The only method that inserts is the one that results in the INSERT INTO queries like below. There are no foreign keys or the like.

a) WTF? b) How do I fix it?

Thanks!

$ 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'

Solution

You have probably hit a bug like:

auto increment does not work properly with InnoDB after update

You need to track the change history from the release you are using to identify whether fixed bugs can affect you and whether you should upgrade.

MySQL 5.1 Change History