MySQL Bugs: #11356: CHANGE TABLE does not care about foreign keys

Bug #11356	CHANGE TABLE does not care about foreign keys
Submitted:	15 Jun 2005 16:43	Modified:	6 Sep 2006 9:56
Reporter:	Kai Ruhnau	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.12	OS:	Linux (Gentoo Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
When ALTERing a column with NULL-values, that reference another table, to a NOT NULL-column, all NULL-values are converted to 0. This conversion does not care about referential integrity.
The result is a table with dead-links.

How to repeat:
See attachment

Suggested fix:
InnoDB should complain before ALTERing the table.

Howto remove referential integrity

Attachment: howto.txt (text/plain), 2.24 KiB.

Hi!

This is a known problem.

http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Please test with sql_mode="TRADITIONAL"

Does the ALTER TABLE then get aborted?

Regards,

Heikki

Sorry, I meant please test 5.0.7 with that SQL mode.

Regards,

Heikki

Hi!

In 5.0,

set session sql_mode='traditional';

makes the behavior sensible. The ALTER TABLE fails because it would need to change the NULL values to 0.

Hmm... we should decide if TRADITIONAL becomes the default mode for transactional tables in 5.1. That would solve this bug in a simple way, and would make MySQL more compatible with other database brands.

I am leaving this bug to the 'Verified' state, so that we do not forget this.

Thank you,

Heikki

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.8-beta-debug-log

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

mysql> set session sql_mode='traditional';
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE `table_1` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `value` char(10) default NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.37 sec)

mysql>
mysql> CREATE TABLE `table_2` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `ID_table_1` int(10) unsigned default NULL,
    ->   `value` char(10) default NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   KEY `ID_table_1` (`ID_table_1`),
    ->   CONSTRAINT `table_2_ibfk_1` FOREIGN KEY (`ID_table_1`) REFERENCES `tabl
e_1` (`ID`) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.25 sec)

mysql>
mysql> INSERT INTO table_1 (value) VALUES ('a'),('b'),('c'),('d');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO table_2 (ID_table_1, value) VALUES (2,'ha'),(2,'hb'),(NULL,'h
c'),(NULL,'hd'), (1,'he'), (3,'hf');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE table_2 CHANGE ID_table_1 ID_table_1 INT(10) UNSIGNED NOT NUL
L;
ERROR 1265 (01000): Data truncated for column 'ID_table_1' at row 3
mysql>
mysql> select * from table_2;
+----+------------+-------+
| ID | ID_table_1 | value |
+----+------------+-------+
|  1 |          2 | ha    |
|  2 |          2 | hb    |
|  3 |       NULL | hc    |
|  4 |       NULL | hd    |
|  5 |          1 | he    |
|  6 |          3 | hf    |
+----+------------+-------+
6 rows in set (0.04 sec)

mysql>

Changing the status to 'Won't fix' because users should really run MySQL/InnoDB in the 'traditional' SQL mode, in which case this bug does not appear.