MySQL Bugs: #35521: Foreign keys: ALTER is destructive

Bug #35521	Foreign keys: ALTER is destructive
Submitted:	24 Mar 2008 17:32	Modified:	28 Apr 2008 10:53
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I create a table with two columns, s1 and s2.
Column s1 has a referential constraint.
I alter the table, modifying column s2 (not column s1).
Column s1 no longer has a referential constraint.

I'm aware that ALTER TABLE does not support foreign
keys yet. But this is more than non-support, it is
destruction. Removing foreign keys, as a side effect,
is a bug.

How to repeat:
create table t2 (s1 int references t1(s1), s2 int);
show create table t2;
alter table t2 modify column s2 smallint;
show create table t2;

The first 'show create table t2;' will show that t2
has a referential constraint. The second 'show create
table t2;' will show that t2 has no referential constraint.

Thank you for the bug report.

Your MySQL connection id is 6
Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t2 (s1 int references t1(s1), s2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `s1` int(11) DEFAULT NULL CONSTRAINT `fk_t2_6_9956` REFERENCES `t1` (`s1`),
  `s2` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t2 modify column s2 smallint;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `s1` int(11) DEFAULT NULL,
  `s2` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45751

ChangeSet@1.2610, 2008-04-21 13:51:04+04:00, dlenev@mockturtle.local +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.

This fix was approved by e-mail.

Fix for this bug was pushed into mysql-6.1-fk tree. Since this bug was reported against tree which is not publicly available yet I am simply closing this bug.