Bug #91712 Adding FOREIGN KEY failed message is incorrect

• Submitted: 19 Jul 2018 8:00
• Modified: 10 Apr 2019 22:07
• Reporter: Tsubasa Tanaka (OCA)
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.22, 8.0.11
• OS: CentOS (7.4)
• CPU Architecture: x86

[19 Jul 2018 8:00] Tsubasa Tanaka

Description:
MySQL returns wrong warning-message when trying to add FOREIGN KEY CONSTRAINT between mismatched-datatype columns.

How to repeat:
mysql80 117> CREATE TABLE t1 (num int signed not null, PRIMARY KEY(num)) /* num is SIGNED int */;
Query OK, 0 rows affected (0.06 sec)

mysql80 117> CREATE TABLE t2 (num int unsigned not null, PRIMARY KEY(num)) /* num is UNSIGNED int */;
Query OK, 0 rows affected (0.12 sec)

mysql80 117> ALTER TABLE t2 ADD FOREIGN KEY (num) REFERENCES t1(num) /* This statement should be fail because of *Datatype mismatch* */;
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql80 117> SHOW WARNINGS /* But warning-message says "There's no index.." */;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create table 'd1/#sql-5d3c_75' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1215 | Cannot add foreign key constraint                                                                                                                                       |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[19 Jul 2018 8:10] MySQL Verification Team

Hello Tanaka-San,

Thank you for the report!

Thanks,
Umesh

[7 Apr 2019 14:42] Dmitry Lenev

Posted by developer:
 
Hello!

Thank you for your report!!!

This issue was solved in MySQL Server release 8.0.14 by the following patch:

=====
Author: Dmitry Lenev <[e-mail]>
Date:   Tue Sep 11 14:13:15 2018 +0300

    Fifth and final part of fix for bug#25722927 "NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT".
    
    After we have started to store information about foreign keys in the
    Transactional Data Dictionary it became possible to move checks of
    foreign key definition validity from the storage engine to SQL-layer
    and thus reduce code duplication and simplify SE implementation.
    
    Moved check that types of referencing and referenced columns in a
    foreign key are compatible to SQL-layer.
    ...

    Added new, more specific error code and message which are used in cases
    when foreign key can't be created due to column types mismatch.
    ...
===

The new error message which above test case emits now is:

ERROR 3780 (HY000): Referencing column 'num' and referenced column 'num' in foreign key constraint 't2_ibfk_1' are incompatible.

I am moving this bug report to Documenting state, since I think that
it might make sense to update the Release Notes for version 8.0.14
,to not only mention that the check for column compatibility was moved
to SQL layer, but also that as result we now produce better error message
in case when columns are not compatible.

[10 Apr 2019 22:07] Paul DuBois

Posted by developer:
 
Fixed in 8.0.14.

A check that ensures compatibility of referencing and referenced
column types in a foreign key definition was moved from the storage
engine layer to the SQL layer. In addition, a better error message is
produced when columns are not compatible.