0

I ran into an issue today, involving character supplementation.

I have a UNIQUE username column, which should support the whole utf-8 encoding, however I discovered that our existing collation is invoking character supplementation

As an example:

Alpha²
Alpha2

Adding Alpha² as a username works perfectly fine, as expected, but attempting to add Alpha2 (non-superscript numeral) afterwards as a seperate row results in a conflict error saying that the value is already in use (by Alpha² - this makes me believe that the supplementation is only occurring on the indexed value).

However if Alpha² already exists and we SELECT * FROM users WHERE username = 'Alpha2' we will get no result (as expected/desired), the lack of result here informs our code that the username is available, but attempting to INSERT with Alpha2 then conflicts with Alpha² as mentioned above. (This could be resolved with WHERE BINARY however using that ignores the index and instead of a <1ms response time it becomes 18 seconds (over 16 million users).

Essentially behind the scenes character supplementation is occurring (converting ² to 2) and this is innate in the utf8mb4 collation we have chosen from what I've learnt stumbling through a few discussions about this... our options to treat these usernames separately are few or none.

Is there a configuration setting somewhere that will allow me to disable that character supplementation (so that Alpha² and Alpha2 are not both indexed as Alpha2) without changing to a binary collation.

If changing to a binary collation is the only way then a few questions I'd have would be

  1. Risk factor (clean utf8mb4 conversion to binary, anything to worry about)
  2. Time (we deal with upwards of 500k requests per hour, how long would it typically take to convert a table with 16m+ users re-indexing etc, best approach)
  3. Can we just change the collation of username to binary instead of the entire table?

Apologies if I'm being broad.

2 Answers 2

1

MariaDB-10.10 has UCA1400 collations that don't do character supplementation:

With this I tested your case like:

MariaDB [test]> create table v (i varchar(30)) character set utf8mb4 default collate  uca1400_ai_cs;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> alter table v add unique key i_u(i);
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into v values ('Alpha²'),('Alpha2');
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from v;
+---------+
| i       |
+---------+
| Alpha2  |
| Alpha² |
+---------+
2 rows in set (0.001 sec)

MariaDB [test]> select hex(i),i from v;
+--------------------+---------+
| hex(i)             | i       |
+--------------------+---------+
| 416C70686132       | Alpha2  |
| 416C706861C382C2B2 | Alpha² |
+--------------------+---------+

MariaDB [test]> show create table v;
CREATE TABLE `v` (
  `i` varchar(30) DEFAULT NULL,
  UNIQUE KEY `i_u` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_cs
    1 row in set (0.000 sec)
3
  • Thanks for this, does this affect case-sensitivity at all? e.g will SELECT .... WHERE i = 'alpha2' still return Alpha2
    – zanderwar
    Jan 13, 2023 at 0:52
  • 1
    Ah the ci implies case-insensitivity anyway
    – zanderwar
    Jan 13, 2023 at 1:03
  • 1
    yes cs vs ci can be applied like before. I don't the the ai makes a difference either, they just have a stronger conformance to the UCA standard.
    – danblack
    Jan 13, 2023 at 1:54
0

Do you want case folding? Other accent equivalences?

Please provide SHOW CREATE TABLE and your queries that caused trouble.

I tested 8.0 with a simple example; I got that Alpha² was treated as equal to Alpha2 for both searching and duplicate key detection.

Also, please provide SELECT HEX(user) FROM table. I expect to see 416C706861C2B2 since the superscript-2 is encoded C2B2.

Note that using the datatype BINARY or COLLATION utf8mb4_bin or BINARY(user) or _binary('Alpha²') should all lead to Alpha² and Alpha2 and alpha2 and ALPHA2 all being different.

3
  • Binary causes searches to become case-sensitive unfortunately and is a deal breaker, a collation swap may be the best bet.
    – zanderwar
    Jan 13, 2023 at 1:05
  • @zanderwar - I don't see any solution -- Please spell out what you mean by "collation swap". Better yet, write a simple test case with pairs of words that need to be the same or different.
    – Rick James
    Jan 13, 2023 at 1:21
  • Swapping collation from utf8mb4_unicode_ci to utf8mb4_uca1400_as_ci seems to be the best bet as it will prevent character supplementation whilst also retaining case-insensitivity. In binary collation or casting alpha2 does not equal Alpha2 so using binary in any sense breaks some search functionality that uses LIKE as I cannot expect users to get the casing correct, in non-binary collations (particular those suffixed with _ci) alpha2 will return Alpha2
    – zanderwar
    Jan 13, 2023 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.