# # Test the syntax for ALTER TABLE ... PARTITION operations, # covering combinations of different ALGORITHM and LOCK. # CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), purchased DATE, KEY(id)) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005)); INSERT INTO t1 VALUES (1, 'desk organiser', '2003-10-15'), (2, 'CD player', '1993-11-05'); CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), purchased DATE, KEY(id)); CREATE TABLE t2 ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), purchased DATE, KEY(id)) PARTITION BY HASH( YEAR(purchased) ) PARTITIONS 4; INSERT INTO t2 SELECT * FROM t1; # Test ALGORITHM = COPY, LOCK = * for operations ALTER TABLE t1 ALGORITHM = COPY, LOCK = NONE ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010))' at line 1 ALTER TABLE t1 ALGORITHM = COPY, LOCK = NONE DROP PARTITION p0; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP PARTITION p0' at line 1 ALTER TABLE t1 ALGORITHM = COPY, LOCK = SHARED, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ALTER TABLE t1 ALGORITHM = COPY, LOCK = SHARED, DROP PARTITION p4; ALTER TABLE t1 ALGORITHM = COPY, LOCK = EXCLUSIVE, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ALTER TABLE t1 ALGORITHM = COPY, LOCK = EXCLUSIVE, DROP PARTITION p4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 ALTER TABLE t1 ALGORITHM = COPY, LOCK = NONE, REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN (2003), PARTITION p32 VALUES LESS THAN (2005)); ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. ALTER TABLE t1 ALGORITHM = COPY, LOCK = SHARED, REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN (2003), PARTITION p32 VALUES LESS THAN (2005)); ALTER TABLE t1 ALGORITHM = COPY, LOCK = EXCLUSIVE, REORGANIZE PARTITION p31, p32 INTO (PARTITION p3 VALUES LESS THAN (2005)); ALTER TABLE t1 ALGORITHM = COPY, LOCK = NONE, REBUILD PARTITION p0; ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. ALTER TABLE t1 ALGORITHM = COPY, LOCK = SHARED, REBUILD PARTITION p1; ALTER TABLE t2 ALGORITHM = COPY, LOCK = EXCLUSIVE, REBUILD PARTITION p2; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 ALTER TABLE t2 ALGORITHM = COPY, LOCK = NONE, ADD PARTITION PARTITIONS 1; ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. ALTER TABLE t2 ALGORITHM = COPY, LOCK = NONE, COALESCE PARTITION 1; ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. ALTER TABLE t2 ALGORITHM = COPY, LOCK = SHARED, ADD PARTITION PARTITIONS 1; ALTER TABLE t2 ALGORITHM = COPY, LOCK = SHARED, COALESCE PARTITION 1; ALTER TABLE t2 ALGORITHM = COPY, LOCK = EXCLUSIVE, ADD PARTITION PARTITIONS 1; ALTER TABLE t2 ALGORITHM = COPY, LOCK = EXCLUSIVE, COALESCE PARTITION 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 # Test ALGORITHM = INPLACE, LOCK = * for all operations ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = NONE, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = NONE, DROP PARTITION p4; ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = SHARED, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = SHARED, DROP PARTITION p4; ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010)); ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, DROP PARTITION p4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = NONE, REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN (2003), PARTITION p32 VALUES LESS THAN (2005)); ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED. ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = SHARED, REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN (2003), PARTITION p32 VALUES LESS THAN (2005)); ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, REORGANIZE PARTITION p31, p32 INTO (PARTITION p3 VALUES LESS THAN (2005)); ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = NONE, REBUILD PARTITION p0; ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED. ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = SHARED, REBUILD PARTITION p1; ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, REBUILD PARTITION p2; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = NONE, ADD PARTITION PARTITIONS 1; ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED. ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = NONE, COALESCE PARTITION 1; ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED. ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = SHARED, ADD PARTITION PARTITIONS 1; ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = SHARED, COALESCE PARTITION 1; ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, ADD PARTITION PARTITIONS 1; ALTER TABLE t2 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, COALESCE PARTITION 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 ALTER TABLE t1 ALGORITHM = COPY, LOCK = NONE, EXCHANGE PARTITION p0 WITH TABLE t; ALTER TABLE t1 ALGORITHM = COPY, LOCK = SHARED, EXCHANGE PARTITION p0 WITH TABLE t; ALTER TABLE t1 ALGORITHM = COPY, LOCK = EXCLUSIVE, EXCHANGE PARTITION p0 WITH TABLE t; ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = NONE, EXCHANGE PARTITION p0 WITH TABLE t; ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = SHARED, EXCHANGE PARTITION p0 WITH TABLE t; ALTER TABLE t1 ALGORITHM = INPLACE, LOCK = EXCLUSIVE, EXCHANGE PARTITION p0 WITH TABLE t; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ SELECT * FROM t1 ORDER BY id; id name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 DROP TABLE t1, t2, t;