@@global.character_set_connection seems take no effect for new connections

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> set @@global.character_set_connection="latin1";

Then schedule a new connection using MySQL client:

$ mysql --comments --host 127.0.0.1 -uroot -P4000
mysql> select @@global.character_set_connection, @@character_set_connection;

2. What did you expect to see? (Required)

I use a mysql client with version 8.0.33 to do this test

$ mysql --version
mysql  Ver 8.0.33 for macos13.3 on arm64 (Homebrew)

In mysql 8.0.33, the new connection has the same @@character_set_connection with global settings:

$ mysql --comments --host 127.0.0.1 -uroot -P3306
mysql> select @@global.character_set_connection, @@character_set_connection;
+-----------------------------------+----------------------------+
| @@global.character_set_connection | @@character_set_connection |
+-----------------------------------+----------------------------+
| latin1                            | latin1                     |
+-----------------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

3. What did you see instead (Required)

But for TiDB, @@global.character_set_connection does not take effect:

> mysql --comments --host 127.0.0.1 -uroot -P4001
mysql> select @@global.character_set_connection, @@character_set_connection;
+-----------------------------------+----------------------------+
| @@global.character_set_connection | @@character_set_connection |
+-----------------------------------+----------------------------+
| latin1                            | utf8mb4                    |
+-----------------------------------+----------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

master

[lcwangchao]

There are 3 places where the charset can be set:

• handshake. When the client starts to set up a connection. A 8-bits char set is included in proto: handshake_response41 , and tidb will parse it and set it to collection.
  

  tidb/pkg/server/internal/parse/parse.go

  Line 65 in 1bac22a

  packet.Collation = data[offset]

• load global variables to the session in the first statement. See:
  

  tidb/pkg/session/session.go

  Line 1561 in 1bac22a

  if err := s.loadCommonGlobalVariablesIfNeeded(); err != nil {

When ExecuteStmt is called, it will call loadCommonGlobalVariablesIfNeeded to load the global variable to the current session. However, for character_set_connection, the skipInit is set as true which means this step will be skipped:

tidb/pkg/sessionctx/variable/sysvar.go

Line 1648 in 6d92e7a

{Scope: ScopeGlobal | ScopeSession, Name: CollationConnection, Value: mysql.DefaultCollationName, skipInit: true, Validation: func(vars *SessionVars, normalizedValue string, originalValue string, scope ScopeFlag) (string, error) {

That's why the global settings of character_set_connection does not take effect.

• Some clients will also run an extra statement SET NAMES ... after the handshake. Then the charset/collation in the current session will be reset too. That depends on the client and its configurations. For MySQL client with version 8.0.3, it does not do it.