Debian Codegraph @

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

raw
1 ######################################################################
2 # Test restoring backups with preserving padding in char/bin conversions
3 ######################################################################
4
5 -- source include/have_ndb.inc
6 -- source suite/ndb/include/backup_restore_setup.inc
7
8 # mysqld's configuration is not relevant to this test
9
10 # Turn off STRICT sql_mode since test intentionally truncates
11 # data when preparing the test data(those offending statements
12 # are within enable/disable_warnings)
13 --source suite/ndb/include/turn_off_strict_sql_mode.inc
14
15 --echo ************************************************************
16 --echo * Creating table with char+binary types
17 --echo ************************************************************
18
19 CREATE TABLE t3(
20 c1 char(32),
21 b1 binary(32)
22 ) CHARACTER SET latin1 ENGINE=NDB;
23
24 INSERT INTO t3 VALUES('aaaaaaaa', 'bbbbbbbb');
25 SELECT length(c1), length(b1) FROM t3;
26
27 --echo ************************************************************
28 --echo * Backing up table with char+binary types
29 --echo ************************************************************
30
31 --source include/ndb_backup.inc
32
33 # command shortcuts
34 --let $restore_cmd=$NDB_RESTORE -b $the_backup_id
35 --let $restore_cmd=$restore_cmd -r --backup_path=$NDB_BACKUPS-$the_backup_id
36
37 --echo ************************************************************
38 --echo * Restoring table with unchanged char+binary types (sanity check):
39 --echo * char(32) --> char(32)
40 --echo * binary(32) --> binary(32)
41 --echo ************************************************************
42
43 # create a MyISAM table from NDB table, against which to compare data
44 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
45
46 # restore table
47 DELETE FROM t3;
48
49 # for debugging:
50 # --exec $NDB_RESTORE -b $the_backup_id -n 1 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
51 # --exec $NDB_RESTORE -b $the_backup_id -n 2 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
52 --exec $restore_cmd -n 1 --print > /dev/null
53 --exec $restore_cmd -n 2 --print > /dev/null
54 SELECT length(c1), length(b1) FROM t3;
55
56 # verify data
57 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
58 DROP TABLE t3_myisam;
59
60 --echo ************************************************************
61 --echo * Restoring table with promoted char+binary types preserving padding:
62 --echo * char(32) --> varchar(32)
63 --echo * binary(32) --> varbinary(32)
64 --echo ************************************************************
65
66 # demote char+binary type attibutes
67 --disable_warnings
68 ALTER TABLE t3 MODIFY c1 varchar(32), MODIFY b1 varbinary(32);
69 --enable_warnings
70 SELECT length(c1), length(b1) FROM t3;
71
72 # create a MyISAM table from NDB table, against which to compare data
73 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
74
75 # restore table
76 DELETE FROM t3;
77 --exec $restore_cmd -n 1 --print -A -P > /dev/null
78 --exec $restore_cmd -n 2 --print -A -P > /dev/null
79 SELECT length(c1), length(b1) FROM t3;
80
81 # verify data
82 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
83 DROP TABLE t3_myisam;
84
85 --echo ************************************************************
86 --echo * Restoring table with promoted char+binary types preserving padding:
87 --echo * char(32) --> varchar(64)
88 --echo * binary(32) --> varbinary(64)
89 --echo ************************************************************
90
91 # demote char+binary type attibutes
92 --disable_warnings
93 ALTER TABLE t3 MODIFY c1 varchar(64), MODIFY b1 varbinary(64);
94 --enable_warnings
95 SELECT length(c1), length(b1) FROM t3;
96
97 # create a MyISAM table from NDB table, against which to compare data
98 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
99
100 # restore table
101 DELETE FROM t3;
102 --exec $restore_cmd -n 1 --print -A -P > /dev/null
103 --exec $restore_cmd -n 2 --print -A -P > /dev/null
104 SELECT length(c1), length(b1) FROM t3;
105
106 # verify data
107 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
108 DROP TABLE t3_myisam;
109
110 --echo ************************************************************
111 --echo * Restoring table with promoted char+binary types preserving padding:
112 --echo * char(32) --> varchar(512)
113 --echo * binary(32) --> varbinary(512)
114 --echo ************************************************************
115
116 # demote char+binary type attibutes
117 --disable_warnings
118 ALTER TABLE t3 MODIFY c1 varchar(512), MODIFY b1 varbinary(512);
119 --enable_warnings
120 SELECT length(c1), length(b1) FROM t3;
121
122 # create a MyISAM table from NDB table, against which to compare data
123 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
124
125 # restore table
126 DELETE FROM t3;
127 --exec $restore_cmd -n 1 --print -A -P > /dev/null
128 --exec $restore_cmd -n 2 --print -A -P > /dev/null
129 SELECT length(c1), length(b1) FROM t3;
130
131 # verify data
132 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
133 DROP TABLE t3_myisam;
134
135 --echo ************************************************************
136 --echo * Restoring table with demoted char+binary types preserving padding:
137 --echo * char(32) --> varchar(16)
138 --echo * binary(32) --> varbinary(16)
139 --echo ************************************************************
140
141 # demote char+binary type attibutes
142 --disable_warnings
143 ALTER TABLE t3 MODIFY c1 varchar(16), MODIFY b1 varbinary(16);
144 --enable_warnings
145 SELECT length(c1), length(b1) FROM t3;
146
147 # create a MyISAM table from NDB table, against which to compare data
148 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
149
150 # restore table
151 DELETE FROM t3;
152 --exec $restore_cmd -n 1 --print -L -P > /dev/null
153 --exec $restore_cmd -n 2 --print -L -P > /dev/null
154 SELECT length(c1), length(b1) FROM t3;
155
156 # verify data
157 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
158 DROP TABLE t3_myisam;
159
160 --echo ************************************************************
161 --echo * Restoring table with promoted char+binary types discarding padding:
162 --echo * char(32) --> varchar(32)
163 --echo * binary(32) --> varbinary(32)
164 --echo ************************************************************
165
166 # demote char+binary type attibutes
167 --disable_warnings
168 DELETE FROM t3;
169 ALTER TABLE t3 MODIFY c1 varchar(32), MODIFY b1 varbinary(32);
170 INSERT INTO t3 VALUES('aaaaaaaa', 'bbbbbbbb');
171 --enable_warnings
172 SELECT length(c1), length(b1) FROM t3;
173
174 # create a MyISAM table from NDB table, against which to compare data
175 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
176
177 # restore table
178 DELETE FROM t3;
179 --exec $restore_cmd -n 1 --print -A > /dev/null
180 --exec $restore_cmd -n 2 --print -A > /dev/null
181 SELECT length(c1), length(b1) FROM t3;
182
183 # verify data
184 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
185 DROP TABLE t3_myisam;
186
187 --echo ************************************************************
188 --echo * Restoring table with promoted char+binary types discarding padding:
189 --echo * char(32) --> varchar(64)
190 --echo * binary(32) --> varbinary(64)
191 --echo ************************************************************
192
193 # demote char+binary type attibutes
194 --disable_warnings
195 ALTER TABLE t3 MODIFY c1 varchar(64), MODIFY b1 varbinary(64);
196 --enable_warnings
197 SELECT length(c1), length(b1) FROM t3;
198
199 # create a MyISAM table from NDB table, against which to compare data
200 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
201
202 # restore table
203 DELETE FROM t3;
204 --exec $restore_cmd -n 1 --print -A > /dev/null
205 --exec $restore_cmd -n 2 --print -A > /dev/null
206 SELECT length(c1), length(b1) FROM t3;
207
208 # verify data
209 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
210 DROP TABLE t3_myisam;
211
212 --echo ************************************************************
213 --echo * Restoring table with promoted char+binary types discarding padding:
214 --echo * char(32) --> varchar(512)
215 --echo * binary(32) --> varbinary(512)
216 --echo ************************************************************
217
218 # demote char+binary type attibutes
219 --disable_warnings
220 ALTER TABLE t3 MODIFY c1 varchar(512), MODIFY b1 varbinary(512);
221 --enable_warnings
222 SELECT length(c1), length(b1) FROM t3;
223
224 # create a MyISAM table from NDB table, against which to compare data
225 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
226
227 # restore table
228 DELETE FROM t3;
229 --exec $restore_cmd -n 1 --print -A > /dev/null
230 --exec $restore_cmd -n 2 --print -A > /dev/null
231 SELECT length(c1), length(b1) FROM t3;
232
233 # verify data
234 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
235 DROP TABLE t3_myisam;
236
237 --echo ************************************************************
238 --echo * Restoring table with demoted char+binary types discarding padding:
239 --echo * char(32) --> varchar(16)
240 --echo * binary(32) --> varbinary(16)
241 --echo ************************************************************
242
243 # demote char+binary type attibutes
244 --disable_warnings
245 ALTER TABLE t3 MODIFY c1 varchar(16), MODIFY b1 varbinary(16);
246 --enable_warnings
247 SELECT length(c1), length(b1) FROM t3;
248
249 # create a MyISAM table from NDB table, against which to compare data
250 CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
251
252 # restore table
253 DELETE FROM t3;
254 --exec $restore_cmd -n 1 --print -L > /dev/null
255 --exec $restore_cmd -n 2 --print -L > /dev/null
256 SELECT length(c1), length(b1) FROM t3;
257
258 # verify data
259 SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
260 DROP TABLE t3_myisam;
261
262 --echo ************************************************************
263 --echo * Deleting table with char+binary types
264 --echo ************************************************************
265
266 DROP TABLE t3;
267
268
269
270 --source suite/ndb/include/restore_sql_mode_after_turn_off_strict.inc
271 --source suite/ndb/include/backup_restore_cleanup.inc
272 --remove_file $NDB_TOOLS_OUTPUT