1# Test for COALESCE PARTITION, ALTER TABLE and ADD PARTITIONS 2# for tables with HASH partitions 3CREATE TABLE t1 ( 4c1 INT DEFAULT NULL 5) ENGINE=Aria 6PARTITION BY HASH (c1) 7PARTITIONS 3; 8INSERT INTO t1 VALUE (1), (2), (101), (102), (201), (202); 9ALTER TABLE t1 ENGINE=S3; 10SELECT count(*) FROM t1; 11count(*) 126 13SHOW TABLES; 14Tables_in_s3 15t1 16ALTER TABLE t1 COALESCE PARTITION 2; 17ERROR 42000: Table 't1' uses an extension that doesn't exist in this MariaDB version 18SHOW WARNINGS; 19Level Code Message 20Error 1112 Table 't1' uses an extension that doesn't exist in this MariaDB version 21Error 6 Error on delete of './s3/t1#P#p0#TMP#.MAI' (Errcode: 2 "No such file or directory") 22Error 6 Error on delete of './s3/t1#P#p0#TMP#.MAD' (Errcode: 2 "No such file or directory") 23ALTER TABLE t1 ADD PARTITION PARTITIONS 6; 24SELECT count(*) FROM t1; 25count(*) 266 27ALTER TABLE t1 ADD COLUMN c INT; 28SELECT count(*) FROM t1; 29count(*) 306 31DROP TABLE t1; 32# Test for simple change engine to S3 33CREATE TABLE t1 ( 34c1 int DEFAULT NULL, 35c2 int DEFAULT NULL 36) ENGINE=Aria 37PARTITION BY RANGE (c1) 38SUBPARTITION BY HASH(c2) 39SUBPARTITIONS 2 40(PARTITION p0 VALUES LESS THAN (100), 41PARTITION p1 VALUES LESS THAN (200), 42PARTITION p3 VALUES LESS THAN (300)); 43INSERT INTO t1 VALUE (1,1), (2,2), (101,101), (102,102), (201,201), (202,202); 44ALTER TABLE t1 ENGINE=S3; 45SELECT count(*) FROM t1; 46count(*) 476 48# Test for rename table 49RENAME TABLE t1 TO t2; 50SELECT count(*) FROM t2; 51count(*) 526 53# Test for TRUNCATE, ANALYZE, CHECK, REBUILD, OPTIMIZE, REPAIR, 54# ADD, DROP, REORGANIZE partition 55ALTER TABLE t2 TRUNCATE PARTITION p3; 56ERROR HY000: Table 't2' is read only 57ALTER TABLE t2 ANALYZE PARTITION p3; 58Table Op Msg_type Msg_text 59s3.t2 analyze status Table 's3.t2' is read only 60s3.t2 analyze status Engine-independent statistics collected 61s3.t2 analyze status OK 62SELECT count(*) FROM t2; 63count(*) 646 65ALTER TABLE t2 CHECK PARTITION p3; 66Table Op Msg_type Msg_text 67s3.t2 check status OK 68SELECT count(*) FROM t2; 69count(*) 706 71ALTER TABLE t2 REBUILD PARTITION p0, p1; 72ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version 73ALTER TABLE t2 OPTIMIZE PARTITION p0, p1; 74Table Op Msg_type Msg_text 75s3.t2 optimize Error Table 't2' is read only 76s3.t2 optimize status Operation failed 77SELECT count(*) FROM t2; 78count(*) 796 80ALTER TABLE t2 REPAIR PARTITION p0, p1; 81Table Op Msg_type Msg_text 82s3.t2 repair Error Table 't2' is read only 83s3.t2 repair status Operation failed 84SELECT count(*) FROM t2; 85count(*) 866 87ALTER TABLE t2 ADD PARTITION (PARTITION p4 VALUES LESS THAN (400)); 88SHOW CREATE TABLE t2; 89Table Create Table 90t2 CREATE TABLE `t2` ( 91 `c1` int(11) DEFAULT NULL, 92 `c2` int(11) DEFAULT NULL 93) ENGINE=S3 DEFAULT CHARSET=latin1 94 PARTITION BY RANGE (`c1`) 95SUBPARTITION BY HASH (`c2`) 96SUBPARTITIONS 2 97(PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3, 98 PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3, 99 PARTITION `p3` VALUES LESS THAN (300) ENGINE = S3, 100 PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3) 101ALTER TABLE t2 102REORGANIZE PARTITION p4 INTO ( 103PARTITION n0 VALUES LESS THAN (500), 104PARTITION n1 VALUES LESS THAN (600) 105); 106ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version 107ALTER TABLE t2 DROP PARTITION p3; 108SHOW CREATE TABLE t2; 109Table Create Table 110t2 CREATE TABLE `t2` ( 111 `c1` int(11) DEFAULT NULL, 112 `c2` int(11) DEFAULT NULL 113) ENGINE=S3 DEFAULT CHARSET=latin1 114 PARTITION BY RANGE (`c1`) 115SUBPARTITION BY HASH (`c2`) 116SUBPARTITIONS 2 117(PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3, 118 PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3, 119 PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3) 120SELECT count(*) from t2; 121count(*) 1224 123# Test for ALTER TABLE 124ALTER TABLE t2 ADD COLUMN c INT; 125SELECT count(*) FROM t2; 126count(*) 1274 128ALTER TABLE t2 DROP COLUMN c; 129SELECT count(*) FROM t2; 130count(*) 1314 132# Test for REMOVE PARTITIONING 133ALTER TABLE t2 REMOVE PARTITIONING; 134SHOW CREATE TABLE t2; 135Table Create Table 136t2 CREATE TABLE `t2` ( 137 `c1` int(11) DEFAULT NULL, 138 `c2` int(11) DEFAULT NULL 139) ENGINE=S3 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 140SELECT count(*) FROM t2; 141count(*) 1424 143DROP TABLE t2; 144# Test for EXCHANGE PARTITION 145CREATE TABLE t1 ( 146c1 int DEFAULT NULL 147) ENGINE=Aria 148PARTITION BY RANGE (c1) 149(PARTITION p0 VALUES LESS THAN (100), 150PARTITION p1 VALUES LESS THAN (200)); 151INSERT INTO t1 VALUE (1), (2), (101), (102); 152ALTER TABLE t1 ENGINE=S3; 153CREATE TABLE t_part ( 154c1 int DEFAULT NULL 155) ENGINE=Aria; 156INSERT INTO t_part VALUE (120), (130), (140); 157ALTER TABLE t_part ENGINE=S3; 158ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t_part; 159SELECT count(*) FROM t_part; 160count(*) 1612 162SELECT count(*) FROM t1; 163count(*) 1645 165DROP TABLE t1; 166DROP TABLE t_part; 167