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, 3 insertions, 1 deletions

diff --git a/sql/sql_table.h b/sql/sql_table.h
index 043a8085452..6f09db12bb9 100644
--- a/sql/sql_table.h
+++ b/sql/sql_table.h
@@ -1,4 +1,4 @@
-/* Copyright (c) 2006, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2006, 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
@@ -122,6 +122,8 @@ enum enum_explain_filename_mode
 #define FN_IS_TMP       (FN_FROM_IS_TMP | FN_TO_IS_TMP)
 #define NO_FRM_RENAME   (1 << 2)
 #define FRM_ONLY        (1 << 3)
+/** Don't check foreign key constraints while renaming table */
+#define NO_FK_CHECKS    (1 << 4)
 
 uint filename_to_tablename(const char *from, char *to, uint to_length
 #ifndef DBUG_OFF