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
|
include/master-slave.inc
[connection master]
set sql_mode="";
connection slave;
set sql_mode="";
connection master;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
call mtr.add_suppression("Unsafe statement written to the binary log*");
CREATE TABLE t1 (a bigint unsigned not null);
CREATE TABLE t2 (a char(255) not null);
There are the following types of variables:
1) variables that are NOT replicated correctly when using STATEMENT mode;
connection master;
SELECT @@max_join_size;
@@max_join_size
18446744073709551615
connection slave;
SELECT @@max_join_size;
@@max_join_size
18446744073709551615
connection master;
SET STATEMENT max_join_size=2 FOR
INSERT INTO t1 VALUES(@@max_join_size);
SELECT @@max_join_size;
@@max_join_size
18446744073709551615
connection slave;
SELECT * FROM t1;
a
18446744073709551615
SELECT @@max_join_size;
@@max_join_size
18446744073709551615
connection master;
DELETE FROM t1;
2) variables thar ARE replicated correctly
They must be replicated correctly with "SET STATEMENT" too.
connection master;
SELECT @@auto_increment_increment;
@@auto_increment_increment
1
connection slave;
SELECT @@auto_increment_increment;
@@auto_increment_increment
1
connection master;
SET STATEMENT auto_increment_increment=10 FOR
INSERT INTO t1 VALUES(@@auto_increment_increment);
SELECT @@auto_increment_increment;
@@auto_increment_increment
1
connection slave;
SELECT * FROM t1;
a
10
SELECT @@auto_increment_increment;
@@auto_increment_increment
1
connection master;
DELETE FROM t1;
3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
connection master;
SELECT @@sql_mode;
@@sql_mode
connection slave;
SELECT @@sql_mode;
@@sql_mode
connection master;
SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR
INSERT INTO t2 VALUES(@@sql_mode);
SELECT @@sql_mode;
@@sql_mode
connection slave;
SELECT * FROM t2;
a
ERROR_FOR_DIVISION_BY_ZERO
SELECT @@sql_mode;
@@sql_mode
connection master;
DELETE FROM t2;
connection master;
SELECT @@sql_mode;
@@sql_mode
connection slave;
SELECT @@sql_mode;
@@sql_mode
connection master;
SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR
INSERT INTO t2 VALUES(@@sql_mode);
SELECT @@sql_mode;
@@sql_mode
connection slave;
SELECT * FROM t2;
a
SELECT @@sql_mode;
@@sql_mode
connection master;
DELETE FROM t2;
4) variables that are not replicated at all:
default_storage_engine, storage_engine, max_heap_table_size
connection master;
SELECT @@max_heap_table_size;
@@max_heap_table_size
1048576
connection slave;
SELECT @@max_heap_table_size;
@@max_heap_table_size
1048576
connection master;
SET STATEMENT max_heap_table_size=16384 FOR
INSERT INTO t1 VALUES(@@max_heap_table_size);
SELECT @@max_heap_table_size;
@@max_heap_table_size
1048576
connection slave;
SELECT * FROM t1;
a
1048576
SELECT @@max_heap_table_size;
@@max_heap_table_size
1048576
connection master;
DELETE FROM t1;
connection master;
DROP TABLE t1;
DROP TABLE t2;
connection slave;
include/stop_slave.inc
|
