Debian Codegraph @

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

raw
1 # Permissions don't work with embedded
2 --source include/not_embedded.inc
3
4 #
5 # General bugs with ALTER TABLE and partitions that doesn't have to be run
6 # on all engines
7 #
8
9 --source include/have_partition.inc
10 --source include/lcase_names.inc
11 --source suite/parts/inc/engines.inc
12
13 --echo #
14 --echo # MDEV-22649 SIGSEGV in ha_partition::create_partitioning_metadata on ALTER
15 --echo #
16
17 CREATE TABLE t1(a INT) engine=myisam PARTITION BY RANGE(a) SUBPARTITION BY KEY(a) (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION s0,SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (20) (SUBPARTITION s2,SUBPARTITION s3));
18 show create table t1;
19 --error ER_ALTER_OPERATION_NOT_SUPPORTED
20 ALTER TABLE t1 ADD COLUMN c INT,ALGORITHM=INSTANT;
21 DROP table if exists t1;
22
23 --echo #
24 --echo # MDEV-22804 SIGSEGV in ha_partition::create_partitioning_metadata |
25 --echo # ERROR 1507 (HY000): Error in list of partitions to DROP
26 --echo #
27
28 CREATE TABLE t1 (a INT) PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (PARTITION p VALUES LESS THAN (5) (SUBPARTITION sp, SUBPARTITION sp1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION sp2, SUBPARTITION sp3));
29 ALTER TABLE t1 DROP PARTITION p;
30 DROP TABLE if exists t1;
31
32 --echo #
33 --echo # MDEV-23357 Server crashes in Sql_cmd_alter_table_exchange_partition::exchange_partition
34 --echo #
35 CREATE TABLE t1 (i INT);
36 CREATE VIEW v1 as SELECT * FROM t1;
37 CREATE TABLE t2 (i INT);
38 --error ER_CHECK_NO_SUCH_TABLE
39 ALTER TABLE v1 EXCHANGE PARTITION p2 WITH TABLE t2 ;
40 DROP VIEW v1;
41 DROP TABLE t1, t2;
42
43 --echo #
44 --echo # MDEV-34813 ALGORITHM=INSTANT does not work for partitioned tables on indexed column
45 --echo #
46
47 --source include/have_innodb.inc
48 CREATE TABLE `t1` (
49 `f1` datetime ,
50 `f2` VARCHAR(2) ,
51 `f3` VARCHAR(200) ,
52 `f4` VARCHAR(100) ,
53 INDEX `i3` (`f4`) )
54 PARTITION BY RANGE COLUMNS(`f2`)
55 (PARTITION `p_01` VALUES LESS THAN ('02') ENGINE = InnoDB,
56 PARTITION `p_31` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
57
58 ALTER online TABLE t1 MODIFY COLUMN `f4` VARCHAR(500) , ALGORITHM=INSTANT, LOCK=NONE;
59
60 drop table t1;
61
62 --echo # End of 10.5 tests
63
64 --echo #
65 --echo # MDEV-34951: InnoDB index corruption when renaming key name with same letter to upper case.
66 --echo #
67
68 --echo # Tests on partitioned tables
69 --echo # partitioned table with index 'idx' (lowercase)
70 CREATE TABLE t_part (a INT, KEY idx(a)) ENGINE=InnoDB
71 PARTITION BY HASH(a) PARTITIONS 1;
72
73 --echo # non-partitioned table with index 'IDX' (uppercase)
74 CREATE TABLE t_nonpart (a INT, KEY IDX(a)) ENGINE=InnoDB;
75
76 INSERT INTO t_part VALUES (1);
77
78 --echo # Exchange partition
79 --echo # ALTER TABLE should fail. Without fix, ALTER TABLE succeeds, SELECT fails.
80 --error ER_TABLES_DIFFERENT_METADATA
81 ALTER TABLE t_part EXCHANGE PARTITION p0 WITH TABLE t_nonpart;
82
83 SELECT * FROM t_nonpart;
84 DROP TABLE t_part, t_nonpart;
85
86 --echo # End of 10.6 tests
87
88 --echo #
89 --echo # MDEV-22165 CONVERT PARTITION: move in partition from existing table
90 --echo #
91 create or replace table tp1 (a int);
92 create or replace table t1 (a int)
93 partition by hash (a) partitions 2;
94 --error ER_ONLY_ON_RANGE_LIST_PARTITION
95 alter table t1 convert table tp1 to partition p2;
96
97 create or replace table t1 (a int)
98 partition by range (a)
99 (partition p0 values less than (0));
100
101 --error ER_NO_SUCH_TABLE
102 alter table t1 convert table non_existent to partition p1 values less than (10);
103 alter table t1 convert table tp1 to partition p1 values less than (10);
104 --error ER_NO_SUCH_TABLE
105 show create table tp1;
106 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
107 show create table t1;
108
109 create table tp2 (x int);
110 --error ER_TABLES_DIFFERENT_METADATA
111 alter table t1 convert table tp2 to partition p2 values less than (20);
112 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
113 show create table tp2;
114 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
115 show create table t1;
116
117 create or replace table tp2 (a int);
118 insert tp2 values (1), (15), (17);
119 --error ER_ROW_DOES_NOT_MATCH_PARTITION
120 alter table t1 convert table tp2 to partition p2 values less than (20);
121 delete from tp2;
122 insert tp2 values (15), (1), (17);
123 --error ER_ROW_DOES_NOT_MATCH_PARTITION
124 alter table t1 convert table tp2 to partition p2 values less than (20);
125 delete from tp2;
126 insert tp2 values (15), (17), (1);
127 --error ER_ROW_DOES_NOT_MATCH_PARTITION
128 alter table t1 convert table tp2 to partition p2 values less than (20);
129 delete from tp2;
130 insert tp2 values (15), (17);
131 alter table t1 convert table tp2 to partition p2 values less than (20);
132 --error ER_NO_SUCH_TABLE
133 show create table tp2;
134 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
135 show create table t1;
136 select * from t1 partition (p2);
137
138 create or replace table t1 (a int)
139 partition by range (a) (
140 p0 values less than (0),
141 pn values less than (30));
142 insert into t1 values (1);
143 create or replace table tp1 (a int);
144 insert into tp1 values (2);
145 # TODO: would be good to automatically detect order of partitions,
146 # as well as move the data from succeeding partitions (ADD PARTITION FR).
147 --error ER_RANGE_NOT_INCREASING_ERROR
148 alter table t1 convert table tp1 to partition p1 values less than (10);
149
150 drop tables t1, tp1;
151
152
153 --echo #
154 --echo # MDEV-22166 CONVERT PARTITION: move out partition into a table
155 --echo #
156
157 create or replace table t1 (x int);
158 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
159 alter table t1 convert partition p1 to table tp1;
160
161 create or replace table t1 (x int)
162 partition by hash(x) partitions 2;
163 --error ER_ONLY_ON_RANGE_LIST_PARTITION
164 alter table t1 convert partition p1 to table tp1;
165
166 create or replace table t1 (x int)
167 partition by key(x) partitions 2;
168 --error ER_ONLY_ON_RANGE_LIST_PARTITION
169 alter table t1 convert partition p1 to table tp1;
170
171 create or replace table t1 (x int)
172 partition by range(x)
173 subpartition by hash(x) subpartitions 3 (
174 partition p1 values less than (10),
175 partition pn values less than maxvalue);
176 --error ER_PARTITION_CONVERT_SUBPARTITIONED
177 alter table t1 convert partition p1 to table p1;
178 --error ER_PARTITION_DOES_NOT_EXIST
179 alter table t1 convert partition p1sp0 to table p1;
180
181 create or replace table t1 (x int)
182 partition by range(x) (
183 partition p1 values less than (10));
184 --error ER_DROP_LAST_PARTITION
185 alter table t1 convert partition p1 to table tp1;
186
187 --error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
188 create or replace temporary table t1 (x int)
189 partition by range(x) (
190 partition p0 values less than (10),
191 partition pn values less than maxvalue);
192
193 if ($MTR_COMBINATION_RANGE)
194 {
195 create or replace table t1 (x int)
196 partition by range(x) (
197 partition p1 values less than (10),
198 partition p2 values less than (20),
199 partition p3 values less than (30),
200 partition p4 values less than (40),
201 partition p5 values less than (50),
202 partition pn values less than maxvalue);
203 }
204
205 if ($MTR_COMBINATION_LIST)
206 {
207 create or replace table t1 (x int)
208 partition by list(x) (
209 partition p1 values in (2, 3, 4),
210 partition p2 values in (12, 13, 14),
211 partition p3 values in (22, 23, 24),
212 partition p4 values in (32, 33, 34),
213 partition p5 values in (42, 43, 44),
214 partition pn values in (52, 53, 54));
215 }
216
217 insert into t1 values (2), (12), (22), (32), (42), (52);
218
219 create or replace table tp2 (y int);
220 insert tp2 values (88);
221 # Multiple ALTER PARTITION statements are not possible
222 --error ER_PARSE_ERROR
223 alter table t1 convert partition p2 to table tp2, drop partition p3;
224 # TODO: probably no need in such specific codes, should be ER_PARTITION_NON_EXISTENT
225 --error ER_PARTITION_DOES_NOT_EXIST
226 alter table t1 convert partition p00 to table tp00;
227 # Better error here is ER_PARTITION_DOES_NOT_EXIST,
228 # but mysql_alter_table() works checks new table before anything else.
229 # So, looks like no big reason to change anything here.
230 --error ER_TABLE_EXISTS_ERROR
231 alter table t1 convert partition p00 to table tp2;
232 --error ER_TABLE_EXISTS_ERROR
233 alter table t1 convert partition p2 to table tp2;
234 --delimiter $
235 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
236 create trigger tr1 before update on t1 for each row
237 begin
238 alter table t1 convert partition p2 to table tp2;
239 end$
240 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
241 create function f1() returns int
242 begin
243 alter table t1 convert partition p2 to table tp2;
244 end$
245 --delimiter ;
246 select * from tp2;
247 drop table tp2;
248 alter table t1 convert partition p2 to table tp2;
249
250 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
251 show create table tp2;
252 select * from tp2;
253 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
254 show create table t1;
255 select * from t1 order by x;
256
257 --error ER_BAD_DB_ERROR
258 alter table t1 convert partition p3 to table inexistent.tp3;
259 create database EXISTENT;
260 alter table t1 convert partition p3 to table EXISTENT.TP3;
261
262 # The only way to put `` into var...
263 --let $tp3=`select '`TP3`'`
264 if ($MTR_COMBINATION_LCASE1)
265 {
266 --let $tp3= `select '`tp3`'`
267 }
268 --replace_result $engine X ' PAGE_CHECKSUM=1' '' $tp3 `TP3`-ok
269 show create table EXISTENT.TP3;
270 select * from EXISTENT.TP3 order by x;
271 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
272 show create table t1;
273 select * from t1 order by x;
274
275 --echo # LOCK TABLES
276 lock tables t1 write;
277 alter table t1 convert partition p4 to table tp4;
278 # TODO: lock table tp4 in ALTER TABLE, otherwise there is no
279 # guarantee in data consistency between t1 and tp4
280 --error ER_TABLE_NOT_LOCKED
281 show create table tp4;
282
283 unlock tables;
284 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
285 show create table tp4;
286 select * from tp4;
287 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
288 show create table t1;
289 select * from t1 order by x;
290
291 --echo # PS
292 prepare stmt from 'alter table t1 convert partition p5 to table tp5';
293 execute stmt;
294 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
295 show create table tp5;
296 select * from tp5;
297 --replace_result $engine X ' PAGE_CHECKSUM=1' ''
298 show create table t1;
299 select * from t1 order by x;
300 drop table tp5;
301 --error ER_PARTITION_DOES_NOT_EXIST
302 execute stmt;
303 --error ER_PARTITION_DOES_NOT_EXIST
304 execute stmt;
305 drop prepare stmt;
306
307 --echo # Privileges
308 create user alan;
309 grant usage on *.* to alan;
310 grant create, insert on test.* to alan;
311 --connect alan,localhost,alan,,test
312 show grants for current_user;
313 --error ER_TABLEACCESS_DENIED_ERROR
314 alter table t1 convert partition p1 to table tp1;
315 --connection default
316 revoke all on test.* from alan;
317 grant create, insert, alter on test.* to alan;
318 --connection alan
319 use test;
320 show grants for current_user;
321 --error ER_TABLEACCESS_DENIED_ERROR
322 alter table t1 convert partition p1 to table tp1;
323 --connection default
324 revoke all on test.* from alan;
325 grant create, drop, alter on test.* to alan;
326 --connection alan
327 use test;
328 show grants for current_user;
329 --error ER_TABLEACCESS_DENIED_ERROR
330 alter table t1 convert partition p1 to table tp1;
331 --connection default
332 revoke all on test.* from alan;
333 grant insert, drop, alter on test.* to alan;
334 --connection alan
335 use test;
336 show grants for current_user;
337 --error ER_TABLEACCESS_DENIED_ERROR
338 alter table t1 convert partition p1 to table tp1;
339 --connection default
340 grant create, insert, drop, alter on test.* to alan;
341 --connection alan
342 use test;
343 show grants for current_user;
344 alter table t1 convert partition p1 to table tp1;
345 --disconnect alan
346 --connection default
347
348 drop database EXISTENT;
349 drop user alan;
350 drop tables t1, tp1, tp2, tp4;
351
352 --echo #
353 --echo # MDEV-31014 Database privileges are insufficient for CONVERT TABLE TO PARTITION
354 --echo #
355 create database db;
356 create user u@localhost;
357 grant all on db.* to u@localhost;
358
359 --connect (con1,localhost,u,,db)
360 create table t1 (a int) partition by range(a) (p1 values less than (100), p2 values less than (1000));
361 alter table t1 convert partition p2 to table tp;
362 alter table t1 convert table tp to partition p2 values less than (1000);
363
364 # Cleanup
365 --disconnect con1
366 --connection default
367 drop user u@localhost;
368 drop database db;
369
370 --echo #
371 --echo # MDEV-37328 Assertion failure in make_empty_rec upon CONVERT PARTITION
372 --echo #
373 create table t (f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int) engine=myisam
374 partition by list (f3) (
375 partition p0 values in (null,0),
376 partition p1 values in (1,2,3),
377 partition p2 default
378 );
379 alter table t convert partition p0 to table tp;
380 drop table if exists tp, t;
381
382 --echo # End of 10.11 tests
383
384 --echo #
385 --echo # MDEV-22164 without validation for exchange partition/convert in
386 --echo #
387 call mtr.add_suppression('was altered WITHOUT');
388 let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
389 let SEARCH_PATTERN=Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted;
390
391 create table validation(x int);
392 drop table validation;
393
394 create table t (a int primary key);
395
396 create table tp (a int primary key) partition by range (a) (
397 partition p0 values less than (100),
398 p1 values less than (300));
399
400 insert into t values (1), (99);
401 insert into tp values (2), (200);
402
403 select * from t order by a;
404 select * from tp partition (p0) order by a;
405 select * from tp partition (p1) order by a;
406
407 alter table tp exchange partition p0 with table t;
408 select * from t order by a;
409 select * from tp partition (p0) order by a;
410 select * from tp partition (p1) order by a;
411
412 alter table tp exchange partition p0 with table t;
413 select * from t order by a;
414 select * from tp partition (p0) order by a;
415 select * from tp partition (p1) order by a;
416
417 --error ER_ROW_DOES_NOT_MATCH_PARTITION
418 alter table tp exchange partition p1 with table t;
419 --error ER_ROW_DOES_NOT_MATCH_PARTITION
420 alter table tp exchange partition p1 with table t with validation;
421 alter table tp exchange partition p1 with table t without validation;
422 source include/search_pattern_in_file.inc;
423 select * from t order by a;
424 select * from tp partition (p1) order by a;
425 alter table tp check partition p0;
426 alter table tp check partition p1;
427
428 alter table tp exchange partition p1 with table t with validation;
429 --error ER_ROW_DOES_NOT_MATCH_PARTITION
430 alter table tp exchange partition p1 with table t with validation;
431 select * from t order by a;
432 select * from tp partition (p1) order by a;
433
434 # SP
435 create or replace procedure validation()
436 alter table tp exchange partition p1 with table t with validation;
437 create or replace procedure without_validation()
438 alter table tp exchange partition p1 with table t without validation;
439 --error ER_ROW_DOES_NOT_MATCH_PARTITION
440 call validation;
441 call without_validation;
442 source include/search_pattern_in_file.inc;
443 call validation;
444 --error ER_ROW_DOES_NOT_MATCH_PARTITION
445 call validation;
446 call without_validation;
447 source include/search_pattern_in_file.inc;
448 select * from t order by a;
449 select * from tp partition (p1) order by a;
450 call validation;
451 select * from t order by a;
452 select * from tp partition (p1) order by a;
453 drop procedure validation;
454 drop procedure without_validation;
455
456 # PS
457 prepare validation from "alter table tp exchange partition p1 with table t with validation";
458 prepare without_validation from "alter table tp exchange partition p1 with table t without validation";
459 --error ER_ROW_DOES_NOT_MATCH_PARTITION
460 execute validation;
461 execute without_validation;
462 source include/search_pattern_in_file.inc;
463 execute validation;
464 --error ER_ROW_DOES_NOT_MATCH_PARTITION
465 execute validation;
466 execute without_validation;
467 source include/search_pattern_in_file.inc;
468 select * from t order by a;
469 select * from tp partition (p1) order by a;
470 execute validation;
471 select * from t order by a;
472 select * from tp partition (p1) order by a;
473 drop prepare validation;
474 drop prepare without_validation;
475
476 # CONVERT IN
477 --error ER_ROW_DOES_NOT_MATCH_PARTITION
478 alter table tp convert table t to partition p2 values less than (maxvalue);
479 --error ER_ROW_DOES_NOT_MATCH_PARTITION
480 alter table tp convert table t to partition p2 values less than (maxvalue) with validation;
481 alter table tp convert table t to partition p2 values less than (maxvalue) without validation;
482 source include/search_pattern_in_file.inc;
483 select * from tp partition (p0) order by a;
484 select * from tp partition (p1) order by a;
485 select * from tp partition (p2) order by a;
486
487 # Hash
488 create table t (a int primary key);
489 insert t values (1), (2);
490
491 create or replace table tp (a int primary key)
492 partition by hash(a) partitions 2;
493
494 insert tp values (1), (2), (3), (4);
495 --error ER_ROW_DOES_NOT_MATCH_PARTITION
496 alter table tp exchange partition p0 with table t;
497 alter table tp exchange partition p0 with table t without validation;
498 source include/search_pattern_in_file.inc;
499 select * from t;
500 alter table tp exchange partition p0 with table t;
501 drop table tp;
502
503 # List
504 create or replace table tp (a int primary key)
505 partition by list(a) (
506 partition p0 values in (2, 3, 4),
507 partition p1 values in (12, 13, 14),
508 partition p2 values in (52, 53, 54));
509
510
511 insert tp values (12), (2), (3), (4);
512 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
513 alter table tp exchange partition p0 with table t;
514 alter table tp exchange partition p0 with table t without validation;
515 source include/search_pattern_in_file.inc;
516 select * from t;
517 alter table tp exchange partition p0 with table t;
518
519 drop tables tp, t;
520
521 --echo # End of 11.4 tests