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

          Activity

            Alternative testcase

            SET autocommit=0,unique_checks=0,foreign_key_checks=0;
            CREATE TABLE t (i INT UNIQUE);
            INSERT INTO t VALUES (0),(0);
            CHECK TABLE t;
            

            Roel Roel Van de Paar added a comment - Alternative testcase SET autocommit=0,unique_checks=0,foreign_key_checks=0; CREATE TABLE t (i INT UNIQUE ); INSERT INTO t VALUES (0),(0); CHECK TABLE t;

            Interestingly, this testcase produces no errors in the CLI output:

            SET sql_mode='',autocommit=0,unique_checks=0,foreign_key_checks=0;
            CREATE TABLE t (c INT AUTO_INCREMENT KEY,c2 CHAR(1) NOT NULL,UNIQUE INDEX uc2 (c2));
            INSERT INTO t VALUES(),();
            DELETE FROM t;
            

            Yet there is clearly corruption recorded in the log:

            10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized)

            2021-11-01  9:03:57 0 [Note] /test/MD301021-mariadb-10.7.1-linux-x86_64-opt/bin/mysqld: ready for connections.
            Version: '10.7.1-MariaDB'  socket: '/test/MD301021-mariadb-10.7.1-linux-x86_64-opt/socket.sock'  port: 31608  MariaDB Server
            2021-11-01  9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
            2021-11-01  9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
            2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
            2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
            2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` was not found on rollback, and a duplicate exists
            

            Thus, besides fixing the bug, a secondary action item would be to improve the error flow so the CLI output shows a corrupt table message, alike to the examples above.

            Roel Roel Van de Paar added a comment - Interestingly, this testcase produces no errors in the CLI output: SET sql_mode= '' ,autocommit=0,unique_checks=0,foreign_key_checks=0; CREATE TABLE t (c INT AUTO_INCREMENT KEY ,c2 CHAR (1) NOT NULL , UNIQUE INDEX uc2 (c2)); INSERT INTO t VALUES (),(); DELETE FROM t; Yet there is clearly corruption recorded in the log: 10.7.1 12eb8ad7b98b03a6a7659fce7b75bdc8696ccaf6 (Optimized) 2021-11-01 9:03:57 0 [Note] /test/MD301021-mariadb-10.7.1-linux-x86_64-opt/bin/mysqld: ready for connections. Version: '10.7.1-MariaDB' socket: '/test/MD301021-mariadb-10.7.1-linux-x86_64-opt/socket.sock' port: 31608 MariaDB Server 2021-11-01 9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4] (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)} 2021-11-01 9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4] (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)} 2021-11-01 9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4] (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)} 2021-11-01 9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4] (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)} 2021-11-01 9:04:04 4 [Warning] InnoDB: Record in index `uc2` was not found on rollback, and a duplicate exists Thus, besides fixing the bug, a secondary action item would be to improve the error flow so the CLI output shows a corrupt table message, alike to the examples above.

            I think that you got what you asked for, by executing SET unique_checks=0 and then inserting data that violates a UNIQUE constraint.

            InnoDB started to honor the session parameter unique_checks in MySQL 4.0.18. The parameter had been introduced in MySQL 4.0.3.

            Even before MDEV-24621 or MDEV-515, it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help.

            For compatibility with files generated by mysqldump, serg suggested that we use foreign_key_checks=0,unique_checks=0 for enabling the bulk insert feature. This was implemented in MDEV-24818. A more proper solution is still waiting to be implemented, in MDEV-25036.

            All that said, I agree that the CHECK TABLE output for index c2 looks strange. Why does it say contains 0 entries, instead of 1 or 2?

            marko Marko Mäkelä added a comment - I think that you got what you asked for, by executing SET unique_checks=0 and then inserting data that violates a UNIQUE constraint. InnoDB started to honor the session parameter unique_checks in MySQL 4.0.18. The parameter had been introduced in MySQL 4.0.3. Even before MDEV-24621 or MDEV-515 , it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help. For compatibility with files generated by mysqldump , serg suggested that we use foreign_key_checks=0,unique_checks=0 for enabling the bulk insert feature. This was implemented in MDEV-24818 . A more proper solution is still waiting to be implemented, in MDEV-25036 . All that said, I agree that the CHECK TABLE output for index c2 looks strange. Why does it say contains 0 entries , instead of 1 or 2?

            I think the problem here is previous user experience, or a lack thereof.

            Because at least in recent history hardly anyone could ever make this option work, users might not realize that it's far from innocent and can corrupt a table, and the option description is way too light for this to to expected. MySQL's a bit better as it at least mentions user's responsibility for the input data, but still, no mention of actual damage.

            Facing the question what effect I myself would expect, I couldn't come up with any sensible answer right away. Indeed, come to think of it, that's the only outcome, but it never occurred to me before. After some thinking, I would say that as a user, I'd expect the table somehow complain about data inconsistency (so errors in CHECK are fine, as long as they are accurate), but then I'd expect to still be able to handle the data – select it and remove the non-unique values or ALTER-IGNORE the table to get rid of them; and currently it's apparently impossible to do either, instead one should fix the structure – dropping the index should fix it, I presume. I guess it needs to be documented somehow now when the option works.

            elenst Elena Stepanova added a comment - I think the problem here is previous user experience, or a lack thereof. Because at least in recent history hardly anyone could ever make this option work, users might not realize that it's far from innocent and can corrupt a table, and the option description is way too light for this to to expected. MySQL's a bit better as it at least mentions user's responsibility for the input data, but still, no mention of actual damage. Facing the question what effect I myself would expect, I couldn't come up with any sensible answer right away. Indeed, come to think of it, that's the only outcome, but it never occurred to me before. After some thinking, I would say that as a user, I'd expect the table somehow complain about data inconsistency (so errors in CHECK are fine, as long as they are accurate), but then I'd expect to still be able to handle the data – select it and remove the non-unique values or ALTER-IGNORE the table to get rid of them; and currently it's apparently impossible to do either, instead one should fix the structure – dropping the index should fix it, I presume. I guess it needs to be documented somehow now when the option works .

            thiru implemented a fix that will trigger ER_ERROR_DURING_COMMIT during the "statement commit" (actually, end of statement) of the INSERT.

            I think that the error message is misleading because it explicitly says COMMIT while the transaction will remain active:

            CREATE TABLE t (c1 INT KEY,c2 INT UNIQUE) ENGINE=InnoDB;
            BEGIN;
            INSERT INTO t VALUES (1,0),(2,0);
            ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
            

            But, that is a separate bug, outside InnoDB. I find the concept of "statement commit" confusing. I would welcome a better term that would capture the concept of releasing statement-level resources. The only somewhat observable resource would be the auto-increment lock, which I would like to disappear (MDEV-19577).

            marko Marko Mäkelä added a comment - thiru implemented a fix that will trigger ER_ERROR_DURING_COMMIT during the "statement commit" (actually, end of statement) of the INSERT . I think that the error message is misleading because it explicitly says COMMIT while the transaction will remain active: CREATE TABLE t (c1 INT KEY,c2 INT UNIQUE) ENGINE=InnoDB; BEGIN; INSERT INTO t VALUES (1,0),(2,0); ERROR HY000: Got error 1 "Operation not permitted" during COMMIT But, that is a separate bug, outside InnoDB. I find the concept of "statement commit" confusing. I would welcome a better term that would capture the concept of releasing statement-level resources. The only somewhat observable resource would be the auto-increment lock, which I would like to disappear ( MDEV-19577 ).

            As per discussion with Marko added comment in MDEV-25036 to improve the cryptic error message.

            Roel Roel Van de Paar added a comment - As per discussion with Marko added comment in MDEV-25036 to improve the cryptic error message.

            I wrote earlier in this ticket:

            Even before MDEV-24621 or MDEV-515, it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help.

            Yesterday, mleich produced an rr replay trace while testing MDEV-30100 on MariaDB Server 10.6. In that trace, an INSERT IGNORE that would violate a UNIQUE INDEX constraint did not lead to a rollback of the duplicate row, because unique_checks=0 allowed the InnoDB change buffer to be used. The duplicate key error was caught some time later during the execution, by a failure of ALTER TABLE…FORCE.

            marko Marko Mäkelä added a comment - I wrote earlier in this ticket: Even before MDEV-24621 or MDEV-515 , it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help. Yesterday, mleich produced an rr replay trace while testing MDEV-30100 on MariaDB Server 10.6. In that trace, an INSERT IGNORE that would violate a UNIQUE INDEX constraint did not lead to a rollback of the duplicate row, because unique_checks=0 allowed the InnoDB change buffer to be used. The duplicate key error was caught some time later during the execution, by a failure of ALTER TABLE…FORCE .

            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.