Debian Codegraph @

mysql-test/suite/ndb/t/ndb_restore_conv_pk_blob.test

raw
1 -- source include/have_ndb.inc
2 -- source include/have_ndb_debug.inc
3 -- source suite/ndb/include/backup_restore_setup.inc
4
5
6 --echo ************************************
7 --echo *Blob table handing with pk changes*
8 --echo ************************************
9 --echo No changes to Blobs
10
11 create table test.t1 (a varchar(20) not null,
12 b varchar(20) not null,
13 c varchar(20) not null,
14 d text(3000),
15 primary key(a,b)) engine=ndb;
16
17 insert into test.t1 values
18 ("1a","1b","1c",repeat("1d", 1000)),
19 ("2a","2b","2c",repeat("2d", 1000)),
20 ("3a","3b","3c",repeat("3d", 1000)),
21 ("4a","4b","4c",repeat("4d", 1000)),
22 ("5a","5b","5c",repeat("5d", 1000)),
23 ("6a","6b","6c",repeat("6d", 1000));
24
25 create table test.t2 (a varchar(20) not null,
26 b varchar(20) not null,
27 c text not null,
28 d text not null,
29 primary key (a,b)) engine=ndb;
30
31 insert into test.t2 values
32 ("Ein", "Ein", repeat("Ein", 1000), repeat("Ein", 1000)),
33 ("Zwei", "Zwei", repeat("Zwei", 1000), repeat("Zwei", 1000)),
34 ("Drei", "Drei", repeat("Drei", 1000), repeat("Drei", 1000)),
35 ("Vier", "Vier", repeat("Vier", 1000), repeat("Vier", 1000)),
36 ("Funf", "Funf", repeat("Funf", 1000), repeat("Funf", 1000)),
37 ("Sechs", "Sechs", repeat("Sechs", 1000), repeat("Sechs", 1000)),
38 ("Sieben", "Sieben", repeat("Sieben", 1000), repeat("Sieben", 1000));
39
40
41 --echo Stall backup completion
42 --exec $NDB_MGM -e "all error 10039" >> $NDB_TOOLS_OUTPUT 2>&1
43 --echo Run backup
44 --source suite/ndb/t/ndb_backup_nowait_start.inc
45
46 --echo Make some changes which will be logged...
47 insert into test.t1 values
48 ("7a","7b","7c",repeat("7d",1000)),
49 ("8a","8b","8c",repeat("8d",1000)),
50 ("9a","9b","9c",repeat("9d",1000));
51
52 # Column c becomes part of key in some testcases
53 # Cause it to be considered updated without any
54 # actual change occurring
55 update test.t1 set c=c, d=concat(d, "-modified");
56
57 delete from test.t1 where a in ("5a","8a");
58
59 # And t2...
60 insert into test.t2 values
61 ("Acht", "Acht", repeat("Acht", 10), repeat("Acht", 2000)),
62 ("Neun", "Neun", repeat("Neun", 200), repeat("Neun", 5));
63
64 # Following is logging as DELETE, INSERT as existing PK col is changed
65 update test.t2 set b=concat(b, "-jah") where a like "%ei";
66
67 delete from test.t2 where a like "S%";
68
69
70 --echo Allow backup to complete...
71 --exec $NDB_MGM -e "all error 0" >> $NDB_TOOLS_OUTPUT 2>&1
72
73 --source suite/ndb/t/ndb_backup_nowait_wait.inc
74
75 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
76 select a,b,length(c), md5(c), right(c, 20), length(d), md5(d), right(d,20) from test.t2 order by a;
77
78
79 drop table test.t1;
80 truncate table test.t2;
81
82
83 --echo ********************************************
84 --echo * Tests with column change from pk <-> !pk *
85 --echo ********************************************
86
87 --echo Extend pk (a,b) -> (a,b,c)
88 --echo **************************
89 create table test.t1 (a varchar(20) not null,
90 b varchar(20) not null,
91 c varchar(20) not null,
92 d text(3000),
93 primary key(a,b,c)) engine=ndb;
94
95 --echo Restore will require use of a staging table due to the blobs
96 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
97 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
98
99 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
100
101 drop table test.t1;
102 truncate table test.t2;
103
104 --echo Extend pk (a,b) -> (a,b,c) with pk type promotion
105 --echo *************************************************
106 create table test.t1 (a varchar(40) not null,
107 b varchar(20) not null,
108 c varchar(20) not null,
109 d text(3000),
110 primary key(a,b,c)) engine=ndb;
111
112 --echo Restore will require use of a staging table due to the blobs
113 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
114 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
115
116 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
117
118 drop table test.t1;
119 truncate table test.t2;
120
121 --echo Extend pk (a,b) -> (a,b,c) with pk type demotion
122 --echo ************************************************
123 create table test.t1 (a varchar(15) not null,
124 b varchar(20) not null,
125 c varchar(20) not null,
126 d text(3000),
127 primary key(a,b,c)) engine=ndb;
128
129 --echo Restore will require use of a staging table due to the blobs
130 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
131 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
132
133 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
134
135 drop table test.t1;
136 truncate table test.t2;
137
138 --echo Extend pk (a,b) -> (a,b,c) with pk and blob type changes
139 --echo ********************************************************
140 create table test.t1 (a varchar(40) not null,
141 b varchar(20) not null,
142 c varchar(20) not null,
143 d varchar(2500),
144 primary key(a,b,c)) engine=ndb;
145
146 --echo Restore will require use of a staging table due to the blobs
147 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
148 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
149
150 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
151
152 drop table test.t1;
153 truncate table test.t2;
154
155 --echo Contract pk (a,b) -> (b)
156 --echo **************************
157 create table test.t1 (a varchar(20) not null,
158 b varchar(20) not null,
159 c varchar(20) not null,
160 d text(3000),
161 primary key(b)) engine=ndb;
162
163 --echo Run restore
164 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
165 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
166
167 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
168
169 drop table test.t1;
170 truncate table test.t2;
171
172 --echo Contract pk (a,b) -> (b), dropping a
173 --echo ************************************
174 create table test.t1 (b varchar(20) not null,
175 c varchar(20) not null,
176 d text(3000),
177 primary key(b)) engine=ndb;
178
179 --echo Run restore
180 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
181 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
182
183 select b,c,length(d), md5(d), right(d,20) from test.t1 order by b;
184
185 drop table test.t1;
186 truncate table test.t2;
187
188 --echo Contract pk (a,b) -> (b)
189 --echo Also add type changes
190 --echo **************************
191 create table test.t1 (a varchar(200) not null,
192 b varchar(15) not null,
193 c varchar(200) not null,
194 d text(3000),
195 primary key(b)) engine=ndb;
196
197 --echo Run restore
198 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
199 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
200
201 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
202
203 drop table test.t1;
204 truncate table test.t2;
205
206
207 --echo Contract pk (a,b) -> (b)
208 --echo Also blob changes requiring staging
209 --echo **************************
210 create table test.t1 (a varchar(200) not null,
211 b varchar(15) not null,
212 c varchar(200) not null,
213 d varchar(2500),
214 primary key(b)) engine=ndb;
215
216 --echo Run restore
217 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
218 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
219
220 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
221
222 drop table test.t1;
223 truncate table test.t2;
224
225 --echo Contract pk (a,b) -> (b)
226 --echo Also blob changes requiring staging
227 --echo Also drop column
228 --echo **************************
229 create table test.t1 (b varchar(15) not null,
230 c varchar(200) not null,
231 d varchar(2500),
232 primary key(b)) engine=ndb;
233
234 --echo Run restore
235 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
236 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
237
238 select b,c,length(d), md5(d), right(d,20) from test.t1 order by b;
239
240 drop table test.t1;
241 truncate table test.t2;
242
243 --echo Test with multiple table requiring changes
244 --echo ------------------------------------------
245 drop table test.t2;
246
247 --echo Contract pk (a,b) -> (b)
248 --echo Also blob changes requiring staging
249 --echo **************************
250 create table test.t1 (a varchar(200) not null,
251 b varchar(15) not null,
252 c varchar(200) not null,
253 d varchar(2500),
254 primary key(b)) engine=ndb;
255
256 --echo Contract pk (a,b) -> (a)
257 --echo Also blob changes requiring staging
258 --echo **************************
259 create table test.t2 (a varchar(20) not null,
260 b varchar(20) not null,
261 c varchar(4000) not null,
262 d text(2000) not null,
263 primary key (a)) engine=ndb;
264
265
266 --echo Run restore
267 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
268 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
269
270 select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a;
271 select a,b,length(c), md5(c), right(c, 20), length(d), md5(d), right(d,20) from test.t2 order by a;
272
273 drop table test.t1;
274 drop table test.t2;
275
276
277 --source suite/ndb/include/backup_restore_cleanup.inc
278 --remove_file $NDB_TOOLS_OUTPUT