• Technical task
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • 11.6
    • None
    • None
    • bb-10.6-midenok-MDEV-16417-atomic


      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

      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]

      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.


      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

      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

      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.


        Issue Links



              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              0 Vote for this issue
              6 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.