Description:
In MySQL 8.0 names of foreign keys which were not explicitly provided by
user are automatically generated by SQL-layer and stored in data-dictionary.
Such names have form "<table_name>_ibfk_<number>" so are aligned with
names which were generated by InnoDB SE in 5.7.
However, NDB SE still generates and uses different format for such names
internally "fk_<some_internal_id>_<other_internal_id>" instead of using
names generated by SQL-layer and stored in DD.
This creates some issues:
- Discrepancies in SHOW CREATE TABLE output and I_S.REFENTIAL_CONSTRAINTS
contents.
- Improper MDL locking for foreign keys (since it relies on names
generated by SQL-layer).
- Confusing names of foreign keys in error messages.
How to repeat:
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDBCLUSTER;
CREATE TABLE child (fk1 INT, fk2 INT,
FOREIGN KEY (fk1) REFERENCES parent (pk),
CONSTRAINT c1 FOREIGN KEY (fk2) REFERENCES parent (pk)) ENGINE=NDBCLUSTER;
SHOW CREATE TABLE child;
# Table Create Table
# child CREATE TABLE `child` (
# `fk1` int(11) DEFAULT NULL,
# `fk2` int(11) DEFAULT NULL,
# KEY `fk1` (`fk1`),
# KEY `c1` (`fk2`),
# CONSTRAINT `FK_13_16` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
# CONSTRAINT `c1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
# ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM information_schema.referential_constraints;
# CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
# def test child_ibfk_1 def test PRIMARY NONE NO ACTION NO ACTION child parent
# def test c1 def test PRIMARY NONE NO ACTION NO ACTION child parent
Suggested fix:
Per discussion with NDB team we need to change NDB SE to use names generated
by SQL-layer. We also should use non-SE dependent suffix _fk_ (rather than
_ibfk_) for such names at least for NDB tables.
