Debian Codegraph @

mysql-test/suite/galera/t/galera_binlog_stmt_autoinc.test

raw
1 ##
2 ## Tests the auto-increment with binlog in STATEMENT mode.
3 ##
4
5 --source include/galera_cluster.inc
6 --source include/force_restart.inc
7
8 --let $node_1=node_1
9 --let $node_2=node_2
10 --source include/auto_increment_offset_save.inc
11
12 --connection node_1
13 SET GLOBAL auto_increment_offset=1;
14 --connection node_2
15 SET GLOBAL auto_increment_offset=2;
16
17 ##
18 ## Verify the correct operation of the auto-increment when the binlog
19 ## format artificially set to the 'STATEMENT' (although this mode is
20 ## not recommended in the current version):
21 ##
22
23 --connection node_2
24 SET GLOBAL wsrep_forced_binlog_format='STATEMENT';
25
26 --disable_query_log
27 call mtr.add_suppression("Unsafe statement written to the binary log");
28 --enable_query_log
29
30 --connection node_1
31 SET GLOBAL wsrep_forced_binlog_format='STATEMENT';
32
33 --disable_query_log
34 call mtr.add_suppression("Unsafe statement written to the binary log");
35 --enable_query_log
36
37 CREATE TABLE t1 (
38 i int(11) NOT NULL primary key AUTO_INCREMENT,
39 c char(32) DEFAULT 'dummy_text'
40 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
41
42 show variables like 'auto_increment%';
43 insert into t1(i) values(null);
44
45 select * from t1 order by i;
46
47 insert into t1(i) values(null), (null), (null);
48
49 select * from t1 order by i;
50
51 # Ensure both appliers have some work to perform in parallel.
52 # This way we can see they both have proper isolation level set.
53 insert into t1(i) values(null);
54 insert into t1(i) values(null);
55 insert into t1(i) values(null);
56 insert into t1(i) values(null);
57
58 select * from t1 order by i;
59
60 --connection node_2
61 --let $wait_condition = SELECT COUNT(*) = 8 FROM t1;
62 --source include/wait_condition.inc
63 show variables like 'auto_increment%';
64 select * from t1 order by i;
65
66 SET GLOBAL wsrep_forced_binlog_format='none';
67
68 --connection node_1
69
70 SET GLOBAL wsrep_forced_binlog_format='none';
71
72 drop table t1;
73
74 ##
75 ## Check the operation when the automatic control over the auto-increment
76 ## settings is switched off, that is, when we use the increment step and
77 ## the offset specified by the user. In the current session, the binlog
78 ## format is set to 'STATEMENT'. It is important that the values of the
79 ## auto-increment options does not changed on other node - it allows us
80 ## to check the correct transmission of the auto-increment options to
81 ## other nodes:
82 ##
83
84 --disable_warnings
85 SET SESSION binlog_format='STATEMENT';
86 --enable_warnings
87
88 show variables like 'binlog_format';
89
90 SET GLOBAL wsrep_auto_increment_control='OFF';
91
92 let $increment_node1 = `SELECT @@session.auto_increment_increment`;
93 let $offset_node1 = `SELECT @@session.auto_increment_offset`;
94 SET SESSION auto_increment_increment = 3;
95 SET SESSION auto_increment_offset = 1;
96
97 CREATE TABLE t1 (
98 i int(11) NOT NULL primary key AUTO_INCREMENT,
99 c char(32) DEFAULT 'dummy_text'
100 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
101
102 show variables like 'auto_increment%';
103 insert into t1(i) values(null);
104
105 select * from t1 order by i;
106
107 insert into t1(i) values(null), (null), (null);
108
109 select * from t1 order by i;
110
111 --connection node_2
112 --let $wait_condition = SELECT COUNT(*) = 4 FROM t1;
113 --source include/wait_condition.inc
114 show variables like 'auto_increment%';
115
116 select * from t1 order by i;
117
118 --connection node_1
119 SET GLOBAL wsrep_auto_increment_control='ON';
120 SET SESSION binlog_format='ROW';
121 --source include/auto_increment_offset_restore.inc
122 --connection node_1
123 show variables like 'binlog_format';
124 show variables like '%auto_increment%';
125
126 ##
127 ## Verify the recovery of original user-defined values after
128 ## stopping the automatic control over auto-increment:
129 ##
130
131 SET GLOBAL wsrep_auto_increment_control='OFF';
132
133 show variables like '%auto_increment%';
134
135 ##
136 ## Restore original options and drop test table:
137 ##
138
139 SET GLOBAL wsrep_auto_increment_control='ON';
140 --source include/auto_increment_offset_restore.inc
141 --connection node_1
142 drop table t1;
143
144 ##
145 ## Verify the correct operation of the auto-increment when the binlog
146 ## format set to the 'ROW':
147 ##
148
149 --connection node_2
150 SET GLOBAL wsrep_forced_binlog_format='ROW';
151
152 --connection node_1
153 SET GLOBAL wsrep_forced_binlog_format='ROW';
154
155 CREATE TABLE t1 (
156 i int(11) NOT NULL primary key AUTO_INCREMENT,
157 c char(32) DEFAULT 'dummy_text'
158 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
159
160 show variables like 'auto_increment%';
161 insert into t1(i) values(null);
162
163 select * from t1 order by i;
164
165 insert into t1(i) values(null), (null), (null);
166
167 select * from t1 order by i;
168
169 --connection node_2
170 --let $wait_condition = SELECT COUNT(*) = 4 FROM t1;
171 --source include/wait_condition.inc
172
173 show variables like 'auto_increment%';
174 select * from t1 order by i;
175
176 SET GLOBAL wsrep_forced_binlog_format='none';
177
178 --connection node_1
179
180 SET GLOBAL wsrep_forced_binlog_format='none';
181
182 drop table t1;
183
184 ##
185 ## Check the operation when the automatic control over the auto-increment
186 ## settings is switched off, that is, when we use the increment step and
187 ## the offset specified by the user. In the current session, the binlog
188 ## format is set to 'ROW'. It is important that the values of the
189 ## auto-increment options does not changed on other node - it allows us
190 ## to check the correct transmission of the auto-increment options to
191 ## other nodes:
192 ##
193
194 SET SESSION binlog_format='ROW';
195
196 show variables like 'binlog_format';
197
198 SET GLOBAL wsrep_auto_increment_control='OFF';
199
200 SET SESSION auto_increment_increment = 3;
201 SET SESSION auto_increment_offset = 1;
202
203 CREATE TABLE t1 (
204 i int(11) NOT NULL primary key AUTO_INCREMENT,
205 c char(32) DEFAULT 'dummy_text'
206 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
207
208 show variables like 'auto_increment%';
209 insert into t1(i) values(null);
210
211 select * from t1 order by i;
212
213 insert into t1(i) values(null), (null), (null);
214
215 select * from t1 order by i;
216
217 --connection node_2
218 --let $wait_condition = SELECT COUNT(*) = 4 FROM t1;
219 --source include/wait_condition.inc
220 show variables like 'auto_increment%';
221
222 select * from t1 order by i;
223
224 --connection node_1
225
226 ##
227 ## Verify the return to automatic calculation of the step
228 ## and offset of the auto-increment:
229 ##
230
231 SET GLOBAL wsrep_auto_increment_control='ON';
232 --source include/auto_increment_offset_restore.inc
233
234 show variables like 'binlog_format';
235 show variables like '%auto_increment%';
236
237 ##
238 ## Verify the recovery of original user-defined values after
239 ## stopping the automatic control over auto-increment:
240 ##
241
242 SET GLOBAL wsrep_auto_increment_control='OFF';
243
244 show variables like '%auto_increment%';
245
246 ##
247 ## Restore original options and drop test table:
248 ##
249
250 SET GLOBAL wsrep_auto_increment_control='ON';
251
252 drop table t1;
253
254 --source include/auto_increment_offset_restore.inc