MySQL Bugs: #96822: Remove dict_create_foreign

Bug #96822	Remove dict_create_foreign_constraints() and friends.
Submitted:	10 Sep 2019 13:48	Modified:	24 Sep 2019 18:44
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.19-git	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 8.0 all information about foreign keys is available from the
new data-dictionary, corresponding DD API and sometimes from TABLE_SHARE.

So it should be possible to perform most of DDL checks related to foreign
keys on SQL-layer. In fact most of them are already done at the SQL-layer
so InnoDB function dict_create_foreign_constraints() which does these
checks (and parses SQL-statement to perform them) seems to be unnecessary
and redundant.

We should remove dict_create_foreign_constraints() and its friends.

How to repeat:
Inspect the code.

Posted by developer:
 
Fixed as of the upcoming 8.0.19 release. The following changelog entry applies to:

Bug #30287895 REMOVE DICT_CREATE_FOREIGN_CONSTRAINTS() AND FRIENDS.
Bug #22364336 ANOMALY IN ALTER TABLE ADD FOREIGN KEY
Bug #28486106 ALTER TABLE ADD FOREIGN KEY ... REMOVE PARTITIONING FAILS IF SELF-REFERENCE.
Bug #28703793 CANNOT ADD FOREIGN KEY CONSTRAINT TO TABLES INSIDE A DATABASE NAMED `AUX`
Bug #16904122 MULTIPLE FK WITH SAME NAME ALLOWED IN ALTER."

"The internal InnoDB dict_create_foreign_constraints() function that
parsed SQL statements and performed foreign key related DDL checks was
removed. The function became redundant with introduction of the data
dictionary in MySQL 8.0 and the subsequent relocation of foreign key
related DDL checks to the SQL layer. 

Removal of the dict_create_foreign_constraints() function also addressed the following
foreign key issues: 

* Spaces around dots (.) in a fully
qualified referenced table name were not permitted by the InnoDB parser.

* Adding a foreign key and removing partitioning in the same
ALTER TABLE statement was not permitted. The InnoDB parser did not detect
that the new table version was no longer partitioned. 

* A foreign key constraint could not reference a table inside a schema named
AUX. The function that parsed referenced table names did not recognize
that special names such as AUX are encoded. 

Additionally, a check was added to the SQL layer to detect attempts to create multiple
foreign keys of the same name on a table at an early stage in the
execution of ALTER TABLE statement."