不存在重复项时出现MySQL重复项错误 [英] MySQL duplicate entry error when no duplicate exists

问题描述

无论我尝试插入什么数据，我似乎都会在每次插入时收到下面列出的错误.所以也许我的桌子坏了还是什么?无论如何，这是我的问题:

I seem to get the error listed below on every insert no matter what data I try to insert. So maybe my table was corrupted or something? Anyway, here's my question:

我有一个MySQL表

CREATE TABLE `AcpConfig` (
  `ndss_id` int(11) NOT NULL default '0',
  `acp_id` int(11) NOT NULL default '0',
  `run_date` date NOT NULL default '0000-00-00',
  `hw_5_threshold` tinyint(1) NOT NULL default '0',
  `stp_on` tinyint(1) NOT NULL default '0',
  `sort_on` tinyint(1) NOT NULL default '0',
  `afcs_ocr_message_format` tinyint(1) NOT NULL default '0',
  `use_hw` tinyint(1) NOT NULL default '0',
  `test_mode` tinyint(1) NOT NULL default '0',
  `afcs_version` varchar(255) NOT NULL default '',
  `acp_build` varchar(255) NOT NULL default '',
  `id` int(11) NOT NULL auto_increment,
  `swstp_in_acp_rack` int(11) NOT NULL default '0',
  `acplookup_id` int(11) NOT NULL default '0',
  `bfind_cksum` varchar(255) NOT NULL default '',
  `tz_cksum` varchar(255) NOT NULL default '',
  `fetched` varchar(4) NOT NULL default '"NO"',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ndss_id` (`ndss_id`,`acp_id`,`run_date`),
  KEY `ndss_acp` (`ndss_id`,`acp_id`),
  KEY `ndss_acp_rundate` (`ndss_id`,`acp_id`,`run_date`),
  KEY `run_date` (`run_date`),
  KEY `acplookup_rundate` (`acplookup_id`,`run_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

它大约有50万行.我正在尝试执行一个简单的INSERT

And it's got about half a million rows. I'm trying to perform a simple INSERT

INSERT INTO AcpConfig (ndss_id, acp_id, run_date, hw_5_threshold, stp_on, sort_on, afcs_ocr_message_format, use_hw, test_mode, afcs_version, acp_build, swstp_in_acp_rack, acplookup_id, bfind_cksum, tz_cksum) VALUES ('75', '5', '2009-07-22', '75', '1', '1', '0', '1', '0', '1.5.2', '041709', '2', '269', '0', '1950359846');

它给了我错误

ERROR 1062 (23000): Duplicate entry '502831' for key 1

表示我违反了对ndss_id，acp_id和run_date这三个字段的唯一约束. (ID 502831不是我的表中的行，并且似乎是如果已插入该行时将使用的下一个ID.)问题是，如果我针对具有相同值的那些字段进行选择

which implies that I'm violating my UNIQUE constraint on the three fields ndss_id, acp_id, and run_date. (The id 502831 is not a row in my table and seems to be the next id that would be used if the row had been inserted.) The problem is, if I SELECT against those fields with the same values

select * from AcpConfig where ndss_id=75 and acp_id=5 and run_date='2009-07-22';

然后不返回任何结果.因此，我实际上并没有复制任何内容.我的其他键只是索引，而不是唯一约束.正如我从CREATE TABLE语句中看到的那样，我还有一个UNIQUE约束.那为什么告诉我我有重复的东西?

then it returns no results. So I'm not actually duplicating anything. My other keys are all just indexes and not unique constraints; I also have the one UNIQUE constraint as you can see from my CREATE TABLE statement. So why is it telling me that I have a duplicate?

推荐答案

显然该表已损坏.我运行CHECK TABLE并看到一些损坏错误，然后运行REPAIR TABLE，它似乎可以正常工作.之后我的INSERTS又开始工作了.

Apparently the table was corrupted. I ran CHECK TABLE and saw some corruption errors and then ran REPAIR TABLE and it seemed to work; afterward my INSERTS started working again.

mysql> check table AcpConfig;
+---------------+-------+----------+----------------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                                 |
+---------------+-------+----------+----------------------------------------------------------+
| acp.AcpConfig | check | warning  | 8 clients are using or havent closed the table properly | 
| acp.AcpConfig | check | warning  | Size of datafile is: 32079848       Should be: 32079784  | 
| acp.AcpConfig | check | error    | Found 495762 keys of 495761                              | 
| acp.AcpConfig | check | error    | Corrupt                                                  | 
+---------------+-------+----------+----------------------------------------------------------+
4 rows in set (3.50 sec)

mysql> repair table AcpConfig;
+---------------+--------+----------+----------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                     |
+---------------+--------+----------+----------------------------------------------+
| acp.AcpConfig | repair | warning  | Number of rows changed from 495761 to 495762 | 
| acp.AcpConfig | repair | status   | OK                                           | 
+---------------+--------+----------+----------------------------------------------+
2 rows in set (13.14 sec)

这篇关于不存在重复项时出现MySQL重复项错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！