1

I need a little bit of help for this problem :

I got this error :

Could not execute Write_rows event on table ....; Duplicate entry '16XXXXX-\xE790\XXXXXXX-16XXXXXX' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log utilBINLog-bin.000029, end_log_pos 794622875

A failover happened between the master and the slave. After the master became the slave this error happens on the slave.

This setup is using GTID/BINLOG. I would like to avoid skipping this error (Set skip counter to ...) because I think that this may provoke inconsistency in the database.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: 
                  Master_Port: 
                Connect_Retry: 
              Master_Log_File: utilBINLog-bin.000030
          Read_Master_Log_Pos: 1590687
               Relay_Log_File: RELAYLog.000002
                Relay_Log_Pos: 168192
        Relay_Master_Log_File: utilBINLog-bin.000029
            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: Could not execute Write_rows event on table ....; Duplicate entry '16008080-\xE790\-1600000' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the events master log utilBINLog-bin.000029, end_log_pos 794622875
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 794621018
              Relay_Log_Space: 280882085
              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: 
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table ...; Duplicate entry '16008080-\xE790\-1600000' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the events master log utilBINLog-bin.000029, end_log_pos 794622875
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101
Master_UUID: a0232561-e21a-11ea-a236-005056af6c92
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: a0232561-e21a-11ea-a236-005056af6c92:10795159-10967882
            Executed_Gtid_Set: 77818f20-e21a-11ea-b443-005056af4111:1-9605354,
a0232561-e21a-11ea-a236-005056af6c92:1-10795259
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace:

**Master :** 


**mysql> SHOW MASTER STATUS\G**
*************************** 1. row ***************************
             File: utilBINLog-bin.000030
         Position: 1590687
     Binlog_Do_DB: ....
 Binlog_Ignore_DB:
Executed_Gtid_Set: 77818f20-e21a-11ea-b443-005056af4111:1-9585630,
a0232561-e21a-11ea-a236-005056af6c92:1-10967882
1 row in set (0.00 sec)

When i search for this record in both database, there's no record corresponding to the one display in the errors.

More information - The binlog utilBINLog-bin.000029 is not present on the slave when I display all binary log on the machine :

Binary log on slave :

mysql> SHOW BINARY LOGS;
+-----------------------+------------+-----------+
| Log_name              | File_size  | Encrypted |
+-----------------------+------------+-----------+
| utilBINLog-bin.000001 | 1073742187 | No        |
| utilBINLog-bin.000002 | 1073743074 | No        |
| utilBINLog-bin.000003 | 1073742588 | No        |
| utilBINLog-bin.000004 |  531615680 | No        |
| utilBINLog-bin.000005 |        235 | No        |
+-----------------------+------------+-----------+
5 rows in set (0.06 sec)

Binary logs on master :

mysql> SHOW BINARY LOGS;
+-----------------------+------------+-----------+
| Log_name              | File_size  | Encrypted |
+-----------------------+------------+-----------+
| utilBINLog-bin.000026 | 1073743702 | No        |
| utilBINLog-bin.000027 | 1073741952 | No        |
| utilBINLog-bin.000028 |  781910611 | No        |
| utilBINLog-bin.000029 | 1073742817 | No        |
| utilBINLog-bin.000030 |    4293615 | No        |
+-----------------------+------------+-----------+

A hard failover happened two weeks ago (Server unexpected reboot), is it possible that this transaction was "stuck" since the last failover ?

It seems that the executed_GTID_set on the slave (77818f20-e21a-11ea-b443-005056af4111:1-9605354) is in advance compare to the current master executed_GTID_set (77818f20-e21a-11ea-b443-005056af4111:1-9585630)

How is that possible ?

Some say that I should do a stop slave; reset slave; start slave but according to the MySQL documentation this is not gonna change the executed_GTID_set, or I can inject a fake transaction to tell the slave to pass the problematic transaction but I don't known how I can check if this error concern only one or more transaction.

More info :

Using MySQL function GTID_SUBSTRACT, I can check how many errant transactions I have :

mysql> select gtid_subtract('77818f20-e21a-11ea-b443-005056af4111:1-9605354,
    '> a0232561-e21a-11ea-a236-005056af6c92:1-10795259','77818f20-e21a-11ea-b443-005056af4111:1-9585630,
    '> a0232561-e21a-11ea-a236-005056af6c92:1-10967882') as errant_transactions;
+------------------------------------------------------+
| errant_transactions                                  |
+------------------------------------------------------+
| 77818f20-e21a-11ea-b443-005056af4111:9585631-9605354 |
+------------------------------------------------------+

9605354-9585631 = 19 723 errant transactions, if this calculation is correct, I don't known if all these transactions are errant or only the first one in this GTID subset.

7
  • Hi @AnonymeJohnDoe , Can you describe in detail what activity you have performed ? Mar 15 at 11:17
  • These databases are manage by a web interface, i've promote the slave to masters (And ofc the master became slave) then this error pop up Mar 15 at 11:58
  • What Engine is the table?
    – Rick James
    Mar 15 at 17:57
  • When you have change master to slave, what syntax is used to start the replication & from which position you have started the replication ? Mar 16 at 4:39
  • @RickJames, it's innoDB Mar 16 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.