Debian Codegraph @

mysql-test/suite/innodb/r/redo_log_disable.result

raw
1 #1. Check Redo logging syntax and status
2 SHOW STATUS LIKE 'Innodb_redo_log_enabled';
3 Variable_name Value
4 Innodb_redo_log_enabled ON
5
6 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
7 VARIABLE_NAME VARIABLE_VALUE
8 Innodb_redo_log_enabled ON
9
10 ALTER INSTANCE DISABLE INNODB REDO_LOG;
11
12 SHOW STATUS LIKE 'Innodb_redo_log_enabled';
13 Variable_name Value
14 Innodb_redo_log_enabled OFF
15
16 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
17 VARIABLE_NAME VARIABLE_VALUE
18 Innodb_redo_log_enabled OFF
19
20 ALTER INSTANCE ENABLE INNODB REDO_LOG;
21
22 SHOW STATUS LIKE 'Innodb_redo_log_enabled';
23 Variable_name Value
24 Innodb_redo_log_enabled ON
25
26 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
27 VARIABLE_NAME VARIABLE_VALUE
28 Innodb_redo_log_enabled ON
29
30 #2. Check privilege
31 CREATE USER 'user_redo'@'localhost' IDENTIFIED BY '123';
32
33 GRANT SELECT ON performance_schema.* to 'user_redo'@'localhost';
34
35 ALTER INSTANCE DISABLE INNODB REDO_LOG;
36 ERROR 42000: Access denied; you need (at least one of) the INNODB_REDO_LOG_ENABLE privilege(s) for this operation
37
38 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
39 VARIABLE_NAME VARIABLE_VALUE
40 Innodb_redo_log_enabled ON
41
42 ALTER INSTANCE ENABLE INNODB REDO_LOG;
43 ERROR 42000: Access denied; you need (at least one of) the INNODB_REDO_LOG_ENABLE privilege(s) for this operation
44
45 GRANT INNODB_REDO_LOG_ENABLE ON *.* TO 'user_redo'@'localhost';
46
47 ALTER INSTANCE DISABLE INNODB REDO_LOG;
48
49 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
50 VARIABLE_NAME VARIABLE_VALUE
51 Innodb_redo_log_enabled OFF
52
53 ALTER INSTANCE ENABLE INNODB REDO_LOG;
54
55 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
56 VARIABLE_NAME VARIABLE_VALUE
57 Innodb_redo_log_enabled ON
58
59 DROP USER 'user_redo'@'localhost';
60
61 #3. Check basic DDL and DML when redo logging is disabled
62 ALTER INSTANCE DISABLE INNODB REDO_LOG;
63 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
64 VARIABLE_NAME VARIABLE_VALUE
65 Innodb_redo_log_enabled OFF
66 Creating partitioned tables
67 ---------------------------
68 CREATE TABLE table_part_1(
69 id int primary key, value int, name varchar(32), data BLOB,
70 key k1(value)) partition by hash (id) partitions 5;
71
72 CREATE TABLE table_part_2(
73 id int primary key, value int, name varchar(32), data BLOB,
74 key k1(value))
75 partition by hash (id) partitions 5;
76
77 CREATE TABLE table_part_sub_1(
78 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
79 key k1(value)) partition by range (value) subpartition by hash (id)
80 subpartitions 2 ( partition Part_1 values less than (101),
81 partition pArt_2 values less than (401),
82 partition part_3 values less than (701),
83 partition PART_4 values less than MAXVALUE);
84
85 CREATE TABLE table_part_sub_2(
86 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
87 key k1(value)) partition by range (value) subpartition by hash (id)
88 ( partition Part_1 values less than (101)(
89 subpartition Sub_1,
90 subpartition sUb_2),
91 partition pArt_2 values less than (501)(
92 subpartition suB_3 ,
93 subpartition SUB_4),
94 partition PART_3 values less than MAXVALUE(
95 subpartition SUB_5,
96 subpartition sub_6));
97
98 CREATE TABLE `table_part_\_special_/_sub_?_3`(
99 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
100 key k1(value)) partition by range (value) subpartition by hash (id)
101 ( partition `Part_?_1` values less than (201)(
102 subpartition `Sub_?_1`,
103 subpartition `sUb_/_2`),
104 partition `pArt_\_2` values less than (501)(
105 subpartition `suB_\_3`,
106 subpartition `SUB_?\_4`),
107 partition `PART_/_3` values less than MAXVALUE(
108 subpartition `SUB_?/_5`,
109 subpartition `sub_\/?_6`));
110
111 Inserting 10 rows to table_part_1
112 ---------------------------------------
113 insert into table_part_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
114 insert into table_part_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
115 insert into table_part_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
116 insert into table_part_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
117 insert into table_part_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
118 insert into table_part_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
119 insert into table_part_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
120 insert into table_part_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
121 insert into table_part_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
122 insert into table_part_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
123
124 Inserting 10 rows to table_part_sub_1
125 ---------------------------------------
126 insert into table_part_sub_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
127 insert into table_part_sub_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
128 insert into table_part_sub_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
129 insert into table_part_sub_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
130 insert into table_part_sub_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
131 insert into table_part_sub_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
132 insert into table_part_sub_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
133 insert into table_part_sub_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
134 insert into table_part_sub_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
135 insert into table_part_sub_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
136
137 Inserting 10 rows to table_part_sub_2
138 ---------------------------------------
139 insert into table_part_sub_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
140 insert into table_part_sub_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
141 insert into table_part_sub_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
142 insert into table_part_sub_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
143 insert into table_part_sub_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
144 insert into table_part_sub_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
145 insert into table_part_sub_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
146 insert into table_part_sub_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
147 insert into table_part_sub_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
148 insert into table_part_sub_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
149
150 Inserting 10 rows to `table_part_\_special_/_sub_?_3`
151 ---------------------------------------
152 insert into `table_part_\_special_/_sub_?_3` VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
153 insert into `table_part_\_special_/_sub_?_3` VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
154 insert into `table_part_\_special_/_sub_?_3` VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
155 insert into `table_part_\_special_/_sub_?_3` VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
156 insert into `table_part_\_special_/_sub_?_3` VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
157 insert into `table_part_\_special_/_sub_?_3` VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
158 insert into `table_part_\_special_/_sub_?_3` VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
159 insert into `table_part_\_special_/_sub_?_3` VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
160 insert into `table_part_\_special_/_sub_?_3` VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
161 insert into `table_part_\_special_/_sub_?_3` VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
162
163 Inserting 10 rows to table_part_2
164 ---------------------------------------
165 insert into table_part_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
166 insert into table_part_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
167 insert into table_part_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
168 insert into table_part_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
169 insert into table_part_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
170 insert into table_part_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
171 insert into table_part_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
172 insert into table_part_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
173 insert into table_part_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
174 insert into table_part_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
175 select id, value, name from table_part_sub_2 partition (sub_1) order by id;
176 id value name
177 0 55 Row - 0
178 select id, value, name from table_part_sub_2 partition (sub_2) order by id;
179 id value name
180 select id, value, name from table_part_sub_2 partition (sub_3) order by id;
181 id value name
182 2 255 Row - 2
183 4 455 Row - 4
184 select id, value, name from table_part_sub_2 partition (sub_4) order by id;
185 id value name
186 1 155 Row - 1
187 3 355 Row - 3
188 select id, value, name from table_part_sub_2 partition (sub_5) order by id;
189 id value name
190 6 655 Row - 6
191 8 855 Row - 8
192 select id, value, name from table_part_sub_2 partition (sub_6) order by id;
193 id value name
194 5 555 Row - 5
195 7 755 Row - 7
196 9 955 Row - 9
197 ALTER INSTANCE ENABLE INNODB REDO_LOG;
198 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
199 VARIABLE_NAME VARIABLE_VALUE
200 Innodb_redo_log_enabled ON
201 DML on partitioned tables
202 -------------------------
203
204 insert into table_part_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
205
206 update table_part_1 set name = 'Row - updated' where value > 500;
207
208 delete from table_part_1 where id = 7;
209
210 insert into table_part_sub_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
211
212 update table_part_sub_1 set name = 'Row - updated' where value > 500;
213
214 delete from table_part_sub_1 where id = 7;
215
216 insert into table_part_sub_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
217
218 update table_part_sub_2 set name = 'Row - updated' where value > 500;
219
220 delete from table_part_sub_2 where id = 7;
221
222 insert into `table_part_\_special_/_sub_?_3` values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
223
224 update `table_part_\_special_/_sub_?_3` set name = 'Row - updated' where value > 500;
225
226 delete from `table_part_\_special_/_sub_?_3` where id = 7;
227
228 insert into table_part_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
229
230 update table_part_2 set name = 'Row - updated' where value > 500;
231
232 delete from table_part_2 where id = 7;
233 ALTER INSTANCE DISABLE INNODB REDO_LOG;
234 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
235 VARIABLE_NAME VARIABLE_VALUE
236 Innodb_redo_log_enabled OFF
237 Dropping partitioned tables
238 ---------------------------
239 DROP TABLE table_part_1;
240 DROP TABLE table_part_sub_1;
241 DROP TABLE table_part_sub_2;
242 DROP TABLE `table_part_\_special_/_sub_?_3`;
243 DROP TABLE table_part_2;
244 Creating partitioned tables
245 ---------------------------
246 CREATE TABLE table_part_1(
247 id int primary key, value int, name varchar(32), data BLOB,
248 key k1(value)) partition by hash (id) partitions 5;
249
250 CREATE TABLE table_part_2(
251 id int primary key, value int, name varchar(32), data BLOB,
252 key k1(value))
253 partition by hash (id) partitions 5;
254
255 CREATE TABLE table_part_sub_1(
256 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
257 key k1(value)) partition by range (value) subpartition by hash (id)
258 subpartitions 2 ( partition Part_1 values less than (101),
259 partition pArt_2 values less than (401),
260 partition part_3 values less than (701),
261 partition PART_4 values less than MAXVALUE);
262
263 CREATE TABLE table_part_sub_2(
264 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
265 key k1(value)) partition by range (value) subpartition by hash (id)
266 ( partition Part_1 values less than (101)(
267 subpartition Sub_1,
268 subpartition sUb_2),
269 partition pArt_2 values less than (501)(
270 subpartition suB_3 ,
271 subpartition SUB_4),
272 partition PART_3 values less than MAXVALUE(
273 subpartition SUB_5,
274 subpartition sub_6));
275
276 CREATE TABLE `table_part_\_special_/_sub_?_3`(
277 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
278 key k1(value)) partition by range (value) subpartition by hash (id)
279 ( partition `Part_?_1` values less than (201)(
280 subpartition `Sub_?_1`,
281 subpartition `sUb_/_2`),
282 partition `pArt_\_2` values less than (501)(
283 subpartition `suB_\_3`,
284 subpartition `SUB_?\_4`),
285 partition `PART_/_3` values less than MAXVALUE(
286 subpartition `SUB_?/_5`,
287 subpartition `sub_\/?_6`));
288
289 Inserting 10 rows to table_part_1
290 ---------------------------------------
291 insert into table_part_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
292 insert into table_part_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
293 insert into table_part_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
294 insert into table_part_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
295 insert into table_part_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
296 insert into table_part_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
297 insert into table_part_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
298 insert into table_part_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
299 insert into table_part_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
300 insert into table_part_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
301
302 Inserting 10 rows to table_part_sub_1
303 ---------------------------------------
304 insert into table_part_sub_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
305 insert into table_part_sub_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
306 insert into table_part_sub_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
307 insert into table_part_sub_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
308 insert into table_part_sub_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
309 insert into table_part_sub_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
310 insert into table_part_sub_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
311 insert into table_part_sub_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
312 insert into table_part_sub_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
313 insert into table_part_sub_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
314
315 Inserting 10 rows to table_part_sub_2
316 ---------------------------------------
317 insert into table_part_sub_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
318 insert into table_part_sub_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
319 insert into table_part_sub_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
320 insert into table_part_sub_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
321 insert into table_part_sub_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
322 insert into table_part_sub_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
323 insert into table_part_sub_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
324 insert into table_part_sub_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
325 insert into table_part_sub_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
326 insert into table_part_sub_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
327
328 Inserting 10 rows to `table_part_\_special_/_sub_?_3`
329 ---------------------------------------
330 insert into `table_part_\_special_/_sub_?_3` VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
331 insert into `table_part_\_special_/_sub_?_3` VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
332 insert into `table_part_\_special_/_sub_?_3` VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
333 insert into `table_part_\_special_/_sub_?_3` VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
334 insert into `table_part_\_special_/_sub_?_3` VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
335 insert into `table_part_\_special_/_sub_?_3` VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
336 insert into `table_part_\_special_/_sub_?_3` VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
337 insert into `table_part_\_special_/_sub_?_3` VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
338 insert into `table_part_\_special_/_sub_?_3` VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
339 insert into `table_part_\_special_/_sub_?_3` VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
340
341 Inserting 10 rows to table_part_2
342 ---------------------------------------
343 insert into table_part_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
344 insert into table_part_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
345 insert into table_part_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
346 insert into table_part_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
347 insert into table_part_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
348 insert into table_part_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
349 insert into table_part_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
350 insert into table_part_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
351 insert into table_part_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
352 insert into table_part_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
353 select id, value, name from table_part_sub_2 partition (sub_1) order by id;
354 id value name
355 0 55 Row - 0
356 select id, value, name from table_part_sub_2 partition (sub_2) order by id;
357 id value name
358 select id, value, name from table_part_sub_2 partition (sub_3) order by id;
359 id value name
360 2 255 Row - 2
361 4 455 Row - 4
362 select id, value, name from table_part_sub_2 partition (sub_4) order by id;
363 id value name
364 1 155 Row - 1
365 3 355 Row - 3
366 select id, value, name from table_part_sub_2 partition (sub_5) order by id;
367 id value name
368 6 655 Row - 6
369 8 855 Row - 8
370 select id, value, name from table_part_sub_2 partition (sub_6) order by id;
371 id value name
372 5 555 Row - 5
373 7 755 Row - 7
374 9 955 Row - 9
375 DML on partitioned tables
376 -------------------------
377
378 insert into table_part_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
379
380 update table_part_1 set name = 'Row - updated' where value > 500;
381
382 delete from table_part_1 where id = 7;
383
384 insert into table_part_sub_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
385
386 update table_part_sub_1 set name = 'Row - updated' where value > 500;
387
388 delete from table_part_sub_1 where id = 7;
389
390 insert into table_part_sub_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
391
392 update table_part_sub_2 set name = 'Row - updated' where value > 500;
393
394 delete from table_part_sub_2 where id = 7;
395
396 insert into `table_part_\_special_/_sub_?_3` values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
397
398 update `table_part_\_special_/_sub_?_3` set name = 'Row - updated' where value > 500;
399
400 delete from `table_part_\_special_/_sub_?_3` where id = 7;
401
402 insert into table_part_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
403
404 update table_part_2 set name = 'Row - updated' where value > 500;
405
406 delete from table_part_2 where id = 7;
407 #3A. Check shutdown and restart
408 # Restart the server with --log-error
409 # restart: --log-error=SEARCH_FILE --no-console
410 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
411 VARIABLE_NAME VARIABLE_VALUE
412 Innodb_redo_log_enabled OFF
413 Dropping partitioned tables
414 ---------------------------
415 DROP TABLE table_part_1;
416 DROP TABLE table_part_sub_1;
417 DROP TABLE table_part_sub_2;
418 DROP TABLE `table_part_\_special_/_sub_?_3`;
419 DROP TABLE table_part_2;
420 Creating partitioned tables
421 ---------------------------
422 CREATE TABLE table_part_1(
423 id int primary key, value int, name varchar(32), data BLOB,
424 key k1(value)) partition by hash (id) partitions 5;
425
426 CREATE TABLE table_part_2(
427 id int primary key, value int, name varchar(32), data BLOB,
428 key k1(value))
429 partition by hash (id) partitions 5;
430
431 CREATE TABLE table_part_sub_1(
432 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
433 key k1(value)) partition by range (value) subpartition by hash (id)
434 subpartitions 2 ( partition Part_1 values less than (101),
435 partition pArt_2 values less than (401),
436 partition part_3 values less than (701),
437 partition PART_4 values less than MAXVALUE);
438
439 CREATE TABLE table_part_sub_2(
440 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
441 key k1(value)) partition by range (value) subpartition by hash (id)
442 ( partition Part_1 values less than (101)(
443 subpartition Sub_1,
444 subpartition sUb_2),
445 partition pArt_2 values less than (501)(
446 subpartition suB_3 ,
447 subpartition SUB_4),
448 partition PART_3 values less than MAXVALUE(
449 subpartition SUB_5,
450 subpartition sub_6));
451
452 CREATE TABLE `table_part_\_special_/_sub_?_3`(
453 id int, value int, name varchar(32), data BLOB, primary key pk(id, value),
454 key k1(value)) partition by range (value) subpartition by hash (id)
455 ( partition `Part_?_1` values less than (201)(
456 subpartition `Sub_?_1`,
457 subpartition `sUb_/_2`),
458 partition `pArt_\_2` values less than (501)(
459 subpartition `suB_\_3`,
460 subpartition `SUB_?\_4`),
461 partition `PART_/_3` values less than MAXVALUE(
462 subpartition `SUB_?/_5`,
463 subpartition `sub_\/?_6`));
464
465 Inserting 10 rows to table_part_1
466 ---------------------------------------
467 insert into table_part_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
468 insert into table_part_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
469 insert into table_part_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
470 insert into table_part_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
471 insert into table_part_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
472 insert into table_part_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
473 insert into table_part_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
474 insert into table_part_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
475 insert into table_part_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
476 insert into table_part_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
477
478 Inserting 10 rows to table_part_sub_1
479 ---------------------------------------
480 insert into table_part_sub_1 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
481 insert into table_part_sub_1 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
482 insert into table_part_sub_1 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
483 insert into table_part_sub_1 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
484 insert into table_part_sub_1 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
485 insert into table_part_sub_1 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
486 insert into table_part_sub_1 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
487 insert into table_part_sub_1 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
488 insert into table_part_sub_1 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
489 insert into table_part_sub_1 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
490
491 Inserting 10 rows to table_part_sub_2
492 ---------------------------------------
493 insert into table_part_sub_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
494 insert into table_part_sub_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
495 insert into table_part_sub_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
496 insert into table_part_sub_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
497 insert into table_part_sub_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
498 insert into table_part_sub_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
499 insert into table_part_sub_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
500 insert into table_part_sub_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
501 insert into table_part_sub_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
502 insert into table_part_sub_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
503
504 Inserting 10 rows to `table_part_\_special_/_sub_?_3`
505 ---------------------------------------
506 insert into `table_part_\_special_/_sub_?_3` VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
507 insert into `table_part_\_special_/_sub_?_3` VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
508 insert into `table_part_\_special_/_sub_?_3` VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
509 insert into `table_part_\_special_/_sub_?_3` VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
510 insert into `table_part_\_special_/_sub_?_3` VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
511 insert into `table_part_\_special_/_sub_?_3` VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
512 insert into `table_part_\_special_/_sub_?_3` VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
513 insert into `table_part_\_special_/_sub_?_3` VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
514 insert into `table_part_\_special_/_sub_?_3` VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
515 insert into `table_part_\_special_/_sub_?_3` VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
516
517 Inserting 10 rows to table_part_2
518 ---------------------------------------
519 insert into table_part_2 VALUES(0, 55, "Row - 0", REPEAT('Large Column Data - 0' , 256));
520 insert into table_part_2 VALUES(1, 155, "Row - 1", REPEAT('Large Column Data - 1' , 256));
521 insert into table_part_2 VALUES(2, 255, "Row - 2", REPEAT('Large Column Data - 2' , 256));
522 insert into table_part_2 VALUES(3, 355, "Row - 3", REPEAT('Large Column Data - 3' , 256));
523 insert into table_part_2 VALUES(4, 455, "Row - 4", REPEAT('Large Column Data - 4' , 256));
524 insert into table_part_2 VALUES(5, 555, "Row - 5", REPEAT('Large Column Data - 5' , 256));
525 insert into table_part_2 VALUES(6, 655, "Row - 6", REPEAT('Large Column Data - 6' , 256));
526 insert into table_part_2 VALUES(7, 755, "Row - 7", REPEAT('Large Column Data - 7' , 256));
527 insert into table_part_2 VALUES(8, 855, "Row - 8", REPEAT('Large Column Data - 8' , 256));
528 insert into table_part_2 VALUES(9, 955, "Row - 9", REPEAT('Large Column Data - 9' , 256));
529 select id, value, name from table_part_sub_2 partition (sub_1) order by id;
530 id value name
531 0 55 Row - 0
532 select id, value, name from table_part_sub_2 partition (sub_2) order by id;
533 id value name
534 select id, value, name from table_part_sub_2 partition (sub_3) order by id;
535 id value name
536 2 255 Row - 2
537 4 455 Row - 4
538 select id, value, name from table_part_sub_2 partition (sub_4) order by id;
539 id value name
540 1 155 Row - 1
541 3 355 Row - 3
542 select id, value, name from table_part_sub_2 partition (sub_5) order by id;
543 id value name
544 6 655 Row - 6
545 8 855 Row - 8
546 select id, value, name from table_part_sub_2 partition (sub_6) order by id;
547 id value name
548 5 555 Row - 5
549 7 755 Row - 7
550 9 955 Row - 9
551 DML on partitioned tables
552 -------------------------
553
554 insert into table_part_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
555
556 update table_part_1 set name = 'Row - updated' where value > 500;
557
558 delete from table_part_1 where id = 7;
559
560 insert into table_part_sub_1 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
561
562 update table_part_sub_1 set name = 'Row - updated' where value > 500;
563
564 delete from table_part_sub_1 where id = 7;
565
566 insert into table_part_sub_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
567
568 update table_part_sub_2 set name = 'Row - updated' where value > 500;
569
570 delete from table_part_sub_2 where id = 7;
571
572 insert into `table_part_\_special_/_sub_?_3` values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
573
574 update `table_part_\_special_/_sub_?_3` set name = 'Row - updated' where value > 500;
575
576 delete from `table_part_\_special_/_sub_?_3` where id = 7;
577
578 insert into table_part_2 values(15, 1555, "Row - 15", REPEAT('Large Column Data - 15' , 256));
579
580 update table_part_2 set name = 'Row - updated' where value > 500;
581
582 delete from table_part_2 where id = 7;
583 DDL on partitioned tables
584 -------------------------
585 ALTER TABLE table_part_1 TRUNCATE PARTITION p0;
586
587 ALTER TABLE table_part_1 COALESCE PARTITION 2;
588
589 SHOW CREATE TABLE table_part_1;
590 Table Create Table
591 table_part_1 CREATE TABLE `table_part_1` (
592 `id` int NOT NULL,
593 `value` int DEFAULT NULL,
594 `name` varchar(32) DEFAULT NULL,
595 `data` blob,
596 PRIMARY KEY (`id`),
597 KEY `k1` (`value`)
598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
599 /*!50100 PARTITION BY HASH (`id`)
600 PARTITIONS 3 */
601
602 ALTER TABLE table_part_sub_1 TRUNCATE PARTITION part_1;
603
604 ALTER TABLE table_part_sub_2 TRUNCATE PARTITION part_3;
605
606 CREATE TABLE test_table LIKE table_part_sub_2;
607
608 ALTER TABLE test_table REMOVE PARTITIONING;
609 insert into test_table values(2, 265, "Row - 2", REPEAT('Large Column Data - 2' , 256));
610
611 SELECT id, value, name FROM table_part_sub_2 PARTITION (sub_3) order by id;
612 id value name
613 2 255 Row - 2
614 4 455 Row - 4
615
616 SELECT id, value, name FROM test_table order by id;
617 id value name
618 2 265 Row - 2
619
620 ALTER TABLE table_part_sub_2 EXCHANGE PARTITION sub_3 WITH TABLE test_table;
621
622 SELECT id, value, name FROM table_part_sub_2 PARTITION (sub_3) order by id;
623 id value name
624 2 265 Row - 2
625
626 SELECT id, value, name FROM test_table order by id;
627 id value name
628 2 255 Row - 2
629 4 455 Row - 4
630
631 DROP TABLE test_table;
632
633 ALTER TABLE table_part_sub_2 RENAME renamed_table;;
634 SHOW CREATE TABLE renamed_table;
635 Table Create Table
636 renamed_table CREATE TABLE `renamed_table` (
637 `id` int NOT NULL,
638 `value` int NOT NULL,
639 `name` varchar(32) DEFAULT NULL,
640 `data` blob,
641 PRIMARY KEY (`id`,`value`),
642 KEY `k1` (`value`)
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
644 /*!50100 PARTITION BY RANGE (`value`)
645 SUBPARTITION BY HASH (`id`)
646 (PARTITION Part_1 VALUES LESS THAN (101)
647 (SUBPARTITION Sub_1 ENGINE = InnoDB,
648 SUBPARTITION sUb_2 ENGINE = InnoDB),
649 PARTITION pArt_2 VALUES LESS THAN (501)
650 (SUBPARTITION suB_3 ENGINE = InnoDB,
651 SUBPARTITION SUB_4 ENGINE = InnoDB),
652 PARTITION PART_3 VALUES LESS THAN MAXVALUE
653 (SUBPARTITION SUB_5 ENGINE = InnoDB,
654 SUBPARTITION sub_6 ENGINE = InnoDB)) */
655
656 ALTER TABLE renamed_table RENAME table_part_sub_2;;
657
658 ALTER TABLE table_part_1 ENGINE = InnoDB;;
659
660 ALTER TABLE table_part_1 ADD COLUMN new_id int AFTER id;
661
662 ALTER TABLE table_part_1 REMOVE PARTITIONING;
663
664 ALTER TABLE table_part_sub_1 ENGINE = InnoDB;;
665
666 ALTER TABLE table_part_sub_1 ADD COLUMN new_id int AFTER id;
667
668 ALTER TABLE table_part_sub_1 REMOVE PARTITIONING;
669
670 ALTER TABLE table_part_sub_2 ENGINE = InnoDB;;
671
672 ALTER TABLE table_part_sub_2 ADD COLUMN new_id int AFTER id;
673
674 ALTER TABLE table_part_sub_2 REMOVE PARTITIONING;
675
676 ALTER TABLE `table_part_\_special_/_sub_?_3` ENGINE = InnoDB;;
677
678 ALTER TABLE `table_part_\_special_/_sub_?_3` ADD COLUMN new_id int AFTER id;
679
680 ALTER TABLE `table_part_\_special_/_sub_?_3` REMOVE PARTITIONING;
681
682 ALTER TABLE table_part_2 ENGINE = InnoDB;;
683
684 ALTER TABLE table_part_2 ADD COLUMN new_id int AFTER id;
685
686 ALTER TABLE table_part_2 REMOVE PARTITIONING;
687 #3B. Check shutdown and restart with innodb_fast_shutdown = 2
688 SET GLOBAL innodb_fast_shutdown = 2;
689 # restart:
690 Pattern "\[Warning\] .*MY-\d+.* InnoDB cannot do cold shutdown 'innodb_fast_shutdown = 2' and is forcing 'innodb_fast_shutdown = 1' as redo logging is disabled. InnoDB would flush all dirty pages to ensure physical data consistency." found
691 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
692 VARIABLE_NAME VARIABLE_VALUE
693 Innodb_redo_log_enabled OFF
694 #3C. Check shutdown and restart with innodb-read-only
695 # restart: --innodb-read-only
696 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
697 VARIABLE_NAME VARIABLE_VALUE
698 Innodb_redo_log_enabled OFF
699 # restart:
700 Checking partitioned tables
701 ---------------------------
702 SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs;
703
704 CHECK TABLE table_part_1;
705 Table Op Msg_type Msg_text
706 test.table_part_1 check status OK
707
708 SHOW CREATE TABLE table_part_1;
709 Table Create Table
710 table_part_1 CREATE TABLE `table_part_1` (
711 `id` int NOT NULL,
712 `new_id` int DEFAULT NULL,
713 `value` int DEFAULT NULL,
714 `name` varchar(32) DEFAULT NULL,
715 `data` blob,
716 PRIMARY KEY (`id`),
717 KEY `k1` (`value`)
718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
719
720 SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM table_part_1 order by id;
721 id value name SUBSTRING(data, 1024, 32)
722 1 155 Row - 1 ta - 1Large Column Data - 1Large
723 2 255 Row - 2 ta - 2Large Column Data - 2Large
724 3 355 Row - 3 ta - 3Large Column Data - 3Large
725 4 455 Row - 4 ta - 4Large Column Data - 4Large
726 6 655 Row - updated ta - 6Large Column Data - 6Large
727 8 855 Row - updated ta - 8Large Column Data - 8Large
728 9 955 Row - updated ta - 9Large Column Data - 9Large
729
730 CHECK TABLE table_part_sub_1;
731 Table Op Msg_type Msg_text
732 test.table_part_sub_1 check status OK
733
734 SHOW CREATE TABLE table_part_sub_1;
735 Table Create Table
736 table_part_sub_1 CREATE TABLE `table_part_sub_1` (
737 `id` int NOT NULL,
738 `new_id` int DEFAULT NULL,
739 `value` int NOT NULL,
740 `name` varchar(32) DEFAULT NULL,
741 `data` blob,
742 PRIMARY KEY (`id`,`value`),
743 KEY `k1` (`value`)
744 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
745
746 SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM table_part_sub_1 order by id;
747 id value name SUBSTRING(data, 1024, 32)
748 1 155 Row - 1 ta - 1Large Column Data - 1Large
749 2 255 Row - 2 ta - 2Large Column Data - 2Large
750 3 355 Row - 3 ta - 3Large Column Data - 3Large
751 4 455 Row - 4 ta - 4Large Column Data - 4Large
752 5 555 Row - updated ta - 5Large Column Data - 5Large
753 6 655 Row - updated ta - 6Large Column Data - 6Large
754 8 855 Row - updated ta - 8Large Column Data - 8Large
755 9 955 Row - updated ta - 9Large Column Data - 9Large
756 15 1555 Row - updated n Data - 15Large Column Data - 1
757
758 CHECK TABLE table_part_sub_2;
759 Table Op Msg_type Msg_text
760 test.table_part_sub_2 check status OK
761
762 SHOW CREATE TABLE table_part_sub_2;
763 Table Create Table
764 table_part_sub_2 CREATE TABLE `table_part_sub_2` (
765 `id` int NOT NULL,
766 `new_id` int DEFAULT NULL,
767 `value` int NOT NULL,
768 `name` varchar(32) DEFAULT NULL,
769 `data` blob,
770 PRIMARY KEY (`id`,`value`),
771 KEY `k1` (`value`)
772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
773
774 SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM table_part_sub_2 order by id;
775 id value name SUBSTRING(data, 1024, 32)
776 0 55 Row - 0 ta - 0Large Column Data - 0Large
777 1 155 Row - 1 ta - 1Large Column Data - 1Large
778 2 265 Row - 2 ta - 2Large Column Data - 2Large
779 3 355 Row - 3 ta - 3Large Column Data - 3Large
780
781 CHECK TABLE `table_part_\_special_/_sub_?_3`;
782 Table Op Msg_type Msg_text
783 test.table_part_\_special_/_sub_?_3 check status OK
784
785 SHOW CREATE TABLE `table_part_\_special_/_sub_?_3`;
786 Table Create Table
787 table_part_\_special_/_sub_?_3 CREATE TABLE `table_part_\_special_/_sub_?_3` (
788 `id` int NOT NULL,
789 `new_id` int DEFAULT NULL,
790 `value` int NOT NULL,
791 `name` varchar(32) DEFAULT NULL,
792 `data` blob,
793 PRIMARY KEY (`id`,`value`),
794 KEY `k1` (`value`)
795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
796
797 SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM `table_part_\_special_/_sub_?_3` order by id;
798 id value name SUBSTRING(data, 1024, 32)
799 0 55 Row - 0 ta - 0Large Column Data - 0Large
800 1 155 Row - 1 ta - 1Large Column Data - 1Large
801 2 255 Row - 2 ta - 2Large Column Data - 2Large
802 3 355 Row - 3 ta - 3Large Column Data - 3Large
803 4 455 Row - 4 ta - 4Large Column Data - 4Large
804 5 555 Row - updated ta - 5Large Column Data - 5Large
805 6 655 Row - updated ta - 6Large Column Data - 6Large
806 8 855 Row - updated ta - 8Large Column Data - 8Large
807 9 955 Row - updated ta - 9Large Column Data - 9Large
808 15 1555 Row - updated n Data - 15Large Column Data - 1
809
810 CHECK TABLE table_part_2;
811 Table Op Msg_type Msg_text
812 test.table_part_2 check status OK
813
814 SHOW CREATE TABLE table_part_2;
815 Table Create Table
816 table_part_2 CREATE TABLE `table_part_2` (
817 `id` int NOT NULL,
818 `new_id` int DEFAULT NULL,
819 `value` int DEFAULT NULL,
820 `name` varchar(32) DEFAULT NULL,
821 `data` blob,
822 PRIMARY KEY (`id`),
823 KEY `k1` (`value`)
824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
825
826 SELECT id, value, name, SUBSTRING(data, 1024, 32) FROM table_part_2 order by id;
827 id value name SUBSTRING(data, 1024, 32)
828 0 55 Row - 0 ta - 0Large Column Data - 0Large
829 1 155 Row - 1 ta - 1Large Column Data - 1Large
830 2 255 Row - 2 ta - 2Large Column Data - 2Large
831 3 355 Row - 3 ta - 3Large Column Data - 3Large
832 4 455 Row - 4 ta - 4Large Column Data - 4Large
833 5 555 Row - updated ta - 5Large Column Data - 5Large
834 6 655 Row - updated ta - 6Large Column Data - 6Large
835 8 855 Row - updated ta - 8Large Column Data - 8Large
836 9 955 Row - updated ta - 9Large Column Data - 9Large
837 15 1555 Row - updated n Data - 15Large Column Data - 1
838
839 Checking IFS views
840 ------------------
841 select table_schema, table_name from information_schema.tables
842 where table_name LIKE '%table_part%' order by table_schema, table_name;
843 TABLE_SCHEMA TABLE_NAME
844 test table_part_1
845 test table_part_2
846 test table_part_\_special_/_sub_?_3
847 test table_part_sub_1
848 test table_part_sub_2
849
850 select name, space_type from information_schema.innodb_tables
851 where name LIKE '%table_part%' order by name, space_type;
852 name space_type
853 test/table_part_1 Single
854 test/table_part_2 Single
855 test/table_part_@005c_special_@002f_sub_@003f_3 Single
856 test/table_part_sub_1 Single
857 test/table_part_sub_2 Single
858
859 select table_schema, table_name, partition_name, subpartition_name
860 from information_schema.partitions
861 where table_name LIKE '%table_part%'
862 order by table_schema, table_name, partition_name, subpartition_name;
863 TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
864 test table_part_1 NULL NULL
865 test table_part_2 NULL NULL
866 test table_part_\_special_/_sub_?_3 NULL NULL
867 test table_part_sub_1 NULL NULL
868 test table_part_sub_2 NULL NULL
869
870 select name from information_schema.innodb_tablespaces
871 where name LIKE '%table_part%' order by name;
872 name
873 test/table_part_1
874 test/table_part_2
875 test/table_part_sub_1
876 test/table_part_sub_2
877 test/table_part_\_special_/_sub_?_3
878
879 select file_type, status, file_name, tablespace_name from information_schema.files
880 where file_name LIKE '%table_part%'
881 order by file_type, status, file_name, tablespace_name;
882 FILE_TYPE STATUS FILE_NAME TABLESPACE_NAME
883 TABLESPACE NORMAL ./test/table_part_1.ibd test/table_part_1
884 TABLESPACE NORMAL ./test/table_part_2.ibd test/table_part_2
885 TABLESPACE NORMAL ./test/table_part_@005c_special_@002f_sub_@003f_3.ibd test/table_part_\_special_/_sub_?_3
886 TABLESPACE NORMAL ./test/table_part_sub_1.ibd test/table_part_sub_1
887 TABLESPACE NORMAL ./test/table_part_sub_2.ibd test/table_part_sub_2
888
889 Checking Innodb stat tables
890 ---------------------------
891 select database_name, table_name from mysql.innodb_table_stats
892 where table_name like '%table_part%' order by database_name, table_name;
893 database_name table_name
894 test table_part_1
895 test table_part_2
896 test table_part_\_special_/_sub_?_3
897 test table_part_sub_1
898 test table_part_sub_2
899
900 select database_name, table_name, index_name, stat_name from mysql.innodb_index_stats
901 where table_name like '%table_part%'
902 order by database_name, table_name, index_name, stat_name;
903 database_name table_name index_name stat_name
904 test table_part_1 PRIMARY n_diff_pfx01
905 test table_part_1 PRIMARY n_leaf_pages
906 test table_part_1 PRIMARY size
907 test table_part_1 k1 n_diff_pfx01
908 test table_part_1 k1 n_diff_pfx02
909 test table_part_1 k1 n_leaf_pages
910 test table_part_1 k1 size
911 test table_part_2 PRIMARY n_diff_pfx01
912 test table_part_2 PRIMARY n_leaf_pages
913 test table_part_2 PRIMARY size
914 test table_part_2 k1 n_diff_pfx01
915 test table_part_2 k1 n_diff_pfx02
916 test table_part_2 k1 n_leaf_pages
917 test table_part_2 k1 size
918 test table_part_\_special_/_sub_?_3 PRIMARY n_diff_pfx01
919 test table_part_\_special_/_sub_?_3 PRIMARY n_diff_pfx02
920 test table_part_\_special_/_sub_?_3 PRIMARY n_leaf_pages
921 test table_part_\_special_/_sub_?_3 PRIMARY size
922 test table_part_\_special_/_sub_?_3 k1 n_diff_pfx01
923 test table_part_\_special_/_sub_?_3 k1 n_diff_pfx02
924 test table_part_\_special_/_sub_?_3 k1 n_leaf_pages
925 test table_part_\_special_/_sub_?_3 k1 size
926 test table_part_sub_1 PRIMARY n_diff_pfx01
927 test table_part_sub_1 PRIMARY n_diff_pfx02
928 test table_part_sub_1 PRIMARY n_leaf_pages
929 test table_part_sub_1 PRIMARY size
930 test table_part_sub_1 k1 n_diff_pfx01
931 test table_part_sub_1 k1 n_diff_pfx02
932 test table_part_sub_1 k1 n_leaf_pages
933 test table_part_sub_1 k1 size
934 test table_part_sub_2 PRIMARY n_diff_pfx01
935 test table_part_sub_2 PRIMARY n_diff_pfx02
936 test table_part_sub_2 PRIMARY n_leaf_pages
937 test table_part_sub_2 PRIMARY size
938 test table_part_sub_2 k1 n_diff_pfx01
939 test table_part_sub_2 k1 n_diff_pfx02
940 test table_part_sub_2 k1 n_leaf_pages
941 test table_part_sub_2 k1 size
942 SET NAMES DEFAULT;
943 Dropping partitioned tables
944 ---------------------------
945 DROP TABLE table_part_1;
946 DROP TABLE table_part_sub_1;
947 DROP TABLE table_part_sub_2;
948 DROP TABLE `table_part_\_special_/_sub_?_3`;
949 DROP TABLE table_part_2;
950 ALTER INSTANCE ENABLE INNODB REDO_LOG;
951 SELECT * FROM performance_schema.global_status WHERE variable_name like 'Innodb_redo_log_enabled';
952 VARIABLE_NAME VARIABLE_VALUE
953 Innodb_redo_log_enabled ON