Bug #118333 The collation_connection value of the session connection does not match the expected value.

• Submitted: 3 Jun 3:44
• Modified: 5 Jun 13:41
• Reporter: junhai wei
• Status: Analyzing
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.41，all
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[3 Jun 3:44] junhai wei

Description:
When the global parameter character_set_client is set, it is changed from utf8mb4 to utf8mb4 (the actual value does not change).In a new session connection, the collation_connection value of the session connection will change and not meet expectations.

How to repeat:
1. The user configures the following parameters in my.cnf to change the default character set and collation of the server:
   character_set_server=utf8mb4
   collation_server=utf8mb4_unicode_ci

2. The user uses the mysql client to connect to the server and specifies `--default-character-set=utf8mb4`.

After the MySQL server is started, query the global variable information as follows：
SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ( 'character_set_client', 'character_set_results','collation_server','character_set_server' ) ORDER BY VARIABLE_NAME;
+-----------------------+--------------------+
| VARIABLE_NAME         | VARIABLE_VALUE     |
+-----------------------+--------------------+
| character_set_client  | utf8mb4            |
| character_set_results | utf8mb4            |
| character_set_server  | utf8mb4            |
| collation_server      | utf8mb4_unicode_ci |
+-----------------------+--------------------+
4 rows in set (0.01 sec)

3. At this time, the user queries the session parameter collation_connection in the session, and its value is utf8mb4_0900_ai_ci.

SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME IN ( 'character_set_client', 'character_set_connection', 'collation_connection' ) ORDER BY VARIABLE_NAME;
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
3 rows in set (0.01 sec)

4. Then, the user modifies the global parameter `character_set_client` in the session. The command is as follows:
   set global character_set_client=utf8mb4;

5. Then, the user exits the current session and reconnects to the server, still specifying `--default-character-set=utf8mb4`.

6. Then, the user queries the session parameter collation_connection again within the current connection, and its value becomes utf8mb4_unicode_ci.This is not what was expected

SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME IN ( 'character_set_client', 'character_set_connection', 'collation_connection' ) ORDER BY VARIABLE_NAME;
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| collation_connection     | utf8mb4_unicode_ci |
+--------------------------+--------------------+
3 rows in set (0.01 sec)

[5 Jun 13:41] junhai wei

To further supplement some information, the above issue will cause an intuitive impact:
In step 3, execute the table creation statement and the view creation statement:

mysql> CREATE TABLE tt(a int DEFAULT NULL, b varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into tt values(1, 'a'),(2, 'b');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> CREATE VIEW tt_v AS select a, (case when (b in ('1','2')) then 'N' else 'Y' end) AS b from tt;
Query OK, 0 rows affected (0.01 sec)

At this time, executing the following command is successful,
mysql> select * from tt_v where b='Y';
+------+---+
| a | b |
+------+---+
| 1 | Y |
| 2 | Y |
+------+---+
2 rows in set (0.01 sec)

However, after executing step 4, it fails when executed in a new session.

mysql> select * from tt_v where b='Y';
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='