Bug #84331 Incorrect LATEST FOREIGN KEY ERROR for partitioned table
Submitted: 24 Dec 2016 18:37 Modified: 10 May 2019 11:33
Reporter: Grigory Rubtsov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[24 Dec 2016 18:37] Grigory Rubtsov
Description:
If the foreign key constraint references a partitioned table, the error message in "SHOW ENGINE INNODB STATUS" incorrectly states: "Cannot resolve table name".

How to repeat:
> CREATE TABLE t1 (a INT, INDEX(a))
ENGINE=INNODB
PARTITION BY RANGE (a)  
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);

> CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=INNODB;

ERROR 1215 (HY000): Cannot add foreign key constraint

> SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-12-24 20:53:17 7f48d005b700 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
------------
[24 Dec 2016 19:58] MySQL Verification Team
Thank you for the bug report. Verified as described.
[20 Apr 2019 14:15] Dmitry Lenev
Posted by developer:
 
Hello!

The behavior in the above case is different for recent 8.0 versions
(I have used 8.0.17-git):

CREATE TABLE t1 (a INT, INDEX(a))
ENGINE=INNODB
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);
# The below statement fails with 1506 "ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning".
CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=INNODB;

# The below statement doesn't show any error in LATEST FOREIGN KEY ERROR section.
SHOW ENGINE INNODB STATUS;

This is because after second part of fix for bug#25722927
"NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT"
was added to 8.0.14, we have started to check if we attempt to create foreign key
involving partitioned table as parent or child on SQL-layer and not in the storage
engine. So InnoDB is not aware of this error at all. The error message returned
to the client also got more clear.

Thus the problem described in this bug report is addressed by fix for
bug#25722927 "NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK
CONSTRAINT", and therefore I am closing this report as fixed in 8.0.14.

Moving to Documenting state to let Documentation to decide if anything
needs to be added to documentation.
[10 May 2019 11:33] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.14 release, and here's the changelog entry:

After attempting to create a table with a foreign key constraint that
referenced a partitioned table, which is an unsupported operation, SHOW
ENGINE INNODB STATUS output incorrectly reported a foreign key error
indicating that the referenced table name could not be resolved. This
error no longer appears, and the error message returned to the client now
states that foreign keys are not yet supported in conjunction with
partitioning.