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

          Activity

            People

            Assignee:
            kevg Eugene Kosov
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration