mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-04 04:46:15 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/parts/r/engine_defined_part_attributes.result
Alexander Barkov 36eba98817 MDEV-19123 Change default charset from latin1 to utf8mb4 
Changing the default server character set from latin1 to utf8mb4.
2024-07-11 10:21:07 +04:00

288 lines
9.4 KiB
Text
Raw Permalink Blame History

#
# 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `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`;
#
# MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values
#
# restart: --innodb-sys-tablespaces
CREATE TABLE `t12` (
id INT
) ENGINE=InnoDB PARTITION BY HASH(id)
(
pt1 PAGE_COMPRESSED=0
);
SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
name flag
test/t12#P#pt1 21
ALTER TABLE `t12` ADD PARTITION (
PARTITION pt2 PAGE_COMPRESSED=1
);
SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
name flag
test/t12#P#pt1 21
test/t12#P#pt2 1610612789
ALTER TABLE `t12` ADD PARTITION (
PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
);
SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
name flag
test/t12#P#pt1 21
test/t12#P#pt2 1610612789
test/t12#P#pt3 805306421
DROP TABLE `t12`;
CREATE TABLE `t13` (
`id` INT
) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0,
PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
PARTITION pt3 VALUES LESS THAN MAXVALUE
);
SHOW CREATE TABLE `t13`;
Table Create Table
t13 CREATE TABLE `t13` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `PAGE_COMPRESSED`=1
PARTITION BY RANGE (`id`)
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 0,
PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
name flag
test/t13#P#pt1 21
test/t13#P#pt2 805306421
test/t13#P#pt3 1610612789
ALTER TABLE `t13` PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
PARTITION pt2 VALUES LESS THAN (200),
PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0
);
SHOW CREATE TABLE `t13`;
Table Create Table
t13 CREATE TABLE `t13` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `PAGE_COMPRESSED`=1
PARTITION BY RANGE (`id`)
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB PAGE_COMPRESSED = 0)
SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
name flag
test/t13#P#pt1 805306421
test/t13#P#pt2 1610612789
test/t13#P#pt3 21
DROP TABLE `t13`;
Reference in a new issue View git blame Copy permalink