1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
# Check if non-strict mode can work when there is implicit row format change
CREATE TABLE t1 (a INT, b INT, key(a)) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 PARTITION BY RANGE (a % 7) (PARTITION p1 VALUES LESS THAN(1), PARTITION p2 VALUES LESS THAN (2), PARTITION p3 VALUES LESS THAN (5), PARTITION p4 VALUES LESS THAN(MAXVALUE));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
/*!50100 PARTITION BY RANGE ((`a` % 7))
(PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SET @orig_innodb_file_per_table= @@innodb_file_per_table;
SET GLOBAL innodb_file_per_table = 0;
SET @save_innodb_strict_mode=@@session.innodb_strict_mode;
SET SESSION innodb_strict_mode = 0;
ALTER TABLE t1 REORGANIZE PARTITION p1, p2 INTO (PARTITION p1 VALUES LESS THAN(2));
Warnings:
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2.
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
/*!50100 PARTITION BY RANGE ((`a` % 7))
(PARTITION p1 VALUES LESS THAN (2) TABLESPACE = `innodb_system` ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (5) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN MAXVALUE TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
DROP TABLE t1;
SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
SET SESSION innodb_strict_mode=@save_innodb_strict_mode;
#
# This checks after ALTER TABLE ... COPY which changes the row format,
# it should still work
#
SET @orig_innodb_file_per_table= @@innodb_file_per_table;
SET @save_innodb_strict_mode=@@session.innodb_strict_mode;
CREATE TABLE t1 (a INT NOT NULL, c VARCHAR(186), INDEX(c(99))) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 16 PARTITION BY LINEAR KEY(c) PARTITIONS 3;
Warnings:
Warning 1681 Column 'test.t1.c' having prefix key part 'c(99)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release.
SET GLOBAL innodb_file_per_table = 0;
SET SESSION innodb_strict_mode = 0;
ALTER TABLE t1 PARTITION BY LINEAR KEY(c) PARTITIONS 3;
Warnings:
Warning 1681 Column 'test.t1.c' having prefix key part 'c(99)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release.
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16.
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16.
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16.
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16.
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
SET SESSION innodb_strict_mode=@save_innodb_strict_mode;
# restart
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int NOT NULL,
`c` varchar(186) DEFAULT NULL,
KEY `c` (`c`(99))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16
/*!50100 PARTITION BY LINEAR KEY (c)
PARTITIONS 3 */
SELECT * FROM t1;
a c
DROP TABLE t1;
|
