Debian Codegraph @

mysql-test/suite/rpl/t/rpl_alter_extra_persistent.test

raw
1 --source include/have_binlog_format_row.inc
2 --source include/master-slave.inc
3
4 --enable_connect_log
5 --connection master
6 create table t1(a int primary key);
7 insert into t1 values(1);
8 insert into t1 values(2);
9 insert into t1 values(3);
10 insert into t1 values(4);
11
12 --sync_slave_with_master
13 select * from t1 order by a;
14 alter table t1 add column z1 int as(a+1) virtual, add column z2 int as (a+2) persistent;
15 select * from t1 order by a;
16
17 --connection master
18 insert into t1 values(5);
19 insert into t1 values(6);
20
21 --sync_slave_with_master
22 select * from t1 order by a;
23 alter table t1 add column z3 int default(a+2);
24 --connection master
25 insert into t1 values(7);
26 insert into t1 values(8);
27
28 --sync_slave_with_master
29 select * from t1 order by a;
30
31 --connection master
32 delete from t1 where a > 6;
33
34 --echo #UPDATE query
35
36 update t1 set a = a+10;
37 select * from t1 order by a;
38
39 --sync_slave_with_master
40 select * from t1 order by a;
41
42 --connection master
43 update t1 set a = a-10;
44 select * from t1 order by a;
45
46 --sync_slave_with_master
47 select * from t1 order by a;
48
49 --echo #DELETE quert
50 --connection master
51 delete from t1 where a > 2 and a < 4;
52 select * from t1 order by a;
53
54 --sync_slave_with_master
55 select * from t1 order by a;
56
57 --echo #REPLACE query
58 --connection master
59 replace into t1 values(1);
60 replace into t1 values(3);
61 replace into t1 values(1);
62
63 --sync_slave_with_master
64 select * from t1 order by a;
65
66 --echo #SELECT query
67 --connection master
68 select * from t1 where a > 2 and a < 4;
69
70 --connection slave
71 select * from t1 where a > 2 and a < 4;
72
73 --echo #UPDATE with SELECT query
74 --connection master
75 update t1 set a = a + 10 where a > 2 and a < 4;
76 select * from t1 order by a;
77
78 --sync_slave_with_master
79 select * from t1 order by a;
80
81 --connection master
82 update t1 set a = a - 10 where a = 13;
83 select * from t1 order by a;
84
85 --sync_slave_with_master
86 select * from t1 order by a;
87
88 --echo #Break Unique Constraint
89 alter table t1 add column z4 int as (a % 6) persistent unique;
90
91 --connection master
92
93 --echo #entering duplicate value for slave persistent column
94 insert into t1 values(7);
95 select * from t1 order by a;
96
97 --connection slave
98 --let $slave_sql_errno= 1062
99 --source include/wait_for_slave_sql_error.inc
100 select * from t1 order by a;
101 alter table t1 drop column z4;
102 start slave;
103
104 --source include/wait_for_slave_sql_to_start.inc
105
106 --connection master
107 --sync_slave_with_master
108 select * from t1 order by a;
109
110 --connection master
111 select * from t1 order by a;
112 drop table t1;
113 --sync_slave_with_master
114 --connection master
115
116 set binlog_row_image=minimal;
117
118 create table t1(a int primary key auto_increment, b int unique);
119 insert into t1 values(1, 1);
120 insert into t1 values(2, 2);
121 insert into t1 values(3, 3);
122 insert into t1 values(4, 4);
123 insert into t1 values(5, 5);
124
125 --sync_slave_with_master
126 alter table t1 add column d1 int default (b),
127 add column z1 int as (b+1) virtual,
128 add column z2 int as (b+2) persistent;
129 --connection master
130
131 insert into t1 values(6, 6);
132 update t1 set a = 11 where a = 1;
133 update t1 set b = 12 where b = 2;
134
135 delete from t1 where a = 3;
136 delete from t1 where b = 5;
137
138 update t1 set b = 16 where a = 6;
139
140 --sync_slave_with_master
141 select * from t1;
142
143 --echo # Cleanup
144 --connection master
145 drop table t1;
146 --sync_slave_with_master
147 --connection master
148
149
150 set binlog_row_image=minimal;
151
152 --echo #
153 --echo # MDEV-29069 ER_KEY_NOT_FOUND upon online autoinc addition and
154 --echo # concurrent DELETE
155 --echo #
156
157 create or replace table t (a int);
158 insert into t values (10),(20),(30);
159
160 --sync_slave_with_master
161 alter table t add pk int auto_increment primary key;
162
163 --connection master
164 delete from t where a = 20;
165 update t set a = a + 1 where a = 10;
166 --sync_slave_with_master
167 select * from t;
168 --connection master
169
170 --echo #
171 --echo # Add clumsy DEFAULT
172 --echo #
173 create or replace table t (a int);
174 insert into t values (10),(20),(30);
175
176 --sync_slave_with_master
177 alter table t add b int default(RAND() * 20), add key(b),
178 algorithm=copy, lock=none;
179 --connection master
180 delete from t where a = 20;
181 update t set a = a + 1 where a = 10;
182 --sync_slave_with_master
183 select a from t;
184 --connection master
185
186 --echo # CURRENT_TIMESTAMP
187 create or replace table t (a int);
188 insert into t values (10),(20),(30);
189
190 --sync_slave_with_master
191 alter table t add b timestamp default CURRENT_TIMESTAMP, add key(b);
192 --connection master
193 delete from t where a = 20;
194 update t set a = a + 1 where a = 10;
195 --sync_slave_with_master
196 select a from t;
197 --connection master
198
199 --echo # CURRENT_TIMESTAMP, mixed key
200 create or replace table t (a int);
201 insert into t values (10),(20),(30);
202
203 --sync_slave_with_master
204 alter table t add b timestamp default CURRENT_TIMESTAMP, add key(a, b);
205 --connection master
206 delete from t where a = 20;
207 update t set a = a + 1 where a = 10;
208
209 --sync_slave_with_master
210 select a from t;
211 --connection master
212
213 --echo # Mixed primary key
214 create or replace table t (a int);
215 insert into t values (10),(20),(30);
216
217 --sync_slave_with_master
218 alter table t add b int default (1), add primary key(b, a);
219 --connection master
220 delete from t where a = 20;
221 update t set a = a + 1 where a = 10;
222
223 --sync_slave_with_master
224 select a from t;
225 --connection master
226
227 --echo #
228 --echo # Normal row, could be used as a key
229 --echo #
230 create or replace table t (a int);
231 insert into t values (10),(20),(30);
232
233 --sync_slave_with_master
234 alter table t add b int as (a * 10) unique;
235 --connection master
236 delete from t where a = 20;
237 update t set a = a + 1 where a = 10;
238
239 --sync_slave_with_master
240 select * from t;
241 --connection master
242
243 --echo #
244 --echo # Add key for old row
245 --echo #
246 create or replace table t (a int);
247 insert into t values (10),(20),(30);
248
249 --sync_slave_with_master
250 alter table t add unique(a);
251 --connection master
252 delete from t where a = 20;
253 update t set a = a + 1 where a = 10;
254
255 --sync_slave_with_master
256 select * from t;
257
258 --echo # Cleanup
259 --connection master
260 --sync_slave_with_master
261 --connection master
262 drop table t;
263
264 --echo #
265 --echo # MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types
266 --echo #
267 create table t(geo geometrycollection default st_geomfromtext('point(1 1)'));
268 insert into t () values (),(),();
269 --sync_slave_with_master
270 alter table t add vcol9 point as (geo), add key(vcol9);
271 --connection master
272
273 delete from t;
274 --sync_slave_with_master
275 --connection master
276
277 drop table t;
278
279 --source include/rpl_end.inc