How to solve “ERROR 1062 (23000) at line X: Duplicate entry ?????? for key X”

I recently imported a dump into MySQL and found the following error:

ERROR 1062 (23000) at line 14096: Duplicate entry '????????' for key 'name'

Judging by the question marks in the error, the problem was rather in encoding, looked at the encoding of the dump via Notepad ++, it was – UTF8.

Connected to mysql, looked at the encoding of the database:

mysql -u root
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "database_name";

The encoding of the database was latin1, by the way, I installed it by default when creating the database.

So I deleted the database and created the encoding:

DROP DATABASE database_name;
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
exit

After that I imported:

mysql database_name < database_name.sql;

Import was successful, no errors were displayed.

If the base and dump encoding is the same, and you really want to record duplicate data, you can replace the INSERT INTO with INSERT IGNORE INTO in the dump, with these data, where these duplicate data can be found by the line number in the error, for example, me “at line 14096”.

If you want to import a dump over an existing database, you can change the INSERT INTO to REPLACE INTO.

For example, from the command line, you can replace everything like this:

replace 'INSERT INTO' 'REPLACE INTO' -- database_name.sql

Before any actions you need to create a backup of the database, and also know why and what you are doing.

See also my article:
Import and export MySQL databases

Leave a comment

Leave a Reply

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading