MDEV-21652 Foreign key migration from old versionbb-10.6-midenok-MDEV-21652

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

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

== WITH_INNODB_LEGACY_FOREIGN_STORAGE macro ==

Every SYS_FOREIGN_[COLS] handling is wrapped inside
WITH_INNODB_LEGACY_FOREIGN_STORAGE compilation macro. When this macro
is disabled the foreign key upgrade is not possible. Future versions
will obsolete the upgrade completely.

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

1 files changed, 2 insertions, 0 deletions

diff --git a/storage/innobase/include/dict0dict.h b/storage/innobase/include/dict0dict.h
index b5319d51c9a..2edadb9ede5 100644
--- a/storage/innobase/include/dict0dict.h
+++ b/storage/innobase/include/dict0dict.h
@@ -462,6 +462,7 @@ dict_foreign_replace_index(
 					to use table->col_names */
 	const dict_index_t*	index)	/*!< in: index to be replaced */
 	MY_ATTRIBUTE((nonnull(1,3), warn_unused_result));
+#ifdef WITH_INNODB_LEGACY_FOREIGN_STORAGE
 /**********************************************************************//**
 Parses the CONSTRAINT id's to be dropped in an ALTER TABLE statement.
 @return DB_SUCCESS or DB_CANNOT_DROP_CONSTRAINT if syntax error or the
@@ -478,6 +479,7 @@ dict_foreign_parse_drop_constraints(
 	const char***	constraints_to_drop)	/*!< out: id's of the
 						constraints to drop */
 	MY_ATTRIBUTE((nonnull, warn_unused_result));
+#endif /* WITH_INNODB_LEGACY_FOREIGN_STORAGE */
 /**********************************************************************//**
 Returns a table object and increments its open handle count.
 NOTE! This is a high-level function to be used mainly from outside the