MDEV-21652 Foreign key 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. The table is upgraded whenever it is opened.
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.
== 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.
