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.