From 06eaf7232e9a920468c0f8d74dcf2fe8b555501c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 14:24:36 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/partition_alter.test | 325 +++++++++++++++++++++++++++++++++++ 1 file changed, 325 insertions(+) create mode 100644 mysql-test/main/partition_alter.test (limited to 'mysql-test/main/partition_alter.test') diff --git a/mysql-test/main/partition_alter.test b/mysql-test/main/partition_alter.test new file mode 100644 index 00000000..82752769 --- /dev/null +++ b/mysql-test/main/partition_alter.test @@ -0,0 +1,325 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE `test_data` ( + `hid` bigint(20) unsigned NOT NULL, + `itid` bigint(20) unsigned NOT NULL, + `clocktime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `values` double(16,4) NOT NULL, + PRIMARY KEY (`hid`,`itid`,`clocktime`) + ) ; + +INSERT INTO `test_data` (`hid`, `itid`, `clocktime`, `values`) VALUES + (1, 1, '2015-03-10 06:25:16', 0.0000), + (1, 1, '2015-03-10 06:26:24', 0.0000), + (1, 1, '2015-03-10 06:27:32', 0.0000), + (1, 1, '2015-03-10 06:28:40', 0.0000), + (1, 1, '2015-03-10 06:29:49', 0.0000), + (1, 1, '2015-03-10 06:30:57', 0.0000), + (1, 1, '2015-03-10 06:32:05', 0.0000), + (1, 1, '2015-03-10 06:33:14', 0.0000), + (1, 1, '2015-03-10 06:34:22', 0.0000), + (1, 1, '2015-03-10 06:35:30', 0.0000), + (1, 1, '2015-03-10 06:36:39', 0.0000), + (1, 1, '2015-03-10 06:37:47', 0.0000), + (1, 1, '2015-03-10 06:38:55', 0.0000), + (1, 1, '2015-03-10 06:40:03', 0.0000), + (1, 1, '2015-03-10 06:41:09', 0.0000), + (1, 1, '2015-03-10 06:42:21', 0.0000), + (1, 1, '2015-03-10 06:43:29', 0.0000), + (1, 1, '2015-03-10 06:44:37', 0.0000), + (1, 1, '2015-03-10 06:45:46', 0.0000), + (1, 1, '2015-03-10 06:47:05', 0.0000), + (1, 1, '2015-03-10 06:48:21', 0.0000), + (1, 1, '2015-03-10 06:49:41', 0.0000), + (1, 1, '2015-03-10 06:50:58', 0.0000), + (1, 1, '2015-03-10 06:52:08', 0.0000), + (1, 1, '2015-03-10 06:53:17', 0.0000), + (1, 1, '2015-03-10 06:54:25', 0.0000), + (563, 1, '2015-03-17 14:28:28', 0.3125), + (563, 1, '2015-03-17 14:29:39', 0.2775), + (563, 1, '2015-03-17 14:30:49', 0.2675); + + +CREATE PROCEDURE `create_part_max`() + alter table `test_data` + partition by range(unix_timestamp(clocktime)) ( + partition partMAX values less than MAXVALUE + ); + +call create_part_max(); + +call create_part_max(); + +drop procedure create_part_max; + +prepare stmt from "alter table `test_data` + partition by range(unix_timestamp(clocktime)) ( + partition partMAX values less than MAXVALUE + )"; + +execute stmt; +execute stmt; + +deallocate prepare stmt; + +drop table test_data; + +# +# MDEV-12389 ADD CHECK leaves an orphaned .par file +# + +--let $datadir=`SELECT @@datadir` + +# InnoDB +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +engine=innodb +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +--error ER_CONSTRAINT_FAILED +alter table t1 add check (b in (0, 1)); +alter table t1 add check (b in (0, 10)); +show create table t1; +--error ER_CONSTRAINT_FAILED +insert t1 values (2, '2020-01-03', 20); +drop table t1; +--list_files $datadir/test + +# MyISAM, different execution path +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +--error ER_CONSTRAINT_FAILED +alter table t1 add check (b in (0, 1)); +alter table t1 add check (b in (0, 10)); +show create table t1; +--error ER_CONSTRAINT_FAILED +insert t1 values (2, '2020-01-03', 20); +drop table t1; +--list_files $datadir/test + +# +# MDEV-13097 Online alter of a partitioned MyISAM table with auto_increment +# +create table t1 (id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) partition by hash(id_2) partitions 3 (partition p01, partition p02, partition p03); +insert into t1 values(0, 1, 1, NULL, now(), now()); +alter online table t1 delay_key_write=1; +show create table t1; +drop table t1; + +--echo # +--echo # MDEV-19751 Wrong partitioning by KEY() after key dropped +--echo # +create or replace table t1 (pk int, x timestamp(6), primary key (pk, x)) engine innodb +partition by key() partitions 2; +insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01'); +--echo # Inplace for DROP PRIMARY KEY when partitioned by default field list is denied +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 drop primary key, drop column x, add primary key (pk), algorithm=inplace; +alter table t1 drop primary key, drop column x, add primary key (pk); +select * from t1 partition (p0); +drop table t1; + +create or replace table t1 (pk int not null, x timestamp(6) not null, unique u(pk, x)) engine innodb +partition by key() partitions 2; +insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01'); +--echo # Same for NOT NULL UNIQUE KEY as this is actually primary key +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 drop key u, drop column x, add unique (pk), algorithm=inplace; +alter table t1 drop key u, drop column x, add unique (pk); +select * from t1 partition (p0); +drop table t1; + +create or replace table t1 (pk int, x timestamp(6) not null, primary key (pk)) engine innodb +partition by key(pk) partitions 2; +insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01'); +--echo # Inplace for DROP PRIMARY KEY when partitioned by explicit field list is allowed +alter table t1 drop primary key, add primary key (pk, x), algorithm=inplace; +select * from t1 partition (p0); +drop table t1; + +create or replace table t1 (k int, x timestamp(6), unique key u (x, k)) engine innodb +partition by key(k) partitions 2; +insert into t1 (k, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01'); +--echo # Inplace for DROP KEY is allowed +alter table t1 drop key u, algorithm=inplace; +select * from t1 partition (p0); +drop table t1; + +--echo # End of 10.2 tests + +--echo # +--echo # MDEV-14817 Server crashes in prep_alter_part_table() +--echo # after table lock and multiple add partition +--echo # +create table t1 (x int) partition by hash (x) (partition p1, partition p2); +lock table t1 write; +--error ER_SAME_NAME_PARTITION +alter table t1 add partition (partition p1); +--error ER_SAME_NAME_PARTITION +alter table t1 add partition (partition p1); +drop table t1; + +--echo # +--echo # MDEV-17091 Assertion `old_part_id == m_last_part' failed in +--echo # ha_partition::update_row or `part_id == m_last_part' in +--echo # ha_partition::delete_row upon UPDATE/DELETE after dropping versioning +--echo # +create table t1 (pk int, f int, primary key(pk, f)) engine=innodb + partition by key() partitions 2; + +insert into t1 values (1,10),(2,11); +--echo # expected to hit same partition +select * from t1 partition (p0); + +alter table t1 drop primary key, drop f, add primary key(pk); +--echo # 1 and 2 are expected to be in different partitions +select * from t1 partition(p0); +select * from t1 partition(p1); +delete from t1; +drop table t1; + +--echo # +--echo # MDEV-28576 RENAME COLUMN with NOCOPY algorithm leads to corrupt partitioned table +--echo # +create table t (a int, b int) partition by list (b) (partition p1 values in (1, 2)); +insert into t values (0, 1), (2, 2); +alter table t change b f int, change a b int, algorithm=nocopy; +check table t; +delete from t order by b limit 1; +# cleanup +drop table t; + +--echo # +--echo # MDEV-30112 ASAN errors in Item_ident::print / generate_partition_syntax +--echo # +create table t (a int) partition by hash(a); +--error ER_BAD_FIELD_ERROR +alter table t change a b int, drop a; +show create table t; +# Cleanup +drop table t; + +--echo # End of 10.3 tests + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28545 MyISAM reorganize partition corrupt older table format +--echo # + +SET GLOBAL mysql56_temporal_format=OFF; +CREATE TABLE t (ts timestamp, KEY (ts)) ENGINE=MyISAM +PARTITION BY RANGE (unix_timestamp(ts)) ( + PARTITION p1 VALUES LESS THAN (1645398000), + PARTITION pn VALUES LESS THAN MAXVALUE +); + +SET GLOBAL mysql56_temporal_format=ON; +FLUSH TABLES; +ALTER TABLE t DROP PARTITION p1; +CHECK TABLE t; +DROP TABLE t; +SET GLOBAL mysql56_temporal_format=DEFAULT; + +--echo # +--echo # End of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28576 RENAME COLUMN with NOCOPY algorithm leads to corrupt partitioned table +--echo # +create table t (a int) +partition by list (a) +subpartition by hash(a) subpartitions 2 +(partition p0 values in (1)); +alter table t rename column a to b, algorithm=nocopy; +show create table t; +alter table t rename column b to c, algorithm=copy; +show create table t; +drop table t; + +create table t (d int, e int) +partition by list columns (d, e) +subpartition by key (d, e) +(partition p0 values in ((2, 3))); +alter table t rename column d to f, rename column e to g, algorithm=nocopy; +show create table t; +alter table t rename column f to h, rename column g to i, algorithm=copy; +show create table t; +drop table t; + +create table t (k int, l int) +partition by range (k) +subpartition by hash(l) subpartitions 4 +(partition p0 values less than (5)); +alter table t rename column k to l, rename column l to k; +show create table t; +drop table t; + +create table t (a int, b int) partition by list (b) (partition p1 values in (1, 2)); +insert into t values (0, 1), (2, 2); +alter table t rename column b to f, rename column a to b, algorithm=nocopy; +check table t; +delete from t order by b limit 1; +# cleanup +drop table t; + +--echo # End of 10.5 tests + +create or replace table t1 (x int primary key) +partition by range(x) ( + p1 values less than (10), + partition p2 values less than (20), + p3 values less than (30), + partition p4 values less than (40), + p5 values less than (50), + pn values less than maxvalue); + +show create table t1; + +create or replace table t1 (x int) +partition by list(x) ( + partition p1 values in (2, 3, 4), + partition p2 values in (12, 13, 14), + partition p3 values in (22, 23, 24), + p4 values in (32, 33, 34), + p5 values in (42, 43, 44), + pn values in (52, 53, 54)); + +show create table t1; + +--error ER_PARSE_ERROR +create or replace table t1 (x int) +partition by list(x) ( + partition partition p1 values in (2, 3, 4), + pn values in (52, 53, 54)); + +--error ER_PARSE_ERROR +create or replace table t1 (x int) +partition by list(x) ( + partition partition values in (2, 3, 4), + pn values in (52, 53, 54)); + +--error ER_PARSE_ERROR +create or replace table t1 (x int) +partition by list(x) ( + partition values in (2, 3, 4), + pn values in (52, 53, 54)); + +create or replace table t1 (x int) +partition by list(x) ( + partitio values in (2, 3, 4), + pn values in (52, 53, 54)); + +drop table t1; + +--echo # End of 10.7 tests -- cgit v1.2.3