FK migration from old version

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(), fk_upgrade_legacy_storage()

fk_check_legacy_storage() checks whether upgrade is required for the
given table name by looking SYS_FOREIGN table for corresponding record
existence.

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]
processing.

Upgrade foreign keys via backoff action

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
given table.

DB_LEGACY_FK and HA_ERR_FK_UPGRADE error codes

Indicate that table has legacy data in SYS_FOREIGN[_COLS].

Check foreign/referenced indexes existence

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].

Internal SQL: select into both func and vars extension

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
fk_id variable.

Rename table, rename column, drop table, drop column handling

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
SYS_FOREIGN_[COLS] directly.

Reverted some SYS_FOREIGN(_COLS) routines

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.

ALGORITHM=COPY handling

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.

HA_EXTRA_CHECK_LEGACY_FK

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.

WITH_INNODB_FOREIGN_UPGRADE macro

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
removed.

innodb_eval_sql debug interface

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().

Some syntax error-friendly parser handling

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().

Drop empty SYS_FOREIGN[_COLS] after import

fk_create_legacy_storage debug flag to create SYS_FOREIGN[_COLS] on
innodb_eval_sql.

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.

pars_info_t::fatal_syntax_err (debug only)

When false don't abort server on syntax errors while parsing internal
SQL code.