Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16417 Store Foreign Key metadata outside of InnoDB
  3. MDEV-23433

Parent table discovery for children (update parent's frm on REPAIR TABLE)

    XMLWordPrintable

Details

    • Technical task
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • 11.8
    • 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

          Activity

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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