Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing the influence of slave_skip_errors parameters of MySQL on MGR usability". In daily operation, I believe that many people have doubts in analyzing the impact of slave_skip_errors parameters of MySQL on MGR usability. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "analyzing the impact of MySQL slave_skip_errors parameters on MGR usability". Next, please follow the editor to study!

I. case description

When MGR encounters that the table does not exist, the node does not exit the node but issues a warning, and the node status is normal. The warning is as follows:

2019-10-17T21:16:11.564211+08:00 10 [Warning] Slave SQL for channel group_replication_applier': Worker 1 failed executing transaction 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8' at master log, end_log_pos 220; Error executing row event:' Table 'test.a_1' doesn't exist', Error_code: 1146

The cluster status is as follows:

[root@mysql.sock] [test] > select * from performance_schema.replication_group_members +-+ | CHANNEL_NAME | | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +-- | -group_replication_applier | 9fd479bb-f0d8-11e9-9381-000c29105312 | mysql_1 | 3306 | ONLINE | | group_replication_applier | a8833a96-f0d8-11e9-a9f4-000c291fd9a5 | mysql_2 | 3306 | ONLINE | | group_replication_applier | b2968fe2-f0d8-11e9-a8ff-000c29c89e42 | mysql_3 | 3306 | ONLINE | + -+-- + 3 rows in set (0.00 sec)

At that time, it was very strange that we knew that this error was reported by the SQL thread even in the master-slave case, and the MGR could still be online. In this case, the data is no longer synchronized, and the error should be reported and the node should be eliminated.

Second, problem analysis

Then some interested students immediately carried out the test, and the test results were inconsistent with the above. The test results reported errors rather than warnings as follows:

2019-10-17T09:16:34.317542Z 84 [ERROR] Slave SQL for channel 'group_replication_applier': Error executing row event:' Table 'test.emp1' doesn't exist', Error_code: 1146

And the nodes in which the table does not exist have been removed. The following is the node status of the normal situation:

Secondary 1 node: [root@mysql.sock] [test] > select * from performance_schema.replication_group_members +-+ | CHANNEL_NAME | | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +-- | -+-+ | group_replication_applier | a8833a96-f0d8-11e9-a9f4-000c291fd9a5 | mysql_2 | 3306 | ERROR | +-+-- -+ 1 row in set (0.00 sec) secondary 2 node: [root@mysql.sock] [test] > select * from performance_schema.replication_group_members +-+ | CHANNEL_NAME | | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +-- | -+-+ | group_replication_applier | b2968fe2-f0d8-11e9-a8ff-000c29c89e42 | mysql_3 | 3306 | ERROR | +-+-- -+ 1 row in set (0.00 sec)

So the question is why the same MGR is warning and the other is wrong, and the former can still be in the normal synchronization state. Yes, when you see the title, you know that it has something to do with the slave_skip_errors parameter.

Third, test simulation

We know that if a slave table does not exist in Master-Slave, it will definitely report an error unless the slave_skip_errors parameter is set. Of course, I have not set this parameter online, and through this case, we find that this parameter also has an impact on MGR. The test method is as follows:

We enable slave-skip-errors= ddl_exist_errors on all three nodes

As shown below:

Then build a 3-node MGR cluster in single-primary mode.

The cluster is built normally.

Then do the following:

[root@mysql.sock] [(none)] > set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec) [root@mysql.sock] [(none)] > create table test.a_1 (id bigint auto_increment primary key,name varchar (20)); Query OK, 0 rows affected (0.01 sec) [root@mysql.sock] [(none)] > set sql_log_bin=1;Query OK, 0 rows affected (0.00 sec)

At this point, the primary node has an APC1 table, but because the binlog is turned off, there is no APC1 table for the two secondary nodes.

Then we insert the data:

[root@mysql.sock] [test] > insert into test.a_1 values (null,'tom'); Query OK, 1 row affected (0.02 sec)

At this point, the primary node can be inserted because of the existence of the APC1 table, but the two secondary nodes do not have the APC1 table, so the insert fails. The data generate inconsistencies. Normally, this data inconsistency will cause two secondary nodes to be proposed to the cluster. But in fact, all three nodes are normal, and the cluster does not fail.

[root@mysql.sock] [test] > select * from test.a_1;+----+-+ | id | name | +-- +-+ | 1 | tom | +-+-- + 1 row in set (0.00 sec) [root@mysql.sock] [test] > select * from performance_schema.replication_group_members +-+ | CHANNEL_NAME | | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +-- | -group_replication_applier | 9fd479bb-f0d8-11e9-9381-000c29105312 | mysql_1 | 3306 | ONLINE | | group_replication_applier | a8833a96-f0d8-11e9-a9f4-000c291fd9a5 | mysql_2 | 3306 | ONLINE | | group_replication_applier | b2968fe2-f0d8-11e9-a8ff-000c29c89e42 | mysql_3 | 3306 | ONLINE | + -+-- + 3 rows in set (0.00 sec)

At this point, go to 2 secondary nodes to read the test.a_1 table, the table does not exist.

Secondary 1: [root@mysql.sock] [test] > select * from test.a_1;ERROR 1146 (42S02): Table 'test.a_1' doesn't exist [root@mysql.sock] [test] > secondary 2: [root@mysql.sock] [test] > select * from test.a_1;ERROR 1146 (42S02): Table' test.a_1' doesn't exist

Error log output information: (set global log_error_verbosity = 3;)

2019-10-17T21:16:11.564211+08:00 10 [Warning] Slave SQL for channel 'group_replication_applier': Worker 1 failed executing transaction' aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8' at master log, end_log_pos 220; Error executing row event: 'Table' test.a_1' doesn't exist', Error_code: 1146 IV. Slave_skip_errors source code effectiveness point

This setting takes effect in the Rows_log_event::do_apply_event function, that is, when DML Event starts to be applied, which is called by a regular SQL thread (or Worker thread).

# ifdef HAVE_REPLICATION if (opt_slave_skip_errors) add_slave_skip_errors (opt_slave_skip_errors); # endifif (open_and_lock_tables (thd, rli- > tables_to_lock, 0)) / / Open table {uint actual_error= thd- > get_stmt_da ()-> mysql_errno () If (thd- > is_slave_error | | thd- > is_fatal_error) {if (ignored_error_code (actual_error)) / / here is controlled by the slave_skip_errors parameter ignored_error_code will read the parameter settings of slave_skip_errors {if (log_warnings > 1) rli- > report (WARNING_LEVEL, actual_error) "Error executing row event:'% s'", (actual_error? Thd- > get_stmt_da ()-> message_text (): "unexpected success or fatal error"); thd- > get_stmt_da ()-> reset_condition_info (thd); clear_all_errors (thd, const_cast (rli)); error= 0; goto end } else {rli- > report (ERROR_LEVEL, actual_error, "Error executing row event:'% s'", (actual_error? Thd- > get_stmt_da ()-> message_text (): "unexpected success or fatal error"); thd- > is_slave_error= 1; const_cast (rli)-> slave_close_thread_tables (thd); DBUG_RETURN (actual_error);}}

You can see that the execution logic of MGR is affected by this parameter.

At this point, the study on "analyzing the impact of slave_skip_errors parameters of MySQL on MGR usability" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.