Debian Codegraph @

mysql-test/suite/parts/t/engine_defined_part_attributes.test

raw
1 --echo #
2 --echo # MDEV-5271 Support engine-defined attributes per partition
3 --echo #
4
5 --source include/have_partition.inc
6 --source include/have_innodb.inc
7
8 --echo # partitioned tables
9
10 CREATE TABLE `t1` (
11 `id` INT
12 ) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) (
13 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1,
14 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1
15 );
16 SHOW CREATE TABLE `t1`;
17
18 INSERT INTO t1 VALUES (1), (2), (3);
19 DELETE FROM t1 WHERE id = 1;
20 UPDATE t1 SET id = 4 WHERE id = 3;
21 SELECT * FROM t1 WHERE id IN (2, 3);
22
23 DROP TABLE `t1`;
24
25 --error ER_CANT_CREATE_TABLE
26 CREATE TABLE `t2` (
27 `id` INT
28 ) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) (
29 PARTITION pt1 VALUES LESS THAN (100),
30 PARTITION pt2 VALUES LESS THAN MAXVALUE
31 );
32
33 --error ER_CANT_CREATE_TABLE
34 CREATE TABLE `t3` (
35 `id` INT
36 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) (
37 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
38 PARTITION pt2 VALUES LESS THAN MAXVALUE
39 );
40
41 CREATE TABLE `t4` (
42 `id` INT
43 ) ENGINE=InnoDB ENCRYPTED="NO";
44 SHOW CREATE TABLE `t4`;
45
46 ALTER TABLE `t4` PARTITION BY RANGE(id) (
47 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO",
48 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
49 );
50 SHOW CREATE TABLE `t4`;
51
52 ALTER TABLE `t4` PARTITION BY RANGE(id) (
53 PARTITION pt1 VALUES LESS THAN (100),
54 PARTITION pt2 VALUES LESS THAN MAXVALUE
55 );
56 SHOW CREATE TABLE `t4`;
57
58 --error ER_CANT_CREATE_TABLE
59 ALTER TABLE `t4` PARTITION BY RANGE(id) (
60 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
61 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
62 );
63
64 DROP TABLE `t4`;
65
66 --echo # subpartitioned tables
67
68 CREATE TABLE `t5` (
69 `id` INT
70 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
71 SUBPARTITION BY HASH(id)
72 SUBPARTITIONS 2 (
73 PARTITION pt1 VALUES LESS THAN (100),
74 PARTITION pt2 VALUES LESS THAN MAXVALUE
75 );
76 SHOW CREATE TABLE `t5`;
77
78 DROP TABLE `t5`;
79
80 --error ER_CANT_CREATE_TABLE
81 CREATE TABLE `t6` (
82 `id` INT
83 ) ENGINE=InnoDB PARTITION BY RANGE(id)
84 SUBPARTITION BY HASH(id)
85 SUBPARTITIONS 2 (
86 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES",
87 PARTITION pt2 VALUES LESS THAN MAXVALUE
88 );
89
90 --error ER_PARSE_ERROR
91 CREATE TABLE `t7` (
92 id INT
93 ) ENGINE=InnoDB PARTITION BY RANGE(id)
94 SUBPARTITION BY HASH(id) (
95 PARTITION pt1 VALUES LESS THAN (100)(
96 SUBPARTITION spt1 ENCRYPTED="NO",
97 SUBPARTITION spt2
98 ),
99 PARTITION pt2 VALUES LESS THAN MAXVALUE (
100 SUBPARTITION spt3,
101 SUBPARTITION spt4
102 )
103 );
104
105 CREATE TABLE `t8` (
106 id INT
107 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
108 SUBPARTITION BY HASH(id) (
109 PARTITION pt1 VALUES LESS THAN (100) (
110 SUBPARTITION spt1,
111 SUBPARTITION spt2
112 ),
113 PARTITION pt2 VALUES LESS THAN MAXVALUE (
114 SUBPARTITION spt3,
115 SUBPARTITION spt4
116 )
117 );
118 SHOW CREATE TABLE `t8`;
119
120 DROP TABLE `t8`;
121
122 CREATE TABLE `t9` (
123 id INT
124 ) ENGINE=InnoDB PARTITION BY RANGE(id)
125 SUBPARTITION BY HASH(id) (
126 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
127 SUBPARTITION spt1,
128 SUBPARTITION spt2
129 ),
130 PARTITION pt2 VALUES LESS THAN MAXVALUE (
131 SUBPARTITION spt3,
132 SUBPARTITION spt4
133 )
134 );
135 SHOW CREATE TABLE `t9`;
136
137 DROP TABLE `t9`;
138
139 --error ER_CANT_CREATE_TABLE
140 CREATE TABLE `t10` (
141 id INT
142 ) ENGINE=InnoDB PARTITION BY RANGE(id)
143 SUBPARTITION BY HASH(id) (
144 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" (
145 SUBPARTITION spt1,
146 SUBPARTITION spt2
147 ),
148 PARTITION pt2 VALUES LESS THAN MAXVALUE (
149 SUBPARTITION spt3,
150 SUBPARTITION spt4
151 )
152 );
153
154 CREATE TABLE `t11` (
155 id INT
156 ) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id)
157 SUBPARTITION BY HASH(id) (
158 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
159 SUBPARTITION spt1,
160 SUBPARTITION spt2
161 ),
162 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" (
163 SUBPARTITION spt3,
164 SUBPARTITION spt4
165 )
166 );
167 SHOW CREATE TABLE `t11`;
168
169 DROP TABLE `t11`;
170
171 --echo #
172 --echo # MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values
173 --echo #
174
175 --let $restart_parameters= --innodb-sys-tablespaces
176 --source include/restart_mysqld.inc
177
178 CREATE TABLE `t12` (
179 id INT
180 ) ENGINE=InnoDB PARTITION BY HASH(id)
181 (
182 pt1 PAGE_COMPRESSED=0
183 );
184 --sorted_result
185 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
186
187 ALTER TABLE `t12` ADD PARTITION (
188 PARTITION pt2 PAGE_COMPRESSED=1
189 );
190 --sorted_result
191 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
192
193 ALTER TABLE `t12` ADD PARTITION (
194 PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
195 );
196 --sorted_result
197 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
198
199 DROP TABLE `t12`;
200
201 CREATE TABLE `t13` (
202 `id` INT
203 ) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) (
204 PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0,
205 PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
206 PARTITION pt3 VALUES LESS THAN MAXVALUE
207 );
208 SHOW CREATE TABLE `t13`;
209 --sorted_result
210 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
211
212 ALTER TABLE `t13` PARTITION BY RANGE(id) (
213 PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
214 PARTITION pt2 VALUES LESS THAN (200),
215 PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0
216 );
217 SHOW CREATE TABLE `t13`;
218 --sorted_result
219 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
220
221 DROP TABLE `t13`;