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
[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=latin1
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=latin1
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=latin1
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=latin1
show global status like 'slave_skipped_errors';
Variable_name Value
Slave_skipped_errors 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;
show global status like 'slave_skipped_errors';
Variable_name Value
Slave_skipped_errors 5
**** We cannot execute a select as there are differences in the
**** behavior between STMT and RBR.
==== Clean Up ====
connection master;
DROP TABLE t1;
DROP TABLE t2;
connection slave;
include/rpl_end.inc
|
