Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34309

CHECK TABLE should check that foreign key relations are correct

Details

    Description

      Currently there is no easy way for a user to check if a database is consistent.
      One can check individual tables with CHECK TABLE but there is now to check that
      all foreign key tables has all the required keys and that the original table has all the rows
      expected by the foreign key table.

      We should extended CHECK TABLE .. EXTENDED or alternative CHECK TABLE ... REFERENCES to check that all foreign key relations are consistent.

      This should preferably be done on the upper, sql, level and not in InnoDB.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35730 Server crashes in Field::ptr_in_record / handler::check_record_reference

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35731 Assertion `(mem_root->flags & 4) == 0' failed upon 2nd execution of CHECK TABLE with FK

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35733 ASAN errors in row_mysql_store_col_in_innobase_format / check_key_referential_integrity

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35734 ASAN errors in cmp_dtuple_rec_with_match_low / check_key_referential_integrity

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35737 MSAN errors in check_table_referential_checks_needed upon LOAD INDEX

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35908 Unexpected error, crash, MSAN errors, assertion failures upon CHECK

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35936 More ASAN errors in check_key_referential_integrity

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35961 Not quite correct error message upon CHECK with FK violation due to wrong engine

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36643 Assertion failures upon concurrent CHECK and DML on table with FK

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          is blocked by

          Task - A task that needs to be done. MDEV-36171 Specify requirements for foreign keys

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          relates to

          Task - A task that needs to be done. MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          mentioned in

          Page Loading...

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            sanja Oleksandr Byelkin added a comment -

            I rebased it on main to bb-11.8-check-table.

            I also see 8 commits there who review other 4 ?

            sanja Oleksandr Byelkin added a comment - I rebased it on main to bb-11.8-check-table. I also see 8 commits there who review other 4 ?
            nikitamalyavin Nikita Malyavin added a comment -

            Sergei reviews them.

            nikitamalyavin Nikita Malyavin added a comment - Sergei reviews them.
            sanja Oleksandr Byelkin added a comment -

            It is OK to push after renaming commits related to this MDEV starting with: MDEV-34309, i.e something like this.:

            MDEV-34309 prereq. FOREIGN_KEY_INFO: Store Lex_* strings without extra pointer indirection.

            MDEV-34309 prereq. Fix deallocation of Sql_cmd_dml::result

            Just to link them to the task which inspeared you for such changes (I'd put the MDEV-34309 before your cleanup also)

            sanja Oleksandr Byelkin added a comment - It is OK to push after renaming commits related to this MDEV starting with: MDEV-34309 , i.e something like this.: MDEV-34309 prereq. FOREIGN_KEY_INFO: Store Lex_* strings without extra pointer indirection. MDEV-34309 prereq. Fix deallocation of Sql_cmd_dml::result Just to link them to the task which inspeared you for such changes (I'd put the MDEV-34309 before your cleanup also)
            serg Sergei Golubchik added a comment -

            cleanup is ok too

            serg Sergei Golubchik added a comment - cleanup is ok too
            elenst Elena Stepanova added a comment -

            It's been agreed that the feature won't go to 11.8 as we try to find legacy issues when

            • foreign keys are claimed to be broken when they should be okay (example: MDEV-35962),
            • foreign keys pretend to work when they probably shouldn't (example: MDEV-35908, INET6 column referencing TIME column),
            • there is no clarity whether the foreign keys should work or not (example: MDEV-35936 – different field lengths, more in the comments to MDEV-35908) – this may be just a documentation issue, but it would be really good to get it documented while we are at it.

            The goal is to avoid the hard choice between releasing CHECK which alerts about fake problems, or CHECK which doesn't show real problems, or CHECK which is inconsistent with DML/DDL related to the same foreign keys.

            Thus, while the bugs will probably be fixed outside the scope of this task, as the fixes would go to earlier versions when possible, the testing scope of the task will increase.

            elenst Elena Stepanova added a comment - It's been agreed that the feature won't go to 11.8 as we try to find legacy issues when foreign keys are claimed to be broken when they should be okay (example: MDEV-35962 ), foreign keys pretend to work when they probably shouldn't (example: MDEV-35908 , INET6 column referencing TIME column), there is no clarity whether the foreign keys should work or not (example: MDEV-35936 – different field lengths, more in the comments to MDEV-35908 ) – this may be just a documentation issue, but it would be really good to get it documented while we are at it. The goal is to avoid the hard choice between releasing CHECK which alerts about fake problems, or CHECK which doesn't show real problems, or CHECK which is inconsistent with DML/DDL related to the same foreign keys. Thus, while the bugs will probably be fixed outside the scope of this task, as the fixes would go to earlier versions when possible, the testing scope of the task will increase.

            People

              elenst Elena Stepanova
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.