Bug #14227431: CHARACTER SET MISMATCH WHEN ALTERING FOREIGN KEYS

CAN LEAD TO MISSING TABLES

Overview
--------
If the FOREIGN_KEY_CHECKS system variable is set to 0, it is
possible to break a foreign key constraint by changing the type
or character set of the foreign key column, or by dropping the
foreign key index (without carrying out corresponding changes on
another table in the relationship).

If we subsequently set FOREIGN_KEY_CHECKS to 1 and execute ALTER
TABLE involving the COPY algorithm on such a table, the following
happens:

1) If ALTER TABLE does not contain a RENAME clause, the attempt 
   to install the new version of the table instead of the old one
   will fail due to the fact that the inconsistency will be 
   detected. An attempt to revert the partially executed alter 
   table operation by restoring the old table definition will 
   fail as well due to FOREIGN_KEY_CHECKS == 1. As a result, the 
   table being altered will be lost.
2) If ALTER TABLE contains the RENAME clause, the inconsistency 
   will not be detected (most probably due to other bugs). But if
   an attempt to install the new version of the table fails (for 
   example, due to a failure when updating triggers associated 
   with the table), reverting the partially executed alter table 
   by restoring the old table definition will fail too. So the 
   table being altered might be lost as well.


Suggested fix
-------------
The suggested fix is to temporarily unset the option bit
representing FOREIGN_KEY_CHECKS when the old table definition is
restored while reverting the partially executed operation.

1 files changed, 2 insertions, 2 deletions

diff --git a/sql/sql_rename.cc b/sql/sql_rename.cc
index f32dc75148a..76a4b50d73a 100644
--- a/sql/sql_rename.cc
+++ b/sql/sql_rename.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -293,7 +293,7 @@ do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db, char *new_table_name,
             (void) mysql_rename_table(ha_resolve_by_legacy_type(thd,
                                                                 table_type),
                                       new_db, new_alias,
-                                      ren_table->db, old_alias, 0);
+                                      ren_table->db, old_alias, NO_FK_CHECKS);
           }
         }
       }