When encountering the error 1210: Key already exists in MySQL, the user should:
SHOW INDEXES FROM table_name;
Replace `table_name` with the name of the table you're working with. This command shows the indexes and their unique properties.
SELECT uniquekeycolumns, COUNT(*)
FROM table_name
GROUP BY uniquekeycolumns
HAVING COUNT(*) > 1;
Replace `uniquekeycolumns` with the columns making up the unique key or index, and `table_name` with the actual name of your table.
DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE
t1.id > t2.id AND
t1.uniquekeycolumns = t2.uniquekeycolumns;
Replace `id` with the primary key column and `uniquekeycolumns` with the columns making up the unique key.
SELECT HEX(column_name), COUNT(*)
FROM table_name
GROUP BY HEX(column_name)
HAVING COUNT(*) > 1;
Replace `column_name` with the name of the column suspected of having hidden characters.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
