Debian Codegraph @

mysql-test/suite/innodb/include/partition_upgrade_check.inc

raw
1 # WL 13352: SCHEMA, DDL, DML, CHECK for upgrade of partitioned tables
2 # Caller need to set check_option: CREATE, DROP, CHECK, DDL, DML, EXPORT, IMPORT
3 # set check_dd: 1, 0 DD table can be checked on debug build
4 # set NO_EXT_PATH: 1, 0 No file outside data directory
5 # set EXTERNAL_DIRECTORY: Place for external files
6 # set EXPORT_DIRECTORY: Place for exported data files
7 # set EXTRA_TABLES: Include non partitioned tables
8
9 --let $TABLE_1 = table_part_1
10 --let $TABLE_2 = table_part_sub_1
11 --let $TABLE_3 = table_part_sub_2
12 --let $TABLE_4 = `select "`table_part_\\_special_/_sub_?_3`"`
13 --let $TABLE_5 = table_part_2
14
15 --let $num_tables = 5
16 --let $num_rows = 10
17
18 if ($EXTRA_TABLES)
19 {
20 --let $TABLE_6 = table_part_CAP_6
21 --let $TABLE_7 = table_part_NOPART_CAP_7
22 --let $num_tables = 7
23 }
24
25 if (!$NO_EXT_PATH)
26 {
27 --let $DIR_CLAUSE= DATA DIRECTORY = '$EXTERNAL_DIRECTORY'
28 }
29
30 if ($NO_EXT_PATH)
31 {
32 --let $DIR_CLAUSE=
33 }
34
35 if ($check_option == 'DROP')
36 {
37 --echo Dropping partitioned tables
38 --echo ---------------------------
39
40 --let $loop_idx = 1
41 while ($loop_idx <= $num_tables)
42 {
43 --let $table_name = \$TABLE_$loop_idx
44 --eval DROP TABLE $table_name
45 --inc $loop_idx
46 }
47
48 if ($imported) {
49 DROP TABLE table_part_encrypt;
50 }
51
52 if ($EXTRA_TABLES)
53 {
54 DROP TABLESPACE SPACE_part;
55 }
56 }
57
58 if (`select '$check_option' = 'EXPORT' || '$check_option' = 'IMPORT'`)
59 {
60 --replace_result $MYSQL_TMP_DIR ""
61 eval CREATE TABLE table_part_encrypt(
62 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
63 key k1(value)) ENCRYPTION = 'Y'
64 partition by range (value) subpartition by hash (id)
65 ( partition Part_1 values less than (101)(
66 subpartition Sub_1,
67 subpartition sUb_2),
68 partition pArt_2 values less than (501)(
69 subpartition suB_3 $DIR_CLAUSE,
70 subpartition SUB_4),
71 partition PART_3 values less than MAXVALUE(
72 subpartition SUB_5,
73 subpartition sub_6));
74
75 INSERT INTO table_part_encrypt SELECT * FROM table_part_sub_2;
76 }
77
78 if ($check_option == 'EXPORT')
79 {
80 FLUSH TABLES table_part_encrypt, table_part_sub_2 FOR EXPORT;
81
82 --copy_files_wildcard $MYSQLD_DATADIR/test $EXPORT_DIRECTORY table_part_encrypt*
83 --copy_files_wildcard $EXTERNAL_DIRECTORY/test $EXPORT_DIRECTORY table_part_encrypt*
84
85 --copy_files_wildcard $MYSQLD_DATADIR/test $EXPORT_DIRECTORY table_part_sub_2*
86 --copy_files_wildcard $EXTERNAL_DIRECTORY/test $EXPORT_DIRECTORY table_part_sub_2*
87
88 UNLOCK TABLES;
89
90 DROP TABLE table_part_encrypt;
91 }
92
93 if ($check_option == 'IMPORT')
94 {
95 ALTER TABLE table_part_sub_2 DISCARD PARTITION sub_3, sub_5 tablespace;
96 ALTER TABLE table_part_encrypt DISCARD PARTITION sub_3, sub_5 tablespace;
97
98 --copy_files_wildcard $EXPORT_DIRECTORY $MYSQLD_DATADIR/test *_5.*
99 --copy_files_wildcard $EXPORT_DIRECTORY $EXTERNAL_DIRECTORY/test *_3.*
100
101 --echo
102 --echo List files before import
103 --echo ---------------------------
104 --list_files $MYSQLD_DATADIR/test
105 --echo
106 --list_files $EXTERNAL_DIRECTORY/test
107 --echo
108
109 ALTER TABLE table_part_sub_2 IMPORT PARTITION sub_3, sub_5 tablespace;
110 --echo
111 ALTER TABLE table_part_encrypt IMPORT PARTITION sub_3, sub_5 tablespace;
112
113 --echo
114 --echo List all files after import
115 --echo ---------------------------
116 --list_files $MYSQLD_DATADIR/test
117 --echo
118 --list_files $EXTERNAL_DIRECTORY/test
119 }
120
121 if ($check_option == 'CREATE')
122 {
123 --echo Creating partitioned tables
124 --echo ---------------------------
125
126 CREATE TABLE table_part_1(
127 id int primary key, value int, name varchar(32), data BLOB,
128 key k1(value)) partition by hash (id) partitions 5;
129 --echo
130
131 --replace_result $MYSQL_TMP_DIR ""
132 eval CREATE TABLE table_part_2(
133 id int primary key, value int, name varchar(32), data BLOB,
134 key k1(value)) $DIR_CLAUSE
135 partition by hash (id) partitions 5;
136 --echo
137
138 CREATE TABLE table_part_sub_1(
139 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
140 key k1(value)) partition by range (value) subpartition by hash (id)
141 subpartitions 2 ( partition Part_1 values less than (101),
142 partition pArt_2 values less than (401),
143 partition part_3 values less than (701),
144 partition PART_4 values less than MAXVALUE);
145 --echo
146
147 --replace_result $MYSQL_TMP_DIR ""
148 eval CREATE TABLE table_part_sub_2(
149 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
150 key k1(value)) partition by range (value) subpartition by hash (id)
151 ( partition Part_1 values less than (101)(
152 subpartition Sub_1,
153 subpartition sUb_2),
154 partition pArt_2 values less than (501)(
155 subpartition suB_3 $DIR_CLAUSE,
156 subpartition SUB_4),
157 partition PART_3 values less than MAXVALUE(
158 subpartition SUB_5,
159 subpartition sub_6));
160 --echo
161
162 CREATE TABLE `table_part_\_special_/_sub_?_3`(
163 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
164 key k1(value)) partition by range (value) subpartition by hash (id)
165 ( partition `Part_?_1` values less than (201)(
166 subpartition `Sub_?_1`,
167 subpartition `sUb_/_2`),
168 partition `pArt_\_2` values less than (501)(
169 subpartition `suB_\_3`,
170 subpartition `SUB_?\_4`),
171 partition `PART_/_3` values less than MAXVALUE(
172 subpartition `SUB_?/_5`,
173 subpartition `sub_\/?_6`));
174
175 if ($EXTRA_TABLES)
176 {
177 CREATE TABLESPACE SPACE_part add datafile 'SPACE_part_1.ibd';
178
179 CREATE TABLE table_part_CAP_6(
180 id int primary key, value int, name varchar(32), data BLOB,
181 key k1(value)) partition by hash (id) partitions 5;
182
183 CREATE TABLE table_part_NOPART_CAP_7(
184 id int primary key, value int, name varchar(32), data BLOB,
185 key k1(value)) TABLESPACE SPACE_part;
186 }
187
188 --let $loop_idx = 1
189
190 while ($loop_idx <= $num_tables)
191 {
192 --let $table_name = \$TABLE_$loop_idx
193 --echo
194 --echo Inserting $num_rows rows to $table_name
195 --echo ---------------------------------------
196
197 --let $row_idx = 0
198
199 while ($row_idx < $num_rows)
200 {
201 --let $row_val = `select 100 * $row_idx + 55`
202 --let $row_name = "Row - $row_idx"
203 --let $row_data = REPEAT('Large Column Data - $row_idx' , 256)
204
205 --eval insert into $table_name VALUES($row_idx, $row_val, $row_name, $row_data)
206 --inc $row_idx
207 }
208 --inc $loop_idx
209 }
210
211 --let $part_idx = 1
212 while ($part_idx <= 6)
213 {
214 --eval select id, value, name from $TABLE_3 partition (sub_$part_idx) order by id
215 --inc $part_idx
216 }
217 }
218
219 if ($check_option == 'CHECK')
220 {
221 --echo Checking partitioned tables
222 --echo ---------------------------
223 # Switch to case sensitive collation for consistent order.
224 SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs;
225
226 --let $loop_idx = 1
227 --let $max_index = $num_tables
228
229 # After import include also the encrypted table.
230 if ($imported) {
231 --inc $max_index
232 }
233
234 while ($loop_idx <= $max_index)
235 {
236 --let $table_name = \$TABLE_$loop_idx
237
238 # After import include also the encrypted table.
239 if ($imported) {
240 if ($loop_idx == $max_index) {
241 --let $table_name = table_part_encrypt
242 }
243 }
244
245 --echo
246 --eval CHECK TABLE $table_name
247 --echo
248 --replace_result $MYSQL_TMP_DIR ""
249 --replace_regex /mysql_wl13352_data\/\//mysql_wl13352_data\//
250 --eval SHOW CREATE TABLE $table_name
251 --echo
252 --eval SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM $table_name order by id
253
254 --inc $loop_idx
255 }
256
257 --echo
258 --echo Checking IFS views
259 --echo ------------------
260
261 select table_schema, table_name from information_schema.tables
262 where table_name LIKE '%table_part%' order by table_schema, table_name;
263
264 --echo
265 select name, space_type from information_schema.innodb_tables
266 where name LIKE '%table_part%' order by name, space_type;
267
268 --echo
269 select table_schema, table_name, partition_name, subpartition_name
270 from information_schema.partitions
271 where table_name LIKE '%table_part%'
272 order by table_schema, table_name, partition_name, subpartition_name;
273
274 --echo
275 select name from information_schema.innodb_tablespaces
276 where name LIKE '%table_part%' order by name;
277
278 --echo
279 --replace_result $MYSQL_TMP_DIR ""
280 --replace_regex /innodb_file_per_table_[0-9]*/innodb_file_per_table/
281 select file_type, status, file_name, tablespace_name from information_schema.files
282 where file_name LIKE '%table_part%'
283 order by file_type, status, file_name, tablespace_name;
284
285 --echo
286 --echo Checking Innodb stat tables
287 --echo ---------------------------
288
289 select database_name, table_name from mysql.innodb_table_stats
290 where table_name like '%table_part%' order by database_name, table_name;
291
292 --echo
293 select database_name, table_name, index_name, stat_name from mysql.innodb_index_stats
294 where table_name like '%table_part%'
295 order by database_name, table_name, index_name, stat_name;
296
297 if ($check_dd)
298 {
299 --echo
300 --echo Checking DD tables
301 --echo ------------------
302
303 SET DEBUG='+d,skip_dd_table_access_check';
304
305 --echo
306 select t.name as "table", p.name as "partition", p.number
307 from mysql.tables t, mysql.table_partitions p
308 where p.table_id = t.id
309 order by t.name, p.name, p.number;
310
311 --echo
312 select name from mysql.tablespaces
313 where name like '%table_part%' order by name;
314
315 --echo
316 --replace_result $MYSQL_TMP_DIR "" \\test\\ /test/
317 select file_name from mysql.tablespace_files
318 where file_name like '%table_part%' order by file_name;
319
320 SET DEBUG='-d,skip_dd_table_access_check';
321 }
322 SET NAMES DEFAULT;
323 }
324
325 if ($check_option == 'DDL')
326 {
327 --echo DDL on partitioned tables
328 --echo -------------------------
329
330 --eval ALTER TABLE $TABLE_1 TRUNCATE PARTITION p0
331 --echo
332 --eval ALTER TABLE $TABLE_1 COALESCE PARTITION 2
333 --echo
334 --eval SHOW CREATE TABLE $TABLE_1
335
336 --echo
337 --eval ALTER TABLE $TABLE_2 TRUNCATE PARTITION part_1
338 --echo
339 --eval ALTER TABLE $TABLE_3 TRUNCATE PARTITION part_3
340
341 --echo
342 --eval CREATE TABLE test_table LIKE $TABLE_3
343 --echo
344 --eval ALTER TABLE test_table REMOVE PARTITIONING
345
346 --let $row_idx = 2
347 --let $row_val = `select 100 * $row_idx + 65`
348 --let $row_name = "Row - $row_idx"
349 --let $row_data = REPEAT('Large Column Data - $row_idx' , 256)
350 --eval insert into test_table values($row_idx, $row_val, $row_name, $row_data)
351
352 --echo
353 --eval SELECT id, value, name FROM $TABLE_3 PARTITION (sub_3) order by id
354 --echo
355 --eval SELECT id, value, name FROM test_table order by id
356
357 --echo
358 --eval ALTER TABLE $TABLE_3 EXCHANGE PARTITION sub_3 WITH TABLE test_table
359
360 --echo
361 --eval SELECT id, value, name FROM $TABLE_3 PARTITION (sub_3) order by id
362 --echo
363 --eval SELECT id, value, name FROM test_table order by id
364
365 --echo
366 --eval DROP TABLE test_table
367
368 --echo
369 --eval ALTER TABLE $TABLE_3 RENAME renamed_table;
370 SHOW CREATE TABLE renamed_table;
371
372 --echo
373 --eval ALTER TABLE renamed_table RENAME $TABLE_3;
374
375 --let $loop_idx = 1
376
377 while ($loop_idx <= $num_tables)
378 {
379 --let $table_name = \$TABLE_$loop_idx
380 --echo
381 --eval ALTER TABLE $table_name ENGINE = InnoDB;
382 --echo
383 --eval ALTER TABLE $table_name ADD COLUMN new_id int AFTER id
384 --echo
385
386 # Only for partitioned table
387 if ($loop_idx != 7)
388 {
389 --eval ALTER TABLE $table_name REMOVE PARTITIONING
390 }
391
392 --inc $loop_idx
393 }
394 }
395
396 if ($check_option == 'DML')
397 {
398 --echo DML on partitioned tables
399 --echo -------------------------
400
401 --let $loop_idx = 1
402
403 while ($loop_idx <= $num_tables)
404 {
405 --let $table_name = \$TABLE_$loop_idx
406 --let $row_idx = 15
407 --let $row_val = `select 100 * $row_idx + 55`
408 --let $row_name = "Row - $row_idx"
409 --let $row_data = REPEAT('Large Column Data - $row_idx' , 256)
410
411 --echo
412 --eval insert into $table_name values($row_idx, $row_val, $row_name, $row_data)
413
414 --echo
415 --eval update $table_name set name = 'Row - updated' where value > 500
416
417 --echo
418 --eval delete from $table_name where id = 7
419
420 --inc $loop_idx
421 }
422 }