| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
###################################################################### |
| 7 |
# BUG#37331118 Applier prepare error retry |
| 8 |
# |
| 9 |
# The replication applier normally retries temporary errors occurring |
| 10 |
# while applying transactions. Such retry logic is not performed for |
| 11 |
# transactions containing row events where the STMT_END_F flag is |
| 12 |
# missing. |
| 13 |
# For such events, the statement will instead be committed as an |
| 14 |
# additional step while applying the subsequent COMMIT query event when |
| 15 |
# it is detected that there are still locked tables. When committing the |
| 16 |
# statement, temporary errors are not handled properly. |
| 17 |
# |
| 18 |
# This patch reproduces the problem by writing an epoch trans with |
| 19 |
# simulated writes from multiple server ids on the source. The replica |
| 20 |
# then uses IGNORE_SERVER_IDS(<last_server_id_in_binlog>) to cause the |
| 21 |
# STMT_END_F to be filtered away, thus activating the above-described |
| 22 |
# code path in the applier. By holding a lock on one of the rows to be |
| 23 |
# updated by the applier, error handling is triggered. |
| 24 |
# For reference the test then also triggers error and retry handling |
| 25 |
# when STMT_END_F has not been filtered away. |
| 26 |
###################################################################### |
| 27 |
# Populate the source and replica |
| 28 |
# - create table and populate with two rows, sync to replica |
| 29 |
[connection master] |
| 30 |
CREATE TABLE test_multi_server_id ( |
| 31 |
id INT PRIMARY KEY, |
| 32 |
what VARCHAR(128), |
| 33 |
epoch INT UNSIGNED |
| 34 |
) ENGINE = NDB; |
| 35 |
INSERT INTO test_multi_server_id |
| 36 |
VALUES (31, "not updated", 0), (32, "not updated", 0); |
| 37 |
# Source need log_replica_updates turned on |
| 38 |
show variables like 'log_replica_updates'; |
| 39 |
Variable_name Value |
| 40 |
log_replica_updates ON |
| 41 |
include/sync_slave_sql_with_master.inc |
| 42 |
# Replica need both workers and retries to be greater than 1 |
| 43 |
show variables like 'replica_parallel_workers'; |
| 44 |
Variable_name Value |
| 45 |
replica_parallel_workers 2 |
| 46 |
show variables like 'replica_transaction_retries'; |
| 47 |
Variable_name Value |
| 48 |
replica_transaction_retries 3 |
| 49 |
include/stop_slave.inc |
| 50 |
SELECT * FROM test_multi_server_id ORDER BY id; |
| 51 |
id what epoch |
| 52 |
31 not updated 0 |
| 53 |
32 not updated 0 |
| 54 |
# Supress MTA errors generated by test |
| 55 |
call mtr.add_suppression(".*worker thread retried transaction [0-9] time.*"); |
| 56 |
call mtr.add_suppression(".*Worker [0-9] failed executing transaction.*"); |
| 57 |
call mtr.add_suppression(".*replica coordinator and worker threads are stopped.*"); |
| 58 |
[connection master] |
| 59 |
FLUSH LOGS; |
| 60 |
SET @save_debug= @@GLOBAL.debug; |
| 61 |
SET @@GLOBAL.debug="+d,ndb_binlog_log_multi_server_id"; |
| 62 |
SET @@GLOBAL.debug= @save_debug; |
| 63 |
SELECT id, what FROM test_multi_server_id ORDER BY id; |
| 64 |
id what |
| 65 |
31 change from 31 |
| 66 |
32 change from 32 |
| 67 |
# Wait for ndb_binlog thread... |
| 68 |
[connection slave1] |
| 69 |
begin; |
| 70 |
SELECT * FROM test_multi_server_id WHERE id=<other_server_id> FOR UPDATE; |
| 71 |
id what epoch |
| 72 |
<other_server_id> not updated 0 |
| 73 |
# |
| 74 |
# A) Test error and retry with STMT_END_F filtered away |
| 75 |
# - i.e statement commit from Query_log_event("COMMIT") |
| 76 |
# |
| 77 |
[connection slave] |
| 78 |
CHANGE REPLICATION SOURCE TO |
| 79 |
IGNORE_SERVER_IDS = (<last_server_id>); |
| 80 |
include/start_slave.inc |
| 81 |
# Wait for replication error |
| 82 |
include/wait_for_slave_sql_error.inc [errno=1205] |
| 83 |
SELECT LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, |
| 84 |
APPLYING_TRANSACTION_RETRIES_COUNT |
| 85 |
FROM performance_schema.replication_applier_status_by_worker |
| 86 |
WHERE LAST_ERROR_NUMBER != 0; |
| 87 |
LAST_ERROR_NUMBER 1205 |
| 88 |
LAST_ERROR_MESSAGE Worker NNN failed executing transaction 'ANONYMOUS' at source log master-bin.000002, end_log_pos NNN; Lock wait timeout exceeded; try restarting transaction |
| 89 |
APPLYING_TRANSACTION_RETRIES_COUNT 3 |
| 90 |
SELECT LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE |
| 91 |
FROM performance_schema.replication_applier_status_by_coordinator; |
| 92 |
LAST_ERROR_NUMBER 1205 |
| 93 |
LAST_ERROR_MESSAGE Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker NNN failed executing transaction 'ANONYMOUS' at source log master-bin.000002, end_log_pos NNN. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. |
| 94 |
SELECT id, what FROM test_multi_server_id ORDER BY id; |
| 95 |
id what |
| 96 |
31 not updated |
| 97 |
32 not updated |
| 98 |
# |
| 99 |
# B) Test error and retry when STMT_END_F has NOT been filtered |
| 100 |
# - i.e statement commit from Rows_log_event |
| 101 |
# |
| 102 |
[connection slave] |
| 103 |
include/stop_slave.inc |
| 104 |
CHANGE REPLICATION SOURCE TO IGNORE_SERVER_IDS = (); |
| 105 |
include/start_slave.inc |
| 106 |
# Wait for replication error |
| 107 |
include/wait_for_slave_sql_error.inc [errno=1205] |
| 108 |
SELECT LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, |
| 109 |
APPLYING_TRANSACTION_RETRIES_COUNT |
| 110 |
FROM performance_schema.replication_applier_status_by_worker |
| 111 |
WHERE LAST_ERROR_NUMBER != 0; |
| 112 |
LAST_ERROR_NUMBER 1205 |
| 113 |
LAST_ERROR_MESSAGE Worker NNN failed executing transaction 'ANONYMOUS' at source log master-bin.000002, end_log_pos NNN; Lock wait timeout exceeded; try restarting transaction |
| 114 |
APPLYING_TRANSACTION_RETRIES_COUNT 3 |
| 115 |
SELECT LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE |
| 116 |
FROM performance_schema.replication_applier_status_by_coordinator; |
| 117 |
LAST_ERROR_NUMBER 1205 |
| 118 |
LAST_ERROR_MESSAGE Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker NNN failed executing transaction 'ANONYMOUS' at source log master-bin.000002, end_log_pos NNN. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. |
| 119 |
SELECT id, what FROM test_multi_server_id ORDER BY id; |
| 120 |
id what |
| 121 |
31 not updated |
| 122 |
32 not updated |
| 123 |
[connection slave1] |
| 124 |
# Release lock |
| 125 |
commit; |
| 126 |
# Start replication again |
| 127 |
[connection slave] |
| 128 |
include/start_slave.inc |
| 129 |
SELECT id, what FROM test_multi_server_id ORDER BY id; |
| 130 |
id what |
| 131 |
31 change from 31 |
| 132 |
32 change from 32 |
| 133 |
# Cleanup |
| 134 |
[connection master] |
| 135 |
DROP TABLE test_multi_server_id; |
| 136 |
include/rpl_end.inc |