Store Foreign Key metadata outside of InnoDB (MDEV-16417)

[MDEV-23433] Parent table discovery for children (REPAIR TABLE) Created: 2020-08-07  Updated: 2023-10-30

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 11.5

Type: Technical task Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: foreign-keys

Issue Links:
Relates
relates to MDEV-21052 InnoDB foreign key refactoring for TA... Stalled

 Description   

When child table is created before parent table like in the example below:

set foreign_key_checks= 0;
 
create table child (fk int references parent (id));
 
set foreign_key_checks= 1;
 
 
 
create table parent (id int primary key);

parent doesn't know anything about child. We cannot acknowledge child by scanning because it would require to scan all the databases on every CREATE TABLE. But we can discover the child when DML happens on it. MDEV-21052 solved the problem of updating parent's TABLE_SHARE.

This task is the subject of updating parent's FRM file:

set foreign_key_checks= 0;
 
create table child (fk int references parent (id));
 
set foreign_key_checks= 1;
 
 
 
create table parent (id int primary key);
 
check table child;
 
+------------+-------+----------+--------------------------------------------------------+
 
| Table      | Op    | Msg_type | Msg_text                                               |
 
+------------+-------+----------+--------------------------------------------------------+
 
| test.child | check | Error    | Referenced table test.parent does not refer this table |
 
| test.child | check | Note     | Found 1 foreign keys                                   |
 
| test.child | check | status   | Operation failed                                       |
 
+------------+-------+----------+--------------------------------------------------------+
 
check table parent;
 
+-------------+-------+----------+----------+
 
| Table       | Op    | Msg_type | Msg_text |
 
+-------------+-------+----------+----------+
 
| test.parent | check | status   | OK       |
 
+-------------+-------+----------+----------+
 
 
 
repair table child;
 
(some message about repairing `parent` referenced hints)
 
 
 
check table child;
 
+------------+-------+----------+----------------------+
 
| Table      | Op    | Msg_type | Msg_text             |
 
+------------+-------+----------+----------------------+
 
| test.child | check | Note     | Found 1 foreign keys |
 
| test.child | check | status   | OK                   |
 
+------------+-------+----------+----------------------+
 
check table parent;
 
+-------------+-------+----------+-------------------------+
 
| Table       | Op    | Msg_type | Msg_text                |
 
+-------------+-------+----------+-------------------------+
 
| test.parent | check | Note     | Found 1 referenced keys |
 
| test.parent | check | status   | OK                      |
 
+-------------+-------+----------+-------------------------+

Note that we don't want to scan all databases for collecting referenced hints and thus we cannot repair `parent` directly by repair table parent: only REPAIR TABLE on `child` fixes missing hints of foreign keys `child` provides.
Generated at Thu Feb 08 09:22:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.