title: mysql: Duplicate entry '0' for key 'PRIMARY' and bizarre ID behavior
body: <p>See log below. (Snipped just for brevity; unsnipped @ <a href="http://pastebin.com/k9sCM6Ee" rel="nofollow noreferrer">http://pastebin.com/k9sCM6Ee</a>)</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>a) WTF?
  b) How do I fix it?</p>
  
  <p>Thanks!</p>
  
  <pre><code>$ mysql --version
  mysql  Ver 14.14 Distrib 5.1.30, for apple-darwin9.4.0 (i386) using readline 5.1
  
  $ mysql
  
  mysql&gt; 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&gt; show triggers;
  Empty set (0.04 sec)
  
  mysql&gt; 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&gt; 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&gt; select * from visitations where id = 0;
  +----+-------------+---------+---------+
  | id | scraping_id | site_id | visited |
  +----+-------------+---------+---------+
  |  0 |         645 |   46177 |       0 | 
  +----+-------------+---------+---------+
  1 row in set (0.00 sec)
  
  mysql&gt; delete from visitations where id &lt; 363;
  Query OK, 363 rows affected (0.11 sec)
  
  mysql&gt; select * from visitations where id = 0;
  Empty set (0.00 sec)
  
  mysql&gt; 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&gt; select * from visitations where id = 0;
  Empty set (0.00 sec)
  
  mysql&gt; 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'
  </code></pre>