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

Allow ALGORITHM=NOCOPY for ADD CONSTRAINT

Details

    Description

      The ADD FOREIGN KEY and ADD CHECK constraints currently require the table to be rebuilt with ALGORITHM=COPY.

      We could support these operations instantly if the user specified a WITHOUT VALIDATION clause. We currently do support instant ADD FOREIGN KEY after SET foreign_key_checks=0, but there is no such mechanism for ADD CONSTRAINT CHECK.

      When validation is not suppressed, we should only scan the table and validate the added constraints. There is no need to write any data to the table. For LOCK=NONE operation, we may need to ‘subscribe’ to concurrent modifications somehow.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-16291 Allow ALGORITHM=NOCOPY for most ALTER TABLE in InnoDB

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-16354 Allow ALGORITHM=INPLACE for ADD COLUMN…PERSISTENT AS

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30669 Changing the Data Type of a Column from Text to JSON causes a unexpected Table Rebuild

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31008 Instant ALTER allows to violate column check constraint

          • Major - Major loss of function.
          • Confirmed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30416 Can't redefine constraint in a single ALTER TABLE

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32171 When updating cascaded foreign keys, closing foreign key constraints is ignored and invalid.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32270 ALTER TABLE: DROP CONSTRAINT is ignored when ADD CONSTRAINT is also present

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16332 Allow ALGORITHM=NOCOPY or INSTANT for changes of virtual column type

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19453 FK errors are hidden behind an obscure command

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-28298 Add Foreign key to a table instantly without checking existing data

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-30788 ONLINE Add Foreign Key

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30934 InnoDB: Changing a Column Data Type from VARCHAR/TEXT to a super type causes a unexpected Table Rebuild

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33087 ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34309 CHECK TABLE should check that foreign key relations are correct

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

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            This feature would solve the problem described in MDEV-30669 (TEXT to JSON conversion).

            bar Alexander Barkov added a comment - This feature would solve the problem described in MDEV-30669 (TEXT to JSON conversion).
            marko Marko Mäkelä added a comment -

            I think that this is easiest to implement in the SQL layer. For checking FOREIGN KEY constraints, some type of join (outer join?) will be necessary. For anything else (CHECK or JSON validity constraints or adding a NOT NULL attribute to a column in a ROW_FORMAT=REDUNDANT table), a plain table scan would do. In the storage engine, this can be facilitated by the same APIs that serve regular SELECT and joins.

            marko Marko Mäkelä added a comment - I think that this is easiest to implement in the SQL layer. For checking FOREIGN KEY constraints, some type of join (outer join?) will be necessary. For anything else ( CHECK or JSON validity constraints or adding a NOT NULL attribute to a column in a ROW_FORMAT=REDUNDANT table), a plain table scan would do. In the storage engine, this can be facilitated by the same APIs that serve regular SELECT and joins.

            People

              sanja Oleksandr Byelkin
              marko Marko Mäkelä
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.