请参阅下面的日志. (仅出于简洁目的而被剪切;在http://pastebin.com/k9sCM6Ee时未被剪切)
简而言之:以某种方式为行分配了ID0.即使发生这种情况,它也会阻止插入,即使这些插入实际上并不与ID 0发生冲突(尽管实际上不应首先发生).
尽管该表已被大量读取并非常繁重地插入(每分钟约30万行),但该表从未更新.唯一插入的方法是导致INSERT INTO查询的方法,如下所示.没有外键等.
a)WTF?
b)我该如何解决?
谢谢!
$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,13689,85739,1),4388,100346,1245,[snip];
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
MysqL> SELECT * FROM `visitations` WHERE (`scraping_id`,`visited`) IN ((647,[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`,[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`,[snip];
ERROR 1062 (23000): Duplicate entry '647-196' for key 'index_visitations_on_scraping_id_and_site_id'
最佳答案
您可能遇到了类似以下的错误:
auto increment does not work properly with InnoDB after update