This additional subtask requires several days to finish as it is required to keep in sync referenced TABLE_SHARE objects (TABLE_SHARE::referenced_keys list) after DROP TABLE, ALTER ADD/DROP FOREIGN KEY, RENAME TABLE. It was expected to be a subtask of MDEV-16417, but I already started it here and implemented a draft for RENAME TABLE: https://github.com/MariaDB/server/commit/abcce38a623d86bdd9e27cfd0b04ae9698cfef0b

And the above fix should be remade for avoiding TABLE_SHARE invalidation.

MDEV-20865 will track further progress.

P.S. Actually this big enough subtask took more than several days.

midenok, you mean, refactor InnoDB to use foreign keys from TABLE_SHARE? I don't think this is particularly important. InnoDB has dict_table_t and dict_col_t and dict_index_t despite the fact that table metadata, and all columns and indexes are stored in the TABLE_SHARE too.

But the server does not use InnoDB data structures, the server uses what's in the TABLE_SHARE. Only for foreign keys the server asks InnoDB for FK information. Instead it should store all FK metadata in the TABLE_SHARE and use them from there.

While an engine can still store them internally and not peek into TABLE_SHARE every time.

Refactor InnoDB to use foreign keys from TABLE_SHARE is large subtask of MDEV-16417. There are at least 16 functions that need to be remade or refactored including ~35 occurrences of `SYS_FOREIGN` and ~27 occurrences of `SYS_FOREIGN_COLS` in InnoDB SQL code.

What is possible in scope of this task is to pass via `TABLE_SHARE` foreign keys *only* for `create_table_info_t::create_table()`. There is no much importance in that for now as there is raw data in `alter_info` and there is processed data in `TABLE_SHARE` for `create_table_info_t::create_table()`, so we just switch from one to another. And there is no final design of foreign keys structure in `TABLE_SHARE` until MDEV-16417, so IMO passing them now via `TABLE_SHARE` is a bit premature.

bar thinks and I agree that the FOREIGN KEY metadata would better be stored in TABLE_SHARE. Sure, it would still not be stored anywhere outside InnoDB (until MDEV-16417 is implemented), but TABLE_SHARE would be the logical place for it.

Note: Until MDEV-16417, we will need some interface that allows the storage engine to fill in the FOREIGN KEY metadata. That might be some kind of an extension to handler::open(). This is a bit tricky, because there could be multiple concurrent calls to that function for the same table, while at the same time there was no prior call of handler::open() for that table. Inside ha_innobase::open() there is some distinction of ‘first-time open’ and ‘reopen’. If the ‘first-time open’ would initialize the FOREIGN KEY metadata in TABLE_SHARE, we would need some locking to make sure that the concurrent calls that would reach the ‘reopen’ code path will not return before that metadata has been initialized.

I sent some lower-level review comments on GitHub.

Please rebase the work on the latest 10.5 and remember to update the commit date when updating the patch.

Made as separate MDEV-20729.

I think that as part of this, we should fix the SQL parser (both sql_yacc.yy and sql_yacc_ora.yy) so that also the following form of REFERENCES constraints will be recognized and not ignored by the parser:

column_def:

          field_spec

          { $$= $1; }

        | field_spec references

          { $$= $1; }

        ;

Currently, the grammar rule is ignoring the semantic value of the REFERENCES clause. The non-terminal references is being more correctly used in the key_def rule. Also, I wonder whether the second production should include opt_constraint:

column_def:

          field_spec

          { $$= $1; }

        | field_spec opt_constraint references

          { $$= … $1 … $2 … $3 …; }

        ;