Debian Codegraph @

mysql-test/suite/innodb/t/partition.test

raw
1 --echo #
2 --echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows all the columns
3 --echo # in a non-partitioned table
4 --echo #
5
6 CREATE TABLE t_nopart (a int PRIMARY KEY, b int, c varchar(64));
7
8 SELECT lower(t.name), lower(c.name)
9 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
10 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
11 WHERE t.table_id = c.table_id and t.name like '%t_nopart%'
12 ORDER BY t.name, c.name;
13
14 DROP TABLE t_nopart;
15
16 --echo #
17 --echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for
18 --echo # all the partitions in a partitioned table
19 --echo #
20
21 CREATE TABLE t_part
22 (a int,
23 b int,
24 c varchar(64),
25 PRIMARY KEY (a),
26 KEY (b),
27 KEY (c,b))
28 ENGINE = InnoDB
29 PARTITION BY HASH (a) PARTITIONS 5;
30
31 SELECT lower(t.name), lower(c.name)
32 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
33 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
34 WHERE t.table_id = c.table_id and t.name like '%t_part%'
35 ORDER BY t.name, c.name;
36
37 DROP TABLE t_part;
38
39 --echo #
40 --echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for all
41 --echo # the subpartitions of a table
42 --echo #
43
44 CREATE TABLE t_subpart (a INT, b INT)
45 PARTITION BY RANGE(b)
46 SUBPARTITION BY HASH(b)
47 SUBPARTITIONS 2 (
48 PARTITION p0 VALUES LESS THAN (1000),
49 PARTITION p1 VALUES LESS THAN (2000),
50 PARTITION p2 VALUES LESS THAN MAXVALUE
51 );
52
53 SELECT lower(t.name), lower(c.name)
54 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
55 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
56 WHERE t.table_id = c.table_id and t.name like '%t_subpart%'
57 ORDER BY t.name, c.name;
58
59 DROP TABLE t_subpart;
60
61 --echo #
62 --echo # Test of optimizer estimates
63 --echo #
64 CREATE TABLE t0 (a int PRIMARY KEY, b int, c varchar(64));
65 CREATE TABLE t1
66 (a int,
67 b int,
68 c varchar(64),
69 PRIMARY KEY (a),
70 KEY (b),
71 KEY (c,b))
72 ENGINE = InnoDB
73 PARTITION BY HASH (a) PARTITIONS 5;
74
75 --echo # Set up the rows so that the following query would match
76 --echo # 100 PK values but only 10 'b' values. But 90 of the matching
77 --echo # PKs will be in the smallest partition.
78 --echo # Since the fix for bug#1364811 will ha_partition only check the biggest
79 --echo # partitions, but ha_innopart will check all partitions when estimating
80 --echo # numbers of records in range. Resulting in ha_partition will
81 --echo # assume there are only 10 matching PK values and choose to do search
82 --echo # by PK but ha_innopart will search by secondary index 'b' since it
83 --echo # will prefer to read 10 records from the secondary index over reading
84 --echo # 100 records from the PRIMARY index.
85 --echo # SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
86
87
88 --echo # Using t0 to prevent any issues with purge in t1.
89 INSERT INTO t0 VALUES (1,0,"common"),(2,0,"common"),(3,0,"common"),
90 (4,0,"common"),(5,0,"common"), (6,0,"common"),(7,0,"common"),(8,0,"common"),
91 (9,0,"common"),(10,0,"common");
92 INSERT INTO t0 SELECT a + 10, b, c FROM t0;
93 INSERT INTO t0 SELECT a + 20, b, c FROM t0;
94 INSERT INTO t0 SELECT a + 40, b, c FROM t0;
95 INSERT INTO t0 SELECT a + 80, b, c FROM t0;
96 INSERT INTO t0 SELECT a + 160, b, c FROM t0;
97 INSERT INTO t0 SELECT a + 320, b, c FROM t0;
98 INSERT INTO t0 SELECT a + 640, b, c FROM t0;
99 DELETE FROM t0 WHERE a BETWEEN 13 AND 450 AND (a % 5) <> 0;
100 DELETE FROM t0 WHERE a > 450 AND (a % 5) = 0 LIMIT 250;
101
102 # Uncomment these to see the resulting distribution...
103 #SELECT COUNT(*) FROM t0 WHERE a BETWEEN 1 AND 450;
104 #SELECT COUNT(*) FROM t0 WHERE a BETWEEN 1 AND 450 AND (a % 5) = 0;
105 #SELECT (a % 5) as part, COUNT(*) FROM t0 GROUP BY part;
106
107 UPDATE t0 SET b = a, c = "MATCH!" WHERE a <= 10;
108 #SELECT COUNT(*) FROM t0 WHERE b BETWEEN 1 AND 10;
109 #SELECT (a % 5) as part, COUNT(*) FROM t0 WHERE b BETWEEN 1 AND 10 GROUP BY part;
110 INSERT INTO t1 SELECT * FROM t0;
111 DROP TABLE t0;
112
113 ANALYZE TABLE t1;
114 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
115
116 --disable_warnings
117 # Access session_status, so that the session_status metadata is accessed and
118 # kept in DD cache. Else the HANDLER_READ_KEY would increment more than
119 # required.
120 SELECT * FROM performance_schema.session_status
121 WHERE VARIABLE_NAME LIKE 'dummy';
122 --enable_warnings
123
124 FLUSH STATUS;
125 --sorted_result
126 SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
127 let $get_handler_status_counts= SELECT * FROM performance_schema.session_status
128 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
129 --source include/get_handler_status_counts.inc
130
131 DROP TABLE t1;
132
133 --echo # Test of index_merge and delete
134 CREATE TABLE `t1` (
135 `a` int(11) NOT NULL,
136 `b` int(11) NOT NULL,
137 KEY `a` (`a`),
138 KEY `b` (`b`)
139 )
140 /*!50100 PARTITION BY HASH (a)
141 PARTITIONS 3 */;
142 INSERT INTO t1 VALUES (0,0),(0,0),(1,0),(1,0),(2,0),(2,0),(0,0);
143 INSERT INTO t1 SELECT t1.a,t1.b FROM t1, t1 t2;
144 INSERT INTO t1 VALUES (1,1),(2,2);
145
146 ANALYZE TABLE t1;
147 --replace_column 10 #
148 EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b > 0;
149 --replace_column 10 #
150 EXPLAIN DELETE FROM t1 WHERE a = 4 OR b > 0;
151 --sorted_result
152 SELECT * FROM t1 WHERE a = 4 OR b > 0;
153 SELECT COUNT(*) FROM t1;
154 DELETE FROM t1 WHERE a = 4 OR b > 0;
155 SELECT COUNT(*) FROM t1;
156 --sorted_result
157 SELECT * FROM t1 WHERE a = 4 OR b > 0;
158 DROP TABLE t1;
159
160 --echo # more testing of index_merge and delete
161 CREATE TABLE t1
162 (a int not null,
163 b int not null,
164 INDEX(a),
165 INDEX(b))
166 engine=InnoDB
167 PARTITION BY KEY(a,b) PARTITIONS 5;
168 INSERT INTO t1 VALUES (0,0),(1,1), (2,2), (3,3), (4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
169 INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
170 INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
171 INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
172 INSERT INTO t1 SELECT a + 80, b + 80 FROM t1;
173 INSERT INTO t1 SELECT a + 160, b + 160 FROM t1;
174
175 ANALYZE TABLE t1;
176
177 --replace_column 10 #
178 EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b < 3;
179 --sorted_result
180 SELECT * FROM t1 WHERE a = 4 OR b < 3;
181
182 --replace_column 10 #
183 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
184 --sorted_result
185 SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
186
187 --replace_column 10 #
188 EXPLAIN DELETE FROM t1 WHERE a = 4 OR b < 3;
189 DELETE FROM t1 WHERE a = 4 OR b < 3;
190
191 --replace_column 10 #
192 EXPLAIN DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
193 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
194 DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
195
196 DROP TABLE t1;
197
198 --echo #
199 --echo # Bug#20584754: CAN'T FIND RECORD IN 'TABLE100_KEY_PK_PARTS_2_DATETIME_3'
200 --echo #
201 let $MYSQLD_DATADIR=`SELECT @@datadir`;
202 CREATE TABLE `t1` (
203 `c1` tinyint unsigned DEFAULT NULL,
204 `c2` char(1) DEFAULT NULL,
205 `pk` tinyint NOT NULL,
206 PRIMARY KEY (`pk`),
207 KEY `c1` (`c1`),
208 KEY `c2` (`c2`)
209 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
210 /*!50100 PARTITION BY HASH (pk)
211 PARTITIONS 2 */;
212
213 --echo # Create the dataset in a separate table, so it can be loaded
214 --echo # directly without update/delete which may give unstable EXPLAIN
215 --echo # due to background pruning.
216 CREATE TABLE t0
217 (a tinyint not null auto_increment primary key,
218 b char(1) DEFAULT 'a',
219 c tinyint DEFAULT 1);
220 INSERT INTO t0 (a) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
221 INSERT INTO t0 (a) SELECT NULL FROM t0, t0 t0_2 LIMIT 50;
222 UPDATE t0 SET c = NULL WHERE a IN (2,3,6,13,18);
223 INSERT INTO t1 SELECT c,b,a FROM t0;
224 DROP TABLE t0;
225
226 ANALYZE TABLE t1;
227
228 --replace_column 10 #
229 EXPLAIN SELECT
230 pk
231 FROM `t1`
232 WHERE `c1` IS NULL OR `c2` = '0';
233
234 --sorted_result
235 SELECT
236 pk
237 FROM `t1`
238 WHERE `c1` IS NULL OR `c2` = '0';
239
240 DROP TABLE t1;
241
242 --echo #
243 --echo # Bug#20516390: PARTITIONED TABLE, TBLSPACE MOVE, ALTER, ASSERT
244 --echo # !(M_FILE_PER_TABLE && M_USE_SHAR
245 --echo #
246 # CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
247 CREATE TABLE t1
248 (a int not null,
249 d int not null,
250 b varchar(198) not null,
251 c varchar(177),
252 index(d),
253 index(a),
254 PRIMARY KEY (b(10), a, d),
255 INDEX (c(95), b(95)),
256 INDEX (b(5), c(10), a))
257 ENGINE=InnoDB stats_persistent=DEFAULT
258 PARTITION BY LINEAR HASH(d) PARTITIONS 2;
259 # TODO : Enable following once shared tablespace are allowed in Partitioned
260 # Table (wl#12034).
261 # ALTER TABLE t1 TABLESPACE ts1;
262 ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT;
263 DROP TABLE t1;
264 # DROP TABLESPACE ts1;
265
266 --echo #
267 --echo # Bug #20593808: CRASH WITH PARTITIONED TABLES
268 --echo # MULTITABLE DELETE
269 --echo #
270
271 --echo Test DML with index scan on partitioned table
272 --echo #1. test delete with join using PK index scan on partitioned table
273 eval CREATE TABLE t1 (
274 col_1_int int,
275 col_2_text text)
276 ENGINE=InnoDB;
277
278 SHOW CREATE TABLE t1;
279
280 --echo #insert two records in t1
281 INSERT INTO t1 VALUES (5, 'row to delete');
282 INSERT INTO t1 VALUES (3, 'row to keep');
283
284 SELECT * FROM t1 ORDER BY col_1_int;
285
286 eval CREATE TABLE t2 (
287 col_1_int int,
288 col_2_int int,
289 col_3_text text,
290 PRIMARY KEY(col_1_int, col_3_text(100)),
291 KEY idx1 (col_2_int, col_1_int))
292 ENGINE=InnoDB
293 PARTITION BY KEY(col_1_int) partitions 5;
294
295 SHOW CREATE TABLE t2;
296
297 --echo #insert five records in t2
298 INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
299 INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
300 INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
301 INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
302 INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');
303
304 SELECT * FROM t2 ORDER BY col_1_int;
305
306 --echo # test delete with join and index scan
307 DELETE t1 FROM t2, t1 WHERE t2.col_1_int <= t1.col_1_int;
308
309 --echo expect one record
310 SELECT * FROM t1;
311
312 DROP TABLE t1;
313 DROP TABLE t2;
314
315 --echo #2. test delete with join using secondary index scan on partitioned table
316 eval CREATE TABLE t1 (
317 col_1_int int,
318 col_2_text text)
319 ENGINE=InnoDB;
320
321 SHOW CREATE TABLE t1;
322
323 --echo #insert two records in t1
324 INSERT INTO t1 VALUES (5, 'row to delete');
325 INSERT INTO t1 VALUES (3, 'row to keep');
326
327 SELECT * FROM t1 ORDER BY col_1_int;
328
329 eval CREATE TABLE t2 (
330 col_1_int int,
331 col_2_int int,
332 col_3_text text,
333 PRIMARY KEY(col_2_int, col_1_int),
334 KEY idx1(col_1_int, col_3_text(100)))
335 ENGINE=InnoDB
336 PARTITION BY KEY(col_2_int) partitions 5;
337
338 SHOW CREATE TABLE t2;
339
340 --echo #insert five records in t2
341 INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
342 INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
343 INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
344 INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
345 INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');
346
347 SELECT * FROM t2 ORDER BY col_1_int;
348
349 --echo # test delete with join and index scan
350 DELETE t1 FROM t2, t1 WHERE t2.col_1_int <= t1.col_1_int;
351
352 --echo expect one record
353 SELECT * FROM t1;
354
355 DROP TABLE t1;
356 DROP TABLE t2;
357
358 --echo #
359 --echo # Bug #20949314: PARTITION_HELPER::PH_RND_INIT(BOOL): ASSERTION
360 --echo #
361
362 CREATE TABLE t1 (c1 INT);
363 INSERT INTO t1 VALUES (3), (4), (5), (1), (2);
364
365 CREATE TABLE t2 PARTITION BY KEY (c1) PARTITIONS 3
366 AS
367 SELECT * FROM t1;
368
369 DELETE A FROM t1 AS A WHERE A.c1 IN
370 (SELECT c1 FROM t2 AS B WHERE B.c1 = A.c1);
371
372 SELECT * from t1;
373
374 DROP TABLE t1;
375 DROP TABLE t2;
376
377 --echo #
378 --echo # Bug#21211524: CRASH IN ACTUAL_KEY_PARTS WITH PARTITIONED TABLES
379 --echo #
380 CREATE TABLE t1(
381 col1 SET('') CHARACTER SET UTF16 COLLATE UTF16_SPANISH_CI,
382 col2 INT,
383 PRIMARY KEY (col2, col1),
384 UNIQUE KEY (col1, col2))
385 ENGINE=INNODB
386 PARTITION BY KEY (col1);
387
388 SELECT * from t1 WHERE col1 BETWEEN 0x92 AND "";
389
390 DROP TABLE t1;
391
392 --echo #
393 --echo # Bug#21794110: ASSERTION `MP->COUNT > 0 && MY_THREAD_EQUAL
394 --echo #
395
396 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 CHAR(10))
397 ENGINE=INNODB
398 PARTITION BY HASH(col1) PARTITIONS 2;
399
400 INSERT INTO t1 VALUES(0, 'string1');
401 ALTER TABLE t1 CHANGE col1 col1 INT UNSIGNED AUTO_INCREMENT;
402 SHOW CREATE TABLE t1;
403
404 DROP TABLE t1;
405
406 --echo #
407 --echo # Bug#21620577: ASSERTION H->ACTIVE_INDEX == 64
408 --echo #
409
410 CREATE TABLE t1(col1 YEAR not null, col2 int, KEY(col1), KEY(col1, col2))
411 ENGINE=INNODB
412 PARTITION BY KEY(col1) PARTITIONS 2;
413
414 INSERT INTO t1 VALUES('2015', 10);
415
416 SELECT /*+ bka()*/ a.col2 from t1 AS a
417 RIGHT JOIN t1 AS b on a.col1<=>null
418 RIGHT JOIN t1 AS c on 1;
419
420 DROP TABLE t1;
421
422 --echo #
423 --echo # Bug#21754608: HANDLE_FATAL_SIGNAL PARTITION_HELPER::PH_READ_RANGE_FIRST
424 --echo #
425
426 CREATE TABLE t1(col1 int PRIMARY KEY, col2 int, col3 int)
427 ENGINE=INNODB
428 PARTITION BY LIST(col1) PARTITIONS 2
429 (PARTITION p0 VALUES IN (11,12),
430 PARTITION p1 VALUES IN (0,1));
431
432 INSERT INTO t1 values(1, 10, 100);
433
434 --disable_warnings
435 SELECT * from t1 WHERE col1 BETWEEN '2000-01-01 01:02:00' AND '2000-01-01 01:03:00';
436 --enable_warnings
437
438 DROP TABLE t1;
439
440 --echo #
441 --echo # Bug#21755994 ALTER INPLACE CRASHES ON A INNODB TABLE WITH PARTITION ON SEPARATE TABLESPACE
442 --echo #
443 --echo # Note that we do not support partitions in shared tablespaces
444 --echo # (at least not until WL12034).
445
446 # This test case was disabled during WL#6378 due to Bug#21914047.
447 # The bug has been fixed, but InnoDB now does not support partitions
448 # in shared tablespaces. Hence, the test case is temporarily rewritten
449 # and the original test case may be restored when partitinos are again
450 # allowed to be in shared tablespaces.
451 #
452 # TODO WL#12034: Enable the test case below.
453 #
454 #CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
455 #
456 #CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
457 #ENGINE=InnoDB
458 #PARTITION BY RANGE (a) PARTITIONS 3
459 #( PARTITION P1 VALUES LESS THAN (2) TABLESPACE ts1,
460 # PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_system`,
461 # PARTITION P3 VALUES LESS THAN (6));
462 #
463 #ALTER TABLE t1 ADD COLUMN f int;
464 #
465 #DROP TABLE t1;
466 #DROP TABLESPACE ts1;
467
468 --error ER_ILLEGAL_HA_CREATE_OPTION
469 CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
470 ENGINE=InnoDB
471 PARTITION BY RANGE (a) PARTITIONS 3
472 ( PARTITION P1 VALUES LESS THAN (2),
473 PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_system`,
474 PARTITION P3 VALUES LESS THAN (6));
475
476 CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
477 ENGINE=InnoDB
478 PARTITION BY RANGE (a) PARTITIONS 3
479 ( PARTITION P1 VALUES LESS THAN (2),
480 PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_file_per_table`,
481 PARTITION P3 VALUES LESS THAN (6));
482
483 ALTER TABLE t1 ADD COLUMN f int;
484
485 DROP TABLE t1;
486
487 # TODO : Enable following once shared tablespace are allowed in Partitioned
488 # Table (wl#12034).
489 # --echo #
490 # --echo # Bug#21796845 ASSERT ERR == DB_SUCCESS, ROW_QUIESCE_TABLE_START(), FLUSH TABLES FOR EXPORT
491 # --echo #
492 # CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
493 #
494 # CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB
495 # PARTITION BY RANGE(a) (
496 # PARTITION p1 VALUES LESS THAN (100) TABLESPACE innodb_system,
497 # PARTITION p2 VALUES LESS THAN (200),
498 # PARTITION p3 VALUES LESS THAN (300) TABLESPACE ts1,
499 # PARTITION p4 VALUES LESS THAN (400) TABLESPACE innodb_system);
500 #
501 # FLUSH table t1 FOR EXPORT;
502 # UNLOCK TABLES;
503 #
504 # DROP TABLE t1;
505 # DROP TABLESPACE ts1;
506
507 --echo #
508 --echo # Bug#21957001 INNODB: FAILING ASSERTION: 0 IN FILE HA_INNOPART.CC LINE 3526
509 --echo #
510 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Trying to access missing tablespace .*");
511 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Missing .ibd file for table `test`\.`t1` .* ");
512 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table .*");
513 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
514 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
515 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");
516
517 CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;
518
519 ALTER TABLE t1 DISCARD PARTITION p3 TABLESPACE;
520 ANALYZE TABLE t1;
521 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
522
523 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
524 ANALYZE TABLE t1;
525 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
526
527 ALTER TABLE t1 DISCARD PARTITION p2 TABLESPACE;
528 ANALYZE TABLE t1;
529 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
530
531 ALTER TABLE t1 DISCARD PARTITION p0 TABLESPACE;
532 ANALYZE TABLE t1;
533 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
534
535 DROP TABLE t1;
536
537 CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;
538
539 ALTER TABLE t1 DISCARD TABLESPACE;
540 ANALYZE TABLE t1;
541 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
542
543 DROP TABLE t1;
544
545 --echo #
546 --echo # Bug#21864838: SIG11 IN FIELD_BLOB::CMP| AT SQL/FIELD.CC
547 --echo #
548
549 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
550 ENGINE=INNODB
551 PARTITION BY HASH(col1) PARTITIONS 2;
552
553 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
554 ENGINE=INNODB
555 PARTITION BY HASH(col1) PARTITIONS 2;
556
557 INSERT INTO t1 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');
558 INSERT INTO t2 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');
559
560 SELECT t1.col1 FROM t1, t2 where t1.col1 = 3 AND t1.col3 = t2.col3 ORDER BY t2.col1 DESC;
561
562 DROP TABLE t1;
563 DROP TABLE t2;
564
565 --echo #
566 --echo # Bug #21881155: UNINTIALIZED READ IN KEY_REC_CMP CAUSE CRASH LATER
567 --echo #
568
569 CREATE TABLE t1 (col1 int, col2 BLOB AS ('a') VIRTUAL, col3 INT,
570 PRIMARY KEY(col1), KEY (col3, col2(1), col1))
571 ENGINE=INNODB
572 PARTITION BY KEY (col1) PARTITIONS 2;
573
574 INSERT INTO t1(col1) values (1),(2);
575 SELECT 1 FROM t1 WHERE col2 > 'a' GROUP BY col3;
576
577 DROP TABLE t1;
578
579 # TODO : Enable following once shared tablespace are allowed in Partitioned
580 # Table (wl#12034).
581 # --echo #
582 # --echo # Bug#21914047 SHOW CREATE DUMPS AFTER ALTER ON INNODB TABLE WITH PARTITIONS USING TABLESPACE
583 # --echo #
584 # CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
585 # CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd';
586 #
587 # CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY (a))
588 # ENGINE=InnoDB TABLESPACE ts2
589 # PARTITION BY RANGE(a)
590 # PARTITIONS 4
591 # ( PARTITION p1 VALUES LESS THAN (2) TABLESPACE ts1,
592 # PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_system,
593 # PARTITION p3 VALUES LESS THAN (6),
594 # PARTITION p4 VALUES LESS THAN (8) TABLESPACE innodb_file_per_table);
595 #
596 # SHOW CREATE TABLE t1;
597 #
598 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
599 # --replace_result #P# #p#
600 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
601 # FROM information_schema.innodb_tables A
602 # LEFT JOIN
603 # information_schema.innodb_tablespaces B
604 # ON A.SPACE = B.SPACE
605 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
606 #
607 # ALTER TABLE t1 ADD COLUMN b int;
608 # SHOW CREATE TABLE t1;
609 #
610 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
611 # --replace_result #P# #p#
612 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
613 # FROM information_schema.innodb_tables A
614 # LEFT JOIN
615 # information_schema.innodb_tablespaces B
616 # ON A.SPACE = B.SPACE
617 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
618 #
619 # ALTER TABLE t1 ADD COLUMN c int, TABLESPACE ts1;
620 # SHOW CREATE TABLE t1;
621 #
622 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
623 # --replace_result #P# #p#
624 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
625 # FROM information_schema.innodb_tables A
626 # LEFT JOIN
627 # information_schema.innodb_tablespaces B
628 # ON A.SPACE = B.SPACE
629 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
630 #
631 # ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (10) TABLESPACE ts2);
632 # SHOW CREATE TABLE t1;
633 #
634 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
635 # --replace_result #P# #p#
636 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
637 # FROM information_schema.innodb_tables A
638 # LEFT JOIN
639 # information_schema.innodb_tablespaces B
640 # ON A.SPACE = B.SPACE
641 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
642 #
643 # ALTER TABLE t1 ADD PARTITION (PARTITION p6 VALUES LESS THAN (12));
644 # SHOW CREATE TABLE t1;
645 #
646 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
647 # --replace_result #P# #p#
648 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
649 # FROM information_schema.innodb_tables A
650 # LEFT JOIN
651 # information_schema.innodb_tablespaces B
652 # ON A.SPACE = B.SPACE
653 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
654 #
655 # ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (PARTITION p4 VALUES LESS THAN (8));
656 # SHOW CREATE TABLE t1;
657 #
658 # --replace_result #P# #p#
659 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
660 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
661 # FROM information_schema.innodb_tables A
662 # LEFT JOIN
663 # information_schema.innodb_tablespaces B
664 # ON A.SPACE = B.SPACE
665 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
666 #
667 # ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (6) TABLESPACE innodb_system);
668 # SHOW CREATE TABLE t1;
669 #
670 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
671 # --replace_result #P# #p#
672 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
673 # FROM information_schema.innodb_tables A
674 # LEFT JOIN
675 # information_schema.innodb_tablespaces B
676 # ON A.SPACE = B.SPACE
677 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
678 #
679 # DROP TABLE t1;
680 #
681 # CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY (a))
682 # ENGINE=InnoDB
683 # PARTITION BY RANGE(a)
684 # PARTITIONS 4
685 # ( PARTITION p1 VALUES LESS THAN (2) TABLESPACE ts1,
686 # PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_system,
687 # PARTITION p3 VALUES LESS THAN (6),
688 # PARTITION p4 VALUES LESS THAN (8) TABLESPACE innodb_file_per_table);
689 #
690 # SHOW CREATE TABLE t1;
691 #
692 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
693 # --replace_result #P# #p#
694 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
695 # FROM information_schema.innodb_tables A
696 # LEFT JOIN
697 # information_schema.innodb_tablespaces B
698 # ON A.SPACE = B.SPACE
699 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
700 #
701 # ALTER TABLE t1 ADD COLUMN b int;
702 # SHOW CREATE TABLE t1;
703 #
704 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
705 # --replace_result #P# #p#
706 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
707 # FROM information_schema.innodb_tables A
708 # LEFT JOIN
709 # information_schema.innodb_tablespaces B
710 # ON A.SPACE = B.SPACE
711 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
712 #
713 # ALTER TABLE t1 ADD COLUMN c int, TABLESPACE ts1;
714 # SHOW CREATE TABLE t1;
715 #
716 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
717 # --replace_result #P# #p#
718 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
719 # FROM information_schema.innodb_tables A
720 # LEFT JOIN
721 # information_schema.innodb_tablespaces B
722 # ON A.SPACE = B.SPACE
723 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
724 #
725 # ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (10) TABLESPACE ts2);
726 # SHOW CREATE TABLE t1;
727 #
728 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
729 # --replace_result #P# #p#
730 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
731 # FROM information_schema.innodb_tables A
732 # LEFT JOIN
733 # information_schema.innodb_tablespaces B
734 # ON A.SPACE = B.SPACE
735 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
736 #
737 # ALTER TABLE t1 ADD PARTITION (PARTITION p6 VALUES LESS THAN (12));
738 # SHOW CREATE TABLE t1;
739 #
740 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
741 # --replace_result #P# #p#
742 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
743 # FROM information_schema.innodb_tables A
744 # LEFT JOIN
745 # information_schema.innodb_tablespaces B
746 # ON A.SPACE = B.SPACE
747 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
748 #
749 # ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (PARTITION p4 VALUES LESS THAN (8));
750 # SHOW CREATE TABLE t1;
751 #
752 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
753 # --replace_result #P# #p#
754 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
755 # FROM information_schema.innodb_tables A
756 # LEFT JOIN
757 # information_schema.innodb_tablespaces B
758 # ON A.SPACE = B.SPACE
759 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
760 #
761 # ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (6) TABLESPACE innodb_system);
762 # SHOW CREATE TABLE t1;
763 #
764 # --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
765 # --replace_result #P# #p#
766 # SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
767 # FROM information_schema.innodb_tables A
768 # LEFT JOIN
769 # information_schema.innodb_tablespaces B
770 # ON A.SPACE = B.SPACE
771 # WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
772 #
773 # DROP TABLE t1;
774 # DROP TABLESPACE ts1;
775 # DROP TABLESPACE ts2;
776 #
777 # --echo #
778 # --echo # Bug#22245554 OUTPUT OF SHOW CREATE TABLE INVOLVING PARTITION TABLESPACE
779 # --echo # LEADS TO CONFUSION
780 # --echo #
781 #
782 # CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
783 #
784 # --let $innodb_file_per_table=`select @@innodb_file_per_table`
785 #
786 # --let $TABLESPACE1=
787 # --let $TABLESPACE2=TABLESPACE innodb_system
788 # --let $TABLESPACE3=TABLESPACE innodb_file_per_table
789 # --let $TABLESPACE4=
790 #
791 # --let $INNODB_FILE_PER_TABLE_FOR_CREATE=OFF
792 # --let $INNODB_FILE_PER_TABLE_FOR_ALTER=ON
793 # --echo # CASE A
794 # --echo # file_per_table OFF for CREATE, ON for ALTER
795 # --source suite/innodb/include/create_alter_partition.inc
796 #
797 # --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
798 # --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
799 # --echo # CASE B
800 # --echo # file_per_table ON for CREATE, OFF for ALTER
801 # --source suite/innodb/include/create_alter_partition.inc
802 #
803 # --let $TABLESPACE1=
804 # --let $TABLESPACE2=
805 # --let $TABLESPACE3=
806 # --let $TABLESPACE4=
807 #
808 # --let $INNODB_FILE_PER_TABLE_FOR_CREATE=OFF
809 # --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
810 # --echo # CASE C
811 # --echo # file_per_table OFF for CREATE, OFF for ALTER
812 # --source suite/innodb/include/create_alter_partition.inc
813 #
814 # --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
815 # --let $INNODB_FILE_PER_TABLE_FOR_ALTER=ON
816 # --echo # CASE D
817 # --echo # file_per_table ON for CREATE, ON for ALTER
818 # --source suite/innodb/include/create_alter_partition.inc
819 #
820 # --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
821 # --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
822 # --echo # CASE E
823 # --echo # file_per_table ON for CREATE, OFF for ALTER
824 # --source suite/innodb/include/create_alter_partition.inc
825 #
826 # --eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table
827 #
828 # DROP TABLESPACE ts1;
829
830 --echo # Bug#22444530 - GCOLS + PARTITIONED TABLE, CRASH IN
831 --echo #
832 set sql_mode='';
833 create table t (
834 a int not null,
835 b int generated always as (1) virtual not null,
836 c int generated always as (1) virtual not null,
837 key (c)
838 ) engine=innodb partition by key (a) partitions 2;
839 insert into t(a) values(1);
840 select b from t group by c;
841
842 drop table t;
843
844 # Make column b a BLOB
845 create table t (
846 a int not null,
847 b blob generated always as ("a") virtual not null,
848 c int generated always as (1) virtual not null,
849 key (c)
850 ) engine=innodb partition by key (a) partitions 2;
851 insert into t(a) values(1);
852 select b from t group by c;
853
854 drop table t;
855
856 --echo #
857 --echo # Bug#23037025 COL+PARTITION SIG11 IN ROW_SEL_STORE_MYSQL_REC
858 --echo #
859 set sql_mode='';
860
861 CREATE TABLE t1 (
862 col1 INT,
863 col2 INT AS (col1) VIRTUAL NOT NULL,
864 col3 INT AS (1) VIRTUAL,
865 KEY (col3)
866 ) ENGINE=INNODB PARTITION BY KEY (col1) PARTITIONS 2;
867
868 CREATE TABLE t2 (col1 INTEGER);
869 CREATE TABLE t3 (col1 INTEGER);
870
871 CREATE TRIGGER t2_trig1 BEFORE INSERT ON t2 FOR EACH ROW
872 INSERT INTO t3 SELECT col2 FROM t1 GROUP BY col3;
873
874 --disable_warnings
875 INSERT IGNORE INTO t1(col1) values(NULL);
876 --enable_warnings
877
878 --error ER_BAD_NULL_ERROR
879 INSERT INTO t2 VALUES(1);
880
881 DROP TRIGGER t2_trig1;
882 DROP TABLE t3;
883 DROP TABLE t2;
884 DROP TABLE t1;
885
886 --echo #
887 --echo # Bug#23194259 ASSERTION FAILURE:TRX0TRX.H:1416:SRV_READ_ONLY_MODE
888 --echo #
889
890 CREATE TABLE t1(col1 INT AS(1), KEY(col1))
891 ENGINE=INNODB
892 PARTITION BY KEY(col1) PARTITIONS 2;
893
894 --disable_warnings
895 SELECT * FROM t1 WHERE sha(uuid_short());
896 --enable_warnings
897
898 DROP TABLE t1;
899
900 # TODO : Enable following once shared tablespace are allowed in Partitioned
901 # Table (wl#12034).
902 # --echo #
903 # --echo # Bug#25516367 - CRASH IN HA_INNOBASE::COMMIT_INPLACE_ALTER_TABLE_IMPL
904 # --echo #
905 #
906 # CREATE TABLESPACE s1 ADD DATAFILE '1.ibd' ENGINE = InnoDB;
907 # CREATE TABLE t1 (col1 INT, col2 INT) ENGINE = InnoDB ROW_FORMAT = DYNAMIC TABLESPACE s1;
908 #
909 # ALTER TABLE t1 PARTITION BY RANGE(col1)
910 # ( PARTITION p0 VALUES LESS THAN (128), PARTITION p3 VALUES LESS THAN MAXVALUE );
911 # --error ER_ILLEGAL_HA_CREATE_OPTION
912 # ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
913 #
914 # DROP TABLE t1;
915 # DROP TABLESPACE s1;
916 #
917 # --echo #
918 # --echo # This checks if operation bit is set properly for REORGANIZE PARTITION
919 # --echo #
920 #
921 # CREATE TABLE t1 (a INTEGER, b INTEGER NOT NULL, KEY(b)) ENGINE = INNODB /*!50100 PARTITION BY KEY(b) PARTITIONS 3 */;
922 # SHOW CREATE TABLE t1;
923 # ALTER TABLE t1 REORGANIZE PARTITION;
924 # SHOW CREATE TABLE t1;
925 # DROP TABLE t1;
926 #
927 #
928 # --echo # Ignore incompatible options of a table which was created in not strict mode
929 #
930 # SET @orig_innodb_file_per_table= @@innodb_file_per_table;
931 # SET GLOBAL innodb_file_per_table = 0;
932 # SET @save_innodb_strict_mode=@@session.innodb_strict_mode;
933 # SET SESSION innodb_strict_mode = 0;
934 #
935 # CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, KEY(a), KEY(d)) ENGINE = InnoDB PARTITION BY HASH(d) PARTITIONS 02;
936 #
937 # SET SESSION innodb_strict_mode = 0;
938 #
939 # # To prevent the compression warning differences in different platform
940 # --disable_warnings
941 #
942 # ALTER TABLE t1 COMPRESSION='zlib', ALGORITHM=INPLACE;
943 #
944 # ALTER TABLE t1 TABLESPACE innodb_system, ALGORITHM=INPLACE, LOCK=DEFAULT;
945 #
946 # OPTIMIZE TABLE t1;
947 #
948 # --enable_warnings
949 #
950 # SHOW CREATE TABLE t1;
951 #
952 # SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
953 # SET SESSION innodb_strict_mode=@save_innodb_strict_mode;
954 #
955 # --source include/restart_mysqld.inc
956 #
957 # CHECK TABLE t1;
958 #
959 # INSERT INTO t1 VALUES(0, 1), (2, 3), (5, 6), (10, 11);
960 #
961 # SELECT * FROM t1 ORDER BY a;
962 #
963 # DROP TABLE t1;
964 #
965 #
966 # --echo # DATA DIRECTORY should be read correctly after restart
967 #
968 # --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
969 # eval CREATE TABLE t1 (a INT, b INT) PARTITION BY RANGE(a)
970 # (PARTITION P0 VALUES LESS THAN (10) DATA DIRECTORY '$MYSQL_TMP_DIR',
971 # PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY '$MYSQL_TMP_DIR');
972 #
973 # --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
974 # SHOW CREATE TABLE t1;
975 #
976 # --source include/restart_mysqld.inc
977 #
978 # TRUNCATE TABLE t1;
979 #
980 # --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
981 # SHOW CREATE TABLE t1;
982 #
983 # DROP TABLE t1;
984 # # Remove directory left behind by the above
985 # --rmdir $MYSQL_TMP_DIR/test