Bug #96611 Generation of FK names is not re-execution safe

• Submitted: 22 Aug 2019 7:32
• Modified: 30 Sep 2019 17:32
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.18-git
• OS: Any
• CPU Architecture: Any

[22 Aug 2019 7:32] Dmitry Lenev

Description:
After fix for bug#30171959 "INCONSISTENT NAMING OF FOREIGN KEYS OVER NDB TABLES"
has been applied NDB and InnoDB SE use similar approaches to generation of foreign
key names in cases when they were not provided explicitly.

However, this change made process of generation of such names not safe for
re-execution in prepared statements and stored routine. Re-execution leads
to use of the same name and duplicate foreign key name error.

How to repeat:
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, a INT);
PREPARE stmt1 FROM 'ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent (pk)';
EXECUTE stmt1;
SHOW CREATE TABLE child;
#	Table   Create Table
#	child   CREATE TABLE `child` (
#		  `fk` int(11) DEFAULT NULL,
#		  `a` int(11) DEFAULT NULL,
#		  KEY `fk` (`fk`),
#		  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
#		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE child RENAME COLUMN fk TO fkold, RENAME COLUMN a TO fk;

# The below statement fails with ERROR 1826 (HY000): Duplicate foreign key constraint name 'child_ibfk_1'
# which it should not...
EXECUTE stmt1;

[30 Sep 2019 17:32] Paul DuBois

Posted by developer:
 
Fixed in 8.0.19.

A statement that added a foreign key without an explicit name failed
when re-executed as a prepared statement or in a stored program with
an unwarranted duplicate foreign key name error.