| Bug #2094 | ALTER TABLE allows change of the column containing NULL's to NOT NULL | ||
|---|---|---|---|
| Submitted: | 11 Dec 2003 5:03 | Modified: | 27 Sep 2008 9:37 |
| Reporter: | SINISA MILIVOJEVIC | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1.1 | OS: | Any (any) |
| Assigned to: | CPU Architecture: | Any | |
[20 Dec 2003 8:56]
Victor Vagin
I can't repeat this bug complitelly.. It seems to me, you've skipped several lines in 'how to repeat', which fill tables..
[20 Dec 2003 9:41]
MySQL Verification Team
Comments are by Heikki, BTW. Can you please tell me what results do you get ???
[20 Dec 2003 12:40]
Sergei Golubchik
As discussed, MySQL indeed converts NULL to 0 in some situations. It is a feature, not a bug.
And this code works as expected:
mysql> create table t1(id int primary key)type=innodb;
Query OK, 0 rows affected (0.06 sec)
mysql> create table t2(
-> id int not null,
-> name varchar(10),
-> index(id),
-> foreign key(id) references t1(id) on delete cascade)type=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t2 values (1, 'foobar'),(NULL, 'blabla');
ERROR 1216 (00000): Cannot add or update a child row: a foreign key constraint fails
that is NULL is converted to 0, but InnoDB catches is and prevents 0 from being inserted,
Thus the original bug is that InnoDB does not catches when NULL is converted to 0 in ALTER TABLE, as it does for INSERT.
[21 Dec 2003 8:02]
Heikki Tuuri
All, an easy fix for this is that a 'STRICT' mode is implemented to MySQL and enforced for InnoDB type tables. Sergei and I exchanged some comments: " > But no matter in which sql_mode MySQL works, foreign key constraints > should be enforced. Do you agree ? yes, and a simple way to fix this InnoDB problem is to enable Monty's STRICT mode for ALTER TABLEs of InnoDB type tables. The automatic conversion NULL -> 0 derives from the fact that MyISAM cannot roll back. It should be disabled for transactional tables. > Btw, I suspect that any ALTER TABLE that reduces the range of valid > column values could potentially break foreign key constraints. Not only > that sets the column NOT NULL, but also that reduces the length of CHAR > column or the one that changes INT to TINYINT. The same here: the ALTER TABLE should roll back if all those INTs do not fit in TINYINTs. " Regards, Heikki
[12 Jan 2004 6:48]
Victor Vagin
ChangeSet 1.1675 04/01/12 18:34:47 vva@eagle.mysql.r18.ru +4 -0 fixed BUG #2094 "ALTER TABLE aloows change of the column containing NULL's to NOT NULL" the reason of bug was the passing of foreigns from old table to new (temporary) one after real copying of data..
[27 Jan 2004 10:27]
Heikki Tuuri
Hi! This bug and maybe all other similar bugs are fixed when the 'strict' SQL mode becomes the default for InnoDB tables, so that ALTER TABLE cannot change a column value on the fly. Or, we must explicitly check after an ALTER TABLE that FOREIGN KEY constraints referring the table are still satisfied. Regards, Heikki
[29 Jan 2004 12:09]
Heikki Tuuri
Hi! Monty said that the 'strict' SQL mode is expected to become the default in 5.0.x for InnoDB. Then the ALTER TABLE will fail and roll back if any column value changes. This will remove the FOREIGN KEY problem, because this bug is caused by a column changing its value during the ALTER operation. Regards, Heikki
[11 Aug 2005 14:35]
Heikki Tuuri
The SQL mode 'traditional' does fix this in 4.1. But maybe in 5.1 the 'traditional' mode becomes default for transactional table types.
[27 Sep 2008 9:37]
Konstantin Osipov
As acknowledged by Heikki, this bug is fixed in strict/traditional modes. Making these modes a default is a separate issue.
Description: The bug is that MySQL automatically changes the value NULL in a column to 0 in the ALTER TABLE! The ALTER TABLE below should fail because you cannot enforce id to be NOT NULL if the column contains a NULL value. Thus, the bug is in MySQL, not InnoDB. How to repeat: mysql> create table t1( -> id int primary key); Query OK, 0 rows affected (0.04 sec) mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> create table t1(id int primary key)type=innodb; Query OK, 0 rows affected (0.06 sec) mysql> create table t2( -> id int, -> name varchar(10), -> index(id), -> foreign key(id) references t1(id) on delete cascade)type=innodb; Query OK, 0 rows affected (0.05 sec) mysql> alter table t2 CHANGE id id INT NOT NULL; Query OK, 2 rows affected, 1 warning (0.18 sec) Records: 2 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1264 | Data truncated for column 'id' at row 2 | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> select * from t2; +----+------+ | id | name | +----+------+ | 1 | vita | | 0 | egor | +----+------+ 2 rows in set (0.01 sec)