Opera News

Opera News App

[Solved] How to solve MySQL replication error code: 1062 duplicate entry

DBAMIND
By DBAMIND | self meida writer
Published 7 months ago - 0 views

Error Message:

Error Code: 1062. Duplicate entry ‘%s’ for key %d

Example:

Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

Possible Reason:

Case 1: Duplicate value.

The data you are trying to insert is already present in the column primary key. The primary key column is unique, and it will not accept the duplicate entry.

Case 2: Unique data field.

You are trying to add a column to an existing table which contains data and set it as unique.

Case 3: Data type –upper limit.

The auto_increment field reached its maximum range.


Resolution Steps:

Case 1: Duplicate value.

Set the primary key column as AUTO_INCREMENT.

ALTER TABLE ‘table_name’ ADD ‘column_name’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Now, when you are trying to insert values, ignore the primary key column. Also, you can insert NULL value to primary key column to generate sequence number. If no value specified MySQL will assign sequence number automatically.

Case 2: Unique data field.

Create the new column without the assigning it as unique field, then insert the data and now set it as unique field now. It will work now!!!

Case 3: Data type-upper limit.

When the data type reached its upper limit, for example, if you were assigned your primary key column as TINYINT, once the last record is with the id 127, when you insert a new record the id should be 128. But 128 is out of range for TINYINT so MySQL reduce it inside the valid range and tries to insert it with the id 127, therefore it produces the duplicate key error.

In order to solve this, you can alter the index field, setting it into signed / unsigned or INT/ BIGINT depending on the requirement, so that the maximum range will increase. You can do that by using the following command:

MySQL> Alter table ‘customers MODIFY id  int unsigned not null  auto_increment;

 

You can use the following function to retrieve the most recently automatically generated AUTO_INCREMENT value:

MySQL> select   last_insert_id ();


Final workaround and the only solution for this issue is as follows:.

After applying all the above-mentioned solutions and still facing this error code: 1062 Duplicate entry error, you can try the following workaround.

Step 1: Backup database:

You can backup your database by using following command:

mysqldump -u root -p dbamind > /backup/dbamind_backup.sql

 

Step 2: Drop and recreate database:

Drop the database using the following command:

Mysql> DROP DATABASE dbamind;


Step 3: Create the database using the following command:

Mysql> CREATE DATABASE dbamind;

Step 4: Import/Restore the database:

You can import your database by using following command:

mysql -u root -p dbamind < /backup/dbamind_backup.sql

After applying this workaround, the duplicate entry error will be solved. I hope this post will help you to understand and solve the MySQL Error code: 1062. Duplicate entry error.


REBUILDING THE SLAVE DATABASE

Step 2: Drop and recreate database:

Drop the database using the following command:

Mysql> DROP DATABASE dbamind;


Step 3: Create the database using the following command:

Mysql> CREATE DATABASE dbamind;

Step 4: Restore the master database dump file/backup to the slave server:

mysql -u root -p dbamind < /backup/dbamind_backup.sql


Step 4: Configure and sync the master and slave servers.


Hope this helps... My next article will demonstrate how to setup MySQL Replication between (Master and Slave servers) 

Content created and supplied by: DBAMIND (via Opera News )

Tags:      

MySQL
Opera News is a free to use platform and the views and opinions expressed herein are solely those of the author and do not represent, reflect or express the views of Opera News. Any/all written content and images displayed are provided by the blogger/author, appear herein as submitted by the blogger/author and are unedited by Opera News. Opera News does not consent to nor does it condone the posting of any content that violates the rights (including the copyrights) of any third party, nor content that may malign, inter alia, any religion, ethnic group, organization, gender, company, or individual. Opera News furthermore does not condone the use of our platform for the purposes encouraging/endorsing hate speech, violation of human rights and/or utterances of a defamatory nature. If the content contained herein violates any of your rights, including those of copyright, and/or violates any the above mentioned factors, you are requested to immediately notify us using via the following email address operanews-external(at)opera.com and/or report the article using the available reporting functionality built into our Platform

YOU MAY LIKE

Ankara Fashion For Easter Celebration

2 minutes ago

0 🔥

Ankara Fashion For Easter Celebration

Here Is The Best Way To Secure Your Sim From Account Hackers.

31 minutes ago

15 🔥

Here Is The Best Way To Secure Your Sim From Account Hackers.

No water in Enugu: Enugu people cries out over scarcity of water

41 minutes ago

0 🔥

No water in Enugu: Enugu people cries out over scarcity of water

The amount a player pays for receiving a card.

53 minutes ago

4 🔥

The amount a player pays for receiving a card.

Click and read on how to become a graphic designer and book-keeping

1 hours ago

0 🔥

Click and read on how to become a graphic designer and book-keeping

Learn How To Make Money With Online Programs Like Google AdSense And Opera News

1 hours ago

0 🔥

Learn How To Make Money With Online Programs Like Google AdSense And Opera News

Mercy Eke is currently trending on Twitter, see the reason behind her latest trend.

1 hours ago

15 🔥

Mercy Eke is currently trending on Twitter, see the reason behind her latest trend.

Need A Good Laugh? Here Are Hilarious Memes For You

1 hours ago

3 🔥

Need A Good Laugh? Here Are Hilarious Memes For You

Check out Barcelona's possible jersey for 2021/22

1 hours ago

64 🔥

Check out Barcelona's possible jersey for 2021/22

IPOB Warns Governor To Protect The Igbo Communities

1 hours ago

14 🔥

IPOB Warns Governor To Protect The Igbo Communities

COMMENTS