MDEV-28933: Per-table unique FOREIGN KEY constraint names

[dr-m]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-28933

Description

dict_foreign_t::sql_id(): Return the SQL constraint name. Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and /. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be of the form 1, 2, …, internally stored as schemaname/tablename\3771, schemaname/tablename\3772, ….

Release Notes

InnoDB FOREIGN KEY constraint names need not be unique within a schema any more, but only unique within a table. Auto-generated constraint names will be of the form 1, 2, ….

The INFORMATION_SCHEMA views INNODB_SYS_FOREIGNS and INNODB_SYS_FOREIGN_COLS will only display the user-specified constraint name, no longer any databasename/ prefix.

How can this PR be tested?

Upgrade testing

./mtr --manual-gdb innodb.innodb-fk

Before starting the first test step in GDB, specify with file an older mariadbd executable that does not contain these changes. For the subsequent test steps, specify the updated mariadbd executable. The test will pass but display a warning during the execution of ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY:

***Warnings generated in error logs during shutdown after running tests: innodb.innodb-fk

2025-04-15  9:20:00 3 [Warning] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.

It turns out that this warning is expected by the test, but call mtr.add_suppression() apparently has no effect when running ./mtr --manual-gdb.

For some additional diagnostics, you can apply the following patch:

diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test
index 28dc4a28ce1..d78d08fecf9 100644
--- a/mysql-test/suite/innodb/t/innodb-fk.test
+++ b/mysql-test/suite/innodb/t/innodb-fk.test
@@ -39,6 +39,7 @@ select * from fk_50;
 let $i = $fk_tables;
 while ($i)
 {
+  eval show create table fk_$i;
   eval drop table fk_$i;
   dec $i;
 }

This will correctly display the constraint names pc120 through pc1 even though they were internally stored as test/pc120 through test/pc1.

Downgrade testing

If you run the test innodb.innodb-fk as it is adjusted in this PR, and run the unmodified mariadbd on the 3rd test step (the server is being started 3 times for running the test), then it will obviously fail because a generated constraint has a different name:

CURRENT_TEST: innodb.innodb-fk
mysqltest: At line 203: query 'ALTER TABLE `boroda` DROP FOREIGN KEY `2`' failed: ER_CANT_DROP_FIELD_OR_KEY (1091): Can't DROP FOREIGN KEY `2`; check that it exists

This happens even if you run the modified test against the old server all the time. We can work around this by commenting out the problematic statement:

diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test
index 28dc4a28ce1..aba9e0f44c9 100644
--- a/mysql-test/suite/innodb/t/innodb-fk.test
+++ b/mysql-test/suite/innodb/t/innodb-fk.test
@@ -200,7 +200,7 @@ CREATE TABLE `boroda` (
 ALTER TABLE `boroda`
   ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`);
 
-ALTER TABLE `boroda` DROP FOREIGN KEY `2`;
+#ALTER TABLE `boroda` DROP FOREIGN KEY `2`;
 
 RENAME TABLE `boroda` TO `#boroda`;
 

If we run the old mariadbd against that for all test steps, there will be not only result differences for the generated constraint names, but also the following:

@@ -216,7 +216,7 @@
 ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 ID	FOR_NAME	REF_NAME	N_COLS	TYPE
-fk	test/t1_fk	test/t1	1	4
+test/fk	test/t1_fk	test/t1	1	4
 SET FOREIGN_KEY_CHECKS=0;
 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;

If we run this modified test with ./mtr --manual-gdb innodb.innodb-fk and run the updated server only for the first or second step or both, the test will work in the same way.

This confirms that DROP TABLE can drop not only the old-format constraints in the updated server, but also the new-format constraints in the old server. Also DROP DATABASE schemaname is expected to work, because the SYS_FOREIGN.ID and SYS_FOREIGN_COLS.ID will continue to start with schemaname/. But, ALTER TABLE…DROP FOREIGN KEY definitely is not going to work after a downgrade, because it is not expected to be possible for the user to input the invalid UTF-8 sequence 0xff.

Basing the PR against the correct MariaDB version

• [x] This is a new feature or a refactoring, and the PR is based against the main branch.
• [ ] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [ ] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.