| Bug #118333 | The collation_connection value of the session connection does not match the expected value. | ||
|---|---|---|---|
| Submitted: | 3 Jun 3:44 | Modified: | 4 Aug 13:20 |
| Reporter: | junhai wei | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.41，all | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[3 Jun 3:44]
junhai wei
[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 '='
[31 Jul 13:11]
MySQL Verification Team
Hello Junhai Wei, Thank you for the bug report. Imho this is duplicate of Bug #109507 when selecting from a view with = sign, please see Bug #109507. Regards, Ashwini Patil
[1 Aug 2:43]
junhai wei
I apologize if my previous message was not entirely clear. What I intended to convey is as follows: 1. In the my.cnf configuration file, the following settings are specified: character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci 2. Under these settings, when a user connects (specifying --default-character-set=utf8mb4), the session-level parameter collation_connection should be utf8mb4_0900_ai_ci. Additionally, the global-level character_set_client query should display as utf8mb4. 3. However, if the user then executes the command set global character_set_client=utf8mb4; (this action might seem redundant as the character_set_client value appears unchanged before and after the modification) 4. Upon reconnecting, with the same --default-character-set=utf8mb4 setting, the user will notice that the session-level collation_connection has changed to utf8mb4_unicode_ci. 5. This means that executing the command set global character_set_client=utf8mb4; (even though the value is already utf8mb4, the user is essentially re-setting it) results in a change to the session-level collation_connection parameter, which is not as expected. ---------------------------------------------------------------------------------- Bug #109507. This seems to be a normal scenario. As explained on the official website: https://dev.mysql.com/doc/refman/8.0/en/show-create-view.html The collation_connection in the created view is based on the system variable value at the time of view creation. Once the view is created, this parameter is fixed, and the collation for the defined concat('test', a) as b in the view comes from the collation_connection value in the view's definition. The collation for the literal 'test1' depends on the current collation_connection variable value in the connection. When there is a conflict between these two, an "Illegal mix of collations" error is reported. This is as expected. Perhaps, Bug #109507 maybe not a bug. ^_^ ---------------------------------------------------------------------------------- Thanks.
[4 Aug 12:11]
MySQL Verification Team
Hello Junhai Wei, Thank you for the feedback. I tried to reproduce your issue on Windows 11 and followed exact steps. Here is the output. C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p --default-character-set=utf8mb4 Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 8.0.42 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 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_0900_ai_ci | +-----------------------+--------------------+ 4 rows in set (0.00 sec) mysql> 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.00 sec) mysql> set global character_set_client=utf8mb4; Query OK, 0 rows affected (0.00 sec) ========================================================================= After re-connecting ---------------------- C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p --default-character-set=utf8mb4 Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 8.0.42 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates.a Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 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.00 sec) Regards, Ashwini Patil
[4 Aug 12:27]
junhai wei
1, Maybe, first of all, you should modify the configuration content of my.cnf as shown below, In the my.cnf configuration file, the following settings are specified: character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci (This information is extremely important.^_^) 2, then proceed with the subsequent verification. This modification of the my.cnf content is a prerequisite. THANK YOU
[4 Aug 12:40]
MySQL Verification Team
Hello junhai wei, As I mentioned above, I have followed exact steps including modifying configuration file. Let us know if we are missing out something. Regards, Ashwini Patil
[4 Aug 12:47]
junhai wei
I am so sorry, I test on linux/x86 system. I don't have Windows applications. and I tried versions 8.0.22 and 8.0.41.
[4 Aug 13:09]
MySQL Verification Team
Please see the screenshot
Attachment: Screenshot_my_cnf.png (image/png, text), 48.12 KiB.
[4 Aug 13:20]
junhai wei
I'm not sure if the my.cnf file you configured has been recognized and used by the system. However, based on your test results, the collation_server value at the global level does not seem to have taken effect according to the my.cnf configuration. I'm quite puzzled.
