ProxySQL Error Detected a broken connection during SET NAMES, Can''t initialize character set (null)

[billy522]

Hi, When I use Sequel Ace to connect proxysql, i will get "MySQL_Session.cpp:1871:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on 10.XXX.XXX.XXX , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)" message. This message will appear tens of thousands of times in about a minute, and it will continue until I close the GUI.

Using other methods to connect to proxysql will not encounter this problem, but I can't restrict the way users use to connect to proxysql, how can i fix it?

MySQL config

mysql> SHOW VARIABLES like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

ProxySQL

mysql> SHOW VARIABLES LIKE 'mysql-default_charset';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| mysql-default_charset | utf8  |
+-----------------------+-------+
1 row in set (0.00 sec)

Version: 1.4.16

Thank you!

[renecannao]

Hi,

Do you think it is relevant that you are using version 1.4.16? Do you think I need to guess what ProxySQL version is this issue about?

Please, when opening an issue, report the ProxySQL version! It is listed as a requirement in the New Issue Template! Thanks

[billy522]

I am very sorry that I forgot to attach the version information, my version is 1.4.16, I hope to get an answer, sincerely

[renecannao]

Your client (Sequel Ace in this case) is requesting a charset/collation that the ProxySQL's doesn't know about. Because ProxySQL needs to configure the backend connection with the same charset/collation required by the client, the fact that doesn't know about the required charset/collation leads to this error. You have two options: a) upgrade ProxySQL: it is very likely that the charset/collation that Sequel Ace is requesting is supported in newer versions of ProxySQL b) configure the client (Sequel Ace) to use a different charset/collation

If you go for option A and the issue persist, please let me know: I would be curious to know what charset/collation the client is trying to use.

Thanks

[billy522]

Can I through the table or log to find what charset/collation the client is trying to use?

Thank you!

[renecannao]

You could set mysql-show_processlist_extended to either 1 or 2 , than run SELECT * FROM stats_mysql_processlist while the client is connected, and identify which charset/collation the client has requested.

[billy522]

If I want to work around it temporarily (upgrade needs to be evaluated), can I use something like the following to bypass the null problem?

INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) VALUES(2,1,'SET NAMES = NULL','SET NAMES = utf8mb4',1);

Thank you!

[renecannao]

No, it will not work. It is not a NULL charset. The error you get is proxysql not finding the charset/collation names for what the client specified. Because it doesn't find the charset/collation names, it returns NULL. Furthermore, the client is probably not using any SET NAMES command, but negotiating it during the handshake in the form of a numeric value representing the collation.