Below is the list of changes that have just been committed into a local
6.1 repository of dlenev. When dlenev does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-04-21 13:51:04+04:00, dlenev@stripped +6 -0
Fix for bug #35521 "Foreign keys: ALTER is destructive".
In --foreign_key_all_engines mode ALTERing table with foreign key
constraints led to losing of these constraints.
The problem was that on the 2nd milestone I have not changed ALTER
TABLE implementation to copy foreign key definitions from old version
of table to new one (I have assumed that this should be done on one
of later milestones). This patch fixes this problem by adding this
copying.
mysql-test/r/foreign_key_all_engines.result@stripped, 2008-04-21 13:50:53+04:00, dlenev@stripped +24 -0
Added test for bug #35521 "Foreign keys: ALTER is destructive".
mysql-test/t/foreign_key_all_engines.test@stripped, 2008-04-21 13:50:53+04:00, dlenev@stripped +22 -0
Added test for bug #35521 "Foreign keys: ALTER is destructive".
sql/fk_dd.cc@stripped, 2008-04-21 13:50:53+04:00, dlenev@stripped +11 -5
Foreign_key::restore_from_frm():
Mark foreign keys which definitions we load from .FRM as already
existing.
fk_create_constraint_names(), fk_check_constraint_added():
Don't do any checks/create .CNS files for foreign keys which
already exist and which definitions we are simply saving to .FRM.
Particularly this allows to avoid errors due to already existing
.CNS files during ALTER TABLE.
sql/sql_class.cc@stripped, 2008-04-21 13:50:53+04:00, dlenev@stripped +2 -1
Introduced Foreign_key::exists member to be able distinguish newly
created foreign keys from already existing foreign keys for which
we are just saving information to .FRM (e.g. during ALTER TABLE).
sql/sql_class.h@stripped, 2008-04-21 13:50:54+04:00, dlenev@stripped +9 -3
Introduced Foreign_key::exists member to be able distinguish newly
created foreign keys from already existing foreign keys for which
we are just saving information to .FRM (e.g. during ALTER TABLE).
sql/sql_table.cc@stripped, 2008-04-21 13:50:54+04:00, dlenev@stripped +14 -0
mysql_prepare_alter_table():
Copy foreign keys for the new definition of table from the old one.
This is temporary measure to avoid losing foreign keys during ALTER.
This code is going to be modified heavily once we reach milestone 8
of WL#148 "DDL checks and changes: ALTER word".
diff -Nrup a/mysql-test/r/foreign_key_all_engines.result b/mysql-test/r/foreign_key_all_engines.result
--- a/mysql-test/r/foreign_key_all_engines.result 2008-04-20 18:57:07 +04:00
+++ b/mysql-test/r/foreign_key_all_engines.result 2008-04-21 13:50:53 +04:00
@@ -329,3 +329,27 @@ t3 CREATE TABLE `t3` (
`fk` int(11) DEFAULT NULL COMMENT 'my comment' CONSTRAINT `c1` REFERENCES `t1` (`i`) CONSTRAINT `c2` REFERENCES `t2` (`j`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop tables t1, t2, t3;
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+set @@pseudo_thread_id= 1;
+drop tables if exists t1, t2;
+create table t1 (i int primary key);
+create table t2 (fk1 int constraint c1 references t1 (i),
+fk2 int,
+foreign key (fk2) references t1 (i) on delete set null);
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `c1` REFERENCES `t1` (`i`),
+ `fk2` int(11) DEFAULT NULL,
+ CONSTRAINT `fk_t2_1_288` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t2 add column a int;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `c1` REFERENCES `t1` (`i`),
+ `fk2` int(11) DEFAULT NULL,
+ `a` int(11) DEFAULT NULL,
+ CONSTRAINT `fk_t2_1_288` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1, t2;
diff -Nrup a/mysql-test/t/foreign_key_all_engines.test b/mysql-test/t/foreign_key_all_engines.test
--- a/mysql-test/t/foreign_key_all_engines.test 2008-04-20 18:57:07 +04:00
+++ b/mysql-test/t/foreign_key_all_engines.test 2008-04-21 13:50:53 +04:00
@@ -256,3 +256,25 @@ create table t3 (fk int comment 'my comm
constraint c2 references t2 (j));
show create table t3;
drop tables t1, t2, t3;
+
+
+#
+# Test for bug #35521 "Foreign keys: ALTER is destructive"
+# Information about foreign keys was lost if one altered
+# child table.
+#
+# Make automatically generated constraint names repeatable
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+set @@pseudo_thread_id= 1;
+--disable_warnings
+drop tables if exists t1, t2;
+--enable_warnings
+create table t1 (i int primary key);
+create table t2 (fk1 int constraint c1 references t1 (i),
+ fk2 int,
+ foreign key (fk2) references t1 (i) on delete set null);
+show create table t2;
+# Alter below should not affect foreign keys in any way.
+alter table t2 add column a int;
+show create table t2;
+drop table t1, t2;
diff -Nrup a/sql/fk_dd.cc b/sql/fk_dd.cc
--- a/sql/fk_dd.cc 2008-04-20 17:42:47 +04:00
+++ b/sql/fk_dd.cc 2008-04-21 13:50:53 +04:00
@@ -348,7 +348,8 @@ Foreign_key *Foreign_key::restore_from_f
is_within_one_db, cols, table, ref_cols,
fkey_descr_start[FK_FRM_ON_DELETE_OPT_OFFSET],
fkey_descr_start[FK_FRM_ON_UPDATE_OPT_OFFSET],
- fkey_descr_start[FK_FRM_MATCH_OPT_OFFSET]);
+ fkey_descr_start[FK_FRM_MATCH_OPT_OFFSET],
+ TRUE);
}
@@ -714,7 +715,7 @@ bool fk_create_constraint_names(List<For
Foreign_key *fkey;
while ((fkey= fkey_iterator++))
- if (fk_create_constraint_name(db, fkey->name.str))
+ if ((!fkey->exists) && fk_create_constraint_name(db, fkey->name.str))
return TRUE;
return FALSE;
}
@@ -812,6 +813,14 @@ bool fk_check_constraint_added(Foreign_k
DBUG_ENTER("fk_check_constraint_added");
+ /*
+ Don't do checks in this function when we just saving information
+ about already existing foreign keys in .FRM (e.g. during ALTER).
+ This might change once we reach later milestones of WL#148.
+ */
+ if (fkey->exists)
+ DBUG_RETURN(FALSE);
+
if (fkey->match_opt == Foreign_key::FK_MATCH_PARTIAL)
{
my_error(ER_FK_MATCH_PARTIAL, MYF(0), fkey->name.str);
@@ -864,9 +873,6 @@ bool fk_check_constraint_added(Foreign_k
DBUG_RETURN(TRUE);
}
}
-
- /* At this point we should know names of constraints already. */
- DBUG_ASSERT(fkey->name.str);
DBUG_RETURN(FALSE);
}
diff -Nrup a/sql/sql_class.cc b/sql/sql_class.cc
--- a/sql/sql_class.cc 2008-03-21 13:55:41 +03:00
+++ b/sql/sql_class.cc 2008-04-21 13:50:53 +04:00
@@ -129,7 +129,8 @@ Foreign_key::Foreign_key(const Foreign_k
fkey_name_used(rhs.fkey_name_used),
is_table_constraint(rhs.is_table_constraint),
is_name_generated(rhs.is_name_generated),
- is_within_one_db(rhs.is_within_one_db)
+ is_within_one_db(rhs.is_within_one_db),
+ exists(rhs.exists)
{
list_copy_and_replace_each_value(columns, mem_root);
list_copy_and_replace_each_value(ref_columns, mem_root);
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h 2008-03-21 13:55:41 +03:00
+++ b/sql/sql_class.h 2008-04-21 13:50:54 +04:00
@@ -223,6 +223,12 @@ public:
the same database.
*/
bool is_within_one_db;
+ /**
+ Indicates that this object represents already existing constraint
+ so we don't need to perform some of checks and actions for it when
+ saving its description to .FRM file.
+ */
+ bool exists;
Foreign_key(const LEX_STRING &name_arg, bool fkey_name_used_arg,
bool table_con_arg, List<Key_part_spec> &cols,
TABLE_LIST *table, List<Key_part_spec> &ref_cols,
@@ -232,20 +238,20 @@ public:
delete_opt(delete_opt_arg), update_opt(update_opt_arg),
match_opt(match_opt_arg), fkey_name_used(fkey_name_used_arg),
is_table_constraint(table_con_arg), is_name_generated(FALSE),
- is_within_one_db(FALSE)
+ is_within_one_db(FALSE), exists(FALSE)
{}
Foreign_key(const char *name_arg, size_t name_len_arg,
bool fkey_name_used_arg, bool table_con_arg,
bool within_one_db_arg, List<Key_part_spec> &cols,
TABLE_LIST *table, List<Key_part_spec> &ref_cols,
uint delete_opt_arg, uint update_opt_arg,
- uint match_opt_arg)
+ uint match_opt_arg, bool exists_arg)
: columns(cols),
ref_table(table), ref_columns(ref_cols),
delete_opt(delete_opt_arg), update_opt(update_opt_arg),
match_opt(match_opt_arg), fkey_name_used(fkey_name_used_arg),
is_table_constraint(table_con_arg), is_name_generated(FALSE),
- is_within_one_db(within_one_db_arg)
+ is_within_one_db(within_one_db_arg), exists(exists_arg)
{
name.str= (char *)name_arg;
name.length= name_len_arg;
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc 2008-04-18 15:49:29 +04:00
+++ b/sql/sql_table.cc 2008-04-21 13:50:54 +04:00
@@ -5861,6 +5861,8 @@ mysql_prepare_alter_table(THD *thd, TABL
List<Create_field> new_create_list;
/* New key definitions are added here */
List<Key> new_key_list;
+ /* FKs definitions for new version of table are added here. */
+ List<Foreign_key> new_foreign_key_list;
List_iterator<Alter_drop> drop_it(alter_info->drop_list);
List_iterator<Create_field> def_it(alter_info->create_list);
List_iterator<Alter_column> alter_it(alter_info->alter_list);
@@ -6053,6 +6055,17 @@ mysql_prepare_alter_table(THD *thd, TABL
goto err;
}
+ if (opt_fk_all_engines)
+ {
+ /*
+ Until milestone 8 of WL#148 "DDL checks and changes: ALTER word"
+ is implemented we simply copy foreign keys from old definition
+ of table without adding or dropping anything.
+ */
+ new_foreign_key_list= table->s->fkeys;
+ list_copy_and_replace_each_value(new_foreign_key_list, thd->mem_root);
+ }
+
/*
Collect all keys which isn't in drop list. Add only those
for which some fields exists.
@@ -6225,6 +6238,7 @@ mysql_prepare_alter_table(THD *thd, TABL
rc= FALSE;
alter_info->create_list.swap(new_create_list);
alter_info->key_list.swap(new_key_list);
+ alter_info->foreign_key_list.swap(new_foreign_key_list);
err:
DBUG_RETURN(rc);
}
