XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 10.12
    • Component/s: None
    • Labels:

      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

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.