Details

    Description

      In the following test case, the REPLACE statement should successfully insert data, instead of returning an error.

      CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
      CREATE UNIQUE INDEX i1 ON t0(c1);
      INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');
      INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');
      REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); --  Duplicate entry '0' for key 'i1'
      

      The reference test case can be obtained by defining the UNIQUE constraint in CREATE TABLE, and the same REPLACE statement successfully executed.

      CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL UNIQUE, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
      INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');
      INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');
      REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); -- no errors
      

      As a result, the final table content differs because of the different ways of creating UNIQUE constraints.

      Attachments

        Activity

          CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
          CREATE UNIQUE INDEX i1 ON t0(c1);
          INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');
          INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');
          REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11');
          

          After unique index creation, .frm file has been rebuild with the ALTER_INDEX_ORDER flag. So it made i1 as 1st secondary key and c2 as 2nd
          secondary index. But there is only addition of secondary index inside innodb. i.e. i1 has been added as 2nd secondary index and c2 as 1st secondary index

          During replace statement, InnoDB fails with DB_DUPLICATE_KEY while inserting into c2 index. write_row() has the check whether it is
          last_uniq_index(). Since .frm file has different key order compared to InnoDB. It wrongly assumes that it is last unique index and does
          update_row() and fails with DB_DUPLICATE_KEY in 2nd secondary index (i1).

          Fix should be avoid enabling ALTER_INDEX_ORDER flag if the unique index already exist for InnoDB table.

          thiru Thirunarayanan Balathandayuthapani added a comment - CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY); CREATE UNIQUE INDEX i1 ON t0(c1); INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01'); INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10'); REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); After unique index creation, .frm file has been rebuild with the ALTER_INDEX_ORDER flag. So it made i1 as 1st secondary key and c2 as 2nd secondary index. But there is only addition of secondary index inside innodb. i.e. i1 has been added as 2nd secondary index and c2 as 1st secondary index During replace statement, InnoDB fails with DB_DUPLICATE_KEY while inserting into c2 index. write_row() has the check whether it is last_uniq_index() . Since .frm file has different key order compared to InnoDB. It wrongly assumes that it is last unique index and does update_row() and fails with DB_DUPLICATE_KEY in 2nd secondary index (i1). Fix should be avoid enabling ALTER_INDEX_ORDER flag if the unique index already exist for InnoDB table.

          The fix of setting the HA_DUPLICATE_KEY_NOT_IN_ORDER is elegant. But could we only set that flag when the unique indexes are actually defined in a different order? In that way, the error handling of REPLACE would for most tables keep invoking UPDATE instead of DELETE and INSERT. This could make a huge performance difference especially when there are BLOBs in the record.

          marko Marko Mäkelä added a comment - The fix of setting the HA_DUPLICATE_KEY_NOT_IN_ORDER is elegant. But could we only set that flag when the unique indexes are actually defined in a different order? In that way, the error handling of REPLACE would for most tables keep invoking UPDATE instead of DELETE and INSERT . This could make a huge performance difference especially when there are BLOBs in the record.

          The revised test now nicely shows the impact of this adjustment on operation counters of REPLACE. The reason for this failure is that when unique indexes are being added without ALGORITHM=COPY, InnoDB can’t maintain the same order of indexes as the .frm file, because the new indexes will be always added to the end of the index list in InnoDB. This is because they are ordered by SYS_INDEXES.ID. With ALGORITHM=COPY, all indexes will be rebuilt and their metadata (with new ID) stored in order. We can’t change an index ID without rewriting the entire index (or table), because the index ID is being stored in PAGE_INDEX_ID. This seems to be necessary for the correct operation of the adaptive hash index.

          marko Marko Mäkelä added a comment - The revised test now nicely shows the impact of this adjustment on operation counters of REPLACE . The reason for this failure is that when unique indexes are being added without ALGORITHM=COPY , InnoDB can’t maintain the same order of indexes as the .frm file, because the new indexes will be always added to the end of the index list in InnoDB. This is because they are ordered by SYS_INDEXES.ID . With ALGORITHM=COPY , all indexes will be rebuilt and their metadata (with new ID) stored in order. We can’t change an index ID without rewriting the entire index (or table), because the index ID is being stored in PAGE_INDEX_ID . This seems to be necessary for the correct operation of the adaptive hash index.

          People

            thiru Thirunarayanan Balathandayuthapani
            John Jove John Jove
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.