Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5795

8.0 MyRocks memcmp-able key packing and comparison is not Unicode 9.0.0 compliant

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Duplicate
    • Affects Version/s: 8.0.13-3, Not 5.7.x
    • Fix Version/s: None
    • Component/s: MyRocks
    • Labels:
      None

      Description

      See https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html "Trailing Space Handling in Comparisons" for definition of expected behavior.

       

      CREATE TABLE `t1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        UNIQUE KEY `name` (`name`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
      
      mysql> INSERT INTO t1 (`name`) VALUES ('Foppy');
      ERROR 1062 (23000): Duplicate entry 'Foppy' for key 'name'
      

      This is expected behavior in 5.6 and 5.7, but not in 8.0.  8.0 and Unicode 9.0.0 standards define that binary collations are to be handled as NO PAD and that means that trailing spaces are included within the comparison and thus this should not result in a DUP_ENTRY error but two unique rows inserted.

      MyRocks key encoding and comparison does not account for this character set attribute (CHARSET_INFO::pad_attribute).  This requires a data format change to fix correctly within 8.0.  Older indexes created under 5.7 or early versions of 8.0 prior to this being fixed will need to be rebuilt in order to behave properly.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                george.lorch George Lorch
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 6 hours
                    6h