From c8fc3db1b0b3a16a1f1b8863df39e10598521758 Mon Sep 17 00:00:00 2001 From: Sivert Sorumgard Date: Wed, 12 Jun 2013 09:35:33 +0200 Subject: 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. --- sql/sql_rename.cc | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'sql/sql_rename.cc') 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); } } } -- cgit v1.2.1