Bug #115154 Dynamic Changes to the character_set_% are not honored duing the handshake
Submitted: 29 May 5:40 Modified: 31 May 13:44
Reporter: Chelluru Vidyadhar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:8.0.37 OS:Any
Assigned to: CPU Architecture:Any

[29 May 5:40] Chelluru Vidyadhar
Description:
When we change the character set dynamically, the server is still sending the default values to the client during handshake instead of the updated values. 

When we set character_set client, connection, results to latin1 and database, server to utf32, the server still sending utf8mb4 during the initial handshake (Charset).

If we restart the mysqld instance by adding character_set_server to utf32 (all other parameters will be automatically adjusted to above mentioned changes only), then the server sending character set unknown with code 60 (which belongs to utf32_general_ci)

How to repeat:
>> start new 8.0.37 instance and we see below character_set values as default and the script runs as expected and provide result 1 (its running "select 1" query)

mysql> show global variables like 'character_set%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8mb4                                 |
| character_set_connection | utf8mb4                                 |
| character_set_database   | utf8mb4                                 |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8mb4                                 |
| character_set_server     | utf8mb4                                 |
| character_set_system     | utf8mb3                                 |
| character_sets_dir       | /root/dbdeployer/8.0.37/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)

[root@testbox ~]# dotnet run
Result: 1
Press any key to exit...
[root@testbox ~]# 

>> Now, change the character_set parameters dynamically to respective values and run the same script and it works as expected. When we check the tcpdump, we see that the server sent utf8mb4 (255) as language during handshake. We can observe same in general logs as well because driver (.net) runs the SET NAMES command using the same char set received.

mysql> set global character_set_server=utf32;
Query OK, 0 rows affected (0.00 sec)

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

mysql> set global character_set_connection=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global character_set_client=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | latin1                                  |
| character_set_connection | latin1                                  |
| character_set_database   | utf32                                   | <-----------------
| character_set_filesystem | binary                                  |
| character_set_results    | latin1                                  |
| character_set_server     | utf32                                   | <-----------------
| character_set_system     | utf8mb3                                 |
| character_sets_dir       | /root/dbdeployer/8.0.37/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)

 
[root@testbox ~]# dotnet run
Result: 1
Press any key to exit...
[root@testbox ~]# 

General log:
	
2024-05-28T20:17:21.857832Z	   21 Connect	root@localhost on sbtest using SSL/TLS
2024-05-28T20:17:21.916432Z	   21 Query	SELECT @@max_allowed_packet, @@character_set_client, 
        @@character_set_connection, @@license, @@sql_mode, @@lower_case_table_names, @@autocommit
2024-05-28T20:17:21.975317Z	   21 Query	SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP())
2024-05-28T20:17:21.979816Z	   21 Query	SHOW COLLATION
2024-05-28T20:17:21.990416Z	   21 Query	SET NAMES utf8mb4
2024-05-28T20:17:21.991423Z	   21 Query	SET character_set_results=NULL
2024-05-28T20:17:21.997279Z	   21 Init DB	sbtest
2024-05-28T20:17:22.017173Z	   21 Query	SELECT 1

>> When I add character_set_server parameter to configuration file and restart the instance, we see that the driver try to run same set names using utf32 and fails to establish connection.

[root@testbox msb_8_0_37]# cat my.sandbox.cnf 

[client]
user               = vidya
password           = test1234
port               = 8037
socket             = /tmp/mysql_sandbox8037.sock

[mysqld]
user               = root
port               = 8037
socket             = /tmp/mysql_sandbox8037.sock
basedir            = /root/dbdeployer/8.0.37
datadir            = /root/sandboxes/msb_8_0_37/data
tmpdir             = /root/sandboxes/msb_8_0_37/tmp
pid-file           = /root/sandboxes/msb_8_0_37/data/mysql_sandbox8037.pid
bind-address       = *
report-host=single-8037
report-port=8037
log-error=/root/sandboxes/msb_8_0_37/data/msandbox.err
character_set_server = utf32  <-----------------------------------------------------
mysqlx-port=18037
mysqlx-socket=/tmp/mysqlx-18037.sock

[root@testbox msb_8_0_37]# 

mysql> \s
--------------
/root/dbdeployer/8.0.37/bin/mysql  Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		8
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.37 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf32
Db     characterset:	utf32
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql_sandbox8037.sock
Binary data as:		Hexadecimal
Uptime:			12 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 136  Flush tables: 3  Open tables: 55  Queries per second avg: 0.500
--------------

mysql> show global variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | latin1                                  |
| character_set_connection | latin1                                  |
| character_set_database   | utf32                                   | <-----------------
| character_set_filesystem | binary                                  |
| character_set_results    | latin1                                  |
| character_set_server     | utf32                                   | <-----------------
| character_set_system     | utf8mb3                                 |
| character_sets_dir       | /root/dbdeployer/8.0.37/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)

[root@testbox ~]# dotnet run
Error: Variable 'character_set_client' can't be set to the value of 'utf32'
Press any key to exit...
[root@testbox ~]# 

Suggested fix:
The server should send the appropriate character set configured on the instance irrespective of the way the changes configured (configuration file or dynamic).

Initially, I suspected below change may resolve this issue but this behavior is reproducible even in 8.0.37.

When the character_set_server system variable was set using SET PERSIST or SET GLOBAL, it did not take effect for new client sessions or for a client establishing a connection to the server after the server was restarted. The only workaround was to set the corresponding command-line option when starting the server. To fix this, we now make sure that, at the time of server restart, the configuration data is read in the correct order so that the variable setting takes effect as expected. (Bug #35529604)

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-37.html#mysqld-8-0-37-charset
[31 May 13:44] MySQL Verification Team
Hello Chelluru,

Thank you for the report and feedback.

regards,
Umesh