MySQL Bugs: #40550: Invalid value set to foreign key column after alter table

Bug #40550	Invalid value set to foreign key column after alter table
Submitted:	6 Nov 2008 13:05	Modified:	8 Nov 2008 16:07
Reporter:	Marc MENDEZ	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.0.67	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, foreign key, null, zero

Description:
Hi,

On a integer column used in foreign key, nullable, if I alter it so that the column can be NULL, the column value is set to zero (the number), even if no record with this value exists in the referenced table.

How to repeat:
I create two tables (family and familymembers). The second one is linked to the first one with a foreign key :

CREATE TABLE `family` (                                  
`id` int(11) NOT NULL auto_increment,                  
`name` varchar(50) default NULL,                       
PRIMARY KEY  (`id`)                                    
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1  

CREATE TABLE `familymembers` (                                                       
`id` int(11) NOT NULL auto_increment,
`idfamily` int(11) default NULL,
`name` varchar(50) default NULL,
PRIMARY KEY  (`id`),
KEY `FK_familymembers` (`idfamily`),
CONSTRAINT `FK_familymembers` FOREIGN KEY (`idfamily`) REFERENCES `family` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1                              

I add the folowing data 

In family table :

id 1, name "Simpson"
id 2, name "Flanders"

In family members :

for idfamily = 1, I add, "Marge", "Omer" and "Maggy"
I add as well "Barney" but attached to no family (the idfamily colum is set to NULL). BTW, if I set the idfamily field to 0, I get an error : it's normal, there is no record in the family table with a primary key equals to 0.

Then, I run :

alter table `familymembers` change `idfamily` `idfamily` int(11) NOT NULL;

In the row corresponding to "Barney", the value of the idfamily column is now set to 0 (before, it was NULL). But there is no record in the family table with a primary key equals to 0. So, the integrity is now false !!

If you have problem to understand my example, I can then you an export of the tables.

Thank you for a problem report. I can not repeat with a newer version, 5.0.70, though:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE `family` (
    -> `id` int(11) NOT NULL auto_increment,
    -> `name` varchar(50) default NULL,
    -> PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.30 sec)

mysql> CREATE TABLE `familymembers` (

    -> `id` int(11) NOT NULL auto_increment,
    -> `idfamily` int(11) default NULL,
    -> `name` varchar(50) default NULL,
    -> PRIMARY KEY  (`id`),
    -> KEY `FK_familymembers` (`idfamily`),
    -> CONSTRAINT `FK_familymembers` FOREIGN KEY (`idfamily`) REFERENCES `family
` (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1  ;
Query OK, 0 rows affected (0.30 sec)

mysql> insert into family values(1, 'Simpsons'), (2, 'Flanders');
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into familymembers values (1, 1, 'Marge');
Query OK, 1 row affected (0.08 sec)

mysql> insert into familymembers values (2, 1, 'Omer');
Query OK, 1 row affected (0.08 sec)

mysql> insert into familymembers values (3, 1, 'Maggy');
Query OK, 1 row affected (0.06 sec)

mysql> insert into familymembers values (4, 0, 'Barney');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test/familymembers`, CONSTRAINT `FK_familymembers` FOREIGN KEY (`idfamily
`) REFERENCES `family` (`id`))
mysql> insert into familymembers values (4, NULL, 'Barney');
Query OK, 1 row affected (0.09 sec)

mysql> alter table `familymembers` change `idfamily` `idfamily` int(11) NOT NULL
;
ERROR 1265 (01000): Data truncated for column 'idfamily' at row 4
mysql> select * from familymembers;
+----+----------+--------+
| id | idfamily | name   |
+----+----------+--------+
|  1 |        1 | Marge  |
|  2 |        1 | Omer   |
|  3 |        1 | Maggy  |
|  4 |     NULL | Barney |
+----+----------+--------+
4 rows in set (0.00 sec)

Did I miss something? What is the result of the following:

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

in your environment?

Your test is right : you did not miss anything.
It seems the problem was fixed with the newer version.
BTW, I get nothing when I run "select @@sql_mode"