Bug #101692 collation_connection global set not Don't take effect

• Submitted: 20 Nov 2020 7:37
• Modified: 23 Nov 2020 15:45
• Reporter: HULONG CUI
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.19
• OS: Any
• CPU Architecture: Any
• Tags: collation_connection

[20 Nov 2020 7:37] HULONG CUI

Description:
login mysql server
set global collation_connection=utf8mb4_general_ci

mysql> \s
--------------
/opt/idc/mysql8.0.19/bin/mysql  Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:		9
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.19 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/zxdata/my3311/socket/mysql.sock
Binary data as:		Hexadecimal
Uptime:			35 sec

mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8_general_ci    |
| collation_server              | utf8_general_ci    |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> set global collation_connection=utf8_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8_general_ci    |
| collation_server              | utf8_general_ci    |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

relogin mysql but not effect

How to repeat:
1.my.cnf setting
character_set_server=utf8
collation_server = utf8_general_ci

2.start mysql server

3.set global collation_connection=utf8_general_ci;

4.get variables info

Suggested fix:
i think this bug

[20 Nov 2020 7:54] HULONG CUI

i have set session value.
that's ok

mysql> set session collation_connection=utf8_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8_general_ci    |
| collation_database            | utf8_general_ci    |
| collation_server              | utf8_general_ci    |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

global set not's effect

[20 Nov 2020 13:53] MySQL Verification Team

Hi Mr. jacky,

Thank you for your bug report.

However, it is not a bug.

Namely, when changing "collation_connection" on the global level, it will affect all future connections, but not the current one. Current one is affected only by setting the session variable. This is all fully explained in our Reference Manual.

Not a bug.

[23 Nov 2020 15:45] HULONG CUI

i try do:
step1:
mysql>set global collation_connection=utf8_general_ci;
mysql>exit;

step2:
mysql -uroot -p
mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |

find not have changed。
It's the same default value as before。