MASTER-MASTER replication failed on Production: Coordinator stopped because there were error(s) in the worker(s)
0
votes
1
answer
2502
views
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?
Asked by Breaking News
(101 rep)
Jan 21, 2024, 06:56 PM
Last activity: Jul 3, 2024, 04:24 PM
Last activity: Jul 3, 2024, 04:24 PM