How do i fix a duplicate entry in mysql replication?

Question

160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE    
   activity
SET
   activity_type_id = 9,
   subject = 'Send departure email',
   date_due = '2016-05-26 01:00',
   date_start = '2016-05-23 01:00',
   activity_status_id = 1,
   content = 'Send departure email'
WHERE
   id = 2888555', Error_code: 1062
160523 13:07:29 [Warning] Slave: Duplicate entry '914166' for key 'PRIMARY'     
Error_code: 1062
160523 13:07:29 [ERROR] Error running query, slave SQL thread aborted. Fix     
the problem, and restart the slave SQL thread with "SLAVE START". We stopped   
at log 'mysql-bin.000004' position 14847360

Many people have suggested to use slave-skip-error option etc.. but that will completely avoid the query. How do you actually continue with the query that poses the error ? Also, I could not find a good answer as to why this actully occurs and what the number "914166" indicates ? Can someone please explain ?

Table of Contents Show

How do you stop duplicates in MySQL?
How does MySQL handle duplicate entries?
How we can resolve replication errors?
What does duplicate entry mean in MySQL?

asked May 23, 2016 at 13:53

RaviRavi

2,4623 gold badges17 silver badges25 bronze badges

3

The number (914166) has to be an ID that is saved in an integer ID-column which is either the unique key or part of it.

Your query shows "id = 2888555" , which is probably the column MySQL is referring to.

Check values for the row with id 914166 and compare it to the values you wanted to set for id 2888555. You only have to look for columns that are part of your Primary Key. The values in these columns will be identical therefore MySQL throws out an error.

MySQL is basically saying:

Your query makes no sense! You are trying to update a row , but the columns you are trying to update are part of the Primary Key and there is already a row that has the same values. Please correct your query to not violate the Primary Key Constraint!

However there shouldn't be a problem as long as all your rows have different IDs. Posting table structure and indices would allow a more specific analysis of your problem at this point.

answered May 23, 2016 at 15:04

iLikeMySqliLikeMySql

7363 silver badges7 bronze badges

5

The triggered table causes the duplicate entry error. Thanks for the suggestions.

answered May 26, 2016 at 8:16

RaviRavi

2,4623 gold badges17 silver badges25 bronze badges

5

Looking for guidance as to what I continue to do wrong when trying to perform a basic replication setup between a single primary and replication servers.

Through several trials over the years, this has been inconsistent at best. My more recent attempt (steps) are shown below, which is a culmination from several sites, including the MariaDB support site.

Goal: Use mariabackup or other recommended method to backup the database from the primary server, restore to the replication server and successfully replicate data from the primary to the replication server.

Using Mariabackup to capture a backup of the data

On the master server

Run the following command at MySQL command prompt

flush privileges; flush tables with read lock;

WHILE THE DB IS LOCKED

Run this command to backup

mariabackup --defaults-file="m:\mariadb\my.ini" --backup --target-dir="m:\backup" --user user --password pass

After the backup, run the command to prepare

mariabackup --defaults-file="m:\mariadb\my.ini" --prepare --target-dir="m:\backup" --user user --password pass

NOTE Once prepare is complete, make sure to run 'unlock tables' in the MariaDB command prompt window.

The prepare command will show the binlog file and position to use in CHANGE MASTER TO, so be sure to capture the output from the prepare command after it completes.

Example output from prepare command

mariabackup based on MariaDB server 10.3.12-MariaDB Win64 (AMD64)
mariabackup: cd to m:\backup\
mariabackup: This target seems to be not prepared yet.
mariabackup: using the following InnoDB configuration for recovery:
mariabackup:   innodb_data_home_dir = .
mariabackup:   innodb_data_file_path = ibdata1:10M:autoextend
mariabackup:   innodb_log_group_home_dir = .
mariabackup: Starting InnoDB instance for recovery.
mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory paramete
r)
2020-11-02 22:46:49 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocke
d functions
2020-11-02 22:46:49 0 [Note] InnoDB: Uses event mutexes
2020-11-02 22:46:49 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-11-02 22:46:49 0 [Note] InnoDB: Number of pools: 1
2020-11-02 22:46:49 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-11-02 22:46:49 0 [Note] InnoDB: Initializing buffer pool, total size = 100M
, instances = 1, chunk size = 100M
2020-11-02 22:46:49 0 [Note] InnoDB: Completed initialization of buffer pool
2020-11-02 22:46:49 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN
=936805148847
2020-11-02 22:46:49 0 [Note] InnoDB: Last binlog file '.\master-bin.000003', pos
ition 167707395
Last binlog file .\master-bin.000003, position 167707395
201102 22:46:50 completed OK!

RESTORING THE DB Copy the backup directory from the master over to the root of the DB drive on the slave

Log in to the slave server and open a command prompt to the backup directory

Run the command to copy restore the backup to the slave data directory

mariabackup --copy-back --target-dir="M:\backup" --datadir="m:\mariadb\data" --user infinity --password infinitydb

Start the MariaDB service on the slave server

Run the following change master to command with appropriate values

Example

CHANGE MASTER TO master_host="idk3-vm5", master_log_file='master-bin.000003', master_log_pos=167707395, master_port=3306, master_user="repl", master_password="repl", master_use_gtid=current_pos;

Start and check slave

start slave; 

show slave status \G

Error upon starting replication

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Queueing master event to the relay log
                   Master_Host: idk3-vm5
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-bin.000002
           Read_Master_Log_Pos: 604529766
                Relay_Log_File: idk3-vm8-relay-bin.000002
                 Relay_Log_Pos: 1631
         Relay_Master_Log_File: master-bin.000002
              Slave_IO_Running: Yes
             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: 1062
                    Last_Error: Error 'Duplicate entry 'idk3-vm8-OrderInjector' for key 'PRIMARY'' on query. Default database: 'idf'. Query: 'INSERT INTO `idf`. `idf_client_version` (`idf`.`idf_client_version`.`HOSTNAME`,`idf`.`idf_client_ve rsion`.`SOFTWARE`,`idf`.`idf_client_version`.`VERSION`,`idf`.`idf_client_version `.`LAST_CONNECTED`) VALUES ('idk3-vm8','OrderInjector','20.2.11','2020-11-02 11: 29:13')'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 1331
               Relay_Log_Space: 604530378
               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: 1062
                Last_SQL_Error: Error 'Duplicate entry 'idk3-vm8-OrderInjector' for key 'PRIMARY'' on query. Default database: 'idf'. Query: 'INSERT INTO `idf`. `idf_client_version` (`idf`.`idf_client_version`.`HOSTNAME`,`idf`.`idf_client_ve rsion`.`SOFTWARE`,`idf`.`idf_client_version`.`VERSION`,`idf`.`idf_client_version `.`LAST_CONNECTED`) VALUES ('idk3-vm8','OrderInjector','20.2.11','2020-11-02 11: 29:13')'    Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-701693
       Replicate_Do_Domain_Ids:    Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 3 1 row in set (0.003 sec)

Given that I have flushed privileges, flushed tables, and locked the DB and THEN captures the backup, I do not understand why I continue to get duplicate key after restoring and completing the replication configuration.

If someone can help me understand what I'm doing wrong, I would greatly appreciate it.

How do you stop duplicates in MySQL?

Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.

How does MySQL handle duplicate entries?

How to Remove Duplicate Rows in MySQL.

Option 1: Remove Duplicate Rows Using INNER JOIN..

Option 2: Remove Duplicate Rows Using an Intermediate Table..

Option 3: Remove Duplicate Rows Using ROW_NUMBER().

How we can resolve replication errors?

To fix the replication error we follow the below steps..

First, we log into the MYSQL. ... .

On the MySQL shell, we check the slave status. ... .

For that, we stop the slave from replication, using the below command. ... .

Next, we tell the slave to simply skip the invalid SQL query. ... .

Again, we start the slave..

What does duplicate entry mean in MySQL?

When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database.

kode mysql