MDEV-13626 Merge InnoDB test cases from MySQL 5.7 (part 2)

Import the changes to innodb.innodb-index innodb.innodb-index-debug

Note: As noted in MDEV-13613, due to the behaviour change in MDEV-11114,
DROP COLUMN will not imply DROP/ADD PRIMARY/UNIQUE KEY, like it does
in MySQL. The tests have been adjusted accordingly.

5 files changed, 1784 insertions, 29 deletions

diff --git a/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc b/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc
new file mode 100644
index 00000000000..6a2fcd15be0
--- /dev/null
+++ b/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc
@@ -0,0 +1,265 @@
+# skip sort for prefix change
+# pk(o1(2)) to pk(o1(3))
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(3)), lock=none;
+drop table t1;
+
+# pk(o1(2)) to pk(o1)
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+
+# pk(o1(2)) to pk(o1(3),n1)
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
+drop table t1;
+
+# pk(o1(2)) to pk(o1,n1)
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
+drop table t1;
+
+# pk(o1(2)) to pk(o1(3), o2)
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+
+# pk(o1(2)) to pk(o1, o2)
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+
+# pk(o1(3)) to pk(o1(2))
+create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1(2))
+create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+
+# pk(o1(3),o2) to pk(o1(2))
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1(2))
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+
+# pk(o1(3),o2) to pk(o1(2),n1)
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1(2),n1)
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+
+# pk(o1(3),o2) to pk(o1(3),n1)
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1,n1)
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+
+# pk(o1,o2(3)) to pk(o1,o2(2))
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1,o2(2))
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2(2)) to pk(o1,o2(3))
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2(2)) to pk(o1,o2)
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2(3),o3) to pk(o1,o2(2))
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o2(2))
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+
+# pk(o1(3),o2(3)) to pk(o1(3),o2(2))
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1,o2(2))
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+
+# pk(o1(3),o2(2)) to pk(o1(3),o2(3))
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2(2)) to pk(o1,o2)
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+
+# pk(o1(3),o2,o3(2)) to pk(o1(3),o2,o3(3))
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3(2)) to pk(o1,o2,o3)
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+
+# pk(o1(3),o2,o3(3)) to pk(o1(3),o2,o3(2))
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o2,o3(2))
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
+drop table t1;
+
+# skip sort for adding existing columns/newly added columns, dropping pk columns at the end.
+# pk(o1) to pk(o1,o2)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,n1)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+
+# pk(o1) to pk(n1,o1)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
+drop table t1;
+
+# pk(o1) to pk(n1,o1,n2)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
+drop table t1;
+
+# pk(o1) to pk(n1,n2,o1)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,n1,n2)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,o2,n1)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,n1,o2)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+
+# pk(o1) to pk(n1,o1,o2)
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,o2,o3)
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+
+# pk(o1) to pk(o1,o3,o2)
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o2)
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o2,o3,o4)
+create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2,2),(2,2,1,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o2,n1)
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,n1,o2)
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1)
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
diff --git a/mysql-test/suite/innodb/r/innodb-index-debug.result b/mysql-test/suite/innodb/r/innodb-index-debug.result
index 09c13e37cf2..172e4ebf454 100644
--- a/mysql-test/suite/innodb/r/innodb-index-debug.result
+++ b/mysql-test/suite/innodb/r/innodb-index-debug.result
@@ -34,9 +34,8 @@ CREATE UNIQUE INDEX ui ON bug13861218(c1);
 SET DEBUG_DBUG = @saved_debug_dbug;
 DROP TABLE bug13861218;
 #
-# Bug #21762319	ADDING INDEXES ON EMPTY TABLE IS SLOW
-#	WITH LARGE INNODB_SORT_BUFFER_SIZE.
-call mtr.add_suppression("InnoDB: Cannot create temporary merge file");
+# Bug #17657223	EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
+#
 create table t480(a serial)engine=innodb;
 insert into t480
 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
@@ -47,14 +46,252 @@ insert into t480 select 0 from t480;
 insert into t480 select 0 from t480;
 create table t1(f1 int auto_increment not null,
 f2 char(200) not null, f3 char(200) not null,
-f4 char(200) not null,primary key(f1))engine=innodb;
-insert into t1 select NULL,'aaa','bbb','ccc' from t480;
-insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
-insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
-insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
-insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
-insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
-select count(*) from t1;
-count(*)
-2880
-drop table t1, t480;
+primary key(f1,f2,f3), key(f1))engine=innodb;
+insert into t1 select NULL,'aaa','bbb' from t480;
+insert into t1 select NULL,'aaaa','bbbb' from t480;
+insert into t1 select NULL,'aaaaa','bbbbb' from t480;
+insert into t1 select NULL,'aaaaaa','bbbbbb' from t480;
+SET DEBUG_DBUG = '+d,row_merge_write_failure';
+alter table t1 drop primary key,add primary key(f2,f1);
+ERROR HY000: Temporary file write failure
+SET DEBUG_DBUG = @saved_debug_dbug;
+drop table t1;
+connect  con1,localhost,root;
+create table t1(k1 int auto_increment primary key,
+k2 char(200),k3 char(200))engine=innodb;
+insert into t1 values(NULL,'a','b'), (NULL,'aa','bb');
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+optimize table t1;
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+INSERT INTO t1 select NULL,'aaa','bbb' from t480;
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection con1;
+/*con1 reap*/ Optimize table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+affected rows: 2
+SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
+COUNT(k1)	k2	k3
+1	a	b
+1	aa	bb
+480	aaa	bbb
+drop table t1;
+create table t1(k1 int auto_increment primary key,
+k2 char(200),k3 char(200))engine=innodb;
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+ALTER TABLE t1 ADD COLUMN k4 int;
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+SET debug = '+d,row_log_tmpfile_fail';
+Warnings:
+Warning	1287	'@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead
+INSERT INTO t1 select NULL,'aaa','bbb' from t480;
+INSERT INTO t1 select NULL,'aaaa','bbbb' from t480;
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+SET DEBUG_DBUG = @saved_debug_dbug;
+connection con1;
+/*con1 reap*/ ALTER TABLE t1 ADD COLUMN k4 int;
+ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
+SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
+COUNT(k1)	k2	k3
+480	aaa	bbb
+480	aaaa	bbbb
+disconnect con1;
+connection default;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `k1` int(11) NOT NULL AUTO_INCREMENT,
+  `k2` char(200) DEFAULT NULL,
+  `k3` char(200) DEFAULT NULL,
+  PRIMARY KEY (`k1`)
+) ENGINE=InnoDB AUTO_INCREMENT=1023 DEFAULT CHARSET=latin1
+drop table t1;
+drop table t480;
+SET DEBUG_SYNC='RESET';
+#
+#  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
+#                LAST COLUMN OF OLD PK
+#
+SET DEBUG_DBUG = '+d,innodb_alter_table_pk_assert_no_sort';
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2,2),(2,2,1,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+SET DEBUG_DBUG = @saved_debug_dbug;
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result
index 1b7f44fa8c4..54ad4e8a927 100644
--- a/mysql-test/suite/innodb/r/innodb-index.result
+++ b/mysql-test/suite/innodb/r/innodb-index.result
@@ -1184,3 +1184,667 @@ t2c	CREATE TABLE `t2c` (
   KEY `t2a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t1,t2,t2c,t2i;
+#
+# Bug #17657223	EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
+#
+SET GLOBAL innodb_monitor_enable = module_ddl;
+create table t1(f1 int not null, f2 blob)engine=innodb;
+insert into t1 values(1, repeat('a',20000));
+# Skip sort
+# Reusing the same pk
+alter table t1 force;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+create table t1(f1 int not null, f2 int not null,
+primary key(f1))engine=innodb;
+insert into t1 values(1,2), (3,4);
+# Add Secondary index.
+# Skip temp file usage due to small table size
+alter table t1 add key(f2);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+create table t480(a serial)engine=innodb;
+insert into t480
+values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
+(),(),(),(),(),(),(),();
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1))engine=innodb;
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
+insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
+insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
+insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
+insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
+select count(*) from t1;
+count(*)
+2880
+# Skip sort
+# Change PK from (f1) to (f1,f2,f3,f4)
+alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
+alter table t1 drop primary key,add column f5 int not null,
+add column f6 int not null,add primary key(f1,f2,f5,f6);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
+alter table t1 drop column f6;
+ERROR 42000: Key column 'f6' doesn't exist in table
+alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same PK
+alter table t1 add column f6 int;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 drop column f6;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK from (f1,f2,f5) to (f1,f5)
+alter table t1 drop column f2;
+ERROR 42000: Key column 'f2' doesn't exist in table
+alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+ddl_sort_file_alter_table	2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 add column f2n int after f1, drop primary key, add
+primary key (f1,f5,f2n);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 change f5 f2n int not null,change f2n f5 int not null,
+add column f8 int not null;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
+alter table t1 add column f7 int, drop primary key,
+add primary key (f1,f5,f7,f2n);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+ddl_sort_file_alter_table	2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 force;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 row_format=compact;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 engine=innodb;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Optimize table
+optimize table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+affected rows: 2
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Sort files used for adding secondary index
+alter table t1 drop primary key, add primary key(f1,f5,f7), add index
+i(f3);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# No sort files used for dropping secondary index
+alter table t1 drop primary key, add primary key(f1,f5),drop index i;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK(f1,f5) to (f1,added_columns) and drop f5
+alter table t1 drop primary key, add primary key(f1,f12),
+drop column f5, add column f12 int not null;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK(f1,f12) to (f1,existing_columns)
+alter table t1 drop primary key, add primary key(f1,f3);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+ddl_sort_file_alter_table	2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK(f1,f3) to (f1,added_column,f3,added_column)
+alter table t1 drop primary key, add column f3n int,
+add column f4n int, add primary key(f1,f3n,f3,f4n);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Adding Secondary index alone.
+alter table t1 add key(f1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK(f1,f3) to (existing_column,f1)
+alter table t1 drop primary key, add primary key(f4,f1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+ddl_sort_file_alter_table	3
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort for PK.
+# Change PK(f4,f1) to (added_columns,f4,f1)
+# Secondary index rebuild happens
+alter table t1 drop primary key, add column f5n int,
+add column f6n int, add primary key(f5n,f6n,f4,f1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort for PK.
+# Change PK(f5n,f6n,f4,f1) to
+# (added_columns,f5n,added_column,f6n,f4,f1)
+# Secondary index rebuild happens
+alter table t1 drop primary key, add column f7n int,
+add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name	count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+SET GLOBAL innodb_monitor_disable = module_ddl;
+select count(*) from t1;
+count(*)
+2880
+drop table t1;
+SET GLOBAL innodb_monitor_reset = default;
+SET GLOBAL innodb_monitor_enable = default;
+SET GLOBAL innodb_monitor_disable = default;
+# Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
+# WHEN SKIPPING SORT FOR CLUSTER INDEX
+SELECT @@innodb_sort_buffer_size;
+@@innodb_sort_buffer_size
+1048576
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(106, 'aaa','bbb','cccc');
+select count(*) from t1;
+count(*)
+481
+# Skip sort
+# Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106-aaa-bbb' for key 'PRIMARY'
+select count(*) from t1;
+count(*)
+481
+drop table t1;
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(108,'aaa','bbb','cccc');
+select count(*) from t1;
+count(*)
+481
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '108-aaa-bbb' for key 'PRIMARY'
+select count(*) from t1;
+count(*)
+481
+drop table t1, t480;
+#
+# Bug #19896922	SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
+#		FIELD IS CHANGED
+#
+create table t1(a int not null, b varchar(30) not null,
+primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
+select * from t1;
+a	b
+0	khdHps6UxW8Lwaoxa604oK6zkb
+1	khdHps6UxW8L
+alter table t1 drop primary key, add primary key (b(18),a);
+select * from t1;
+a	b
+1	khdHps6UxW8L
+0	khdHps6UxW8Lwaoxa604oK6zkb
+drop table t1;
+create table t1(a int not null, b varchar(30) not null,
+primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
+select * from t1;
+a	b
+1	khdHps6UtW8L
+0	khdHps6UxW8Lwaoxa604oK6zkb
+alter table t1 drop primary key, add primary key (b(8),a);
+select * from t1;
+a	b
+0	khdHps6UxW8Lwaoxa604oK6zkb
+1	khdHps6UtW8L
+drop table t1;
+#
+# Bug #21103101	SORTING SKIPPED WHEN DROPPING THE SINGLE
+#			COLUMN PRIMARY KEY
+#
+create table t1(f1 int not null, f2 int not null,
+primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+alter table t1 drop column f1;
+ERROR 42000: Key column 'f1' doesn't exist in table
+alter table t1 drop column f1, drop primary key;
+ERROR 42000: Key column 'f1' doesn't exist in table
+alter table t1 drop column f1, drop key f1;
+drop table t1;
+create table t1(f1 int not null, f2 int not null,
+primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+alter table t1 drop primary key, lock=none;
+ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED
+drop table t1;
+#
+#  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
+#		LAST COLUMN OF OLD PK
+#
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2, o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop o1, lock=none;
+ERROR 42000: Key column 'o1' doesn't exist in table
+alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop o2, lock=none;
+ERROR 42000: Key column 'o2' doesn't exist in table
+alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop o1, drop o2, lock=none;
+ERROR 42000: Key column 'o2' doesn't exist in table
+alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2,o1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2,2),(2,2,1,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-index-debug.test b/mysql-test/suite/innodb/t/innodb-index-debug.test
index d4da0c14246..6927120fd5b 100644
--- a/mysql-test/suite/innodb/t/innodb-index-debug.test
+++ b/mysql-test/suite/innodb/t/innodb-index-debug.test
@@ -1,5 +1,7 @@
 -- source include/have_debug.inc
 -- source include/have_innodb.inc
+-- source include/count_sessions.inc
+-- source include/have_debug_sync.inc
 
 let $MYSQLD_DATADIR= `select @@datadir`;
 
@@ -42,13 +44,10 @@ SET DEBUG_DBUG = @saved_debug_dbug;
 DROP TABLE bug13861218;
 
 --echo #
---echo # Bug #21762319	ADDING INDEXES ON EMPTY TABLE IS SLOW
---echo #	WITH LARGE INNODB_SORT_BUFFER_SIZE.
-
-call mtr.add_suppression("InnoDB: Cannot create temporary merge file");
-
-# Table with large data which is greater than sort buffer
+--echo # Bug #17657223	EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
+--echo #
 
+# Error during file creation in alter operation
 create table t480(a serial)engine=innodb;
 insert into t480
 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
@@ -57,14 +56,74 @@ insert into t480 select 0 from t480;
 insert into t480 select 0 from t480;
 insert into t480 select 0 from t480;
 insert into t480 select 0 from t480;
+
+# Error during file write in alter operation.
 create table t1(f1 int auto_increment not null,
 		f2 char(200) not null, f3 char(200) not null,
-		f4 char(200) not null,primary key(f1))engine=innodb;
-insert into t1 select NULL,'aaa','bbb','ccc' from t480;
-insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
-insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
-insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
-insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
-insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
-select count(*) from t1;
-drop table t1, t480;
+		primary key(f1,f2,f3), key(f1))engine=innodb;
+insert into t1 select NULL,'aaa','bbb' from t480;
+insert into t1 select NULL,'aaaa','bbbb' from t480;
+insert into t1 select NULL,'aaaaa','bbbbb' from t480;
+insert into t1 select NULL,'aaaaaa','bbbbbb' from t480;
+SET DEBUG_DBUG = '+d,row_merge_write_failure';
+--error ER_TEMP_FILE_WRITE_FAILURE
+alter table t1 drop primary key,add primary key(f2,f1);
+SET DEBUG_DBUG = @saved_debug_dbug;
+drop table t1;
+
+# Optimize table via inplace algorithm
+connect (con1,localhost,root);
+create table t1(k1 int auto_increment primary key,
+k2 char(200),k3 char(200))engine=innodb;
+insert into t1 values(NULL,'a','b'), (NULL,'aa','bb');
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+send optimize table t1;
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+INSERT INTO t1 select NULL,'aaa','bbb' from t480;
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+connection con1;
+--enable_info
+--echo /*con1 reap*/ Optimize table t1;
+reap;
+--disable_info
+SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
+drop table t1;
+
+# Log file creation failure.
+create table t1(k1 int auto_increment primary key,
+k2 char(200),k3 char(200))engine=innodb;
+SET DEBUG_SYNC= 'row_merge_after_scan
+SIGNAL opened WAIT_FOR flushed';
+send ALTER TABLE t1 ADD COLUMN k4 int;
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+SET debug = '+d,row_log_tmpfile_fail';
+INSERT INTO t1 select NULL,'aaa','bbb' from t480;
+INSERT INTO t1 select NULL,'aaaa','bbbb' from t480;
+SET DEBUG_SYNC= 'now SIGNAL flushed';
+SET DEBUG_DBUG = @saved_debug_dbug;
+connection con1;
+--echo /*con1 reap*/ ALTER TABLE t1 ADD COLUMN k4 int;
+--error ER_OUT_OF_RESOURCES
+reap;
+SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
+disconnect con1;
+connection default;
+show create table t1;
+drop table t1;
+drop table t480;
+SET DEBUG_SYNC='RESET';
+--source include/wait_until_count_sessions.inc
+
+--echo #
+--echo #  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
+--echo #                LAST COLUMN OF OLD PK
+--echo #
+
+SET DEBUG_DBUG = '+d,innodb_alter_table_pk_assert_no_sort';
+
+--source suite/innodb/include/alter_table_pk_no_sort.inc
+
+SET DEBUG_DBUG = @saved_debug_dbug;
diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test
index 4549b3b6b47..721808c038c 100644
--- a/mysql-test/suite/innodb/t/innodb-index.test
+++ b/mysql-test/suite/innodb/t/innodb-index.test
@@ -546,3 +546,533 @@ show create table t2c;
 --disable_info
 
 DROP TABLE t1,t2,t2c,t2i;
+--echo #
+--echo # Bug #17657223	EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
+--echo #
+
+SET GLOBAL innodb_monitor_enable = module_ddl;
+let $innodb_metrics_select=
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+
+# Table with Blob data.
+create table t1(f1 int not null, f2 blob)engine=innodb;
+insert into t1 values(1, repeat('a',20000));
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 force;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+
+# Table with small data.
+create table t1(f1 int not null, f2 int not null,
+primary key(f1))engine=innodb;
+insert into t1 values(1,2), (3,4);
+--echo # Add Secondary index.
+--echo # Skip temp file usage due to small table size
+--enable_info
+alter table t1 add key(f2);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+
+# Table with large data which is greater than sort buffer
+create table t480(a serial)engine=innodb;
+insert into t480
+values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
+(),(),(),(),(),(),(),();
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+create table t1(f1 int auto_increment not null,
+		f2 char(200) not null, f3 char(200) not null,
+		f4 char(200) not null,primary key(f1))engine=innodb;
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
+insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
+insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
+insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
+insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
+select count(*) from t1;
+
+--echo # Skip sort
+--echo # Change PK from (f1) to (f1,f2,f3,f4)
+--enable_info
+alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+
+# Temp file not used during clustered index rebuild
+# for the following alter table commands.
+
+--echo # Skip sort
+--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
+--enable_info
+alter table t1 drop primary key,add column f5 int not null,
+add column f6 int not null,add primary key(f1,f2,f5,f6);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop column f6;
+--enable_info
+alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same PK
+--enable_info
+alter table t1 add column f6 int;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 drop column f6;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Must sort
+--echo # Change PK from (f1,f2,f5) to (f1,f5)
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop column f2;
+--enable_info
+alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 add column f2n int after f1, drop primary key, add
+primary key (f1,f5,f2n);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 change f5 f2n int not null,change f2n f5 int not null,
+add column f8 int not null;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
+--enable_info
+alter table t1 add column f7 int, drop primary key,
+add primary key (f1,f5,f7,f2n);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 force;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 row_format=compact;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Reusing the same pk
+--enable_info
+alter table t1 engine=innodb;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Optimize table
+--enable_info
+optimize table t1;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Sort files used for adding secondary index
+--enable_info
+alter table t1 drop primary key, add primary key(f1,f5,f7), add index
+i(f3);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # No sort files used for dropping secondary index
+--enable_info
+alter table t1 drop primary key, add primary key(f1,f5),drop index i;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Change PK(f1,f5) to (f1,added_columns) and drop f5
+--enable_info
+alter table t1 drop primary key, add primary key(f1,f12),
+drop column f5, add column f12 int not null;
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Must sort
+--echo # Change PK(f1,f12) to (f1,existing_columns)
+--enable_info
+alter table t1 drop primary key, add primary key(f1,f3);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort
+--echo # Change PK(f1,f3) to (f1,added_column,f3,added_column)
+--enable_info
+alter table t1 drop primary key, add column f3n int,
+add column f4n int, add primary key(f1,f3n,f3,f4n);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Adding Secondary index alone.
+--enable_info
+alter table t1 add key(f1);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Must sort
+--echo # Change PK(f1,f3) to (existing_column,f1)
+--enable_info
+alter table t1 drop primary key, add primary key(f4,f1);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort for PK.
+--echo # Change PK(f4,f1) to (added_columns,f4,f1)
+--echo # Secondary index rebuild happens
+--enable_info
+alter table t1 drop primary key, add column f5n int,
+add column f6n int, add primary key(f5n,f6n,f4,f1);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+--echo # Skip sort for PK.
+--echo # Change PK(f5n,f6n,f4,f1) to
+--echo # (added_columns,f5n,added_column,f6n,f4,f1)
+--echo # Secondary index rebuild happens
+--enable_info
+alter table t1 drop primary key, add column f7n int,
+add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
+--disable_info
+eval $innodb_metrics_select;
+SET GLOBAL innodb_monitor_reset = module_ddl;
+
+SET GLOBAL innodb_monitor_disable = module_ddl;
+select count(*) from t1;
+drop table t1;
+--disable_warnings
+SET GLOBAL innodb_monitor_reset = default;
+SET GLOBAL innodb_monitor_enable = default;
+SET GLOBAL innodb_monitor_disable = default;
+--enable_warnings
+
+--echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
+--echo # WHEN SKIPPING SORT FOR CLUSTER INDEX
+
+# last mtuple in previous buffer and first mtuple in next buffer
+#	are equal.
+SELECT @@innodb_sort_buffer_size;
+create table t1(f1 int auto_increment not null,
+		f2 char(200) not null, f3 char(200) not null,
+		f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(106, 'aaa','bbb','cccc');
+select count(*) from t1;
+--echo # Skip sort
+--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
+--error ER_DUP_ENTRY
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+select count(*) from t1;
+drop table t1;
+
+# Duplicates exist with in the buffer
+create table t1(f1 int auto_increment not null,
+		f2 char(200) not null, f3 char(200) not null,
+		f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(108,'aaa','bbb','cccc');
+select count(*) from t1;
+--error ER_DUP_ENTRY
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+select count(*) from t1;
+drop table t1, t480;
+
+--echo #
+--echo # Bug #19896922	SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
+--echo #		FIELD IS CHANGED
+--echo #
+
+# Prefix length changes for the varchar column.
+create table t1(a int not null, b varchar(30) not null,
+		primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
+select * from t1;
+alter table t1 drop primary key, add primary key (b(18),a);
+select * from t1;
+drop table t1;
+
+create table t1(a int not null, b varchar(30) not null,
+		primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
+select * from t1;
+alter table t1 drop primary key, add primary key (b(8),a);
+select * from t1;
+drop table t1;
+
+
+--echo #
+--echo # Bug #21103101	SORTING SKIPPED WHEN DROPPING THE SINGLE
+--echo #			COLUMN PRIMARY KEY
+--echo #
+
+# Drop primary key column.
+create table t1(f1 int not null, f2 int not null,
+		primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop column f1;
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop column f1, drop primary key;
+# DROP PRIMARY KEY is implied for a single-column PRIMARY KEY
+alter table t1 drop column f1, drop key f1;
+drop table t1;
+
+# Drop Primary key when lock is none.
+create table t1(f1 int not null, f2 int not null,
+		primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+alter table t1 drop primary key, lock=none;
+drop table t1;
+
+--echo #
+--echo #  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
+--echo #		LAST COLUMN OF OLD PK
+--echo #
+
+# no skip sort cases
+# pk(o1,o2) to pk(o1,o3), drop o2
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o3), drop o1, o2
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1,o3)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o3)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(n1,o3)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o3,n1)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o2,o1)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2, o1), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o2)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o2,o3)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+
+# pk(o2,o1) to pk(o2,o3)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1,o3,o2)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o3,o1,o2)
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o3)
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o2,o3) by drop o1
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop o1, lock=none;
+alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o1,o3) by drop o2
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop o2, lock=none;
+alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+
+# pk(o1,o2,o3) to pk(o3) by drop o1,o2
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 drop o1, drop o2, lock=none;
+alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
+drop table t1;
+
+# no skip sort for prefix change
+# pk(o1(2),o2) to pk(o1(3),o2)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+
+# pk(o1(2),o2) to pk(o1,o2)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+
+# pk(o1(2),o2) to pk(o1(3),o3)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
+drop table t1;
+
+# pk(o1(2),o2) to pk(o1,o3)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o3), lock=none;
+drop table t1;
+
+# pk(o1(3),o2) to pk(o1(2),o2)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1(2),o2)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+
+# pk(o1(3),o2) to pk(o1(2),o3)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1(2),o3)
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+
+# pk(o1,o2(2),o3) to pk(o1,o2(3))
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2(2),o3) to pk(o1,o2)
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+
+# pk(o1(3),o2(3)) to pk(o1(2),o2(3))
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o1(2),o2)
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
+drop table t1;
+
+# pk(o1(3),o2(3)) to pk(o2(3),o1(3))
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
+drop table t1;
+
+# pk(o1,o2) to pk(o2,o1)
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2,o1), lock=none;
+drop table t1;
+
+# no skip sort cases
+--source suite/innodb/include/alter_table_pk_no_sort.inc