MySQL Replication ERROR 1062: Duplicate Entry for Primary Key when starting Slave6 min read

Devops Engineer
Sorry! The Author has not filled his profile.
follow me

In this blog,we will see how to resolve the MySQL Replication ERROR 1062 : Duplicate Entry for Primary Key when starting Slave.
Here we will skip the MySQL slave error.

ct  2 08:13:36  mysqld: 2020-10-02  8:13:36 608 [ERROR] Slave SQL: Error 'Duplicate entry '6352107' for key 'PRIMARY'' on query. Default database: 'databasename_db'. Query: 'insert into IA_INS_EVENT_ACT (CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON, TENANT_ID, ACT_STATE, BATCH_ID, IS_DELETED, DESCRIPTION, GROUP_MAIL_STATUS, INGHT_ID, LOG, SNAPSHOT_ID, STATUS, THREAD_ID, TRIGGER_TYPE, USER_ID) values ('SYS_ADMIN_INGHT', '2020-09-25 11:35:01.131', 'SYS_ADMIN_INGHT', '2020-09-25 11:35:01.131', 6, 'ENQUEUED', '1133_1601033701127', 0, '', 0, 1133, '', NULL, 1, NULL, 'PERCEPTION', 14767)', Gtid 0-2-73099, Internal MariaDB error code: 1062

Oct  2 08:13:36  mysqld: 2020-10-02  8:13:36 608 [Warning] Slave: Duplicate entry '6352107' for key 'PRIMARY' Error_code: 1062

Oct  2 08:13:36  mysqld: 2020-10-02  8:13:36 608 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master2-bin.000056' position 14905

Oct  2 08:13:36  mysqld: 2020-10-02  8:13:36 605 [Note] Error reading relay log event: slave SQL thread was killed

Oct  2 08:13:36  mysqld: 2020-10-02  8:13:36 605 [Note] Slave SQL thread exiting, replication stopped in log 'master2-bin.000056' at position 15350

This error,will be raising after successfully establishing MySQL Master and Slave Connection and got error like “MySQL slave duplicate entry with MySQL error code 1062

When we take dump from Master database,there will be possibility that our Master Database has duplicate entries,but it wont work in the slave.Hence we have to make one change in Configuration file of MySQL “my.cnf”

#vim /etc/my.cnf
    [mysqld]

    slave-skip-errors=1062
    skip-slave-start

    Save and quit the file by using :wq and then restart the service by using the following command

    #sudo service mysql restart / service  mariadb restart

This means,if the slave host returns the error code 1062,it will skip that query and proceed further.
Once we restart restart mysql/mariadb service.Then login to mysql and run below command.

START SLAVE;

Now all the duplicate-key errors will get bypassed.
Now lets execute below command to view the Slave status.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: XXX.XX.XX.XXX
                   Master_User: replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master2-bin.000065
           Read_Master_Log_Pos: 7028710
                Relay_Log_File: ip-XXX-XX-XX-XXX-relay-bin.000002
                 Relay_Log_Pos: 13533101
         Relay_Master_Log_File: master2-bin.000064
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               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: 659610536
               Relay_Log_Space: 26010563
               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: 15479
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Waiting for room in worker thread event queue
              Slave_DDL_Groups: 22
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 43640
1 row in set (0.000 sec)

Once the “Seconds_behind_Master” gets to 0,we need to remove the below lines from my.cnf and restart mysql/mariadb service.
After few min minutes execute.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: XXX.XX.XX.XXX
                   Master_User: replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master2-bin.000065
           Read_Master_Log_Pos: 7028710
                Relay_Log_File: ip-XXX-XX-XX-XXX-relay-bin.000002
                 Relay_Log_Pos: 13533101
         Relay_Master_Log_File: master2-bin.000064
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               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: 659610536
               Relay_Log_Space: 26010563
               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: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Waiting for room in worker thread event queue
              Slave_DDL_Groups: 22
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 43640
1 row in set (0.000 sec)

Now we can see Seconds_Behind_Master is 0,Now we will remove the below lines and restart the service.

#vim /etc/my.cnf
    [mysqld]

    #slave-skip-errors=1062 //remove this line
    #skip-slave-start       //remove this line

    Save and quit the file by using :wq and then restart the service by using the following command

    #sudo service mysql restart / service  mariadb restart

Hope now the issue related to ERROR 1062 Duplicate Entry for Primary Key when starting Slave has been solved.

© 2020, Techrunnr. All rights reserved.

#1
#2
#3
Questions Answered
Articles Written
Overall Points

Related posts

Leave a Reply