MGR常见问题和处理方式

MGR常见问题和处理

目录

• MGR常见问题和处理
  • MGR中外键约束导致的节点掉出复制组

MGR中外键约束导致的节点掉出复制组

错误现象
MGR突然两个节点掉出了复制组，并且日志中报如下错误：
Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED, Error_code: MY-001451

2021-10-28T10:25:27.304834+08:00 23 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Worker 3 failed executing transaction '0ef61e70-d9b9-11e9-82a1-0800277c2a9f:53684188'; Could not execute Delete_rows event on table schedule_prod.qrtz_job_details; Cannot delete or update a parent row: a foreign key constraint fails (`schedule_prod`.`qrtz_triggers`, CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `qrtz_job_details` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED, Error_code: MY-001451
2021-10-28T10:25:27.339757+08:00 20 [Warning] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756
2021-10-28T10:25:27.339799+08:00 20 [ERROR] [MY-011451] [Repl] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'
2021-10-28T10:25:27.339915+08:00 17 [ERROR] [MY-011452] [Repl] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-10-28T10:25:27.339988+08:00 17 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2021-10-28T10:25:27.340023+08:00 22 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '0ef61e70-d9b9-11e9-82a1-0800277c2a9f:53684186'; Could not execute Delete_rows event on table schedule_prod.qrtz_triggers; Slave worker has stopped after at least one previous worker encountered an error when slave-preserve-commit-order was enabled. To preserve commit order, the last transaction executed by this thread has not been committed. When restarting the slave after fixing any failed threads, you should fix this worker as well., Error_code: 3030;, Error_code: MY-003030

错误分析
bug

Description:
In group replication, a table has foreign keys that depend on another table. If the main database is executed successfully, the slave may execute abnormally and exit the group.

I have tried to commit_order and other configurations, but they are still unsolvable

Currently, it can only be solved by setting slave_parallel_workers to 0, but it seriously affects efficiency.

解决方案
Hello,

Same problem.

• MySQL versions affected at least 8.0.22, 8.0.25
• Group Replication
• slave_parallel_workers default 10
• Delete query relying on FK CASCADE DELETE
• It is written ordered to the relay log (1st child row delete, then 2nd parent row) but applied in parallel and can happen to apply in the wrong order breaking the slave applier thread.

Shouldn't be ER_NO_REFERENCED_ROW type error considered for slave_retries when it is GR + slave_parallel_workers>0?

Temporary fix is whether to not rely on CASCADE DELETE at all or set slave_parallel_workers=0/1 which is not efficient.

Thanks,
Roman

Addition to my previous comment.
Not just FK CASCADE DELETE but also FK unordered INSERT.

Another use/break case:

• Same MySQL versions, Group Replication and slave_parallel_workers.
• INSERT into parent table.
• INSERT into child table.

Then it is Group Replicated to a secondary, written in the right order to relay log but applied in parallel thus sometimes a record to the child table is tried to be inserted before parent and failing the applier thread.

Thanks,
Roman

解决方案：

show varibales like '%slave_parallel_workers%';

stop GROUP_REPLICATION;
set global slave_parallel_workers=1;

start GROUP_REPLICATION;

查询队列情况：
SELECT
        a.*                                           ,
        b.COUNT_TRANSACTIONS_IN_QUEUE                                                 AS trx_in_queue ,
        CASE a.MEMBER_ID WHEN @@server_uuid THEN 'Local node' ELSE 'remote node ' END AS 'location'   ,
        CASE a.MEMBER_ID WHEN @@server_uuid THEN
                        (
                                SELECT
                                        VARIABLE_VALUE
                                FROM
                                        performance_schema.global_variables
                                WHERE
                                        variable_name = 'read_only' ) ELSE '--' END AS 'read_only' ,
        CASE a.MEMBER_ID WHEN @@server_uuid THEN
                        (
                                SELECT
                                        VARIABLE_VALUE
                                FROM
                                        performance_schema.global_variables
                                WHERE
                                        variable_name = 'super_read_only' ) ELSE '-- ' END AS 'super_read_only'
FROM
        performance_schema.replication_group_members      a,
        performance_schema.replication_group_member_stats b
WHERE
        a.MEMBER_ID = b.MEMBER_ID;

错误现象
错误分析
解决方案