MySQL Bugs: #71508: Changing a foreign key column to NOT NULL breaks constraints in case of NULLs

Bug #71508	Changing a foreign key column to NOT NULL breaks constraints in case of NULLs
Submitted:	29 Jan 2014 13:16	Modified:	14 Jul 2015 20:24
Reporter:	Jari Juslin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.35, 5.0.97, 5.1.73, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If you have a foreign key column that allows for NULL values and you change it to NOT NULL, the ALTER TABLE automatically changes NULLs into zeroes. In this transformation foreign key constraint is ignored - the FK column gets zero values regardless of the parent table containing zero value or not. In other words: the resulting DB has lost integrity; foreign key constraint is still defined, but the data does not follow it.

Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2

How to repeat:
CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) ENGINE = InnoDB;
CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_fk INT DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT fk_child_parent FOREIGN KEY (parent_fk) REFERENCES parent (id)) ENGINE = InnoDB;
INSERT INTO child values (1, NULL);
ALTER TABLE child MODIFY COLUMN parent_fk INT NOT NULL;

" ..  the ALTER TABLE automatically changes NULLs into zeroes".

Well, it does in non-strict SQL-mode (for numerical columns, for string columns it changes into 'empty string'). In 'strict mode' you will get an error - and no matter if a FK is defined on the column).

Refer http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html. The same page says "Strict mode does not affect whether foreign key constraints are checked." (a statement I do not fully claim to understand in the context).

No matter SQL_mode I think that an ALTER TABLE should never violate FK CONSTRAINTS between existing data (like orphanize data in a 'child' table). But it looks like this is not checked in this particular case.

Anyway setting 'strict mode' will avoid the situation, as far as I can understand. In 'strict mode' the silent substitution from NULL to ZERO(numbers)/EMPTY STRING(strings) will not happen. You will get an error instead.

Peter
(not a MySQL/Oracle person)

Well .. actually what happens is not to "orphanize data in a 'child' table" - rather the opposite. 

NULL changes into "0" (or 'empty string' and the affected 'child' row(s) may get a 'parent' it/they did not have before. This may again affect results from queries using a JOIN. And this may again not only lead to misleading reports etc. but even data loss if UPDATE statements use JOINs/subqueries on the parent table.

MySQL was not originally designed to handle Foreign Keys (InnoDB wasn't there from the beginning), and I think this is 'a leftover case' from that early time in MySQL history.

One more good reason to use 'strict sql_mode' IMO.

Thank you for the report.

Verified as described.

While Peter seems to be correct about this error can happen, because foreign key constraints supported at storage engine level only I still think data dis-integrity which happens here is not acceptable.

test case for MTR

Attachment: bug71508.test (application/octet-stream, text), 558 bytes.

(just a comment to Sveta's approval and her reference to my postings)

As I see it: it is known that in 'non-strict' sql_mode truncations may occur if client specifies a value that cannot be applied or - as this case shows - if a DDL statement tries to rewrite data an analogous way.  The trunctation result in numbers becoming '0' (ZERO) and strings becoming 'empty string'.

The point here in this report is that this happens without considering/checking possible FK CONSTRAINTS on affected columns.  What actually also implies that 'non-strict' sql_mode itself is (more-or-less) unsafe with FK CONSTRAINTS. Not that I claim it is a big issue - just avoiding FK references to '0' and 'empty string' -values in parent tables for instance will (in most cases, at least) avoid the problem having practical impact.

Is this a duplicate of bug 46599?

Posted by developer:
 
Hello!

Laurynas is correct this is a duplicate of bug https://bugs.mysql.com/bug.php?id=46599 which was fixed in MySQL 5.6.7
and is not repeatable in current 5.6 version (nor in 5.6.7, I've checked).

So I am marking it as such.

Thanks to Laurynas for spotting this!

A good catch. It was worded and reported from different angle enough I didn't find it when filing mine.