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

Duplicate entries in unique index not detected when changing collation with INPLACE / NOCOPY algoritm

Details

    Description

      When creating a table with a case sensitive collation on a column with unique constraint, then trying to alter the collation to a case insensitive one, entries that only differ in case lead to a duplicate entry error, aborting the ALTER TABLE, when using the COPY algorithm, as expected:

      CREATE TABLE t1 (
      		 
        id INT PRIMARY KEY,
      		 
        msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
      		 
        UNIQUE(msg)
      		 
      ) ENGINE=INNODB;
      		 
       
      		 
      INSERT INTO t1 VALUES (1, 'aaa');
      		 
      INSERT INTO t1 VALUES (2, 'AAA');
      		 
       
      		 
      ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=COPY;

      This gives 

      ERROR 1062 (23000): Duplicate entry 'AAA' for key 'msg'

      as expected.

      When trying the same with ALGORITHM=INPLACE or NOCOPY it works without detecting that we now have a duplicate entry though, so the new table version may now contain data violating the UNIQUE constraint.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22365 Assertion `!col->mbmaxlen || len >= col->mbminlen * (fixed_len / col->mbmaxlen)' failed in row_merge_buf_add

          • Major - Major loss of function.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29425 Buffer overflow in dict_index_t::col_info::add()

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17301 Change of COLLATE unnecessarily requires ALGORITHM=COPY

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27209 Assertion `((cursor)->page_cur.rec) == ((next_page) + page_get_infimum_offset(next_page))' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27280 server crashes on CHECK TABLE after COLLATE change for utf8mb4

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

          Task - A task that needs to be done. MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18584 Avoid copying when altering CHAR column in InnoDB table

          • Major - Major loss of function.
          • Confirmed

          Activity

            Transition Time In Source Status Execution Times
            Eugene Kosov (Inactive) made transition -
            Open In Progress
            		77d 20h 39m 1
            Eugene Kosov (Inactive) made transition -
            In Review Stalled
            		16d 2h 17m 1
            Thirunarayanan Balathandayuthapani made transition -
            In Progress Stalled
            		1d 20h 4m 1
            Thirunarayanan Balathandayuthapani made transition -
            Stalled In Progress
            		219d 10h 6m 2
            Thirunarayanan Balathandayuthapani made transition -
            In Progress In Review
            		36s 2
            Marko Mäkelä made transition -
            In Review Closed
            		20d 22h 3m 1

            People

              marko Marko Mäkelä
              hholzgra Hartmut Holzgraefe
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.