SET NAMES utf8mb4 does not change collation_connection when default_collation_for_utf8mb4 is modified

[yahonda]

Bug Report

SET NAMES utf8mb4 does not change collation_connection when default_collation_for_utf8mb4 is modified.

1. Minimal reproduce step (Required)

• Startup tiup playground v8.3.0

% mysql --comments --host 127.0.0.1 --port 4000 -u root

• Confirm the default value of default_collation_for_utf8mb4

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

• Confirm the default value of collation_connection

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

• Change the default_collation_for_utf8mb4 value to utf8mb4_general_ci

mysql> set default_collation_for_utf8mb4 = 'utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

• Execute SET NAMES utf8mb4

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

• Validate the collation_connection value after default_collation_for_utf8mb4 has been modified to utf8mb4_general_ci.

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

mysql>

2. What did you expect to see? (Required)

The last collation_connection value should show utf8mb4_general_ci as default_collation_for_utf8mb4 modified to utf8mb4_general_ci

3. What did you see instead (Required)

The last collation_connection value shows utf8mb4_bin.

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

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v8.3.0
Edition: Community
Git Commit Hash: 1a0c3ac3292fff7742faa0c00a662ccb66ba40db
Git Branch: HEAD
UTC Build Time: 2024-08-20 10:23:00
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql>

[yahonda]

According to the "15.7.6.3 SET NAMES Statement",

https://dev.mysql.com/doc/refman/8.0/en/set-names.html

Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name.

set names statement should change the collation_connection value to the default collation for charset_name, I assume here the default_collation_for_utf8mb4 value should be "the default collation for charset_name", that is "utf8mb4_general_ci" in my repro.

[dveeden]

Note that this variable has been deprecated in MySQL:

mysql-9.0.1> set default_collation_for_utf8mb4 = 'utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql-9.0.1> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

An alternative way of doing this is to specify the collation when running SET NAMES.

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

mysql-9.0.1> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

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

This also works in TiDB:

mysql-8.0.11-TiDB-v8.4.0-alpha-325-g74034d4ac2-dirty> show variables like 'collation_connection';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)

mysql-8.0.11-TiDB-v8.4.0-alpha-325-g74034d4ac2-dirty> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

mysql-8.0.11-TiDB-v8.4.0-alpha-325-g74034d4ac2-dirty> show variables like 'collation_connection';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
1 row in set (0.00 sec)

I think it would still be good to make sure default_collation_for_utf8mb4 works as expected.