Details
-
Technical task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-21052
InnoDB foreign key refactoring for TABLE_SHARE::foreign_keys
-
- Stalled
-