diff options
author | Nayuta Yanagisawa <nayuta.yanagisawa@hey.com> | 2021-11-05 16:39:28 +0900 |
---|---|---|
committer | Nayuta Yanagisawa <nayuta.yanagisawa@hey.com> | 2022-01-24 19:26:09 +0900 |
commit | c5d09f731ac1e7ddd18c019cf0b9cf4716c3aa3d (patch) | |
tree | 6619acb8c028e6d305ccfcb5836baf99423b1bd1 /mysql-test | |
parent | 83dd7db69d73e931d1bdb4295524c4953ba38165 (diff) | |
download | mariadb-git-c5d09f731ac1e7ddd18c019cf0b9cf4716c3aa3d.tar.gz |
MDEV-5271 Support engine-defined attributes per partition
Make it possible to specify engine-defined attributes on partitions
as well as tables.
If an engine-defined attribute is only specified at the table level,
it applies to all the partitions in the table.
This is a backward-compatible behavior.
If the same attribute is specified both at the table level and the
partition level, the per-partition one takes precedence.
So, we can consider per-table attributes as default values.
One cannot specify engine-defined attributes on subpartitions.
Implementation details:
* We store per-partition attributes in the partition_element class
because we already have the part_comment field, which is for
per-partition comments.
* In the case of ALTER TABLE statements, the partition_elements in
table->part_info is set up by mysql_unpack_partition().
So, we parse per-partition attributes after the call of the function.
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/main/partition_error.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/parts/r/engine_defined_part_attributes.result | 218 | ||||
-rw-r--r-- | mysql-test/suite/parts/t/engine_defined_part_attributes.test | 169 |
3 files changed, 388 insertions, 1 deletions
diff --git a/mysql-test/main/partition_error.result b/mysql-test/main/partition_error.result index bff08c0d447..cf057fe5b47 100644 --- a/mysql-test/main/partition_error.result +++ b/mysql-test/main/partition_error.result @@ -896,7 +896,7 @@ partitions 3 (partition tablespace ts1, partition x2 tablespace ts2, partition x3 tablespace ts3); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ts1, +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' partition x2 tablespace ts2, partition x3 tablespace ts3)' at line 8 CREATE TABLE t1 ( diff --git a/mysql-test/suite/parts/r/engine_defined_part_attributes.result b/mysql-test/suite/parts/r/engine_defined_part_attributes.result new file mode 100644 index 00000000000..81063d82c6f --- /dev/null +++ b/mysql-test/suite/parts/r/engine_defined_part_attributes.result @@ -0,0 +1,218 @@ +# +# MDEV-5271 Support engine-defined attributes per partition +# +# partitioned tables +CREATE TABLE `t1` ( +`id` INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1, +PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1 +); +SHOW CREATE TABLE `t1`; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='YES' + PARTITION BY RANGE (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO' ENCRYPTION_KEY_ID = 1, + PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT' ENCRYPTION_KEY_ID = 1) +INSERT INTO t1 VALUES (1), (2), (3); +DELETE FROM t1 WHERE id = 1; +UPDATE t1 SET id = 4 WHERE id = 3; +SELECT * FROM t1 WHERE id IN (2, 3); +id +2 +DROP TABLE `t1`; +CREATE TABLE `t2` ( +`id` INT +) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100), +PARTITION pt2 VALUES LESS THAN MAXVALUE +); +ERROR HY000: Can't create table `test`.`t2` (errno: 140 "Wrong create options") +CREATE TABLE `t3` ( +`id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, +PARTITION pt2 VALUES LESS THAN MAXVALUE +); +ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options") +CREATE TABLE `t4` ( +`id` INT +) ENGINE=InnoDB ENCRYPTED="NO"; +SHOW CREATE TABLE `t4`; +Table Create Table +t4 CREATE TABLE `t4` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='NO' +ALTER TABLE `t4` PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO", +PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); +SHOW CREATE TABLE `t4`; +Table Create Table +t4 CREATE TABLE `t4` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='NO' + PARTITION BY RANGE (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO', + PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT') +ALTER TABLE `t4` PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100), +PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t4`; +Table Create Table +t4 CREATE TABLE `t4` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='NO' + PARTITION BY RANGE (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB, + PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) +ALTER TABLE `t4` PARTITION BY RANGE(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, +PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); +ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options") +DROP TABLE `t4`; +# subpartitioned tables +CREATE TABLE `t5` ( +`id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( +PARTITION pt1 VALUES LESS THAN (100), +PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t5`; +Table Create Table +t5 CREATE TABLE `t5` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='NO' + PARTITION BY RANGE (`id`) +SUBPARTITION BY HASH (`id`) +SUBPARTITIONS 2 +(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB, + PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) +DROP TABLE `t5`; +CREATE TABLE `t6` ( +`id` INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES", +PARTITION pt2 VALUES LESS THAN MAXVALUE +); +ERROR HY000: Can't create table `test`.`t6` (errno: 140 "Wrong create options") +CREATE TABLE `t7` ( +id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( +PARTITION pt1 VALUES LESS THAN (100)( +SUBPARTITION spt1 ENCRYPTED="NO", +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION spt3, +SUBPARTITION spt4 +) +); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENCRYPTED="NO", +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ...' at line 6 +CREATE TABLE `t8` ( +id INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( +PARTITION pt1 VALUES LESS THAN (100) ( +SUBPARTITION spt1, +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION spt3, +SUBPARTITION spt4 +) +); +SHOW CREATE TABLE `t8`; +Table Create Table +t8 CREATE TABLE `t8` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='NO' + PARTITION BY RANGE (`id`) +SUBPARTITION BY HASH (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) + (SUBPARTITION `spt1` ENGINE = InnoDB, + SUBPARTITION `spt2` ENGINE = InnoDB), + PARTITION `pt2` VALUES LESS THAN MAXVALUE + (SUBPARTITION `spt3` ENGINE = InnoDB, + SUBPARTITION `spt4` ENGINE = InnoDB)) +DROP TABLE `t8`; +CREATE TABLE `t9` ( +id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( +SUBPARTITION spt1, +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION spt3, +SUBPARTITION spt4 +) +); +SHOW CREATE TABLE `t9`; +Table Create Table +t9 CREATE TABLE `t9` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`id`) +SUBPARTITION BY HASH (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) + (SUBPARTITION `spt1` ENGINE = InnoDB, + SUBPARTITION `spt2` ENGINE = InnoDB), + PARTITION `pt2` VALUES LESS THAN MAXVALUE + (SUBPARTITION `spt3` ENGINE = InnoDB, + SUBPARTITION `spt4` ENGINE = InnoDB)) +DROP TABLE `t9`; +CREATE TABLE `t10` ( +id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ( +SUBPARTITION spt1, +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION spt3, +SUBPARTITION spt4 +) +); +ERROR HY000: Can't create table `test`.`t10` (errno: 140 "Wrong create options") +CREATE TABLE `t11` ( +id INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( +PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( +SUBPARTITION spt1, +SUBPARTITION spt2 +), +PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" ( +SUBPARTITION spt3, +SUBPARTITION spt4 +) +); +SHOW CREATE TABLE `t11`; +Table Create Table +t11 CREATE TABLE `t11` ( + `id` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`='YES' + PARTITION BY RANGE (`id`) +SUBPARTITION BY HASH (`id`) +(PARTITION `pt1` VALUES LESS THAN (100) + (SUBPARTITION `spt1` ENGINE = InnoDB, + SUBPARTITION `spt2` ENGINE = InnoDB), + PARTITION `pt2` VALUES LESS THAN MAXVALUE + (SUBPARTITION `spt3` ENGINE = InnoDB, + SUBPARTITION `spt4` ENGINE = InnoDB)) +DROP TABLE `t11`; diff --git a/mysql-test/suite/parts/t/engine_defined_part_attributes.test b/mysql-test/suite/parts/t/engine_defined_part_attributes.test new file mode 100644 index 00000000000..8d08f4654cc --- /dev/null +++ b/mysql-test/suite/parts/t/engine_defined_part_attributes.test @@ -0,0 +1,169 @@ +--echo # +--echo # MDEV-5271 Support engine-defined attributes per partition +--echo # + +--source include/have_partition.inc +--source include/have_innodb.inc + +--echo # partitioned tables + +CREATE TABLE `t1` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1, + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1 +); +SHOW CREATE TABLE `t1`; + +INSERT INTO t1 VALUES (1), (2), (3); +DELETE FROM t1 WHERE id = 1; +UPDATE t1 SET id = 4 WHERE id = 3; +SELECT * FROM t1 WHERE id IN (2, 3); + +DROP TABLE `t1`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t2` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t3` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +CREATE TABLE `t4` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO"; +SHOW CREATE TABLE `t4`; + +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO", + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); +SHOW CREATE TABLE `t4`; + +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t4`; + +--error ER_CANT_CREATE_TABLE +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); + +DROP TABLE `t4`; + +--echo # subpartitioned tables + +CREATE TABLE `t5` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t5`; + +DROP TABLE `t5`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t6` ( + `id` INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES", + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +--error ER_PARSE_ERROR +CREATE TABLE `t7` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100)( + SUBPARTITION spt1 ENCRYPTED="NO", + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); + +CREATE TABLE `t8` ( + id INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t8`; + +DROP TABLE `t8`; + +CREATE TABLE `t9` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t9`; + +DROP TABLE `t9`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t10` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); + +CREATE TABLE `t11` ( + id INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t11`; + +DROP TABLE `t11`; |