1

While importing a database I received a Duplicate Entry error.

ERROR 1062 (23000) at line 2825: Duplicate entry 'homerow' for key 'PRIMARY'

Are the import instructions up until line 2825 executed or can I assume the whole import has not been executed?

2
  • Is the dump generated with drop table if exists and then create table statement ? Have you verified if the data is imported till 2825 line of the dump file , the content of dump file in linux can be read with sed or similar commands Sep 6, 2023 at 15:10
  • @ErgestBasha yes it is created with drop table if exists. I have not verified if the data is imported because I find it hard to determine what are new rows as it is a very busy database table (access logs).
    – devunder
    Sep 6, 2023 at 17:25

1 Answer 1

1

I took the time and make some tests in one of my databases. For testing purposes I used MariaDB version 10.5.8-MariaDB-log , it should apply to MySQL as well.

My table has the following DDL

CREATE TABLE `ani_did_zoiper_test` (
  `inoltro_nr` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ani` int(16) DEFAULT NULL,
  `zoiper_path` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stato` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `insert_record_dt` timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY `inoltro_nr` (`inoltro_nr`,`ani`),
  KEY `inoltro_ani_st` (`inoltro_nr`,`ani`,`stato`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

I made a dump of the table which has a few rows 40-50.

mysqldump -u root -p asterisk ani_did_zoiper_test> /root/ani_did_zoiper_test.sql

Then added a duplicate value to the dump file and then restore the file.

dbm:~ # mysql -u root -p asterisk < /root/ani_did_zoiper.sql
Enter password:
ERROR 1062 (23000) at line 42: Duplicate entry '11111111111-111111111' for key 'inoltro_nr'

There was only one INSERT INTO ani_did_zoiper_test VALUES and nothing were imported in the table.

I then made a change to the dump file and added another INSERT INTO statement , the second insert has the duplicate values , so the dump looks like

LOCK TABLES `ani_did_zoiper_test` WRITE;
/*!40000 ALTER TABLE `ani_did_zoiper_test` DISABLE KEYS */;
INSERT INTO `ani_did_zoiper_test` VALUES (xxxxxxxxxxxxxxxxxxxxxxx);
INSERT INTO `ani_did_zoiper_test` VALUES(xxxxxxxxxxxxxxxxxxxxxxx);
/*!40000 ALTER TABLE `ani_did_zoiper_test` ENABLE KEYS */;
UNLOCK TABLES;

Restore again ,

dbm:~ # mysql -u root -p asterisk < /root/ani_did_zoiper.sql
Enter password:
ERROR 1062 (23000) at line 43: Duplicate entry '11111111111-111111111' for key 'inoltro_nr'

The first insert block which had no duplicate was successfully imported on the table, anything after that row failed to insert.

Note line 43 is the line on the dump file .

In your case anything before line 2825 is imported successfully.

To read a specific line content in unix/linux you could use ,

dbm:~ # sed -n '43p' /root/ani_did_zoiper.sql
INSERT INTO `ani_did_zoiper_test` VALUES('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
dbm:~ #
1
  • Thank you very much for taking the time to this. Very helpful to see an example on how to mimic this and verify.
    – devunder
    Sep 7, 2023 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.