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

Question

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)

Answer 1

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