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

Partitioning in child tables with foreign keys

Details

    Description

      Implementation of foreign keys in partitioned tables with limitations is relatively simple and is covered by this task. Full support of foreign keys in partitioning is covered by MDEV-12483.

      This task is basically divided into 3 parts:

      1. Remove prohibiting code, this allows FKs to be created for partitions and DML to work;
      2. Allow partitioned FKs at SQL layer for such functions as SHOW CREATE;
      3. Implement correct handling of FKs when partitioning configuration changes.

      Limitations

      1. Foreign keys cannot refer partitioned table

      Foreign keys cannot refer to partitioned table even SYSTEM_TIME-partitioned still. create_foreign_keys() at InnoDB layer receives Foreign_key about referenced table T, but no such table exists at InnoDB layer, only partition tables T#P#p0, T#P#p1, ..., T#P#pn. Finding out it is SYSTEM_TIME partitioning and current partition at that point is impossible without modification of SYS_TABLES or opening TABLE object of referenced table. Both should be avoided as this is superseded by MDEV-12483.

      2. CASCADE and SET NULL actions cannot work

      In partitioned foreign table CASCADE and SET NULL actions cannot work as these actions update row data which is the subject of row placement into another partition and that cannot be done at InnoDB layer. DELETE CASCADE for SYSTEM_TIME partitioning requires the row to be moved from current to history partition. CASCADE and SET NULL actions disabled completely in CREATE TABLE for partitioned foreign tables in this task.

      Allow partitioned FKs at SQL layer for such functions as SHOW CREATE

      Through standard handler interface get_foreign_key_list() foreign keys are returned to SQL layer. For SYSTEM_TIME partitioning from current partition, for any other partitioning from first read-marked partition.

      Implement correct handling of FKs when partitioning configuration changes

      ALTER operations such as ADD PARTITION, DROP PARTITION, REMOVE PARTITIONING , etc. must be reflected into correct configuration of foreign keys in InnoDB. Handling of foreign key ID for temporary tables in ALTER may be done similar to MDEV-28933.

      Attachments

        1. y.result
          3 kB
        2. y.test
          1 kB

        Issue Links

          Activity

            The reviewers may find the task in bb-11.7-MDEV-19191-fk_partitioning

            midenok Aleksey Midenkov added a comment - The reviewers may find the task in bb-11.7-MDEV-19191-fk_partitioning

            333b49f63c2364e845b034f20a99d98cdf2ef3dc - OK

            51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning,
            irrelevant, harmful IMHO)

            3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no
            description, reasoning

            1784f005e4a9cf8f929ea75bd88cb42d20e5cbe4 - not checked as it is innodb
            fa432696c1e1629c20fd17660850a4b883975e95 - innodb
            f2e7955615be7b838eb1a65d20ed7a9b006452ae - innodb
            5a0ded1a6173cdcd1057d906e11e6f45069b2d30 - innodb

            f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79
            "ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely
            inacceptable error message, if you do not have name explain how or why
            it happened (Duplicate constrain name due to clash with internal innodb
            nameing or something like this)

            37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description,
            what is cleaned up, why?

            83c92bd6644be4be31c1772f7670c8fe0334bb86 - OK

            6d534cca6ca4edbe87b3d38cca6adce111695822 -

            • lines are longer than 80 characters
            • assume that innodb part checked by the innodb team (including strchr)

            otherwise it s ok

            sanja Oleksandr Byelkin added a comment - 333b49f63c2364e845b034f20a99d98cdf2ef3dc - OK 51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning, irrelevant, harmful IMHO) 3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no description, reasoning 1784f005e4a9cf8f929ea75bd88cb42d20e5cbe4 - not checked as it is innodb fa432696c1e1629c20fd17660850a4b883975e95 - innodb f2e7955615be7b838eb1a65d20ed7a9b006452ae - innodb 5a0ded1a6173cdcd1057d906e11e6f45069b2d30 - innodb f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79 "ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely inacceptable error message, if you do not have name explain how or why it happened (Duplicate constrain name due to clash with internal innodb nameing or something like this) 37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description, what is cleaned up, why? 83c92bd6644be4be31c1772f7670c8fe0334bb86 - OK 6d534cca6ca4edbe87b3d38cca6adce111695822 - lines are longer than 80 characters assume that innodb part checked by the innodb team (including strchr) otherwise it s ok

            51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning,
            irrelevant, harmful IMHO)

            Dropped

            3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no
            description, reasoning

            Dropped

            f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79
            "ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely
            inacceptable error message, if you do not have name explain how or why
            it happened (Duplicate constrain name due to clash with internal innodb
            nameing or something like this)

            Fixed

            37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description,
            what is cleaned up, why?

            Fixed

            6d534cca6ca4edbe87b3d38cca6adce111695822 -

            lines are longer than 80 characters

            Fixed

            midenok Aleksey Midenkov added a comment - 51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning, irrelevant, harmful IMHO) Dropped 3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no description, reasoning Dropped f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79 "ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely inacceptable error message, if you do not have name explain how or why it happened (Duplicate constrain name due to clash with internal innodb nameing or something like this) Fixed 37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description, what is cleaned up, why? Fixed 6d534cca6ca4edbe87b3d38cca6adce111695822 - lines are longer than 80 characters Fixed
            midenok Aleksey Midenkov added a comment - https://github.com/MariaDB/server/pull/3641 The reviewers may find the task in bb-main-midenok-MDEV-19191

            I believe that this is blocked by an upcoming InnoDB change in MDEV-25292.

            The common problem with these two works is that the InnoDB SYS_FOREIGN table defines PRIMARY KEY(ID) and until now, the SYS_FOREIGN.ID was constructed as schemaname/constraintname.

            Two days ago in a discussion with monty, we decided to change the format to schemaname/tablename\xffconstraintname. I am yet to implement this change.

            This change would make all constraint names unique within the referencing table (or in the case of this work, within the referencing partition or subpartition). All disagnostics messages would simply display constraintname, which from that point on could be ‘duplicated’ within a schema.

            marko Marko Mäkelä added a comment - I believe that this is blocked by an upcoming InnoDB change in MDEV-25292 . The common problem with these two works is that the InnoDB SYS_FOREIGN table defines PRIMARY KEY(ID) and until now, the SYS_FOREIGN.ID was constructed as schemaname/constraintname . Two days ago in a discussion with monty , we decided to change the format to schemaname/tablename\xffconstraintname . I am yet to implement this change. This change would make all constraint names unique within the referencing table (or in the case of this work, within the referencing partition or subpartition). All disagnostics messages would simply display constraintname , which from that point on could be ‘duplicated’ within a schema.

            People

              serg Sergei Golubchik
              midenok Aleksey Midenkov
              Votes:
              7 Vote for this issue
              Watchers:
              14 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.