[MDEV-20865] Store foreign key info in FRM and TABLE_SHARE - Jira

Aleksey Midenkov created issue - 2019-10-19 14:48

Aleksey Midenkov made changes - 2019-10-23 04:14

Field	Original Value	New Value
Link		This issue is blocked by MDEV-20874 [ MDEV-20874 ]

Aleksey Midenkov made changes - 2019-11-14 11:16

Link

This issue is blocked by ~~MDEV-21049~~ [ ~~MDEV-21049~~ ]

Aleksey Midenkov made changes - 2019-11-14 11:17

Fix Version/s

10.5 [ 23123 ]

Aleksey Midenkov made changes - 2019-11-14 11:17

Status

Open [ 1 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2019-11-14 11:17

Component/s

Server [ 13907 ]

Aleksey Midenkov made changes - 2019-11-14 11:18

Parent	~~MDEV-20480~~ [ 78682 ]
Issue Type	Technical task [ 7 ]	Task [ 3 ]

Aleksey Midenkov made changes - 2019-11-14 11:19

Link

This issue is blocked by MDEV-20874 [ MDEV-20874 ]

Aleksey Midenkov made changes - 2019-11-14 11:19

Link

This issue relates to MDEV-20874 [ MDEV-20874 ]

Aleksey Midenkov made changes - 2019-11-14 11:20

Link

This issue relates to ~~MDEV-20480~~ [ ~~MDEV-20480~~ ]

Aleksey Midenkov made changes - 2019-11-14 11:21

Link

This issue blocks ~~MDEV-20480~~ [ ~~MDEV-20480~~ ]

Aleksey Midenkov made changes - 2019-11-14 11:21

Link

This issue relates to ~~MDEV-20480~~ [ ~~MDEV-20480~~ ]

Aleksey Midenkov made changes - 2019-11-14 11:23

Fixing Priority	250
Parent		MDEV-16417 [ 68014 ]
Issue Type	Task [ 3 ]	Technical task [ 7 ]

Aleksey Midenkov made changes - 2019-11-18 22:43

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Keep in sync referenced TABLE_SHARE::referenced_keys when foreign table is dropped, renamed or altered.

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Release TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column).

Aleksey Midenkov made changes - 2019-11-18 22:44

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Release TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column).

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Release TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Release TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Aleksey Midenkov made changes - 2019-11-18 22:44

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Release TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Release TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Aleksey Midenkov made changes - 2019-11-18 22:47

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

Aleksey Midenkov made changes - 2019-11-19 14:56

Link

This issue blocks ~~MDEV-20480~~ [ ~~MDEV-20480~~ ]

Aleksey Midenkov made changes - 2019-11-20 16:57

Assignee	Aleksey Midenkov [ midenok ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Aleksey Midenkov added a comment - 2019-11-20 16:58 - edited

Branch bb-10.5-midenok-MDEV-16417.

Aleksey Midenkov added a comment - 2019-11-20 16:58 - edited Branch bb-10.5-midenok-MDEV-16417 .

Nikita Malyavin made changes - 2019-12-21 05:17

Link

This issue relates to MDEV-16983 [ MDEV-16983 ]

Ralf Gebhardt made changes - 2020-02-17 15:00

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.5 [ 23123 ]

Aleksey Midenkov made changes - 2020-08-07 18:47

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table() does.

Aleksey Midenkov made changes - 2020-08-07 18:48

Link

This issue is duplicated by ~~MDEV-21051~~ [ ~~MDEV-21051~~ ]

Aleksey Midenkov made changes - 2020-08-07 18:51

Link

This issue is duplicated by ~~MDEV-21311~~ [ ~~MDEV-21311~~ ]

Aleksey Midenkov made changes - 2020-08-07 18:52

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table() does.

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table() does.

h3. Converge Foreign_key and supplemental generated Key together
{{mysql_prepare_create_table()}} does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores {{Foreign_key}} objects. Now as {{Foreign_key}} data needs to be stored in FRM files as well this processing must be done for them like for any other {{Key}} objects.

Replace {{Key::FOREIGN_KEY}} type with {{Key::foreign}} flag of type {{Key::MULTIPLE}} and {{Key::generated}} set to *true*. Construct one object with {{Key::foreign}} == *true* instead of two objects of type {{Key::FOREIGN_KEY}} and {{Key::MULTIPLE}}.

Aleksey Midenkov made changes - 2021-03-23 11:03

Fix Version/s		10.7 [ 24805 ]
Fix Version/s	10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-08-19 14:07

Priority

Critical [ 2 ]

Major [ 3 ]

Ralf Gebhardt made changes - 2021-10-25 09:09

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.7 [ 24805 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 100531 ]

MariaDB v4 [ 143752 ]

Sergei Golubchik made changes - 2022-04-06 10:48

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.8 [ 26121 ]

Ralf Gebhardt made changes - 2022-06-23 08:27

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.10 [ 27530 ]

Sergei Golubchik made changes - 2022-08-02 19:58

Fix Version/s		10.12 [ 28320 ]
Fix Version/s	10.11 [ 27614 ]

Aleksey Midenkov made changes - 2022-08-08 23:40

Assignee

Sergei Golubchik [ serg ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2022-08-08 23:41

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Aleksey Midenkov made changes - 2022-08-08 23:41

Status

Stalled [ 10000 ]

In Progress [ 3 ]

AirFocus made changes - 2022-08-09 16:10

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table() does.

h3. Converge Foreign_key and supplemental generated Key together
{{mysql_prepare_create_table()}} does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores {{Foreign_key}} objects. Now as {{Foreign_key}} data needs to be stored in FRM files as well this processing must be done for them like for any other {{Key}} objects.

Replace {{Key::FOREIGN_KEY}} type with {{Key::foreign}} flag of type {{Key::MULTIPLE}} and {{Key::generated}} set to *true*. Construct one object with {{Key::foreign}} == *true* instead of two objects of type {{Key::FOREIGN_KEY}} and {{Key::MULTIPLE}}.

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE\_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE\_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

# Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY\_INFO field in extra2 section of FRM file.

# Modify mysql_prepare_create\_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

# Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN

# To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE\_SHARE.

Use shadow write and rename like fast_alter_partition\_table() does.

h3. Converge Foreign\_key and supplemental generated Key together

{{mysql_prepare_create_table()}} does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores {{Foreign_key}} objects. Now as {{Foreign_key}} data needs to be stored in FRM files as well this processing must be done for them like for any other {{Key}} objects.

Replace {{Key::FOREIGN_KEY}} type with {{Key::foreign}} flag of type {{Key::MULTIPLE}} and {{Key::generated}} set to *true*. Construct one object with {{Key::foreign}} == *true* instead of two objects of type {{Key::FOREIGN_KEY}} and {{Key::MULTIPLE}}.

Aleksey Midenkov made changes - 2022-08-14 22:25

Assignee	Aleksey Midenkov [ midenok ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Aleksey Midenkov made changes - 2022-09-06 11:57

Assignee

Oleksandr Byelkin [ sanja ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2022-09-06 11:57

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Aleksey Midenkov made changes - 2022-09-06 11:57

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov added a comment - 2022-12-05 23:50 - edited

Please review bb-11.0-midenok-MDEV-20865

Aleksey Midenkov added a comment - 2022-12-05 23:50 - edited Please review bb-11.0-midenok-MDEV-20865

Aleksey Midenkov made changes - 2022-12-05 23:50

Assignee	Aleksey Midenkov [ midenok ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Ralf Gebhardt made changes - 2023-01-31 09:32

Fix Version/s		11.1 [ 28549 ]
Fix Version/s	11.0 [ 28320 ]

Oleksandr Byelkin made changes - 2023-02-22 09:32

Description

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE\_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE\_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

# Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY\_INFO field in extra2 section of FRM file.

# Modify mysql_prepare_create\_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

# Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN

# To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE\_SHARE.

Use shadow write and rename like fast_alter_partition\_table() does.

h3. Converge Foreign\_key and supplemental generated Key together

{{mysql_prepare_create_table()}} does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores {{Foreign_key}} objects. Now as {{Foreign_key}} data needs to be stored in FRM files as well this processing must be done for them like for any other {{Key}} objects.

Replace {{Key::FOREIGN_KEY}} type with {{Key::foreign}} flag of type {{Key::MULTIPLE}} and {{Key::generated}} set to *true*. Construct one object with {{Key::foreign}} == *true* instead of two objects of type {{Key::FOREIGN_KEY}} and {{Key::MULTIPLE}}.

Add TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys as pointers to List<FOREIGN_KEY_INFO>.

Remove handler foreign key interface: get_foreign_key_list(), get_parent_foreign_key_list(), referenced_by_foreign_key().

Remove cached TABLE\_SHARE objects of referenced tables when foreign table is dropped, renamed or altered (add foreign key, rename column of foreign key).

Remove cached TABLE\_SHARE objects of foreign tables when referenced table is renamed or altered (rename column of referenced key).

Debug build: check consistency between opened foreign and referenced tables on table flush.

h3. Store foreign keys into FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY\_INFO field in extra2 section of FRM file.

2. Modify mysql_prepare_create\_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables:

RENAME TABLE
DROP TABLE
CREATE TABLE
ADD FOREIGN KEY
DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on referenced tables:

RENAME TABLE
RENAME COLUMN

4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE\_SHARE.

Use shadow write and rename like fast_alter_partition\_table() does.

h3. Converge Foreign\_key and supplemental generated Key together

{{mysql_prepare_create_table()}} does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores {{Foreign_key}} objects. Now as {{Foreign_key}} data needs to be stored in FRM files as well this processing must be done for them like for any other {{Key}} objects.

Replace {{Key::FOREIGN_KEY}} type with {{Key::foreign}} flag of type {{Key::MULTIPLE}} and {{Key::generated}} set to *true*. Construct one object with {{Key::foreign}} == *true* instead of two objects of type {{Key::FOREIGN_KEY}} and {{Key::MULTIPLE}}.

Oleksandr Byelkin made changes - 2023-02-28 12:27

Assignee	Oleksandr Byelkin [ sanja ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2023-03-07 08:50

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2023-03-08 15:16

Assignee

Aleksey Midenkov [ midenok ]

Oleksandr Byelkin [ sanja ]

Ralf Gebhardt made changes - 2023-05-04 17:49

Fix Version/s		11.2 [ 28603 ]
Fix Version/s	11.1 [ 28549 ]

Aleksey Midenkov made changes - 2023-08-29 12:31

Comment

[ Please review first party of refactorings in [bb-10.10-midenok-MDEV-20865|https://github.com/MariaDB/server/commits/bb-10.10-midenok-MDEV-20865] ]

Aleksey Midenkov made changes - 2023-08-29 12:31

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2023-09-28 08:57

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.2 [ 28603 ]

Oleksandr Byelkin made changes - 2023-09-29 13:24

Status

In Progress [ 3 ]

In Review [ 10002 ]

Aleksey Midenkov made changes - 2023-09-29 13:24

Assignee

Oleksandr Byelkin [ sanja ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2023-09-29 13:25

Assignee

Aleksey Midenkov [ midenok ]

Oleksandr Byelkin [ sanja ]

Ralf Gebhardt made changes - 2023-10-27 14:14

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Oleksandr Byelkin added a comment - 2023-11-23 09:38

OK to push

Oleksandr Byelkin added a comment - 2023-11-23 09:38 OK to push

Oleksandr Byelkin made changes - 2023-11-23 09:38

Assignee	Oleksandr Byelkin [ sanja ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2023-12-12 11:51

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov added a comment - 2023-12-19 12:08

Rebased to 11.4 (bb-11.4-midenok-MDEV-20865)

https://buildbot.mariadb.org/#/builders/551/builds/9239/steps/7/logs/stdio

==112184== Invalid write of size 4

==112184==    at 0xA6859D: base_list::operator=(base_list const&) (sql_list.h:153)

==112184==    by 0xC758F6: List<FK_info>::operator=(List<FK_info> const&) (sql_list.h:497)

==112184==    by 0xC75924: FK_list::operator=(FK_list const&) (handler.h:1093)

==112184==    by 0xCFDDA7: FK_table_backup::rollback() (sql_alter.h:52)

==112184==    by 0xCFDCC4: FK_table_backup::~FK_table_backup() (sql_alter.h:42)

==112184==    by 0xCFF8DD: Alter_table_ctx::~Alter_table_ctx() (sql_alter.h:396)

==112184==    by 0xD90489: vers_create_partitions(THD*, TABLE_LIST*, unsigned int) (partition_info.cc:998)

==112184==    by 0xB07688: Open_table_context::recover_from_failed_open() (sql_base.cc:3531)

==112184==    by 0xB09C68: open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.cc:4675)

==112184==    by 0xAFFE18: open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.h:270)

==112184==  Address 0xbe57610 is 928 bytes inside a block of size 1,800 free'd

==112184==    at 0x484B27F: free (in /usr/libexec/valgrind/vgpreload_memcheck-amd64-linux.so)

==112184==    by 0x1A3D34E: my_free (my_malloc.c:221)

==112184==    by 0x1A2CACF: root_free (my_alloc.c:83)

==112184==    by 0x1A2D865: free_root (my_alloc.c:516)

==112184==    by 0xD485A9: TABLE_SHARE::destroy() (table.cc:525)

==112184==    by 0xD485E0: free_table_share(TABLE_SHARE*) (table.cc:541)

==112184==    by 0xF05311: tdc_delete_share_from_hash(TDC_element*) (table_cache.cc:534)

==112184==    by 0xF06895: tdc_release_share(TABLE_SHARE*) (table_cache.cc:1007)

==112184==    by 0xF03CD8: intern_close_table(TABLE*) (table_cache.cc:231)

==112184==    by 0xF03E59: tc_remove_table(TABLE*) (table_cache.cc:268)

Aleksey Midenkov added a comment - 2023-12-19 12:08 Rebased to 11.4 (bb-11.4-midenok- MDEV-20865 ) https://buildbot.mariadb.org/#/builders/551/builds/9239/steps/7/logs/stdio ==112184== Invalid write of size 4 ==112184== at 0xA6859D: base_list::operator=(base_list const&) (sql_list.h:153) ==112184== by 0xC758F6: List<FK_info>::operator=(List<FK_info> const&) (sql_list.h:497) ==112184== by 0xC75924: FK_list::operator=(FK_list const&) (handler.h:1093) ==112184== by 0xCFDDA7: FK_table_backup::rollback() (sql_alter.h:52) ==112184== by 0xCFDCC4: FK_table_backup::~FK_table_backup() (sql_alter.h:42) ==112184== by 0xCFF8DD: Alter_table_ctx::~Alter_table_ctx() (sql_alter.h:396) ==112184== by 0xD90489: vers_create_partitions(THD*, TABLE_LIST*, unsigned int) (partition_info.cc:998) ==112184== by 0xB07688: Open_table_context::recover_from_failed_open() (sql_base.cc:3531) ==112184== by 0xB09C68: open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.cc:4675) ==112184== by 0xAFFE18: open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.h:270) ==112184== Address 0xbe57610 is 928 bytes inside a block of size 1,800 free'd ==112184== at 0x484B27F: free (in /usr/libexec/valgrind/vgpreload_memcheck-amd64-linux.so) ==112184== by 0x1A3D34E: my_free (my_malloc.c:221) ==112184== by 0x1A2CACF: root_free (my_alloc.c:83) ==112184== by 0x1A2D865: free_root (my_alloc.c:516) ==112184== by 0xD485A9: TABLE_SHARE::destroy() (table.cc:525) ==112184== by 0xD485E0: free_table_share(TABLE_SHARE*) (table.cc:541) ==112184== by 0xF05311: tdc_delete_share_from_hash(TDC_element*) (table_cache.cc:534) ==112184== by 0xF06895: tdc_release_share(TABLE_SHARE*) (table_cache.cc:1007) ==112184== by 0xF03CD8: intern_close_table(TABLE*) (table_cache.cc:231) ==112184== by 0xF03E59: tc_remove_table(TABLE*) (table_cache.cc:268)

Aleksey Midenkov made changes - 2023-12-19 12:09

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Aleksey Midenkov made changes - 2024-01-29 15:06

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov added a comment - 2024-02-27 12:15

Please test bb-11.4-midenok-MDEV-20865

Aleksey Midenkov added a comment - 2024-02-27 12:15 Please test bb-11.4-midenok- MDEV-20865

Aleksey Midenkov made changes - 2024-02-27 12:15

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2024-02-27 12:52

Assignee

Aleksey Midenkov [ midenok ]

Elena Stepanova [ elenst ]

Lena Startseva made changes - 2024-02-27 12:52

Assignee

Elena Stepanova [ elenst ]

Lena Startseva [ JIRAUSER50478 ]

Aleksey Midenkov made changes - 2024-02-27 14:19

Assignee	Lena Startseva [ JIRAUSER50478 ]	Aleksey Midenkov [ midenok ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2024-02-27 14:19

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Aleksey Midenkov made changes - 2024-02-27 14:19

Assignee

Aleksey Midenkov [ midenok ]

Lena Startseva [ JIRAUSER50478 ]

Marko Mäkelä added a comment - 2024-03-05 16:14

I think that the InnoDB changes must be reviewed by an InnoDB developer. One claim in the commit message that I found caught my eye:

Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name.

This is not entirely correct. I think that we may face failure scenarios like MDEV-28933 for the MDEV-25292 CREATE OR REPLACE TABLE that had to be reverted.

Marko Mäkelä added a comment - 2024-03-05 16:14 I think that the InnoDB changes must be reviewed by an InnoDB developer. One claim in the commit message that I found caught my eye: Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name. This is not entirely correct. I think that we may face failure scenarios like MDEV-28933 for the MDEV-25292 CREATE OR REPLACE TABLE that had to be reverted.

Marko Mäkelä added a comment - 2024-03-05 16:50

Do I understand it correctly that this implementation is increasing the memory footprint when FOREIGN KEY constraints exist? If the metadata storage was moved to a higher layer, then the lower-level storage (such as dict_foreign_t) would have to be removed. Apart from increased memory usage, duplicated data structures would create a potential problem that the two data dictionary caches become inconsistent with each other.

Marko Mäkelä added a comment - 2024-03-05 16:50 Do I understand it correctly that this implementation is increasing the memory footprint when FOREIGN KEY constraints exist? If the metadata storage was moved to a higher layer, then the lower-level storage (such as dict_foreign_t ) would have to be removed . Apart from increased memory usage, duplicated data structures would create a potential problem that the two data dictionary caches become inconsistent with each other.

Marko Mäkelä added a comment - 2024-03-07 09:01

The current Description says what is being done but not why it needs to be done. I can imagine one possible motivation: making the implementation of ~~MDEV-21051~~ easier. (It is currently marked as a duplicate of this task; I think that it might be more appropriate to say that it is blocked by this task.)

I think that before this can be included in a release, we also need performance testing, which should include not only execution time but also the memory usage, including any fragmentation. We have dismissed such issues as "not a bug" in the past (for example, in ~~MDEV-14050~~), but I do not think that it is acceptable to increase the memory usage or the amount of allocation anti-patterns.

Marko Mäkelä added a comment - 2024-03-07 09:01 The current Description says what is being done but not why it needs to be done. I can imagine one possible motivation: making the implementation of MDEV-21051 easier. (It is currently marked as a duplicate of this task; I think that it might be more appropriate to say that it is blocked by this task.) I think that before this can be included in a release, we also need performance testing, which should include not only execution time but also the memory usage, including any fragmentation. We have dismissed such issues as "not a bug" in the past (for example, in MDEV-14050 ), but I do not think that it is acceptable to increase the memory usage or the amount of allocation anti-patterns.

Marko Mäkelä made changes - 2024-03-13 06:56

Link

This issue blocks MDEV-21052 [ MDEV-21052 ]

Lena Startseva made changes - 2024-03-21 03:59

Link

This issue causes MDEV-33741 [ MDEV-33741 ]

Aleksey Midenkov made changes - 2024-03-26 11:52

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Lena Startseva made changes - 2024-03-27 12:09

Link

This issue causes ~~MDEV-33780~~ [ ~~MDEV-33780~~ ]

Sergei Golubchik made changes - 2024-06-11 10:05

Assignee

Lena Startseva [ JIRAUSER50478 ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-06-11 10:05

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2024-06-11 10:05

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Aleksey Midenkov added a comment - 2024-06-18 10:35

It is always percentage of overhead which is acceptable or not. 10% total increase is unacceptable. 0.5% is acceptable. What is the threshold between these two numbers is the subject of consideration. I think it should be 2% which is far sufficient for the task IMO.

This subtask should not be merged alone due to MDEV-33741 and it seems to depend on the last subtask (crash safety), so it turns out the whole MDEV-16417 should be pushed at once. I'm currently updating MDEV-21053.

Aleksey Midenkov added a comment - 2024-06-18 10:35 It is always percentage of overhead which is acceptable or not. 10% total increase is unacceptable. 0.5% is acceptable. What is the threshold between these two numbers is the subject of consideration. I think it should be 2% which is far sufficient for the task IMO. This subtask should not be merged alone due to MDEV-33741 and it seems to depend on the last subtask (crash safety), so it turns out the whole MDEV-16417 should be pushed at once. I'm currently updating MDEV-21053 .

Sergei Golubchik made changes - 2024-06-18 14:35

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Marko Mäkelä added a comment - 2024-07-30 13:04

I was asked to review MDEV-21652 in a branch that includes many other changes, including a version of this MDEV-20865, as well as MDEV-21052 which I have not been asked to review.

I think that MDEV-21052 and MDEV-20865 needs to be in one atomic changeset, because InnoDB currently is the only storage engine that handles FOREIGN KEY metadata. Doing that would address my 2024-03-05 comment.

The branch currently bundles too many changes that have a similar topic but should be fixable in isolation.

I think that the changes to memory cache data structures (including MDEV-20865, MDEV-21052) need to be thoroughly tested and reviewed first, in a way that does not involve any file format changes. My point is that we may store FOREIGN KEY metadata in TABLE_SHARE, by populating the data structure both from a .frm file and the InnoDB SYS_FOREIGN and SYS_FOREIGN_COLS tables.

Marko Mäkelä added a comment - 2024-07-30 13:04 I was asked to review MDEV-21652 in a branch that includes many other changes, including a version of this MDEV-20865 , as well as MDEV-21052 which I have not been asked to review. I think that MDEV-21052 and MDEV-20865 needs to be in one atomic changeset, because InnoDB currently is the only storage engine that handles FOREIGN KEY metadata. Doing that would address my 2024-03-05 comment. The branch currently bundles too many changes that have a similar topic but should be fixable in isolation. I think that the changes to memory cache data structures (including MDEV-20865 , MDEV-21052 ) need to be thoroughly tested and reviewed first, in a way that does not involve any file format changes. My point is that we may store FOREIGN KEY metadata in TABLE_SHARE , by populating the data structure both from a .frm file and the InnoDB SYS_FOREIGN and SYS_FOREIGN_COLS tables.

Marko Mäkelä made changes - 2024-07-30 13:06

Link

This issue blocks MDEV-21652 [ MDEV-21652 ]

Sergei Golubchik made changes - 2024-09-03 14:36

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7 [ 29815 ]

Marko Mäkelä added a comment - 2024-09-24 08:20

As far as I understand, implementing this would remove a significant amount of contention on dict_sys.latch, with the following kind of stack traces:

ssux_lock_impl<false>::rd_wait()

ha_innobase::referenced_by_foreign_key()

prepare_fk_prelocking_list(THD*, Query_tables_list*, TABLE_LIST*, bool*, unsigned char)

DML_prelocking_strategy::handle_table(THD*, Query_tables_list*, TABLE_LIST*, bool*)

extend_table_list(THD*, TABLE_LIST*, Prelocking_strategy*, bool)

open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*)

open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*)

Note that this affects any workloads, including those that do not use any FOREIGN KEY constraints at all.

Marko Mäkelä added a comment - 2024-09-24 08:20 As far as I understand, implementing this would remove a significant amount of contention on dict_sys.latch , with the following kind of stack traces: ssux_lock_impl<false>::rd_wait() ha_innobase::referenced_by_foreign_key() prepare_fk_prelocking_list(THD*, Query_tables_list*, TABLE_LIST*, bool*, unsigned char) DML_prelocking_strategy::handle_table(THD*, Query_tables_list*, TABLE_LIST*, bool*) extend_table_list(THD*, TABLE_LIST*, Prelocking_strategy*, bool) open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*) Note that this affects any workloads, including those that do not use any FOREIGN KEY constraints at all.

Marko Mäkelä made changes - 2024-09-24 08:20

Labels

foreign-keys performance

Aleksey Midenkov made changes - 2024-10-29 16:19

Summary

Store foreign key info in TABLE_SHARE

Store foreign key info in FRM and TABLE_SHARE

Marc made changes - 2024-10-31 15:08

Link

This issue relates to ~~MDEV-35303~~ [ ~~MDEV-35303~~ ]

Sergei Golubchik made changes - 2024-11-15 14:08

Link

This issue is duplicated by ~~MDEV-35303~~ [ ~~MDEV-35303~~ ]

Sergei Golubchik made changes - 2024-11-15 14:08

Link

This issue relates to ~~MDEV-35303~~ [ ~~MDEV-35303~~ ]

Sergei Golubchik made changes - 2024-11-19 16:28

Link

This issue is part of MDEV-16417 [ MDEV-16417 ]

Sergei Golubchik made changes - 2024-11-19 16:28

Fix Version/s		11.9 [ 29945 ]
Fix Version/s	11.8 [ 29921 ]

Sergei Golubchik made changes - 2025-03-21 13:57

Fix Version/s		12.1 [ 29992 ]
Fix Version/s	12.0 [ 29945 ]

Julien Fritsch made changes - 6 days ago

Fix Version/s		12.3 [ 30147 ]
Fix Version/s	12.1 [ 29992 ]

MariaDB Server

Store foreign key info in FRM and TABLE_SHARE

Details

Description

Store foreign keys into FRM files

Converge Foreign_key and supplemental generated Key together

Attachments

Issue Links

Activity

People

Dates

Git Integration