TOPIC MOVED IN DBA SO
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 happen beetween the master/slave. When the masters became the slave this error happen 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 happen two weeks ago (Server unexpected reboot), is it possible that this transaction was "stuck" since the last failover ?
It seem's 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 mysql doc 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.