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

long uniques don't work with unicode collations

Details

    Description

      set names utf8;
      create table t1 (a text collate utf8_general_ci unique);
      show create table t1;
      insert t1 values ('a');
      insert t1 values ('ä');
      select * from t1;
      select distinct * from t1;
      select 'a' = 'ä';
      drop table t1;
      

      shows that one can insert two characters into a long unique index that compare equally.

      It happens because calc_hash_for_unique() hashes string length in bytes together with the string content. So even if two utf8 strings hash to the same value, they can have different length in bytes and long unique hash will be different.

      The effect of this bug is that if one has a table with
      CREATE TABLE
      ...
      xxx VARCHAR(255) COLLATE 'utf8mb3_general_ci',
      ...
      UNIQUE INDEX `xxx` (column,...) USING HASH...

      Then the following problems may occur:

      • The table will accept duplicates for UTF8 strings that are different but should compare equal.
      • If master and slave are of different versions where the bug is fixed on one of them or the hash value is different (see MDEV-32093) then the following can also happen:
      • Insert works on master (which does not notice the duplicate key) but fails on slave (which notices the duplicate key)

      Fix is to drop and add back the unique index or run ALTER TABLE xxx ENGINE=InnoDB (for InnoDB tables) to reconstruct the hash index.

      Attachments

        Issue Links

          Activity

            serg Sergei Golubchik added a comment - https://github.com/MariaDB/server/commit/38d6d0dc6651c11f39344b0fe7b0cece85e5e5c8 is ok to push
            rjasdfiii Rick James added a comment - https://dba.stackexchange.com/questions/335247/unique-key-on-varchar-is-ignored-for-maria-db complains about not using a UNIQUE index.

            it's a complain that the optimizer doesn't use an index to optimize the query. This is correct, optimizer doesn't, this is not implemented (yet). But the UNIQUE isn't ignored, the uniqueness constraint holds, it applies on inserts and updates. But the optimizer cannot use it for SELECT.

            serg Sergei Golubchik added a comment - it's a complain that the optimizer doesn't use an index to optimize the query. This is correct, optimizer doesn't, this is not implemented (yet). But the UNIQUE isn't ignored , the uniqueness constraint holds, it applies on inserts and updates. But the optimizer cannot use it for SELECT.
            rjasdfiii Rick James added a comment -

            A possible test case: https://dba.stackexchange.com/questions/339135/how-can-i-get-duplicate-entry-error-on-a-table-that-only-has-one-record This has a composite index with 2 VARCHAR(400) columns.

            rjasdfiii Rick James added a comment - A possible test case: https://dba.stackexchange.com/questions/339135/how-can-i-get-duplicate-entry-error-on-a-table-that-only-has-one-record This has a composite index with 2 VARCHAR(400) columns.

            it's a different bug, I've reported it as MDEV-34052

            serg Sergei Golubchik added a comment - it's a different bug, I've reported it as MDEV-34052

            People

              bar Alexander Barkov
              serg Sergei Golubchik
              Votes:
              3 Vote for this issue
              Watchers:
              9 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.