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
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