Debian Codegraph @

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

raw
1 # Different partitions can have different row format
2 # Case one: between dynamic and redundant
3 #
4 SET GLOBAL innodb_default_row_format = REDUNDANT;
5 CREATE TABLE t1 (a INT NOT NULL, d INT NOT NULL, index(a, d)) ENGINE=InnoDB PARTITION BY LIST COLUMNS (a,d) (PARTITION p36_1 VALUES IN ((10,5),(10,6)), PARTITION p36_2 VALUES IN ((11,5),(11,6)));
6 SET SESSION debug= '+d,skip_dd_table_access_check';
7 SELECT name, se_private_data FROM mysql.table_partitions;
8 name se_private_data
9 p36_1 NULL
10 p36_2 NULL
11 # restart
12 ALTER TABLE t1 ADD PARTITION (PARTITION p_r13 VALUES IN ((741,377), (21,728)), PARTITION p_r23 VALUES IN ((102,337), (453,337), (-9,179)));
13 SET SESSION debug= '+d,skip_dd_table_access_check';
14 SELECT name, se_private_data FROM mysql.table_partitions;
15 name se_private_data
16 p36_1 NULL
17 p36_2 NULL
18 p_r13 format=2;
19 p_r23 format=2;
20 CHECK TABLE t1;
21 Table Op Msg_type Msg_text
22 test.t1 check status OK
23 SHOW CREATE TABLE t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` int NOT NULL,
27 `d` int NOT NULL,
28 KEY `a` (`a`,`d`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 /*!50500 PARTITION BY LIST COLUMNS(a,d)
31 (PARTITION p36_1 VALUES IN ((10,5),(10,6)) ENGINE = InnoDB,
32 PARTITION p36_2 VALUES IN ((11,5),(11,6)) ENGINE = InnoDB,
33 PARTITION p_r13 VALUES IN ((741,377),(21,728)) ENGINE = InnoDB,
34 PARTITION p_r23 VALUES IN ((102,337),(453,337),(-9,179)) ENGINE = InnoDB) */
35 # restart
36 SET SESSION debug= '+d,skip_dd_table_access_check';
37 SELECT name, se_private_data FROM mysql.table_partitions;
38 name se_private_data
39 p36_1 NULL
40 p36_2 NULL
41 p_r13 format=2;
42 p_r23 format=2;
43 CHECK TABLE t1;
44 Table Op Msg_type Msg_text
45 test.t1 check status OK
46 DROP TABLE t1;
47 SET GLOBAL innodb_default_row_format = Default;
48 #
49 # Different partitions can have different row format
50 # Case two: between dynamic and compressed
51 #
52 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));
53 SHOW CREATE TABLE t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `a` int DEFAULT NULL,
57 `b` int DEFAULT NULL,
58 KEY `a` (`a`)
59 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
60 /*!50100 PARTITION BY RANGE ((`a` % 7))
61 (PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
62 PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
63 PARTITION p3 VALUES LESS THAN (5) ENGINE = InnoDB,
64 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
65 SET @orig_innodb_file_per_table= @@innodb_file_per_table;
66 SET GLOBAL innodb_file_per_table = 0;
67 SET @save_innodb_strict_mode=@@session.innodb_strict_mode;
68 SET SESSION innodb_strict_mode = 0;
69 SET SESSION debug= '+d,skip_dd_table_access_check';
70 SELECT name, se_private_data FROM mysql.table_partitions;
71 name se_private_data
72 p1 NULL
73 p2 NULL
74 p3 NULL
75 p4 NULL
76 ALTER TABLE t1 REORGANIZE PARTITION p1, p2 INTO (PARTITION p1 VALUES LESS THAN(2));
77 Warnings:
78 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
79 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2.
80 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
81 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
82 SELECT name, se_private_data FROM mysql.table_partitions;
83 name se_private_data
84 p1 format=2;
85 p3 NULL
86 p4 NULL
87 SHOW CREATE TABLE t1;
88 Table Create Table
89 t1 CREATE TABLE `t1` (
90 `a` int DEFAULT NULL,
91 `b` int DEFAULT NULL,
92 KEY `a` (`a`)
93 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
94 /*!50100 PARTITION BY RANGE ((`a` % 7))
95 (PARTITION p1 VALUES LESS THAN (2) TABLESPACE = `innodb_system` ENGINE = InnoDB,
96 PARTITION p3 VALUES LESS THAN (5) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
97 PARTITION p4 VALUES LESS THAN MAXVALUE TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
98 SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
99 SET SESSION innodb_strict_mode=@save_innodb_strict_mode;
100 # restart
101 SET SESSION debug= '+d,skip_dd_table_access_check';
102 SELECT name, se_private_data FROM mysql.table_partitions;
103 name se_private_data
104 p1 format=2;
105 p3 NULL
106 p4 NULL
107 CHECK TABLE t1;
108 Table Op Msg_type Msg_text
109 test.t1 check status OK
110 SHOW CREATE TABLE t1;
111 Table Create Table
112 t1 CREATE TABLE `t1` (
113 `a` int DEFAULT NULL,
114 `b` int DEFAULT NULL,
115 KEY `a` (`a`)
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
117 /*!50100 PARTITION BY RANGE ((`a` % 7))
118 (PARTITION p1 VALUES LESS THAN (2) TABLESPACE = `innodb_system` ENGINE = InnoDB,
119 PARTITION p3 VALUES LESS THAN (5) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
120 PARTITION p4 VALUES LESS THAN MAXVALUE TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
121 DROP TABLE t1;