Bad set Names sent when ProxySQL process a prepared statement

[fplou]

Hi,

We are facing an issue with ProxySQL version 2.3.2 and mySQL 8.0.29 :

2023-05-04 08:45:38 [INFO] ProxySQL version 2.3.2-10-g8cd66cf

Server version: 8.0.29-21.1 (ProxySQL)

ProxySQL is running on AlmaLinux release 8.6 (Sky Tiger)

Our database is configured using latin1 charset:

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/percona-xtradb-cluster/charsets/ |
+--------------------------+---------------------------------------------+

When we execute standard query, there is no problem. But when we execute prepared statement with an accent we get following error:

2023-05-04 13:57:51 MySQL_Session.cpp:3989:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (10,10.203.3.177,3306,7444): 3988, Conversion from collation utf8mb4_0900_ai_ci into latin1_swedish_ci impossible for parameter

With a tcpdump between proxySQL and mySQL, we can see proxySQL is sending a set Names request:

This set Names utf8mb4 and associated collation produce the error.

Regards

François
proxysql.log

[renecannao]

Is your application executing SET NAMES using prepared statements?
If yes, ProxySQL executes it correctly to the backend, but it is not able to detect that the client was trying to change charset/collation using prepared statements. Thus it will then apply to the backend the charset/collation that was able to track.
In this case it probably means that your client is connecting using collation utf8mb4_0900_ai_ci.

2 possible solutions:

1. connect using the collation you really want, without any extra SET NAMES
2. do not run SET NAMES using prepared statement

Option 1 is preferred, as it completely reduce any extra latency caused by unnecessary SET NAMES

[fplou]

No our application is not executing set Names when we used prepared statements (I checked it with tcpdump between the app and proxySQL to be sure).
By the way, I forgot to mention it works well if we bypass the proxySQL. I do not know if it helps.

Another thing to precise, the mySQL is a Percona-xtradb-cluster.

The problem is easily reproductible on our dev environnement. So if you want me to configure proxySQL with some debug values, just ask.

[fplou]

Hello,

Do you have any ideas? Do you need me to do some tests on our dev environment?

[renecannao]

Can you share the tcpdump between application and proxysql?

[fplou]

Here it is.

The two tcpdump was started and stopped at same time.
capture.6033.cap is the tcpdump between application and proxySQL. As you can see there is no SET NAMES.
capture.3306.cap is the tcpdump between proxySQL and mySQL. You will see multiple SET NAMES. I do not know why proxySQL sends them.

In my previous post, I wrote SET NAMES coincided with the prepared statement but this is not true. I think i got a case where SET NAMES appeared at the same time.

Thanks
tcpdumps.zip

[renecannao]

Hi @fplou .
Thank you for the files, but I couldn't see any "Conversion from collation" error.
If there is, can you point it please?
For a proper troubleshooting we need all traffic of the connection receiving the error, from the moment it connects to proxysql.
Also, in capture.6033.cap I only see traffic where destination port is 6033 : this means that all the traffic where source port is 6033 is not present, but it is necessary for troubleshooting.

Thanks

[fplou]

There is no "Conversion from collation" error in these dumps. I just wanted to show the "SET NAMES" sent by the ProxySQL.
Il will try to provide you with a full dump as you asked.

[fplou]

Here it is.

I stopped the application, started both tcpdumps, started the application, ran the test and then stopped the tcpdumps.

capture.6033 : app to ProxySQL

You will see "SET NAMES latin1" (packets 2737, 2739, 2740, ...). But you will never see SET NAMES utf8.
The collation error is on packet 4719 associated with the request on packet 4716.

capture.3306 : ProxySQL to mySQL

You will see "SET NAMES utf8" (packets 28, 1373, ...). But you will never see SET NAMES latin1.
The collation error is on packet 3154.

Our database is configured with latin1 not utf8.
tcpdumps.zip

[renecannao]

Hi @fplou ,

Thank you for the output provided, now I have everything to perform a proper analysis.

The collation error on packet 4719 (and packet 4735) is on the client connection originated from port 52166 .
This client never explicitly asks for any collation using SET NAMES, but it negotiated it during authentication on packet 1318, asking for charset utf8mb4 collate utf8mb4_0900_ai_ci .

Screenshot attached:

So, as I was mentioning in my initial comment , "it probably means that your client is connecting using collation utf8mb4_0900_ai_ci" .

ProxySQL is enforcing what the client is asking for: the use of collation utf8mb4_0900_ai_ci .

If you really want to use latin1 , you need to either force the jdbc driver to use latin1 during connection time, or issue the appropriate SET NAMES command .

[renecannao]

A further note, if relevant.
You configured proxysql to use latin1 as the default charset, as you can see in packet 1243:

So ProxySQL is telling the client that the default collation is latin1_swedish_ci , but the client asks ProxySQL to use utf8mb4_0900_ai_ci in packet 1318 .

[fplou]

Thanks a lot for your answer. I did not notice this because my wireshark was not well configured.
Have a good day.