1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection master]
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
CALL mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
==== Case 1: Killed query on master -> error on slave ====
include/assert.inc [Test only works if binlog_format is statement]
[connection master]
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
CREATE TABLE trigger_table (a INT) ENGINE = MyISAM;
SET SQL_LOG_BIN=0;
CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
IF NEW.a != 0 THEN
SET @@GLOBAL.BINLOG_FORMAT = 'row';
DO SLEEP(1000000);
END IF;
INSERT INTO trigger_table VALUES (1);
END|
SET SQL_LOG_BIN=1;
INSERT INTO t1 VALUES (0), (1);
[connection master1]
KILL QUERY <CONNECTION_ID>;
[connection master]
ERROR 70100: Query execution was interrupted
SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
include/assert.inc [At most one row should be inserted in db1.t1]
include/assert.inc [At least one row should be inserted in db1.trigger_table]
SET SQL_LOG_BIN=0;
DROP TRIGGER trig1;
SET SQL_LOG_BIN=1;
DROP TABLE trigger_table;
DROP TABLE t1;
DROP DATABASE db1;
USE test;
---- Verify there is an error on slave ----
include/sync_slave_io_with_master.inc
include/wait_for_slave_sql_error.inc [errno=3001]
---- Verify that nothing was inserted on slave -----
include/assert.inc [Nothing should be inserted on slave]
---- Clean up ----
include/stop_slave_io.inc
RESET SLAVE;
Warnings:
Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
RESET MASTER;
DROP DATABASE db1;
[connection master]
RESET MASTER;
[connection slave]
include/start_slave.inc
[connection master]
==== Case 2: Master kills query -> no error if slave filters by db ====
include/assert.inc [Test only works if binlog_format is statement]
[connection master]
CREATE DATABASE ignored_db;
USE ignored_db;
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
CREATE TABLE trigger_table (a INT) ENGINE = MyISAM;
SET SQL_LOG_BIN=0;
CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
IF NEW.a != 0 THEN
SET @@GLOBAL.BINLOG_FORMAT = 'row';
DO SLEEP(1000000);
END IF;
INSERT INTO trigger_table VALUES (1);
END|
SET SQL_LOG_BIN=1;
INSERT INTO t1 VALUES (0), (1);
[connection master1]
KILL QUERY <CONNECTION_ID>;
[connection master]
ERROR 70100: Query execution was interrupted
SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
include/assert.inc [At most one row should be inserted in ignored_db.t1]
include/assert.inc [At least one row should be inserted in ignored_db.trigger_table]
SET SQL_LOG_BIN=0;
DROP TRIGGER trig1;
SET SQL_LOG_BIN=1;
DROP TABLE trigger_table;
DROP TABLE t1;
DROP DATABASE ignored_db;
USE test;
---- Verify all was replicated ok ----
include/sync_slave_sql_with_master.inc
[connection master]
==== Case 3: Master kills query -> no error if slave filters by table ====
include/assert.inc [Test only works if binlog_format is statement]
[connection master]
CREATE DATABASE db1;
USE db1;
CREATE TABLE ignored_table (a INT) ENGINE = MyISAM;
CREATE TABLE trigger_table (a INT) ENGINE = MyISAM;
SET SQL_LOG_BIN=0;
CREATE TRIGGER trig1 BEFORE INSERT ON ignored_table FOR EACH ROW
BEGIN
IF NEW.a != 0 THEN
SET @@GLOBAL.BINLOG_FORMAT = 'row';
DO SLEEP(1000000);
END IF;
INSERT INTO trigger_table VALUES (1);
END|
SET SQL_LOG_BIN=1;
INSERT INTO ignored_table VALUES (0), (1);
[connection master1]
KILL QUERY <CONNECTION_ID>;
[connection master]
ERROR 70100: Query execution was interrupted
SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
include/assert.inc [At most one row should be inserted in db1.ignored_table]
include/assert.inc [At least one row should be inserted in db1.trigger_table]
SET SQL_LOG_BIN=0;
DROP TRIGGER trig1;
SET SQL_LOG_BIN=1;
DROP TABLE trigger_table;
DROP TABLE ignored_table;
DROP DATABASE db1;
USE test;
---- Verify all was replicated ok ----
include/sync_slave_sql_with_master.inc
include/rpl_end.inc
|
