Debian Codegraph @

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

raw
1 ######################################################################
2 # Test restoring backups with widening type conversions (promotion)
3 ######################################################################
4
5 -- source include/have_ndb.inc
6 -- source suite/ndb/include/backup_restore_setup.inc
7
8 #The first step is to test the int type promotion
9
10 create table t1(t1 tinyint, t2 tinyint, ut1 tinyint unsigned,
11 s1 smallint, s2 smallint, us1 smallint unsigned,
12 m1 mediumint, m2 mediumint, um1 mediumint unsigned,
13 i1 int, i2 int, ui1 int unsigned,
14 b1 bigint, b2 bigint, ub1 bigint unsigned
15 )engine=ndb;
16
17 insert into t1 values(127, -128, 255, 32767, -32768, 65535, 8388607, -8388608,
18 16777215, 2147483647, -2147483648, 4294967295, 9223372036854775807,
19 -9223372036854775808, 18446744073709551615);
20
21 ##############################
22 # Backup section #
23 ##############################
24 --echo *********************************
25 --echo * Backup the int type
26 --echo *********************************
27 --source include/ndb_backup.inc
28
29 --echo ************************************
30 --echo * Restore the int type not promoted
31 --echo ************************************
32 delete from t1;
33
34 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
35 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
36
37 select * from t1;
38
39 ######################################
40 # Type promotion and restore section #
41 ######################################
42
43 #The first group type promotion
44 --echo ******************************************
45 --echo * The first group type promotion including:
46 --echo * tinyint(unsigned)->smallint(unsigned),
47 --echo * smallint(unsigned)->mediumint(unsigned),
48 --echo * mediumint(unsigned)->int(unsigned),
49 --echo * int(unsigned)->bigint(unsigned)
50 --echo ******************************************
51
52 alter table t1 modify column t1 smallint, modify column t2 smallint,
53 modify column ut1 smallint unsigned, modify column s1 mediumint,
54 modify column s2 mediumint, modify column us1 mediumint unsigned,
55 modify column m1 int, modify column m2 int, modify column um1 int unsigned,
56 modify column i1 bigint, modify column i2 bigint,
57 modify column ui1 bigint unsigned;
58
59 #Create innodb table from altered ndb table, which is to compare the data in ndb table with that of innodb table after promotion
60 create table t3 as select * from t1;
61
62 delete from t1;
63 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
64 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
65
66 select count(*) from t1 natural join t3;
67
68 #The second group type promotion
69 --echo ********************************************
70 --echo * The second group type promotion including:
71 --echo * tinyint(unsigned)->mediumint(unsigned),
72 --echo * smallint(unsigned)->int(unsigned),
73 --echo * mediumint(unsigned)->bigint(unsigned),
74 --echo * int(unsigned)->bigint(unsigned)
75 --echo *********************************************
76 drop table t3;
77
78 alter table t1 modify column t1 mediumint, modify column t2 mediumint,
79 modify column ut1 mediumint unsigned, modify column s1 int,
80 modify column s2 int, modify column us1 int unsigned,
81 modify column m1 bigint, modify column m2 bigint,
82 modify column um1 bigint unsigned, modify column i1 bigint,
83 modify column i2 bigint, modify column ui1 bigint unsigned;
84
85 #Create innodb table from altered ndb table, which is to compare the data in ndb table with that of innodb table after promotion
86 create table t3 as select * from t1;
87
88 delete from t1;
89 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
90 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
91
92 select count(*) from t1 natural join t3;
93
94 #The third group type promotion
95 --echo *******************************************
96 --echo * The third group type promotion including:
97 --echo * tinyint(unsigned)->int(unsigned),
98 --echo * smallint(unsigned)->bigint(unsigned),
99 --echo * mediumint(unsigned)->bigint(unsigned),
100 --echo * int(unsigned)->bigint(unsigned)
101 --echo *******************************************
102 drop table t3;
103
104 alter table t1 modify column t1 int, modify column t2 int,
105 modify column ut1 int unsigned, modify column s1 bigint,
106 modify column s2 bigint, modify column us1 bigint unsigned,
107 modify column m1 bigint, modify column m2 bigint,
108 modify column um1 bigint unsigned, modify column i1 bigint,
109 modify column i2 bigint, modify column ui1 bigint unsigned;
110
111 #Create innodb table from altered ndb table, which is to compare the data in ndb table with that of innodb table after promotion
112 create table t3 as select * from t1;
113 delete from t1;
114
115 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
116 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
117
118 select count(*) from t1 natural join t3;
119
120 #The fourth group type promotion
121 --echo ********************************************
122 --echo * The fourth group type promotion including:
123 --echo * tinyint(unsigned)->bigint(unsigned),
124 --echo * smallint(unsigned)->bigint(unsigned),
125 --echo * mediumint(unsigned)->bigint(unsigned),
126 --echo * int(unsigned)->bigint(unsigned)
127 --echo ********************************************
128 drop table t3;
129
130 alter table t1 modify column t1 bigint, modify column t2 bigint,
131 modify column ut1 bigint unsigned, modify column s1 bigint,
132 modify column s2 bigint, modify column us1 bigint unsigned,
133 modify column m1 bigint, modify column m2 bigint,
134 modify column um1 bigint unsigned, modify column i1 bigint,
135 modify column i2 bigint, modify column ui1 bigint unsigned;
136
137 #Create innodb table from altered ndb table, which is to compare the data in ndb table with that of innodb table after promotion
138 create table t3 as select * from t1;
139 delete from t1;
140
141 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
142 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
143
144 select count(*) from t1 natural join t3;
145
146 drop table t1;
147 drop table t3;
148
149 #The second step is to test the char and binary type promotion
150 create table t2(ch1 char(20), ch2 char(200), vch1 varchar(20),
151 vch2 varchar(200), vch3 varchar(2000), b1 binary(20), b2 binary(200),
152 vb1 varbinary(20), vb2 varbinary(200), vb3 varbinary(2000)
153 ) character set latin1 engine = ndb;
154
155 insert into t2 values(repeat('a', 20), repeat('b', 200), repeat('c', 20),
156 repeat('d', 200), repeat('e', 1000), repeat('f', 20), repeat('g', 200),
157 repeat('h', 20), repeat('i', 200), repeat('j', 1500));
158
159 --echo *********************************
160 --echo * Backup the char and binary type
161 --echo *********************************
162 --source include/ndb_backup.inc
163
164 --echo ************************************************
165 --echo * Restore the char and binary type not promoted
166 --echo ************************************************
167
168 delete from t2;
169 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
170 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
171
172 select * from t2;
173 select length(ch1), length(ch2),length(vch1),length(vch2),length(vch3),
174 length(b1),length(b2),length(vb1),length(vb2),length(vb3) from t2;
175
176 --echo *************************************************
177 --echo * char, binary, varchar, varbinary type promotion
178 --echo *************************************************
179
180 alter table t2 modify column ch1 char(255), modify column ch2 varchar(200),
181 modify column vch1 varchar(200), modify column vch2 varchar(500),
182 modify column vch3 varchar(2500), modify column b1 binary(255),
183 modify column b2 varbinary(255), modify column vb1 varbinary(100),
184 modify column vb2 varbinary(300), modify column vb3 varbinary(2100);
185
186 #Create innodb table from altered ndb table, which is to compare the data in ndb table with that of innodb table after promotion
187 create table t4 as select * from t2;
188 show create table t4;
189
190 select * from t4;
191 select length(ch1), length(ch2),length(vch1),length(vch2),length(vch3),
192 length(b1),length(b2),length(vb1),length(vb2),length(vb3) from t4;
193
194 --echo *************************************************
195 --echo * Restore data with truncating the tail spaces
196 --echo *************************************************
197 delete from t2;
198
199 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
200 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
201
202 select * from t2;
203 select length(ch1), length(ch2),length(vch1),length(vch2),length(vch3),
204 length(b1),length(b2),length(vb1),length(vb2),length(vb3) from t2;
205
206
207 select count(*) from t2 natural join t4;
208
209 --echo *************************************************
210 --echo * Restore data with reserving the tail spaces
211 --echo *************************************************
212 delete from t2;
213 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -P -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
214 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -P -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
215
216 select * from t2;
217 select length(ch1), length(ch2),length(vch1),length(vch2),length(vch3),
218 length(b1),length(b2),length(vb1),length(vb2),length(vb3) from t2;
219
220 select count(*) from t2 natural join t4;
221
222 drop table t2;
223 drop table t4;
224 # End of 5.1 Test Case
225
226 #
227 # bug#43139
228 # test - 1) changing of order in table
229 # 2) removal/adding of columns
230 create table t1(a int primary key,
231 b char(1),
232 c char(1),
233 d char(1),
234 e char(1)) engine = ndb;
235
236 insert into t1 values (1, 'B', 'c', 'd', 'e');
237 insert into t1 values (2, 'b', 'C', 'd', 'e');
238 insert into t1 values (3, 'b', 'c', 'D', 'e');
239 insert into t1 values (4, 'b', 'c', 'd', 'E');
240
241 --source include/ndb_backup.inc
242 drop table t1;
243 create table t1(c char(1),
244 a int primary key,
245 e char(1),
246 d char(1)) engine = ndb;
247
248 --exec $NDB_RESTORE --exclude-missing-columns -b $the_backup_id -n 1 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
249 --exec $NDB_RESTORE --exclude-missing-columns -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
250
251 select a,c,d,e from t1 order by a;
252 drop table t1;
253
254 # Testcase for restore with native default added to table (WL4197)
255 create table subject(pk int primary key, band varchar(50), pos int) engine=ndb;
256
257 insert into subject values (1, 'Elbow', 1), (2, 'Franz Ferdinand', 2), (3, 'Proclaimers', 3);
258 insert into subject values (4, 'David Bowie', 4), (5, 'Rufus Wainwright', 1);
259
260 select * from subject order by pk;
261
262 --echo **************************
263 --echo Performing backup of table
264 --echo **************************
265
266 --source include/ndb_backup.inc
267
268 --echo ******************
269 --echo Throwing away data
270 --echo ******************
271 delete from subject;
272
273 select * from subject order by pk;
274
275 --echo *******************************************************
276 --echo Modifying table to include new, native defaulted column
277 --echo *******************************************************
278 alter table subject add column song varchar(50) default 'Unknown';
279
280 --echo ***********************************************************
281 --echo Restoring data back into table and picking up default value
282 --echo ***********************************************************
283
284 # Note testcase nastily assumes 2 nodes, called 1 + 2.
285 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print --print_meta --skip-table-check $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
286 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta --skip-table-check $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
287
288 --echo ****************************************************
289 --echo All restored entries should have song name 'Unknown'
290 --echo ****************************************************
291
292 select * from subject order by pk;
293
294 drop table subject;
295 --source suite/ndb/include/backup_restore_cleanup.inc
296 # End of testcase for WL4197
297 --remove_file $NDB_TOOLS_OUTPUT