Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.5k views
in Technique[技术] by (71.8m points)

mysql - "Duplicate entry" even though the column has UNIQUE constraint

I'm trying to drop some columns which I'm no longer using in my table. I've got a single column with a UNIQUE constraint. When I'm trying to drop the columns I'm getting a "Duplicate entry" found for this column.

When I search for rows with this code I'm only returned with a single result, but I figure that might be because it stops looking when it finds the first (as it thinks its unique).

I've tried deleting the row in question, but after trying to delete columns I'm returned with a new code that is "Duplicate entry".

Error when trying to delete columns:

ALTER TABLE attacktable DROP COLUMN fairfightparsed, DROP COLUMN defenderbattlestatssum, DROP COLUMN attackerbsstd, DROP COLUMN defenderdsstd, DROP COLUMN defenderlevel;
ERROR 1062 (23000): Duplicate entry 'e3cce98b6aa8085ed6a960d2afcd4dca' for key 'attacktable.attackcode'

Only one of the selected attackcode:

SELECT * FROM attacktable WHERE attackcode = "e3cce98b6aa8085ed6a960d2afcd4dca";
+----------------------------------+------------+ ...
| attackcode                       | attackerid | ...
+----------------------------------+------------+ ...
| e3cce98b6aa8085ed6a960d2afcd4dca |    2618403 | ...
+----------------------------------+------------+ ...
1 row in set (0,00 sec)

Description of uniqueness:

describe attacktable;
+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| attackcode             | varchar(255) | YES  | UNI | NULL    |       |
| attackerid             | int          | YES  | MUL | NULL    |       |
....

Indexes on the table:

SHOW INDEX FROM attacktable;
+-------------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| attacktable |          0 | attackcode             |            1 | attackcode             | A         |     1022111 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| attacktable |          1 | attackerid             |            1 | attackerid             | A         |        2281 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| attacktable |          1 | resmodchain            |            1 | resmodchain            | A         |          92 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| attacktable |          1 | resmodfair             |            1 | resmodfair             | A         |         202 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| attacktable |          1 | resmodwar              |            1 | resmodwar              | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| attacktable |          1 | attackerbattlestatssum |            1 | attackerbattlestatssum | A         |       76782 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

I'm now worried there are a lot of these duplicates in my table.. Help please :)

EDIT: SO I suspect it's the error-code that is wrong, not that I have duplicates. That would be easier I guess.

DELETE FROM attacktable WHERE attackcode = "e3cce98b6aa8085ed6a960d2afcd4dca";
Query OK, 1 row affected (0,02 sec)
SELECT * FROM attacktable WHERE attackcode = "e3cce98b6aa8085ed6a960d2afcd4dca";
Empty set (0,00 sec)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Solved it after finding this thread.

In my case it was caused due to continued writing to the table while I was trying to drop columns. I locked the table, dropped the columns and unlocked the tables again.

LOCK TABLE attacktable WRITE;

ALTER TABLE DROP COLUMN ...;

UNLOCK TABLES

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...