431
431
partition by key (a);
432
432
# Avg_row_length (col 6) and Data_length (col 7) vary depending
433
# on the page size. Data_free for InnoDB tablespace varies
434
# depending on which tests have been run before this one.
435
--replace_column 6 # 7 # 10 #
437
insert into t1 values (NULL), (NULL), (NULL), (NULL);
439
# Avg_row_length (col 6) and Data_length (col 7) vary depending
440
# on the page size. Data_free for InnoDB tablespace varies
441
# depending on which tests have been run before this one.
442
--replace_column 6 # 7 # 10 #
444
insert into t1 values (NULL), (NULL), (NULL), (NULL);
446
# Avg_row_length (col 6) and Data_length (col 7) vary depending
447
# on the page size. Data_free for InnoDB tablespace varies
448
# depending on which tests have been run before this one.
449
--replace_column 6 # 7 # 10 #
433
# on the page size. Data_free for InnoDB tablespace varies depending on which
434
# tests have been run before this one.create_time(col 12) varies with time
435
--replace_column 6 # 7 # 10 # 12 #
437
insert into t1 values (NULL), (NULL), (NULL), (NULL);
439
# Avg_row_length (col 6) and Data_length (col 7) vary depending
440
# on the page size. Data_free for InnoDB tablespace varies depending on which
441
# tests have been run before this one.create_time(col 12) varies with time
442
--replace_column 6 # 7 # 10 # 12 #
444
insert into t1 values (NULL), (NULL), (NULL), (NULL);
446
# Avg_row_length (col 6) and Data_length (col 7) vary depending
447
# on the page size. Data_free for InnoDB tablespace varies depending on which
448
# tests have been run before this one.create_time(col 12) varies with time
449
--replace_column 6 # 7 # 10 # 12 #
450
450
show table status;
703
703
ALTER TABLE t1 IMPORT TABLESPACE;
708
--echo # Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT
709
--echo # Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE
716
PARTITION BY HASH (a) PARTITIONS 3;
717
START TRANSACTION WITH CONSISTENT SNAPSHOT;
718
--connect (con1, localhost, root,,)
720
ALTER TABLE t1 ADD INDEX idx1 (b);
723
--error ER_TABLE_DEF_CHANGED
724
SELECT b FROM t1 WHERE b = 0;
725
--error ER_TABLE_DEF_CHANGED
726
SELECT b FROM t1 WHERE b = 0;
730
--echo # Same test without partitioning
736
START TRANSACTION WITH CONSISTENT SNAPSHOT;
738
--connect (con1, localhost, root,,)
739
ALTER TABLE t1 ADD INDEX idx1 (b);
742
--error ER_TABLE_DEF_CHANGED
743
SELECT b FROM t1 WHERE b = 0;
744
--error ER_TABLE_DEF_CHANGED
745
SELECT b FROM t1 WHERE b = 0;
749
--echo Bug 17896265 PARTITIONED TABLE HAS MISPLACED ROWS, AFTER INPLACE ALTER
751
#Check basic Interchange
757
PARTITION BY KEY (f1,f2) PARTITIONS 2;
758
INSERT INTO t1 VALUES (9585,5);
759
--error ER_ALTER_OPERATION_NOT_SUPPORTED
760
ALTER TABLE t1 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
762
# Check when interchanging columns do not change the order
770
PARTITION BY KEY (f2,f3) PARTITIONS 2;
771
INSERT INTO t2 VALUES (10,9585,5,20);
773
ALTER TABLE t2 CHANGE f3 f3 INT AFTER f4, ALGORITHM=INPLACE;
774
# Check if row is in the wrong partition!
777
--error ER_ALTER_OPERATION_NOT_SUPPORTED
778
ALTER TABLE t2 CHANGE f3 f3 INT AFTER f1, ALGORITHM=INPLACE;
780
#Bring back to original position
781
ALTER TABLE t2 CHANGE f4 f4 INT AFTER f3, ALGORITHM=INPLACE;
784
#Change the column order of field which are not part of KEY
785
ALTER TABLE t2 CHANGE f1 f1 INT AFTER f4, ALGORITHM=INPLACE;
788
#now order is f2,f3,f4,f1
789
ALTER TABLE t2 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
792
#now order is f2,f1,f3,f4
793
--error ER_ALTER_OPERATION_NOT_SUPPORTED
794
ALTER TABLE t2 CHANGE f2 f2 INT AFTER f4, ALGORITHM=INPLACE;
796
#check if Range partition is effected
797
CREATE TABLE t3 (f1 INT,f2 INT) ENGINE=INNODB
798
PARTITION BY RANGE(f1) (
799
PARTITION p0 VALUES LESS THAN (100),
800
PARTITION p1 VALUES LESS THAN (200),
801
PARTITION p2 VALUES LESS THAN (600),
802
PARTITION p3 VALUES LESS THAN MAXVALUE
804
insert into t3 values (90,120);
805
insert into t3 values (120,300);
806
ALTER TABLE t3 CHANGE f1 f1 int AFTER f2, ALGORITHM=INPLACE;
809
#check with hash partitions
815
PARTITION BY HASH (MOD(f1,f2)) PARTITIONS 2;
816
INSERT INTO t4 VALUES (9585,5);
817
ALTER TABLE t4 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
820
#Check with column partitioning
826
PARTITION BY RANGE COLUMNS(f1,f2) (
827
PARTITION p0 VALUES LESS THAN (10000,12),
828
PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
831
INSERT INTO t5 VALUES (1,20000);
832
ALTER TABLE t5 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
835
#Check with column partitioning and subpartition
841
PARTITION BY RANGE COLUMNS(a,b)
842
SUBPARTITION BY KEY(a,b)
844
PARTITION p0 VALUES LESS THAN (10000,12),
845
PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
847
INSERT INTO t6 VALUES (9585,5);
849
--error ER_ALTER_OPERATION_NOT_SUPPORTED
850
ALTER TABLE t6 CHANGE a a INT AFTER b, ALGORITHM=INPLACE;
852
#check when the columns are not adjacent
862
PARTITION BY KEY (f1,f5) PARTITIONS 2;
863
INSERT INTO t7 VALUES (9585,10,20,10,5);
865
ALTER TABLE t7 CHANGE f5 f5 INT AFTER f3, ALGORITHM=INPLACE;
868
ALTER TABLE t7 CHANGE f5 f5 INT AFTER f2, ALGORITHM=INPLACE;
871
--error ER_ALTER_OPERATION_NOT_SUPPORTED
872
ALTER TABLE t7 CHANGE f1 f1 INT AFTER f4, ALGORITHM=INPLACE;
874
DROP TABLE t1,t2,t3,t4,t5,t6,t7;
877
--echo # Bug #17299181 CREATE_TIME AND UPDATE_TIME ARE
878
--echo # WRONG FOR PARTITIONED TABLES
881
CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB
882
PARTITION BY HASH (a) PARTITIONS 2;
884
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE
885
CREATE_TIME IS NOT NULL AND TABLE_NAME='t1';