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

Add foreign keys support for partitioned tables

Details

    Description

      Please, add support for foreign keys for (at least some subset of) partitioned tables.

      Generic solution for engine-independent foreign keys like the one discussed in https://dev.mysql.com/worklog/task/?id=148 may also work.

      See upstream https://bugs.mysql.com/bug.php?id=46661 and numerous duplicates.

      Attachments

        Issue Links

          includes

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16417 Store Foreign Key metadata outside of InnoDB

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-23798 Add GLOBAL indexes for partitioned tables

          • Major - Major loss of function.
          • Open
          is blocked by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16417 Store Foreign Key metadata outside of InnoDB

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

          Task - A task that needs to be done. MDEV-20480 Obsolete internal parser for FK in InnoDB

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

          Task - A task that needs to be done. MDEV-10393 Foreign keys SET DEFAULT action

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11655 Transactional data dictionary

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22361 Cross-engine foreign keys support

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18320 Foreign key implicit index inconsistency

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19191 Partitioning in child tables with foreign keys

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21175 Remove dict_table_t::n_foreign_key_checks_running from InnoDB

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24273 System versioning replication fails when adding column on master

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32493 support foreign keys in tables partitioned by system time

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I think that properly supporting this would require that the FOREIGN KEY constraint metadata be stored at the SQL layer and not inside InnoDB. MDEV-11655 proposes the table mysql.tables_children for this purpose.

            marko Marko Mäkelä added a comment - I think that properly supporting this would require that the FOREIGN KEY constraint metadata be stored at the SQL layer and not inside InnoDB. MDEV-11655 proposes the table mysql.tables_children for this purpose.
            manjot Manjot Singh (Inactive) added a comment -

            Upstream has moved partitioning to the engine level. This could be fixed within InnoDB right?

            manjot Manjot Singh (Inactive) added a comment - Upstream has moved partitioning to the engine level. This could be fixed within InnoDB right?
            midenok Aleksey Midenkov added a comment -

            manjot This can be fixed at InnoDB and probably should be done so as a preliminary step because the generic solution is much more time consuming.

            midenok Aleksey Midenkov added a comment - manjot This can be fixed at InnoDB and probably should be done so as a preliminary step because the generic solution is much more time consuming.
            manjot Manjot Singh (Inactive) added a comment -

            So perhaps this is something better addressed as a storage engine feature, especially since FK are also storage engine features.

            manjot Manjot Singh (Inactive) added a comment - So perhaps this is something better addressed as a storage engine feature, especially since FK are also storage engine features.
            jscottPTC Justin Scott added a comment -

            We are starting a new project that will utilize Mariadb. The System Versioned tables feature you guys implemented is pretty awesome. When this issue is resolved will we be able to easily move our historical data to another partition? Some of the tables where we use this System Versioned feature have foreign key columns that we want to keep track of.

            jscottPTC Justin Scott added a comment - We are starting a new project that will utilize Mariadb. The System Versioned tables feature you guys implemented is pretty awesome. When this issue is resolved will we be able to easily move our historical data to another partition? Some of the tables where we use this System Versioned feature have foreign key columns that we want to keep track of.
            dlucas-webnet Daniel LUCAS added a comment - - edited

            Hello,

            I'm working on a client project for Webnet.

            In the project we set up MariaDB system versioning on the database which is becoming slow. I read in the docs that using different partitions for the history should improve the application performance.

            However, when setting up the current and history partitions I ran into the following error :

            SQL Error [1506] [HY000]: (conn=6) Partitioned tables do not support FOREIGN KEY

            Are there any plans to make using foreign keys in partitions possible using INNODB ?

            dlucas-webnet Daniel LUCAS added a comment - - edited Hello, I'm working on a client project for Webnet. In the project we set up MariaDB system versioning on the database which is becoming slow. I read in the docs that using different partitions for the history should improve the application performance. However, when setting up the current and history partitions I ran into the following error : SQL Error [1506] [HY000]: (conn=6) Partitioned tables do not support FOREIGN KEY Are there any plans to make using foreign keys in partitions possible using INNODB ?

            People

              midenok Aleksey Midenkov
              valerii Valerii Kravchuk
              Votes:
              22 Vote for this issue
              Watchers:
              33 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.