0

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)
Mixy
  • 69
  • 2
  • 10
  • Is the column that you are trying to drop part of the primary key? – snakecharmerb Jan 12 '21 at 15:55
  • No. I'm trying to drop different columns. The primary key is a single column (unique attackcode). EDIT: Sorry. There are no primary key in the table. Should I add that to the unique "attackcode"? – Mixy Jan 12 '21 at 15:57
  • 'I suspect it's the error-code that is wrong' - I wouldn't have you tried like instead of = – P.Salmon Jan 12 '21 at 16:09
  • Hmm.. Where would I use this? I've now got the error with attackcode "dc7246b27a7dce417a92828a69e71019". If `SELECT * WHERE attackcode like "dc7246b27a7dce417a92828a69e71019"` I still only get a single line. – Mixy Jan 12 '21 at 18:08

1 Answers1

0

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
Mixy
  • 69
  • 2
  • 10