Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34309 CHECK TABLE should check that foreign key relations are correct
  3. MDEV-36171

Specify requirements for foreign keys

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Won't Do
    • N/A
    • N/A
    • Documentation, Server
    • None

    Description

      MDEV-34309 got stalled in 11.8 partially because there was no clear understanding when exactly foreign keys should be allowed and when not, the documentation is very vague in this regard, and implementation is even more so.

      The KB says

      The foreign key columns and the referenced columns must be of the same type, or similar types

      which does not help, as it doesn't say what "similar types" means.
      The MySQL manual is similarly obscure.

      The implementation sometimes demonstrates an odd approach to this, when for example TIME and INET6 data types are considered similar enough (MDEV-35908). Another, less exotic, uncertainty is about varchar(M) <=> char(N) and so on.

      There is also a question of other field/table options, e.g. character sets etc.

      All in all, for MDEV-34309 functional testing, we need to know when CHECK TABLE .. EXTENDED should alert about a problem and when it should return success. It was decided that it doesn't make sense to follow the current implementation blindly, but instead we should try to make CHECK TABLE ... EXTENDED return really correct diagnostics regarding foreign keys, and fix (possibly in earlier versions) legacy inconsistencies.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment -

          I'd say, the definition is — one can do mariadb-dump of everything and then load it back.

          That is, "correct" is whatever InnoDB accepts as correct. And "wrong" is whatever InnoDB doesn't accept.

          serg Sergei Golubchik added a comment - I'd say, the definition is — one can do mariadb-dump of everything and then load it back. That is, "correct" is whatever InnoDB accepts as correct. And "wrong" is whatever InnoDB doesn't accept.
          elenst Elena Stepanova added a comment -

          I'm closing it as "won't do" as i've run out of arguments, the specification for MDEV-34309 testing will be as above,

          "correct" is whatever InnoDB accepts as correct

          and PASS/FAIL criteria will be chosen accordingly (interpolating it to "whatever InnoDB doesn't accept as correct is incorrect").
          As discussed elsewhere, the meaning of "InnoDB acceptance" is that the table can be re-created with the given structure.
          mariadb-dump is irrelevant and cannot be used for verification, as it runs with FOREIGN_KEY_CHECKS=0 because it otherwise it might not be able to create even valid structures.

          elenst Elena Stepanova added a comment - I'm closing it as "won't do" as i've run out of arguments, the specification for MDEV-34309 testing will be as above, "correct" is whatever InnoDB accepts as correct and PASS/FAIL criteria will be chosen accordingly (interpolating it to "whatever InnoDB doesn't accept as correct is incorrect"). As discussed elsewhere, the meaning of "InnoDB acceptance" is that the table can be re-created with the given structure. mariadb-dump is irrelevant and cannot be used for verification, as it runs with FOREIGN_KEY_CHECKS=0 because it otherwise it might not be able to create even valid structures.

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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