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

    XMLWordPrintable

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

          Activity

            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.