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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
include/master-slave.inc
[connection master]
connection slave;
connection master;
==== Test Without sql_mode=strict_trans_tables ====
create table t1 (n int not null primary key);
connection slave;
insert into t1 values (1);
connection master;
insert into t1 values (1);
insert into t1 values (2),(3);
connection slave;
select * from t1 order by n;
n
1
2
3
==== Test With sql_mode=strict_trans_tables ====
insert into t1 values (7),(8);
connection master;
set sql_mode=strict_trans_tables;
insert into t1 values (7), (8), (9);
connection slave;
select * from t1 order by n;
n
1
2
3
7
8
include/check_slave_is_running.inc
==== Clean Up ====
connection master;
drop table t1;
connection slave;
connection master;
create table t1(a int primary key);
insert into t1 values (1),(2);
SET SQL_LOG_BIN=0;
delete from t1;
SET SQL_LOG_BIN=1;
set sql_mode=strict_trans_tables;
insert into t1 values (1), (2), (3);
connection slave;
select * from t1;
a
1
2
include/check_slave_is_running.inc
==== Clean Up ====
connection master;
drop table t1;
connection slave;
==== Using Innodb ====
connection master;
SET SQL_LOG_BIN=0;
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`data` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SET SQL_LOG_BIN=1;
connection slave;
call mtr.add_suppression("Slave SQL.*Could not execute .*te_rows event on table test.t.; Duplicate entry.* error.* 1062");
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`data` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection master;
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
SET SQL_LOG_BIN=0;
DELETE FROM t1 WHERE id = 4;
SET SQL_LOG_BIN=1;
UPDATE t1 SET id= id + 3, data = 2;
connection slave;
**** We cannot execute a select as there are differences in the
**** behavior between STMT and RBR.
==== Using MyIsam ====
connection master;
SET SQL_LOG_BIN=0;
CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`data` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SET SQL_LOG_BIN=1;
connection slave;
CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`data` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# Slave_skipped_errros = 4
connection master;
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t2 VALUES(2, 1);
INSERT INTO t2 VALUES(3, 1);
INSERT INTO t2 VALUES(5, 1);
SET SQL_LOG_BIN=0;
DELETE FROM t2 WHERE id = 5;
SET SQL_LOG_BIN=1;
UPDATE t2 SET id= id + 3, data = 2;
connection slave;
# Slave_skipped_errros = 5
**** We cannot execute a select as there are differences in the
**** behavior between STMT and RBR.
****
**** Ensure transactions which are skipped due to encountering a
**** non-deadlock error which is present in --slave-skip-errors result
**** in partially committed transactions
connection master;
CREATE TABLE t3 (a INT UNIQUE) ENGINE=InnoDB;
connection slave;
connection slave;
INSERT INTO t3 VALUES (3);
connection master;
BEGIN;
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (2);
INSERT INTO t3 VALUES (3);
INSERT INTO t3 VALUES (4);
COMMIT;
connection slave;
**** Master and slave tables should have the same data, due to the
**** partially replicated transaction's data overlapping with the data
**** that pre-existed on the slave. That is, despite the transaction
**** consisting of 4 statements, the errored statement should be ignored
**** and the other 3 should commit successfully.
include/diff_tables.inc [master:t3,slave:t3]
connection master;
DROP TABLE t3;
==== Clean Up ====
connection master;
DROP TABLE t1;
DROP TABLE t2;
connection slave;
include/rpl_end.inc
|
