Bug #106784 Keep current collation_server value when that value matches new character set
Submitted: 19 Mar 2022 13:34 Modified: 20 Mar 2022 6:44
Reporter: Mitani Satoshi (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S4 (Feature request)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[19 Mar 2022 13:34] Mitani Satoshi
Description:
Setting character_set_server leads implicitly reset collation_server.
When issue SET GLOBAL character_set_server, collation_server value is changed to default collation of that character set.

Not only when changing character_set_server but also set character_set_server same value as current character_set_server value, collation_server is reset. This behavior leads unintended collation_server change.

Regarding current behavior, we have to mind the SET GLOBAL order. Issuing SET GLOBAL character_set_server following  SET GLOBAL collation_server, new collation_server value is lost. We always have to SET GLOBAL character_set_server first.

How to repeat:
$ mysql -uroot

mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.01 sec)

mysql> show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL collation_server = 'utf8mb4_bin';
Query OK, 0 rows affected (0.00 sec)

$ mysql -uroot

mysql> show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_server';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| collation_server | utf8mb4_bin |
+------------------+-------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL character_set_server = utf8mb4;
Query OK, 0 rows affected (0.00 sec)

$ mysql -uroot

mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.00 sec)

Suggested fix:
reset collation_server value only when the new character set is not match with current collation_server value.
In other word, keep current collation_server value, when that value matches new character set that specified SET GLOBAL.
[20 Mar 2022 6:44] MySQL Verification Team
Hello Mitani,

Thank you for the report and feedback.

regards,
Umesh
[21 Mar 2022 8:26] Roy Lyseng
Not sure this is a very good idea:
- The rule for setting the collation would become more complicated, and
  would depend on the current setting.
- Changing documented functionality should only be done when there is a clear
  advantage, since it may affect existing users.
- Setting collation_server also sets character_set_server implicitly (for
  obvious reasons), thus simply setting collation_server should achieve what
  you need.