Description:
When we set the character_set_server to utf32, the driver is still trying to execute the SET NAMES command with utf32. When we execute SET NAMES it will internally change the character_set_client to same character_set. When MySQL Server has predefined limitation that character_sets like utf32, ucs2, etc are not supported by character_set_client variable.
Consider to update the .net driver to detect this during the connection setting and handle the same either with better error message or change the charset to default (from global value).
How to repeat:
Configure the server with below values and restart the instance.
mysql> show global variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf32 |
.
| character_set_results | latin1 |
| character_set_server | utf32 |
| character_set_system | utf8mb3 |
.
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
>> Consider to run the below sample code using respective dotnet version and mysql dot net connector. The program fails with error.
[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 ~]#
### Additional details:
dotnet version:
[root@testbox ~]# dotnet --info
.NET SDK:
Version: 7.0.400
Commit: 73bf45718d
Runtime Environment:
OS Name: rhel
OS Version: 7
OS Platform: Linux
RID: rhel.7-x64
Base Path: /usr/share/dotnet/sdk/7.0.400/
Host:
Version: 7.0.10
Architecture: x64
Commit: a6dbb800a4
.NET SDKs installed:
7.0.400 [/usr/share/dotnet/sdk]
.NET runtimes installed:
Microsoft.AspNetCore.App 6.0.21 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 7.0.10 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 6.0.21 [/usr/share/dotnet/shared/Microsoft.NETCore.App]
Microsoft.NETCore.App 7.0.10 [/usr/share/dotnet/shared/Microsoft.NETCore.App]
.
.
[root@testbox ~]#
Config file:
[root@testbox ~]# cat root.csproj
<Project Sdk="Microsoft.NET.Sdk">
<ItemGroup>
<Reference Include="/root/mysql-connector-net-8.4.0/net7.0/MySql.Data.dll" />
</ItemGroup>
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net7.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
</Project>
[root@testbox ~]#
MySQL connector:
mysql-connector-net-8.4.0
Mysql user and server details:
[root@testbox ~]# /root/dbdeployer/8.0.37/bin/mysql -u root -p**** -S /tmp/mysql_sandbox8037.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> \s
--------------
/root/dbdeployer/8.0.37/bin/mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 14
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: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql_sandbox8037.sock
Binary data as: Hexadecimal
Uptime: 6 min 13 sec
Threads: 2 Questions: 65 Slow queries: 0 Opens: 189 Flush tables: 3 Open tables: 105 Queries per second avg: 0.174
--------------
mysql> \q
Bye
[root@testbox ~]#
Sample code:
[root@testbox ~]# cat Program.cs
using System;
using MySql.Data.MySqlClient;
class Program
{
static void Main(string[] args)
{
// MySQL connection string
string connectionString = "server=127.0.0.1;user=root;password=test1234;database=sbtest;port=8037";
// Create a new MySQL connection
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
// Open the connection
connection.Open();
// Create a new MySQL command
using (MySqlCommand command = new MySqlCommand("SELECT 1", connection))
{
// Execute the query and get the result
object result = command.ExecuteScalar();
// Print the result
Console.WriteLine($"Result: {result}");
}
}
catch (MySqlException ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
finally
{
// Close the connection
connection.Close();
}
}
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
}
[root@testbox ~]#
Suggested fix:
We might need to consider to update the driver validation defined below to address unsupported charset code values and change charSet value to character_set_client value and execute SET NAMES accordingly.
```
if ((clientCharSet != null && clientCharSet.ToString() != charSet) ||
(connCharSet != null && connCharSet.ToString() != charSet))
{
using MySqlCommand setNamesCmd = new MySqlCommand("SET NAMES " + charSet, connection);
setNamesCmd.InternallyCreated = true;
await setNamesCmd.ExecuteNonQueryAsync(execAsync, cancellationToken).ConfigureAwait(false);
}
```
https://github.com/mysql/mysql-connector-net/blob/8.x/MySQL.Data/src/Driver.cs#L279
The driver should execute SET NAMES to a value supported by character_set_client. We should not run the SET NAMES with character_set_server value as all character sets values supported by server are not supported by client.
Description: When we set the character_set_server to utf32, the driver is still trying to execute the SET NAMES command with utf32. When we execute SET NAMES it will internally change the character_set_client to same character_set. When MySQL Server has predefined limitation that character_sets like utf32, ucs2, etc are not supported by character_set_client variable. Consider to update the .net driver to detect this during the connection setting and handle the same either with better error message or change the charset to default (from global value). How to repeat: Configure the server with below values and restart the instance. mysql> show global variables like 'char%'; +--------------------------+-----------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf32 | . | character_set_results | latin1 | | character_set_server | utf32 | | character_set_system | utf8mb3 | . +--------------------------+-----------------------------------------+ 8 rows in set (0.00 sec) >> Consider to run the below sample code using respective dotnet version and mysql dot net connector. The program fails with error. [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 ~]# ### Additional details: dotnet version: [root@testbox ~]# dotnet --info .NET SDK: Version: 7.0.400 Commit: 73bf45718d Runtime Environment: OS Name: rhel OS Version: 7 OS Platform: Linux RID: rhel.7-x64 Base Path: /usr/share/dotnet/sdk/7.0.400/ Host: Version: 7.0.10 Architecture: x64 Commit: a6dbb800a4 .NET SDKs installed: 7.0.400 [/usr/share/dotnet/sdk] .NET runtimes installed: Microsoft.AspNetCore.App 6.0.21 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 7.0.10 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App] Microsoft.NETCore.App 6.0.21 [/usr/share/dotnet/shared/Microsoft.NETCore.App] Microsoft.NETCore.App 7.0.10 [/usr/share/dotnet/shared/Microsoft.NETCore.App] . . [root@testbox ~]# Config file: [root@testbox ~]# cat root.csproj <Project Sdk="Microsoft.NET.Sdk"> <ItemGroup> <Reference Include="/root/mysql-connector-net-8.4.0/net7.0/MySql.Data.dll" /> </ItemGroup> <PropertyGroup> <OutputType>Exe</OutputType> <TargetFramework>net7.0</TargetFramework> <ImplicitUsings>enable</ImplicitUsings> <Nullable>enable</Nullable> </PropertyGroup> </Project> [root@testbox ~]# MySQL connector: mysql-connector-net-8.4.0 Mysql user and server details: [root@testbox ~]# /root/dbdeployer/8.0.37/bin/mysql -u root -p**** -S /tmp/mysql_sandbox8037.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, 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> show grants; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> \s -------------- /root/dbdeployer/8.0.37/bin/mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 14 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: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /tmp/mysql_sandbox8037.sock Binary data as: Hexadecimal Uptime: 6 min 13 sec Threads: 2 Questions: 65 Slow queries: 0 Opens: 189 Flush tables: 3 Open tables: 105 Queries per second avg: 0.174 -------------- mysql> \q Bye [root@testbox ~]# Sample code: [root@testbox ~]# cat Program.cs using System; using MySql.Data.MySqlClient; class Program { static void Main(string[] args) { // MySQL connection string string connectionString = "server=127.0.0.1;user=root;password=test1234;database=sbtest;port=8037"; // Create a new MySQL connection using (MySqlConnection connection = new MySqlConnection(connectionString)) { try { // Open the connection connection.Open(); // Create a new MySQL command using (MySqlCommand command = new MySqlCommand("SELECT 1", connection)) { // Execute the query and get the result object result = command.ExecuteScalar(); // Print the result Console.WriteLine($"Result: {result}"); } } catch (MySqlException ex) { Console.WriteLine($"Error: {ex.Message}"); } finally { // Close the connection connection.Close(); } } Console.WriteLine("Press any key to exit..."); Console.ReadKey(); } } [root@testbox ~]# Suggested fix: We might need to consider to update the driver validation defined below to address unsupported charset code values and change charSet value to character_set_client value and execute SET NAMES accordingly. ``` if ((clientCharSet != null && clientCharSet.ToString() != charSet) || (connCharSet != null && connCharSet.ToString() != charSet)) { using MySqlCommand setNamesCmd = new MySqlCommand("SET NAMES " + charSet, connection); setNamesCmd.InternallyCreated = true; await setNamesCmd.ExecuteNonQueryAsync(execAsync, cancellationToken).ConfigureAwait(false); } ``` https://github.com/mysql/mysql-connector-net/blob/8.x/MySQL.Data/src/Driver.cs#L279 The driver should execute SET NAMES to a value supported by character_set_client. We should not run the SET NAMES with character_set_server value as all character sets values supported by server are not supported by client.