MDEV-15874 CREATE TABLE creates extra transactionbb-10.3-MDEV-13134

InnoDB does not allow FOREIGN KEY constraints to exist for TEMPORARY TABLE.
InnoDB introduced a dedicated tablespace for temporary tables, and actually
stopped creating persistent metadata and data for temporary tables.

row_table_add_foreign_constraints(): Do not create a persistent
transaction.

dict_create_foreign_constraints_low(): Add the persistent transaction to
the update the foreign key relation in dictionary.

dict_create_foreign_constraints_low(): Remove a duplicated check for
partitioned tables.

4 files changed, 26 insertions, 23 deletions

diff --git a/mysql-test/suite/innodb/r/temporary_table.result b/mysql-test/suite/innodb/r/temporary_table.result
index 64eb3270934..3b22f8c02cc 100644
--- a/mysql-test/suite/innodb/r/temporary_table.result
+++ b/mysql-test/suite/innodb/r/temporary_table.result
@@ -650,3 +650,13 @@ SELECT * FROM t1;
 f1
 0
 DROP TABLE t1;
+#
+# MDEV-15874 CREATE TABLE creates extra transaction
+#
+call mtr.add_suppression("Warning 150 Create  table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*");
+set @old_check = @@GLOBAL.foreign_key_checks;
+SET FOREIGN_KEY_CHECKS = 0;
+CREATE TEMPORARY TABLE t1(f1 INT NOT NULL,
+FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+SET FOREIGN_KEY_CHECKS = @old_check;
diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test
index 9a640657c4d..a501264dd42 100644
--- a/mysql-test/suite/innodb/t/temporary_table.test
+++ b/mysql-test/suite/innodb/t/temporary_table.test
@@ -475,3 +475,14 @@ UPDATE t1 SET f1 = 0;
 ROLLBACK;
 SELECT * FROM t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-15874 CREATE TABLE creates extra transaction
+--echo #
+call mtr.add_suppression("Warning 150 Create  table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*");
+set @old_check = @@GLOBAL.foreign_key_checks;
+SET FOREIGN_KEY_CHECKS = 0;
+--error ER_CANT_CREATE_TABLE
+CREATE TEMPORARY TABLE t1(f1 INT NOT NULL,
+			  FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB;
+SET FOREIGN_KEY_CHECKS = @old_check;
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index 8fa6f6ad4c6..a69009efccb 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -4589,6 +4589,11 @@ loop:
 		/**********************************************************/
 		/* The following call adds the foreign key constraints
 		to the data dictionary system tables on disk */
+		trx->op_info = "adding foreign keys";
+
+		trx_start_if_not_started_xa(trx, true);
+
+		trx_set_dict_operation(trx, TRX_DICT_OP_TABLE);
 
 		error = dict_create_add_foreigns_to_dictionary(
 			local_fk_set, table, trx);
@@ -4803,23 +4808,6 @@ col_loop1:
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
-	/* Don't allow foreign keys on partitioned tables yet. */
-	ptr1 = dict_scan_to(ptr, "PARTITION");
-	if (ptr1) {
-		ptr1 = dict_accept(cs, ptr1, "PARTITION", &success);
-		if (success && my_isspace(cs, *ptr1)) {
-			ptr2 = dict_accept(cs, ptr1, "BY", &success);
-			if (success) {
-				my_error(ER_FOREIGN_KEY_ON_PARTITIONED,MYF(0));
-				return(DB_CANNOT_ADD_CONSTRAINT);
-			}
-		}
-	}
-	if (dict_table_is_partition(table)) {
-		my_error(ER_FOREIGN_KEY_ON_PARTITIONED,MYF(0));
-		return(DB_CANNOT_ADD_CONSTRAINT);
-	}
-
 	/* Let us create a constraint struct */
 
 	foreign = dict_mem_foreign_create();
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index ceab5d00659..4bcd623e2a7 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -2652,12 +2652,6 @@ row_table_add_foreign_constraints(
 	ut_ad(rw_lock_own(dict_operation_lock, RW_LOCK_X));
 	ut_a(sql_string);
 
-	trx->op_info = "adding foreign keys";
-
-	trx_start_if_not_started_xa(trx, true);
-
-	trx_set_dict_operation(trx, TRX_DICT_OP_TABLE);
-
 	err = dict_create_foreign_constraints(
 		trx, sql_string, sql_length, name, reject_fks);