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

WITHOUT VALIDATION for EXCHANGE PARTITION/CONVERT IN

Details

    Description

      Goal

      Add support for WITH VALIDATION | WITHOUT VALIDATION to
      ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE

      WITH VALIDATION should be the default behavior.

      Must also cover CONVERT {TABLE|PARTITION} TO

      Background

      When WITHOUT VALIDATION is specified, there is no check that all rows in the added table fulfills the partition restrictions. If there is inconsistencies, it will result in rows later not being found in SELECT, UPDATE or DELETE.

      MySQL has had this since 5.7.5 and it's important when adding big tables to a partitioned table to avoid the expensive row-by-row check.

      h3. Supplemental feature

      Make THAN keyword optional in partition definition of range partitioning.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33125 CHECK TABLE does not recognize corruption after EXCHANGE WITHOUT VALIDATION on system-time partitioning

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33128 WITHOUT VALIDATION allows to insert duplicate values into unique key without a way to repair

          • Major - Major loss of function.
          • Open
          is part of

          Task - A task that needs to be done. MDEV-22162 Make partitions more flexible to use

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20498 Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type == 1' failed upon REBUILD PARTITION

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

          Task - A task that needs to be done. MDEV-22165 CONVERT TABLE: move in partition from existing table

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

          Task - A task that needs to be done. MDEV-22166 CONVERT PARTITION: move out partition into a table

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

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33190 Errors or assertion failure upon REPAIR on partitioned Aria table with misplaced rows

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

          Activity

            Ascending order - Click to sort in descending order
            View 9 older comments
            ralf.gebhardt Ralf Gebhardt added a comment -

            After some discussion with serg I personally would conclude that:

            • Feature_without_validation would not help much as it would be reset after server restart, but a partition might still be inconsistent
            • A flag in the .frm file could help at least to print a warning when running CHECK TABLE QUICK. But here the question is if a CHECK PARTITION does not anyhow check an inconsistency anyhow
            ralf.gebhardt Ralf Gebhardt added a comment - After some discussion with serg I personally would conclude that: Feature_without_validation would not help much as it would be reset after server restart, but a partition might still be inconsistent A flag in the .frm file could help at least to print a warning when running CHECK TABLE QUICK. But here the question is if a CHECK PARTITION does not anyhow check an inconsistency anyhow
            elenst Elena Stepanova added a comment -

            Feature_x counters are not an ultimate weapon, but it doesn't mean they are useless. As happens quite often, if they don't show anything, it doesn't mean much, but if it does show something, it is useful information.

            1) Production instances aren't typically restarted without a reason, and issues with invalid tables won't necessarily lead to a crash, so it may well be that a problem under investigation originated from the ongoing server session;
            2) Some users/customers do have regular monitoring which records status values, and may be able to provide the snapshot from before the server restarted;
            3) Real-life instances tend to perform a typical repeating workload, so if they performed a without validation operation in the current session, there is a good chance they did it before.

            That said, I'm not going to insist on adding the counter, I just don't understand why not have it, especially if apparently there isn't any realistic plan to have a more reliable diagnostics. After all, Feature_x is quite a standard thing in MariaDB, not any innovation.

            elenst Elena Stepanova added a comment - Feature_x counters are not an ultimate weapon, but it doesn't mean they are useless. As happens quite often, if they don't show anything, it doesn't mean much, but if it does show something, it is useful information. 1) Production instances aren't typically restarted without a reason, and issues with invalid tables won't necessarily lead to a crash, so it may well be that a problem under investigation originated from the ongoing server session; 2) Some users/customers do have regular monitoring which records status values, and may be able to provide the snapshot from before the server restarted; 3) Real-life instances tend to perform a typical repeating workload, so if they performed a without validation operation in the current session, there is a good chance they did it before. That said, I'm not going to insist on adding the counter, I just don't understand why not have it, especially if apparently there isn't any realistic plan to have a more reliable diagnostics. After all, Feature_x is quite a standard thing in MariaDB, not any innovation.
            ralf.gebhardt Ralf Gebhardt added a comment -

            elenst you are definitely right, Feature_x even only as a counter of the used feature still makes sense. And therefor also indicates that partitions should be checked as long as it is >0.

            serg, could the flag you mentioned for the .frm file be used in information_schema.partitions to mark a partition which was not validated?

            ralf.gebhardt Ralf Gebhardt added a comment - elenst you are definitely right, Feature_x even only as a counter of the used feature still makes sense. And therefor also indicates that partitions should be checked as long as it is >0. serg , could the flag you mentioned for the .frm file be used in information_schema.partitions to mark a partition which was not validated?
            serg Sergei Golubchik added a comment -

            yes

            serg Sergei Golubchik added a comment - yes
            serg Sergei Golubchik added a comment - - edited

            unfortunately, ALTER TABLE ... EXCHANGE PARTITION does not touch the .frm file, so it cannot add a flag there.
            I'll print a warning in the log

            serg Sergei Golubchik added a comment - - edited unfortunately, ALTER TABLE ... EXCHANGE PARTITION does not touch the .frm file, so it cannot add a flag there. I'll print a warning in the log

            People

              midenok Aleksey Midenkov
              monty Michael Widenius
              Votes:
              4 Vote for this issue
              Watchers:
              14 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.