Details
-
New Feature
-
Status: In Testing (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
Description
Starting with MySQL 4.0.18, InnoDB can store user specified FOREIGN KEY constraint names. But, for no good reason, these names must be unique within a schema. Let us allow the constraint names to be unique within a table.
Furthermore, let us make the generated FOREIGN KEY constraint names be simply ASCII numbers 1, 2, 3, and so on. Previously, the user-visible generated names were tablename_ibfk_1, tablename_ibfk_2 and so on, while the internal names were like schemaname/tablename_ibfk_1. In the new scheme, the internal names that are only visible in the InnoDB system tables SYS_FOREIGN and SYS_FOREIGN_COLS are prefixed with schemaname/tablename and the impossible UTF-8 sequence 0xff. This prefix is hidden from the SQL layer.
Upgrade considerations
- Foreign key constraints will no longer be displayed as schemaname/constraintname to the users. Only the constraintname part will be displayed.
- On ALTER TABLE…ALGORITHM=COPY, all internal names of constraints will be adjusted: the schenamame/ prefix will be changed to schemaname/tablename\377.
- The user-visible part of any constraints of the form oldtablename_ibfk_1 will no longer be changed on RENAME TABLE oldtablename TO newtablename.
- The internal names will be changed, e.g. from schemaname/oldtablename_ibfk_1 to schemaname/newtablename\377oldtablename_ibfk_1.
- ALTER TABLE…DROP FOREIGN KEY will attempt to drop both old and new format constraint names.
Downgrade considerations
- Foreign key constraints are expected to work, but the error messages may display an incorrect constraint name.
- If the invalid UTF-8 sequence 0xff is being treated as a string terminator, the constraint name would not be reported as schemaname/constraintname but schemaname/tablename.
- Both DROP TABLE and DROP DATABASE will be able to drop the associated FOREIGN KEY constraints.
- This can be tested by running ./mtr --manual-gdb innodb.innodb-fk with two versions of the server.
- ALTER TABLE…DROP FOREIGN KEY will be unable to drop constraints that are in the new format, because it will be impossible to input the invalid UTF-8 sequence 0xff as part of the constraint name.
Attachments
Issue Links
- blocks
-
-
- In Review
-
-
-
- Stalled
-
- is blocked by
-
MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Open
-
Activity
Please take a look at https://github.com/MariaDB/server/pull/3960 and try to fix the remaining 2 test failures.
I realized that the ibfk_ prefix really adds no value. In the SQL layer we can expose plain decimal ASCII numbers for auto-generated constraint names: 1, 2, … instead of schemaname/tablename_ibfk_1, schemaname/tablename_ibfk_2, …. Internally, the SYS_FOREIGN.ID would be schemaname/tablename\xff followed by the name that is visible in the SQL layer.
I finally figured out the test failures. I was missing some adjustments for ALTER TABLE…DROP FOREIGN KEY. This is currently based on the 10.11 branch; I’m now rebasing it to the main branch.
MySQL 4.0.18 included a fix of MySQL Bug #2167 Foreign key references are not dropped correctly on replication, which made it possible to store a user specified constraint name in SYS_FOREIGN.ID, prepended with the schema name and a / character. If no constraint name is specified, names like schemaname/tablename_ibfk_1 were generted.
The fix that I am working on would prepend the user-specified constraint name with schemaname/tablename\377 and generate constraint names like schemaname/tablename\377ibfk_1 in case no name was provided by the user. The user-visible part of a constraint name (such as ibfk_1 or whatever was specified by the user) would no longer be adjusted on RENAME TABLE t1 TO t2, like we used to do (renaming t1_ibfk_1 to t2_ibfk_1).