MASTER-MASTER replication failed on Production: Coordinator stopped because there were error(s) in the worker(s)

Question

This error occured after I was importing a database dump to master1.

In production our MASTER-MASTER replication failed

UPDATE

SELECT CHANNEL_NAME, WORKER_ID, THREAD_ID, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE FROM performance_schema.replication_applier_status_by_worker;

giving output on Master1:

Worker 1 failed executing transaction 'ANONYMOUS' at master log node1-bin.000018, end_log_pos 594107878; Error 'Duplicate foreign key constraint name 'client_potential_meganet_agreements_client_id_foreign'' on query. Default database: 'netcore_support'. Query: 'ALTER TABLE `client_potential_meganet_agreements`ADD CONSTRAINT client_potential_meganet_agreements_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `client_potential_meganet` (`id`)'

giving output on Master2

Worker 1 failed executing transaction 'ANONYMOUS' at master log node2-bin.000040, end_log_pos 168239245; Could not execute Write_rows event on table netcore_feedback.sessions; Duplicate entry 'ctthXsIZ32QP8lJsYVInWGZW1GwNNgNN1t1wLGbn' for key 'sessions.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 168239245

MASTER1 error:

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log node1-bin.000018, end_log_pos 594107483. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

MASTER2 error:

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log node2-bin.000040, end_log_pos 168239245. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Master1:

SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| node2-bin.000051 | 434103214 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

Master2:

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| node1-bin.000042 |  1451131 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

my configs.

[mysqld]

server-id=2
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2
relay-log=/var/lib/mysql/node2-relay-bin
relay-log-index=/var/lib/mysql/node2-relay-bin.index
log-error=/var/log/mysql/mysql.error
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/node1-relay-log.info
log-bin=/var/lib/mysql/node2-bin
max_connections = 1000

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

How can I fix this error?

Answer 1

I solved this synchronization error by adding the following configuration to my.cnf to ignore this type of error.

slave-skip-errors = 1032

Refer to the official documentation via https://dev.mysql.com/doc/refman/8.4/en/replication-administration-skip.html