- MySQL Master version: 5.5.16-1
- MySQL Slave version: 5.5.18-1
The master's snapshot is created by:
mysql> FLUSH TABLES WITH READ LOCK;
shell> mysqldump --all-databases --master-data > dbname_`date +%F`.sql
This dump file is imported on the slave [which is started with --skip-slave-start option] without error:
shell> pv dbname_`date +%F`.sql | mysql -u root -p
But I got the following error when executing the mysql> start slave;:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '115846' for key
'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO
request_posted [id, user_id, channel, message, link, picture, name, ...
There is only one record with ID 115846 on the master:
mysql> select count[*] from request_posted where id=115846;
Current database: db
+----------+
| count[*] |
+----------+
| 1 |
+----------+
1 row in set [0.01 sec]
Try to skip some queries with:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;
didn't help. I don't want to skip those errors by adding:
slave-skip-errors = 1062
to my.cnf file because it may bring slave inconsistent.
What may be the reason for this error?
UPDATE
This is not how I usually setup mySQL replication
Which steps that you think I don't follow the document?
I wonder if you will encounter the same problem if you were to setup the entire configuration rather that passing the mysqldump command.
No, it works as normally if I also change the master to corresponding coordinates.
I would try dropping the database on the slave, make sure the binlogs are clear, and start again. Also check the table in question on the master to assure the indexes do not have errors.
Is delete [move] all the datadir enough? I did that and get the same result.
Reply to @Dmytro Leonenko
'show slave status\G' on slave to ensure that it is properly configured, MASTER_LOG_POS is 0
Only 'show slave statug\G' after import but before 'start slave;' can give us the answer
I backed up the datadir, delete all and run mysql_install_db, import the dump file, execute change master to and here's the results:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: x.x.x.x
Master_User: xx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set [0.00 sec]
I'm wondering why Master_Log_Pos is 4?
Duplicate Entry error is a very common error that has been experienced by users working with databases. Users have reported that the error has commonly occurred when using the SQL. The error appears when updating the tables. Furthermore, the error has occurred in several scenarios which include using Laravel, PHP, Atlassian, Joomla, and similar web development and databases. Now in this article, the objective is to give you important information regarding the error and to give you some methods by which you can fix the issue by yourself in no time. But before let’s go through its causes.
Causes of Duplicate Entry Error Problem Issue
While researching about the error and its possible solution we come up with some very common users that have been reported by the users. The Duplicate Entry error appears because of multiple reasons depending like if you are working with SQL possible the error comes because of the duplicate key, unique data field, or data type -upper limit. Also if the table indexes are corrupted then also the error seems to appear. However, the error also occurs due to mistakes in the codes or the way the user is updating or modifying the table.
- Duplicate key or entries
- Unique data field
- Data type -upper limit
- Table indexes are corrupted
- Mistakes in the codes
Similar Types of Duplicate Entry Error Problem Issue
- MySQL error 1062 duplicate entry ‘0’ for key ‘primary’
- MySQL for key ‘primary auto_increment
- #1062 – duplicate entry ‘1’ for key ‘primary’ PHPMyAdmin
- ‘0’ for key ‘primary Codeigniter
- Duplicate entry 255 for key ‘primary
- 82 for key primary
- Duplicate entry ‘4294967295’ for key ‘primary
- #1062 40 for key primary
In this section, we will try to cover some methods that you can try to resolve the Duplicate Entry Error. The following are the methods we will go through. Since we do not know the actual cause of the issue we will be giving you solutions according to the scenarios.
1. The Value Already Exist [Duplicate Value]
Now the #1062 – duplicate entry ‘1’ for key ‘primary’ Error may occur when the data or value which you are trying to insert already exists in the Primary key. Furthermore, it is important to know that the Primary key does not accept duplicate entries. To resolve this you can do the below steps.
- STEP 1. Put the Primary Key Colum as to be auto increment
- STEP 2. Use the below syntax
ALTER TABLE ‘table_name’ ADD ‘column_name’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
**NOTE: You can ignore the Primary key column while inserting values.
Alternatively,
you can put NULL vale to Primary Key which in turn automatically generates sequence numbers.2. Unique Data Filed
The error also appears when an existing table has been set to unique. So when you try to add any column the error appears. So the simple fix to this duplicate entry for key ‘primary’ issue is to create a new column but don’t set it to the Unique field. Insert whatever data you wish to insert and the set is as unique if you want to.
3. Data Limit Out Of Range
The error also appears when if you have been using the auto_increment function and the data exceeds the limit of auto_increment function the #1062 – duplicate entry ‘0’ for key ‘primary’ error appears. Suppose you have assigned the primary key column as something. And the limit of the auto_increment is set to a max of 127. Now when you enter a new record whose id is more than 127 the error will emerge. To fix this follow the steps.
- STEP 1. We can resolve the issue by modifying the index field. You may use any of the following signed/unsigned INT/ BIGINT
- STEP 2. Use the below command to increase the maximum range
ALTER TABLE ‘table_name’ MODIFY ‘column_name’ INT UNSIGNED NOT NULL AUTO_INCREMENT;
- STEP 3. Also if you want to retrieve the recently incremented value, the below command
mysql> SELECT LAST_INSERT_ID[];
4. Creating a New Database & Importing
If you have tried the above methods and the duplicate entry 0 for key primary error still persists, try the below step to fix the issue.
- STEP 1. Firstly backup your database using the below command
mysqldump database_name > database_name.sql
- STEP 2. Once the backup is done, drop the database
DROP DATABASE database_name;
- STEP 3. Now recreate the database, by using the below command
CREATE DATABASE database_name;
- STEP 4. Now that you have created the database import using the below command
mysql database_name 1
**NOTE: Instead of #_ use the prefix for your tables.- STEP 2. Fix the 1062 duplicate entry 1 for key primary issue
7. Other Troubleshooting Points
- Error in WordPress Database: So one the cause why this duplicate entry 1 for key primary error occurs is because of adswsc_DbInstall. It is comparing the unequal table names.
- Update the Program: Make sure that if you are using any database application it is updated to the latest version.
- Crosscheck the code: Make sure that the code is accurate and without errors
In this troubleshooting, we have seen various methods that are the solution to fix Duplicate Entry Error. The error may appear due to multiple reasons and we have tried to cover most of them. Furthermore, we have also talked about the possible causes that lead to this error.
We hope this Duplicate Entry troubleshooting guide fixed your issue. For articles on troubleshooting and tips follow us. Thank You!