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

WITHOUT VALIDATION allows to insert duplicate values into unique key without a way to repair

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4
    • 11.4
    • Partitioning
    • None

    Description

      EXCHANGE/CONVERT ... WITHOUT VALIDATION are supposed to bypass the check that each row belongs to the partition in question. However as a side-effect it also allows to bypass unique constraints, and it seems there is no good way to repair it. REPAIR TABLE, REPAIR PARTITION, REBUILD PARTITION don't help. REPAIR TABLE upon the failed attempt points at the duplicate key value suggesting to handle it manually, but since it only shows one violation at a time and there can be thousands or millions such records after unfortunate alter without validation, it is not really a feasible way to recover. Dump/reload which CHECK suggests also doesn't work as it simply tries to re-insert duplicate records into the table.

      Note: REBUILD PARTITION fails with different errors on a release build and with an assertion failure on debug builds (similar to MDEV-20498), which is why it is commented in the test case below.

      --source include/have_partition.inc
      		 
       
      		 
      create table t1 (a int unique) partition by range(a) (
      		 
        partition p0 values less than (100),
      		 
        partition p1 values less than (maxvalue)
      		 
      );
      		 
      insert into t1 values (1),(2),(3);
      		 
       
      		 
      create table t (a int unique);
      		 
      insert into t values (1),(3);
      		 
       
      		 
      alter table t1 exchange partition p1 with table t without validation;
      		 
      check table t1 extended;
      		 
      repair table t1;
      		 
      alter table t1 repair partition p1;
      		 
      #alter table t1 rebuild partition p1;
      		 
      check table t1 extended;
      		 
      select * from t1;
      		 
       
      		 
      # Cleanup
      		 
      drop table if exists t1, t;

      11.4 5462b61b0c3ae59d3996018d7da34220460baf46

      		 
      alter table t1 exchange partition p1 with table t without validation;
      		 
      check table t1 extended;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t1	check	error	Found a misplaced row
      		 
      test.t1	check	error	Partition p1 returned error
      		 
      test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
      		 
      repair table t1;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t1	repair	error	Failed to move/insert a row from part 1 into part 0:
      		 
      Duplicate key found, please update or delete the record:
      		 
       a:1
      		 
      test.t1	repair	error	Partition p1 returned error
      		 
      test.t1	repair	error	Corrupt
      		 
      alter table t1 repair partition p1;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t1	repair	error	Failed to move/insert a row from part 1 into part 0:
      		 
      Duplicate key found, please update or delete the record:
      		 
       a:1
      		 
      test.t1	repair	error	Partition p1 returned error
      		 
      test.t1	repair	error	Corrupt
      		 
      check table t1 extended;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t1	check	error	Found a misplaced row
      		 
      test.t1	check	error	Partition p1 returned error
      		 
      test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
      		 
      select * from t1;
      		 
      a
      		 
      1
      		 
      1
      		 
      2
      		 
      3
      		 
      3

      MySQL 8.2 doesn't look any better, except for maybe the assertion failure.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTITION/CONVERT IN

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

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            I didn't make it a "blocker" for MDEV-22164 feature, as I'm not sure whether anything can be done about it. MySQL behaves in the same way, but it's unclear whether it's the expected outcome or an omission, at least MySQL manual doesn't say anything about it (and usually, if it were a known effect, it would have).

            elenst Elena Stepanova added a comment - I didn't make it a "blocker" for MDEV-22164 feature, as I'm not sure whether anything can be done about it. MySQL behaves in the same way, but it's unclear whether it's the expected outcome or an omission, at least MySQL manual doesn't say anything about it (and usually, if it were a known effect, it would have).
            serg Sergei Golubchik added a comment - - edited

            sure, there is a way to repair the table. something like

            delete from t1 where a in (select a from t1 group by a having count(a) > 1)

            but let's say REPAIR TABLE should be able to repair the table too

            serg Sergei Golubchik added a comment - - edited sure, there is a way to repair the table. something like delete from t1 where a in ( select a from t1 group by a having count (a) > 1) but let's say REPAIR TABLE should be able to repair the table too
            elenst Elena Stepanova added a comment - - edited

            sure, there is a way to repair the table. something like

            delete from t1 where a in (select a from t1 group by a having count(a) > 1)

            Sure, when I said "no way to repair", I meant a meaningful way with a predictable result and preferably with minimal data loss. Otherwise there are other ways – one can truncate the table, or drop the newly added partition, etc.
            For the above query and its likes, as the table is broken, the behavior is undefined. It may delete all rows with these keys (not just duplicates), or, if one's more lucky, it may delete the old good records and preserve the new not-so-good ones. With the data from the description, for InnoDB it does the former and for MyISAM the latter, but I suppose it can depend on many factors.

            elenst Elena Stepanova added a comment - - edited sure, there is a way to repair the table. something like delete from t1 where a in ( select a from t1 group by a having count (a) > 1) Sure, when I said "no way to repair", I meant a meaningful way with a predictable result and preferably with minimal data loss. Otherwise there are other ways – one can truncate the table, or drop the newly added partition, etc. For the above query and its likes, as the table is broken, the behavior is undefined. It may delete all rows with these keys (not just duplicates), or, if one's more lucky, it may delete the old good records and preserve the new not-so-good ones. With the data from the description, for InnoDB it does the former and for MyISAM the latter, but I suppose it can depend on many factors.
            elenst Elena Stepanova added a comment - - edited

            It seems there is a more or less universal (although not immediately obvious) way to fix a variety of problems with such tables, including the one describe in this ticket: ALTER IGNORE TABLE ... FORCE, ALGORITHM=COPY.
            The table gets rebuilt and misplaced rows are moved around, while IGNORE ensures that the operation doesn't fail on duplicate keys, and duplicate entries are removed instead.
            Algorithm is important for InnoDB, as otherwise it's inplace and doesn't do much.

            insert into t1 values (1),(2),(3);
            		 
            create table t (a int unique);
            		 
            insert into t values (1),(3);
            		 
            alter table t1 exchange partition p1 with table t without validation;
            		 
            check table t1 extended;
            		 
            Table	Op	Msg_type	Msg_text
            		 
            test.t1	check	error	Found a misplaced row
            		 
            test.t1	check	error	Partition p1 returned error
            		 
            test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
            		 
            alter ignore table t1 force, algorithm=copy;
            		 
            check table t1 extended;
            		 
            Table	Op	Msg_type	Msg_text
            		 
            test.t1	check	status	OK
            		 
            select * from t1;
            		 
            a
            		 
            1
            		 
            2
            		 
            3
            		 
            select * from t1 partition (p0);
            		 
            a
            		 
            1
            		 
            2
            		 
            3
            		 
            select * from t1 partition (p1);
            		 
            a

            elenst Elena Stepanova added a comment - - edited It seems there is a more or less universal (although not immediately obvious) way to fix a variety of problems with such tables, including the one describe in this ticket: ALTER IGNORE TABLE ... FORCE, ALGORITHM=COPY . The table gets rebuilt and misplaced rows are moved around, while IGNORE ensures that the operation doesn't fail on duplicate keys, and duplicate entries are removed instead. Algorithm is important for InnoDB, as otherwise it's inplace and doesn't do much. insert into t1 values (1),(2),(3); create table t (a int unique ); insert into t values (1),(3); alter table t1 exchange partition p1 with table t without validation; check table t1 extended; Table Op Msg_type Msg_text test.t1 check error Found a misplaced row test.t1 check error Partition p1 returned error test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! alter ignore table t1 force , algorithm=copy; check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK select * from t1; a 1 2 3 select * from t1 partition (p0); a 1 2 3 select * from t1 partition (p1); a

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.