Debian Codegraph @

mysql-test/suite/rpl/r/rpl_alter_extra_persistent.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 create table t1(a int primary key);
5 insert into t1 values(1);
6 insert into t1 values(2);
7 insert into t1 values(3);
8 insert into t1 values(4);
9 connection slave;
10 select * from t1 order by a;
11 a
12 1
13 2
14 3
15 4
16 alter table t1 add column z1 int as(a+1) virtual, add column z2 int as (a+2) persistent;
17 select * from t1 order by a;
18 a z1 z2
19 1 2 3
20 2 3 4
21 3 4 5
22 4 5 6
23 connection master;
24 insert into t1 values(5);
25 insert into t1 values(6);
26 connection slave;
27 select * from t1 order by a;
28 a z1 z2
29 1 2 3
30 2 3 4
31 3 4 5
32 4 5 6
33 5 6 7
34 6 7 8
35 alter table t1 add column z3 int default(a+2);
36 connection master;
37 insert into t1 values(7);
38 insert into t1 values(8);
39 connection slave;
40 select * from t1 order by a;
41 a z1 z2 z3
42 1 2 3 3
43 2 3 4 4
44 3 4 5 5
45 4 5 6 6
46 5 6 7 7
47 6 7 8 8
48 7 8 9 9
49 8 9 10 10
50 connection master;
51 delete from t1 where a > 6;
52 #UPDATE query
53 update t1 set a = a+10;
54 select * from t1 order by a;
55 a
56 11
57 12
58 13
59 14
60 15
61 16
62 connection slave;
63 select * from t1 order by a;
64 a z1 z2 z3
65 11 12 13 3
66 12 13 14 4
67 13 14 15 5
68 14 15 16 6
69 15 16 17 7
70 16 17 18 8
71 connection master;
72 update t1 set a = a-10;
73 select * from t1 order by a;
74 a
75 1
76 2
77 3
78 4
79 5
80 6
81 connection slave;
82 select * from t1 order by a;
83 a z1 z2 z3
84 1 2 3 3
85 2 3 4 4
86 3 4 5 5
87 4 5 6 6
88 5 6 7 7
89 6 7 8 8
90 #DELETE quert
91 connection master;
92 delete from t1 where a > 2 and a < 4;
93 select * from t1 order by a;
94 a
95 1
96 2
97 4
98 5
99 6
100 connection slave;
101 select * from t1 order by a;
102 a z1 z2 z3
103 1 2 3 3
104 2 3 4 4
105 4 5 6 6
106 5 6 7 7
107 6 7 8 8
108 #REPLACE query
109 connection master;
110 replace into t1 values(1);
111 replace into t1 values(3);
112 replace into t1 values(1);
113 connection slave;
114 select * from t1 order by a;
115 a z1 z2 z3
116 1 2 3 3
117 2 3 4 4
118 3 4 5 5
119 4 5 6 6
120 5 6 7 7
121 6 7 8 8
122 #SELECT query
123 connection master;
124 select * from t1 where a > 2 and a < 4;
125 a
126 3
127 connection slave;
128 select * from t1 where a > 2 and a < 4;
129 a z1 z2 z3
130 3 4 5 5
131 #UPDATE with SELECT query
132 connection master;
133 update t1 set a = a + 10 where a > 2 and a < 4;
134 select * from t1 order by a;
135 a
136 1
137 2
138 4
139 5
140 6
141 13
142 connection slave;
143 select * from t1 order by a;
144 a z1 z2 z3
145 1 2 3 3
146 2 3 4 4
147 4 5 6 6
148 5 6 7 7
149 6 7 8 8
150 13 14 15 5
151 connection master;
152 update t1 set a = a - 10 where a = 13;
153 select * from t1 order by a;
154 a
155 1
156 2
157 3
158 4
159 5
160 6
161 connection slave;
162 select * from t1 order by a;
163 a z1 z2 z3
164 1 2 3 3
165 2 3 4 4
166 3 4 5 5
167 4 5 6 6
168 5 6 7 7
169 6 7 8 8
170 #Break Unique Constraint
171 alter table t1 add column z4 int as (a % 6) persistent unique;
172 connection master;
173 #entering duplicate value for slave persistent column
174 insert into t1 values(7);
175 select * from t1 order by a;
176 a
177 1
178 2
179 3
180 4
181 5
182 6
183 7
184 connection slave;
185 include/wait_for_slave_sql_error.inc [errno=1062]
186 select * from t1 order by a;
187 a z1 z2 z3 z4
188 1 2 3 3 1
189 2 3 4 4 2
190 3 4 5 5 3
191 4 5 6 6 4
192 5 6 7 7 5
193 6 7 8 8 0
194 alter table t1 drop column z4;
195 start slave;
196 include/wait_for_slave_sql_to_start.inc
197 connection master;
198 connection slave;
199 select * from t1 order by a;
200 a z1 z2 z3
201 1 2 3 3
202 2 3 4 4
203 3 4 5 5
204 4 5 6 6
205 5 6 7 7
206 6 7 8 8
207 7 8 9 9
208 connection master;
209 select * from t1 order by a;
210 a
211 1
212 2
213 3
214 4
215 5
216 6
217 7
218 drop table t1;
219 connection slave;
220 connection master;
221 set binlog_row_image=minimal;
222 create table t1(a int primary key auto_increment, b int unique);
223 insert into t1 values(1, 1);
224 insert into t1 values(2, 2);
225 insert into t1 values(3, 3);
226 insert into t1 values(4, 4);
227 insert into t1 values(5, 5);
228 connection slave;
229 alter table t1 add column d1 int default (b),
230 add column z1 int as (b+1) virtual,
231 add column z2 int as (b+2) persistent;
232 connection master;
233 insert into t1 values(6, 6);
234 update t1 set a = 11 where a = 1;
235 update t1 set b = 12 where b = 2;
236 delete from t1 where a = 3;
237 delete from t1 where b = 5;
238 update t1 set b = 16 where a = 6;
239 connection slave;
240 select * from t1;
241 a b d1 z1 z2
242 11 1 1 2 3
243 2 12 2 13 14
244 4 4 4 5 6
245 6 16 6 17 18
246 # Cleanup
247 connection master;
248 drop table t1;
249 connection slave;
250 connection master;
251 set binlog_row_image=minimal;
252 #
253 # MDEV-29069 ER_KEY_NOT_FOUND upon online autoinc addition and
254 # concurrent DELETE
255 #
256 create or replace table t (a int);
257 insert into t values (10),(20),(30);
258 connection slave;
259 alter table t add pk int auto_increment primary key;
260 connection master;
261 delete from t where a = 20;
262 update t set a = a + 1 where a = 10;
263 connection slave;
264 select * from t;
265 a pk
266 11 1
267 30 3
268 connection master;
269 #
270 # Add clumsy DEFAULT
271 #
272 create or replace table t (a int);
273 insert into t values (10),(20),(30);
274 connection slave;
275 alter table t add b int default(RAND() * 20), add key(b),
276 algorithm=copy, lock=none;
277 connection master;
278 delete from t where a = 20;
279 update t set a = a + 1 where a = 10;
280 connection slave;
281 select a from t;
282 a
283 11
284 30
285 connection master;
286 # CURRENT_TIMESTAMP
287 create or replace table t (a int);
288 insert into t values (10),(20),(30);
289 connection slave;
290 alter table t add b timestamp default CURRENT_TIMESTAMP, add key(b);
291 connection master;
292 delete from t where a = 20;
293 update t set a = a + 1 where a = 10;
294 connection slave;
295 select a from t;
296 a
297 11
298 30
299 connection master;
300 # CURRENT_TIMESTAMP, mixed key
301 create or replace table t (a int);
302 insert into t values (10),(20),(30);
303 connection slave;
304 alter table t add b timestamp default CURRENT_TIMESTAMP, add key(a, b);
305 connection master;
306 delete from t where a = 20;
307 update t set a = a + 1 where a = 10;
308 connection slave;
309 select a from t;
310 a
311 11
312 30
313 connection master;
314 # Mixed primary key
315 create or replace table t (a int);
316 insert into t values (10),(20),(30);
317 connection slave;
318 alter table t add b int default (1), add primary key(b, a);
319 connection master;
320 delete from t where a = 20;
321 update t set a = a + 1 where a = 10;
322 connection slave;
323 select a from t;
324 a
325 11
326 30
327 connection master;
328 #
329 # Normal row, could be used as a key
330 #
331 create or replace table t (a int);
332 insert into t values (10),(20),(30);
333 connection slave;
334 alter table t add b int as (a * 10) unique;
335 connection master;
336 delete from t where a = 20;
337 update t set a = a + 1 where a = 10;
338 connection slave;
339 select * from t;
340 a b
341 11 110
342 30 300
343 connection master;
344 #
345 # Add key for old row
346 #
347 create or replace table t (a int);
348 insert into t values (10),(20),(30);
349 connection slave;
350 alter table t add unique(a);
351 connection master;
352 delete from t where a = 20;
353 update t set a = a + 1 where a = 10;
354 connection slave;
355 select * from t;
356 a
357 11
358 30
359 # Cleanup
360 connection master;
361 connection slave;
362 connection master;
363 drop table t;
364 #
365 # MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types
366 #
367 create table t(geo geometrycollection default st_geomfromtext('point(1 1)'));
368 insert into t () values (),(),();
369 connection slave;
370 alter table t add vcol9 point as (geo), add key(vcol9);
371 connection master;
372 delete from t;
373 connection slave;
374 connection master;
375 drop table t;
376 include/rpl_end.inc