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: | |
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
[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.