The task is to upgrade table foreign key metadata for the old storage of InnoDB system tables SYS_FOREIGN and SYS_FOREIGN_COLS by reading the foreign key definitions on table open and storing them into table's FRM file. When the system tables SYS_FOREIGN(_COLS) become empty during the process of foreign keys upgrade they are automatically dropped.
The task is required for the database migration from the previous versions of MariaDB. Without this functionality any foreign keys defined in previous versions of MariaDB will not have effect and will become inaccessible.
Foreign key upgrade is transparent operation, i.e. nothing special has to be done to start using the tables. The table is upgraded automatically whenever it is opened. However there is an option to upgrade all tables in all databases at once by issuing mysql_upgrade command (no special options required as long as user tables processed).
fk_check_legacy_storage() checks whether upgrade is required for the
given table name by looking SYS_FOREIGN table for corresponding record
fk_upgrade_legacy_storage() does the upgrade routine which includes
getting the foreign keys from SYS_FOREIGN[_COLS], updating the
foreign/referenced shares as well as their FRM files, deleting the
records from SYS_FOREIGN[_COLS] tables.
Both routines utilize the internal SQL for SYS_FOREIGN[_COLS]
When table is opened fk_check_legacy_storage() detects whether upgrade
is required and HA_ERR_FK_UPGRADE is returned to SQL layer which then
handles this error by backoff action from Open_table_context where
table is opened again with HA_OPEN_FOR_REPAIR flag which indicates
that fk_upgrade_legacy_storage() is required. After
fk_upgrade_legacy_storage() is done fk_check_legacy_storage() is
checked again to ensure that SYS_FOREIGN[_COLS] are empty for the
Indicate that table has legacy data in SYS_FOREIGN[_COLS].
fk_upgrade_legacy_storage() via fk_upgrade_push_fk() fails if there
are no indexes in foreign/referenced tables for the given data
acquired from SYS_FOREIGN[_COLS].
fk_upgrade_legacy_storage() utilizes syntax extension in internal SQL:
FETCH c INTO fk_upgrade_create_fk() fk_id, unused;
Thus the data is fetched into both fk_upgrade_create_fk() function and
When foreign table is opened it is automatically upgraded by backoff
action. But if the referenced table is altered or dropped first there
is no chance for the foreign table to get the correct data. So the
SYS_FOREIGN_[COLS] must be kept in sync with the above DDL operations
in respect of referenced names. DROP TABLE for the referenced table is
disabled as usual. DROP TABLE, DROP COLUMN relied in 10.5 on
dict_foreign_t cached data for the referenced tables. Now there is no
such possibility for the legacy data so we have to look at
Rename table and rename column handling was done in sync with
SYS_FOREIGN_[COLS] in 10.5. To retain the above DDL consistency for
the referenced tables we still use that old synchronization code.
Since we cannot faingrain ALGORITHM=COPY in innobase handler it is
disabled for the referenced tables unless the foreign tables are upgraded.
The check is done in create_table_info_t::create_table() and is
equivalent to DROP TABLE check as we are actually dropping the old
table after the copy routine is done.
Extra interface to check whether ALGORITHM=COPY is possible. If there
is legacy data in SYS_FOREIGN[_COLS] about foreign tables referring
altered table, ALGORITHM=COPY is not possible.
Every SYS_FOREIGN_[COLS] handling is wrapped inside
WITH_INNODB_FOREIGN_UPGRADE compilation macro. When this macro is
disabled the foreign key upgrade is not possible. Future versions will
obsolete the upgrade procedure completely and the wrapped code will be
Test cases must fill SYS_FOREIGN_[COLS] with data. This is done with
setting the new innodb_eval_sql debug variable. The value of that
variable is processed by que_eval_sql().
que_eval_sql() was unfriendly on syntax errors: it just failed with
SIGABRT exception. To keep the server alive some frequent syntax
errors are now returned as DB_ERROR from que_eval_sql().
fk_create_legacy_storage debug flag to create SYS_FOREIGN[_COLS] on
fk_release_locks() iterates through all indexes and pages and releases
locks on SUPREMUM records placed there previously by SELECT FOR
UPDATE. Also it releases all table locks.
When false don't abort server on syntax errors while parsing internal