BUG#27216817: INNODB: FAILING ASSERTION: PREBUILT->TABLE->N_MYSQL_HANDLES_OPENED == 1

disable online alter add primary key for innodb, if the
table is opened/locked more than once in the current connection

(see assert in ha_innobase::add_index())

6 files changed, 76 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_bug27216817.result b/mysql-test/suite/innodb/r/innodb_bug27216817.result
new file mode 100644
index 00000000000..f930171ff23
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_bug27216817.result
@@ -0,0 +1,24 @@
+create table t1 (a int not null, b int not null) engine=innodb;
+insert t1 values (1,2),(3,4);
+lock table t1 write, t1 tr read;
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	3
+unlock tables;
+alter table t1 drop primary key;
+lock table t1 write;
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+unlock tables;
+alter table t1 drop primary key;
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb_bug27216817.test b/mysql-test/suite/innodb/t/innodb_bug27216817.test
new file mode 100644
index 00000000000..a93932b4a04
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_bug27216817.test
@@ -0,0 +1,28 @@
+#
+# BUG#27216817: INNODB: FAILING ASSERTION:
+#               PREBUILT->TABLE->N_MYSQL_HANDLES_OPENED == 1
+#
+
+source include/have_innodb.inc;
+create table t1 (a int not null, b int not null) engine=innodb;
+insert t1 values (1,2),(3,4);
+
+lock table t1 write, t1 tr read;
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+unlock tables;
+alter table t1 drop primary key;
+
+lock table t1 write;
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+unlock tables;
+alter table t1 drop primary key;
+
+flush status;
+alter table t1 add primary key (b);
+show status like 'Handler_read_rnd_next';
+
+drop table t1;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index c3bacee91ff..8da89918b84 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -11147,6 +11147,17 @@ ha_innobase::check_if_incompatible_data(
 	return(COMPATIBLE_DATA_YES);
 }
 
+UNIV_INTERN
+uint
+ha_innobase::alter_table_flags(uint flags)
+{
+	uint mask = 0;
+	if (prebuilt->table->n_mysql_handles_opened > 1) {
+		mask = HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE;
+	}
+	return innobase_alter_table_flags(flags) & ~mask;
+}
+
 /************************************************************//**
 Validate the file format name and return its corresponding id.
 @return	valid file format id */
diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h
index 42aae4dc20e..f80330d6128 100644
--- a/storage/innobase/handler/ha_innodb.h
+++ b/storage/innobase/handler/ha_innodb.h
@@ -228,6 +228,7 @@ class ha_innobase: public handler
 	/** @} */
 	bool check_if_incompatible_data(HA_CREATE_INFO *info,
 					uint table_changes);
+	uint alter_table_flags(uint flags);
 };
 
 /* Some accessor functions which the InnoDB plugin needs, but which
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index a17ab44f49d..94e49d4897a 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -12480,6 +12480,17 @@ ha_innobase::check_if_incompatible_data(
 	DBUG_RETURN(COMPATIBLE_DATA_YES);
 }
 
+UNIV_INTERN
+uint
+ha_innobase::alter_table_flags(uint flags)
+{
+	uint mask = 0;
+	if (prebuilt->table->n_mysql_handles_opened > 1) {
+		mask = HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE;
+	}
+	return innobase_alter_table_flags(flags) & ~mask;
+}
+
 /************************************************************//**
 Validate the file format name and return its corresponding id.
 @return	valid file format id */
diff --git a/storage/xtradb/handler/ha_innodb.h b/storage/xtradb/handler/ha_innodb.h
index 914055a9271..2c3011b0bbb 100644
--- a/storage/xtradb/handler/ha_innodb.h
+++ b/storage/xtradb/handler/ha_innodb.h
@@ -230,6 +230,7 @@ class ha_innobase: public handler
 	/** @} */
 	bool check_if_incompatible_data(HA_CREATE_INFO *info,
 					uint table_changes);
+	uint alter_table_flags(uint flags);
 	bool check_if_supported_virtual_columns(void) { return TRUE; }
 
 private: