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

UNIQUE column checks fail in InnoDB resulting in table corruption

Details

    Description

      SET autocommit=0,foreign_key_checks=0,unique_checks=0;
      		 
      CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2)) ENGINE=InnoDB;
      		 
      INSERT INTO t VALUES (1,0),(2,0);  # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
      		 
      CREATE TABLE t (c2 INT);
      		 
      CHECK TABLE t;

      Leads to:

      10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)

      		 
      10.7.1-dbg>SET autocommit=0,foreign_key_checks=0,unique_checks=0;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      10.7.1-dbg>CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2));
      		 
      Query OK, 0 rows affected (0.023 sec)
      		 
       
      		 
      10.7.1-dbg>INSERT INTO t VALUES (1,0),(2,0);  # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
      		 
      Query OK, 2 rows affected (0.007 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0
      		 
       
      		 
      10.7.1-dbg>CREATE TABLE t (c2 INT);
      		 
      ERROR 1050 (42S01): Table 't' already exists
      		 
      10.7.1-dbg>CHECK TABLE t;
      		 
      +--------+-------+----------+-----------------------------------------------------+
      		 
      | Table  | Op    | Msg_type | Msg_text                                            |
      		 
      +--------+-------+----------+-----------------------------------------------------+
      		 
      | test.t | check | Warning  | InnoDB: Index 'c2' contains 0 entries, should be 2. |
      		 
      | test.t | check | error    | Corrupt                                             |
      		 
      +--------+-------+----------+-----------------------------------------------------+
      		 
      2 rows in set (0.002 sec)

      10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)

      		 
      2021-11-01  8:41:40 4 [ERROR] InnoDB: Flagged corruption of `c2` in table `test`.`t` in CHECK TABLE; Wrong count

      Bug confirmed present in:
      MariaDB: 10.7.1 (dbg), 10.7.1 (opt)

      Bug (or feature/syntax) confirmed not present in:
      MariaDB: 10.2.41 (dbg), 10.2.41 (opt), 10.3.32 (dbg), 10.3.32 (opt), 10.4.22 (dbg), 10.4.22 (opt), 10.5.13 (dbg), 10.5.13 (opt), 10.6.5 (dbg), 10.6.5 (opt)

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27007 SIGSEGV in trx_undo_build_roll_ptr

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27971 SIGSEGV in trx_undo_build_roll_ptr on INSERT with binary logging enabled and using XA

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time

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

          Task - A task that needs to be done. MDEV-25036 use bulk insert optimization for multiple insert statements

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27751 InnoDB: Failing assertion: !cursor->index->is_committed() in row0ins.cc line 221 (10.7+)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            Roel Roel Van de Paar created issue -
            Roel Roel Van de Paar made changes -
            Field Original Value New Value
            Description {code:sql}
            SET autocommit=0,foreign_key_checks=0,unique_checks=0;
            CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2)) ENGINE=InnoDB;
            INSERT INTO t VALUES (1,0),(2,0); # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
            CREATE TABLE t (c2 INT);
            CHECK TABLE t;
            {code}

            Leads to:

            {noformat:title=10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)}
            10.7.1-dbg>SET autocommit=0,foreign_key_checks=0,unique_checks=0;
            Query OK, 0 rows affected (0.000 sec)

            10.7.1-dbg>CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2));
            Query OK, 0 rows affected (0.023 sec)

            10.7.1-dbg>INSERT INTO t VALUES (1,0),(2,0); # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
            Query OK, 2 rows affected (0.007 sec)
            Records: 2 Duplicates: 0 Warnings: 0

            10.7.1-dbg>CREATE TABLE t (c2 INT);
            ERROR 1050 (42S01): Table 't' already exists
            10.7.1-dbg>CHECK TABLE t;
            +--------+-------+----------+-----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +--------+-------+----------+-----------------------------------------------------+
            | test.t | check | Warning | InnoDB: Index 'c2' contains 0 entries, should be 2. |
            | test.t | check | error | Corrupt |
            +--------+-------+----------+-----------------------------------------------------+
            2 rows in set (0.002 sec)
            {noformat}
            {noformat:title=10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)}
            2021-11-01 8:41:40 4 [ERROR] InnoDB: Flagged corruption of `c2` in table `test`.`t` in CHECK TABLE; Wrong count
            {noformat}

            Bug confirmed present in:
            10.7.1 (dbg), 10.7.1 (opt)

            Bug (or feature/syntax) confirmed not present in:
            MariaDB: 10.2.41 (dbg), 10.2.41 (opt), 10.3.32 (dbg), 10.3.32 (opt), 10.4.22 (dbg), 10.4.22 (opt), 10.5.13 (dbg), 10.5.13 (opt), 10.6.5 (dbg), 10.6.5 (opt)             		{code:sql}
            SET autocommit=0,foreign_key_checks=0,unique_checks=0;
            CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2)) ENGINE=InnoDB;
            INSERT INTO t VALUES (1,0),(2,0); # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
            CREATE TABLE t (c2 INT);
            CHECK TABLE t;
            {code}

            Leads to:

            {noformat:title=10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)}
            10.7.1-dbg>SET autocommit=0,foreign_key_checks=0,unique_checks=0;
            Query OK, 0 rows affected (0.000 sec)

            10.7.1-dbg>CREATE TABLE t (c1 INT KEY,c2 INT,UNIQUE (c2));
            Query OK, 0 rows affected (0.023 sec)

            10.7.1-dbg>INSERT INTO t VALUES (1,0),(2,0); # Should fail with ERROR 1062 (23000): Duplicate entry '0' for key 'c2'
            Query OK, 2 rows affected (0.007 sec)
            Records: 2 Duplicates: 0 Warnings: 0

            10.7.1-dbg>CREATE TABLE t (c2 INT);
            ERROR 1050 (42S01): Table 't' already exists
            10.7.1-dbg>CHECK TABLE t;
            +--------+-------+----------+-----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +--------+-------+----------+-----------------------------------------------------+
            | test.t | check | Warning | InnoDB: Index 'c2' contains 0 entries, should be 2. |
            | test.t | check | error | Corrupt |
            +--------+-------+----------+-----------------------------------------------------+
            2 rows in set (0.002 sec)
            {noformat}
            {noformat:title=10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized/Debug)}
            2021-11-01 8:41:40 4 [ERROR] InnoDB: Flagged corruption of `c2` in table `test`.`t` in CHECK TABLE; Wrong count
            {noformat}

            Bug confirmed present in:
            MariaDB: 10.7.1 (dbg), 10.7.1 (opt)

            Bug (or feature/syntax) confirmed not present in:
            MariaDB: 10.2.41 (dbg), 10.2.41 (opt), 10.3.32 (dbg), 10.3.32 (opt), 10.4.22 (dbg), 10.4.22 (opt), 10.5.13 (dbg), 10.5.13 (opt), 10.6.5 (dbg), 10.6.5 (opt)
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.7.1 [ 26120 ]
            Fix Version/s 10.7 [ 24805 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            Comment [ -FWIW, this actually directly contradicts even the existing [description of unique_checks option/variable|https://mariadb.com/kb/en/server-system-variables/#unique_checks], which says that duplicate entries will either be ignored or will cause a *duplicate key* error.-

            -It hasn't started just now though, it was already the case with autocommit=on and the two checks off.-

            While I was writing it, the description got updated, so the comment is no longer relevant, removing. ]
            Roel Roel Van de Paar made changes -
            dbart Daniel Bartholomew made changes -
            Fix Version/s 10.7.2 [ 26813 ]
            Fix Version/s 10.7.1 [ 26120 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 126866 ] MariaDB v4 [ 159818 ]
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.