Add new UCA collations for MariaDB 10.10+

[gabrielrbarbosa]

Some more collations were added and structural changes were done in MariaDB 10.10 as per https://jira.mariadb.org/browse/MDEV-27009 which requires changes in how we get the full list of collations if we are using this engine.

[ansgarbecker]

This is not a valid request. Please provide some details about what you would like to have changed in HeidiSQL.

[gabrielrbarbosa]

Does heidiSql provide utf8mb4_1400_ai_ci collation for example? I coundt find it

[ansgarbecker]

HeidiSQL provides all collations the server returns from SHOW COLLATION. Other collations not returned by that query are not supported.

[gabrielrbarbosa]

Using SHOW COLLATION I could only find "uca1400_ai_ci", then I found more details here:

SELECT * FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY WHERE COLLATION_NAME LIKE 'uca1400_ai_ci';`
 
| COLLATION_NAME | CHARACTER_SET_NAME | FULL_COLLATION_NAME   | ID   | IS_DEFAULT |
|----------------|--------------------|-----------------------|------|------------|
| uca1400_ai_ci  | utf8mb3            | utf8mb3_uca1400_ai_ci | 2048 |            |
| uca1400_ai_ci  | ucs2               | ucs2_uca1400_ai_ci    | 2560 |            |
| uca1400_ai_ci  | utf8mb4            | utf8mb4_uca1400_ai_ci | 2304 |            |
| uca1400_ai_ci  | utf16              | utf16_uca1400_ai_ci   | 2816 |            |
| uca1400_ai_ci  | utf32              | utf32_uca1400_ai_ci   | 3072 |            |

Can we use that full_collation_name maybe or is it already implicit depending on charset chosen?

[ansgarbecker]

No, HeidiSQL's table designer leaves the charset away from the CREATE code. So it will always use the default full collation. And the user is not able to provide a collation different to the default one (e.g. utf8mb3_uca1400_ai_ci).
Means HeidiSQL needs to query IS.COLLATION_CHARACTER_SET_APPLICABILITY if the server version supports that.
Please provide the exact version number where MariaDB started to support that. Probably MySQL (8?) also has that FULL_COLLATION_NAME?

Btw, MySQL Workbench also does not support full collation names: https://bugs.mysql.com/bug.php?id=102456

[gabrielrbarbosa]

All MariaDB versions higher than 10.10.1
https://jira.mariadb.org/browse/MDEV-27009
https://mariadb.com/kb/en/mariadb-10101-changelog/

[ansgarbecker]

I guess you meant "higher or equal" to 10.10.1
Anyway, without knowing when or if MySQL handles full collation names, it's a bit tricky. I could probably query the IS table and silence an error if the "full..." column does not exist.