MySQL Bugs: #78255: Foreign key allowed between InnoDB and MyISAM if "set foreign_key

Bug #78255	Foreign key allowed between InnoDB and MyISAM if "set foreign_key_checks=0"
Submitted:	28 Aug 2015 8:37	Modified:	28 Aug 2015 14:07
Reporter:	Nick Moore	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.24 (and earlier, too)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Foreign key constraint, innodb, myisam

Description:
Putting foreign keys between InnoDB and MyISAM should be disallowed at schema-change time (the create table should fail) because MyISAM doesn't support foreign keys. But it is allowed if foreign keys checks are switched off.

Inserting rows then fails with "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" even if values are valid

This may seem like a perverse set of circumstances, but it happens quite easily when using Django South migrations.

See also: http://nick.zoic.org/sql/mysql-foreign-keys-between-innodb-and-myisam/

How to repeat:
-- Turn foreign key checks off and create a MyISAM table and an InnoDB
-- table which references it.

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table old_table (
    id int(11) primary key
) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table new_table (
    id int(11) primary key,
    old_table_id int(11),
    foreign key (old_table_id) references old_table (id)
) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

-- There's no errors or warnings until you actually try to add data, when the --- foreign key constraint suddenly rears its ugly head:

mysql> insert into old_table values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into new_table values(1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`foo`.`new_table`, CONSTRAINT `new_table_ibfk_1` FOREIGN KEY (`old_table_id`) REFERENCES `old_table` (`id`))

Suggested fix:
If foreign key checks are on, an error occurs at 'create table' time:

mysql> create table new_table (
    id int(11) primary key,
    old_table_id int(11),
    foreign key (old_table_id) references old_table (id)
) engine=InnoDB;
ERROR 1215 (HY000): Cannot add foreign key constraint

This error, or at least a stern warning, should occur even if foreign_key_checks are off.

Actually, this bug describes the expected behavior. But, I do think that a warning might be warranted.

I also think that a warning is warranted for the situation, like in this test case:

-------------------------------------------------
mysql 5.6 > USE test
Database changed
mysql 5.6 > set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > create table old_table ( id int) engine=innodb;
Query OK, 0 rows affected (0.34 sec)

mysql 5.6 > create table new_table (
   ->     id int(11) primary key,
   ->     old_table_id int(11),
   ->     foreign key (old_table_id) references old_table (id)
   -> ) engine=InnoDB;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql 5.6 > drop table old_table;
Query OK, 0 rows affected (0.30 sec)

mysql 5.6 > create table new_table (
   ->     id int(11) primary key,
   ->     old_table_id int(11),
   ->     foreign key (old_table_id) references old_table (id)
   -> ) engine=InnoDB;
Query OK, 0 rows affected (0.38 sec)
-------------------------------------------------

So, we have two situations which would require warnings.

In essence this is a verified bug that is requesting two different warnings, for two similar cases.

G'day Sinisa,

    Yeah, I can see how in the MyISAM -> MyISAM case that silently ignoring the foreign key declaration makes sense ... I've never liked it as a decision, but so it goes.  You can still add data to the tables just without the benefit of foreign key checks.

    But in the InnoDB -> MyISAM case, the table is left in an uninsertable state with no warnings or errors at schema definition time.

    It seems to me that if it is an error to try to define a InnoDB foreign key which references a non-indexed column, or a column of the wrong data type, then it should be an error to try to define a InnoDB foreign key which references a MyISAM table too.  It won't work, so it should tell the user that as soon as possible.

    Thanks for having a look at it!

        Nick

That information is added to the relevant database.