Below is the list of changes that have just been committed into a local
5.1 repository of mydev. When mydev 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
1.2266 06/03/30 20:55:54 ingo@stripped +5 -0
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
InnoDB requires a full table rebuild for foreign key changes.
It was not possible in compare_tables() to detect such changes.
On Heikkis proposal I added a new flag to the syntax parser
where foreign key definition changes are done. I test for
this flag in compare_tables() now.
sql/sql_yacc.yy
1.486 06/03/30 20:55:48 ingo@stripped +4 -1
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Marked foreign key definitions with the new flag.
This flag is tested in ALTER TABLE only. It is
ignored otherwise.
sql/sql_table.cc
1.322 06/03/30 20:55:48 ingo@stripped +1 -1
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Check for foreign key definition changes in
compare_tables(). If present, a table rebuild is required.
sql/sql_lex.h
1.225 06/03/30 20:55:48 ingo@stripped +1 -0
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Added a flag for foreign key definition changes.
mysql-test/t/innodb.test
1.133 06/03/30 20:55:48 ingo@stripped +25 -0
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Added the new test case.
mysql-test/r/innodb.result
1.167 06/03/30 20:55:48 ingo@stripped +32 -3
Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Fixed an old test result.
Added the new test result.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: ingo
# Host: chilla.local
# Root: /home/mydev/mysql-5.1-bug18477
--- 1.224/sql/sql_lex.h 2006-03-20 20:39:47 +01:00
+++ 1.225/sql/sql_lex.h 2006-03-30 20:55:48 +02:00
@@ -706,6 +706,7 @@
#define ALTER_CHECK_PARTITION (1L << 23)
#define ALTER_REPAIR_PARTITION (1L << 24)
#define ALTER_REMOVE_PARTITIONING (1L << 25)
+#define ALTER_FOREIGN_KEY (1L << 26)
typedef struct st_alter_info
{
--- 1.321/sql/sql_table.cc 2006-03-29 13:27:31 +02:00
+++ 1.322/sql/sql_table.cc 2006-03-30 20:55:48 +02:00
@@ -3781,7 +3781,7 @@
create_info->used_fields & HA_CREATE_USED_ENGINE ||
create_info->used_fields & HA_CREATE_USED_CHARSET ||
create_info->used_fields & HA_CREATE_USED_DEFAULT_CHARSET ||
- (alter_info->flags & ALTER_RECREATE) ||
+ (alter_info->flags & (ALTER_RECREATE | ALTER_FOREIGN_KEY)) ||
order_num)
DBUG_RETURN(ALTER_TABLE_DATA_CHANGED);
--- 1.485/sql/sql_yacc.yy 2006-03-29 13:27:31 +02:00
+++ 1.486/sql/sql_yacc.yy 2006-03-30 20:55:48 +02:00
@@ -4167,6 +4167,9 @@
HA_KEY_ALG_UNDEF, 1,
lex->col_list));
lex->col_list.empty(); /* Alloced by sql_alloc */
+
+ /* Only used for ALTER TABLE. Ignored otherwise. */
+ lex->alter_info.flags|= ALTER_FOREIGN_KEY;
}
| constraint opt_check_constraint
{
@@ -5137,7 +5140,7 @@
}
| DROP FOREIGN KEY_SYM opt_ident
{
- Lex->alter_info.flags|= ALTER_DROP_INDEX;
+ Lex->alter_info.flags|= ALTER_DROP_INDEX | ALTER_FOREIGN_KEY;
}
| DROP PRIMARY_SYM KEY_SYM
{
--- 1.166/mysql-test/r/innodb.result 2006-03-10 17:22:02 +01:00
+++ 1.167/mysql-test/r/innodb.result 2006-03-30 20:55:48 +02:00
@@ -3133,9 +3133,7 @@
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
- KEY `t2_ibfk_0` (`a`),
- CONSTRAINT `t2_ibfk_0` FOREIGN KEY (`a`) REFERENCES `t1` (`a`),
- CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
+ KEY `t2_ibfk_0` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2,t1;
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
@@ -3214,3 +3212,34 @@
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
DROP TABLE t2;
DROP TABLE t1;
+create table t1 (
+c1 bigint not null,
+c2 bigint not null,
+primary key (c1),
+unique key (c2)
+) engine=innodb;
+create table t2 (
+c1 bigint not null,
+primary key (c1)
+) engine=innodb;
+alter table t1 add constraint c2_fk foreign key (c2)
+references t2(c1) on delete cascade;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` bigint(20) NOT NULL,
+ `c2` bigint(20) NOT NULL,
+ PRIMARY KEY (`c1`),
+ UNIQUE KEY `c2` (`c2`),
+ CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+alter table t1 drop foreign key c2_fk;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` bigint(20) NOT NULL,
+ `c2` bigint(20) NOT NULL,
+ PRIMARY KEY (`c1`),
+ UNIQUE KEY `c2` (`c2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1, t2;
--- 1.132/mysql-test/t/innodb.test 2006-03-29 22:57:21 +02:00
+++ 1.133/mysql-test/t/innodb.test 2006-03-30 20:55:48 +02:00
@@ -2113,3 +2113,28 @@
DROP TABLE t2;
DROP TABLE t1;
+
+#
+# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
+#
+create table t1 (
+ c1 bigint not null,
+ c2 bigint not null,
+ primary key (c1),
+ unique key (c2)
+) engine=innodb;
+#
+create table t2 (
+ c1 bigint not null,
+ primary key (c1)
+) engine=innodb;
+#
+alter table t1 add constraint c2_fk foreign key (c2)
+ references t2(c1) on delete cascade;
+show create table t1;
+#
+alter table t1 drop foreign key c2_fk;
+show create table t1;
+#
+drop table t1, t2;
+
| Thread |
|---|
| • bk commit into 5.1 tree (ingo:1.2266) BUG#18477 | ingo | 30 Mar |
