InnoDB: Prevent ALTER TABLE ... ENGINE=...

if there are foreign key constraints on the table. (Bug #5574)


sql/ha_innodb.cc:
  Add method can_switch_engines()
sql/ha_innodb.h:
  Add method can_switch_engines()
sql/handler.h:
  Add method can_switch_engines()
sql/sql_table.cc:
  Check handler::can_switch_engines() before switching storage engines

4 files changed, 33 insertions, 0 deletions

diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index 3e3a48f4ab9..322d5188a75 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -4789,6 +4789,32 @@ ha_innobase::get_foreign_key_create_info(void)
   	return(str);
 }
 
+/*********************************************************************
+Checks if ALTER TABLE may change the storage engine of the table.
+Changing storage engines is not allowed for tables for which there
+are foreign key constraints (parent or child tables). */
+
+bool
+ha_innobase::can_switch_engines(void)
+/*=================================*/
+{
+	row_prebuilt_t* prebuilt	= (row_prebuilt_t*) innobase_prebuilt;
+	bool	can_switch;
+
+ 	DBUG_ENTER("ha_innobase::can_switch_engines");
+	prebuilt->trx->op_info =
+			"determining if there are foreign key constraints";
+	row_mysql_lock_data_dictionary(prebuilt->trx);
+
+	can_switch = !UT_LIST_GET_FIRST(prebuilt->table->referenced_list)
+			&& !UT_LIST_GET_FIRST(prebuilt->table->foreign_list);
+
+	row_mysql_unlock_data_dictionary(prebuilt->trx);
+	prebuilt->trx->op_info = "";
+
+	DBUG_RETURN(can_switch);
+}
+
 /***********************************************************************
 Checks if a table is referenced by a foreign key. The MySQL manual states that
 a REPLACE is either equivalent to an INSERT, or DELETE(s) + INSERT. Only a
diff --git a/sql/ha_innodb.h b/sql/ha_innodb.h
index f10785b695d..edf428669d8 100644
--- a/sql/ha_innodb.h
+++ b/sql/ha_innodb.h
@@ -161,6 +161,7 @@ class ha_innobase: public handler
 	int check(THD* thd, HA_CHECK_OPT* check_opt);
         char* update_table_comment(const char* comment);
 	char* get_foreign_key_create_info();
+	bool can_switch_engines();
   	uint referenced_by_foreign_key();
 	void free_foreign_key_create_info(char* str);	
   	THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
diff --git a/sql/handler.h b/sql/handler.h
index 50ce33b5067..4c31da6a492 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -450,6 +450,8 @@ public:
   virtual void append_create_info(String *packet) {}
   virtual char* get_foreign_key_create_info()
   { return(NULL);}  /* gets foreign key create string from InnoDB */
+  /* used in ALTER TABLE; 1 if changing storage engine is allowed */
+  virtual bool can_switch_engines() { return 1; }
   /* used in REPLACE; is > 0 if table is referred by a FOREIGN KEY */
   virtual uint referenced_by_foreign_key() { return 0;}
   virtual void init_table_handle_for_HANDLER()
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5aba764e293..84f51a95691 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3109,6 +3109,10 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
   /* Safety fix for innodb */
   if (lower_case_table_names)
     my_casedn_str(files_charset_info, tmp_name);
+  if (new_db_type != old_db_type && !table->file->can_switch_engines()) {
+    my_error(ER_ROW_IS_REFERENCED, MYF(0));
+    goto err;
+  }
   create_info->db_type=new_db_type;
   if (!create_info->comment)
     create_info->comment=table->comment;