“Duplicate entry” even though the column has UNIQUE constraint

79
January 14, 2021, at 08:00 AM

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
READ ALSO
Run multiple processes in single celery worker on a machine with single CPU

Run multiple processes in single celery worker on a machine with single CPU

I am researching on Celery as background worker for my flask applicationThe application is hosted on a shared linux server (I am not very sure what this means) on Linode platform

63
Import css file from same non-root directory

Import css file from same non-root directory

I would like to distribute my css between multiple files to remain more organizedBut I want to only have to import my main

74
kotlin Converts bytearray data received through communication into string

kotlin Converts bytearray data received through communication into string

Converts bytearray data received through communication into string

107
wsimport on jax-ws who has two global components whith the same name

wsimport on jax-ws who has two global components whith the same name

I' try to use wsimport to use a jax-ws, but I get this message:

75