No response to commands if client specified collation instead of charset

[unknown321]

Hi,

I am trying to set up a Grafana monitoring server, using proxysql (ProxySQL version v1.4.3-1.1, codename Truls, Debian Stretch) as a proxy to Percona XtraDB cluster (a single node in bootstrap mode for testing).

The issue occurs with any Grafana Docker image starting with version 4.5.0-beta1 ( https://hub.docker.com/r/grafana/grafana/tags/ ). Containers with version <4.5.0 work fine, but containers with version >=4.5.0 don't get command responses from proxysql after authorization. Problem doesn't occur without proxysql (direct connection to cluster node).

After some debugging I've found the source of the problem. In version 4.5.0 Grafana's connection options were changed from
cnnstr = fmt.Sprintf("%s:%s@%s(%s)/%s?charset=utf8mb4&allowNativePasswords=true"
to
cnnstr = fmt.Sprintf("%s:%s@%s(%s)/%s?collation=utf8mb4_unicode_ci&allowNativePasswords=true"
in order to avoid using SET NAMES (see grafana/grafana@c5400ff#diff-7cd5adfbae9b8f2f4c6f2d40d5ed6e78 ).

Attaching two tcpdump caps - version 4.5.0 (broken, no response after sending SELECT @@max_allowed_packet), where collation=utf8mb4_unicode_ci was added and version 4.7.0 with my patch (which brings back charset=utf8mb4), where communications work as they should (still broken without patch).

4.5.0-broken.zip
4.7.0-fixed.zip

You can reproduce it by running two Docker Grafana images like this:

docker run -dt --expose 3000 -e "GF_DATABASE_URL=mysql://<username>:<password>@<proxysql_host>:<port>/<grafana_database_name>" --net=host grafana/grafana:4.4.3

After the container starts you can check if it works on localhost:3000. Do the same for image version 4.5.0.

Probably related - #810

[unknown321]

Some more details:

In case where client sends collation parameter, it sends an id of 224 (0xE0), which is a correct collation id for charset utf8mb4 (byte 78, packet 6, which is an authentication packet, in 4.5.0-broken.pcap).

MySQL [(grafana)]> show collation where id=224;
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+

In case where client sends charset parameter (4.7.0-fixed.pcap), it sends an id of 33 (0x21), which is a correct collation for utf8:

MySQL [(grafana)]> show collation where id=33;
+-----------------+---------+----+---------+----------+---------+
| Collation       | Charset | Id | Default | Compiled | Sortlen |
+-----------------+---------+----+---------+----------+---------+
| utf8_general_ci | utf8    | 33 | Yes     | Yes      |       1 |
+-----------------+---------+----+---------+----------+---------+

Setting mysql-default_charset to utf8mb4 doesn't help. Creating database grafana with explicitly specified collation (and character set) doesn't help either (CREATE DATABASE grafana CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;)

Collation utf8mb4_unicode_ci is listed in mysql_collations:

MySQL [(none)]> select * from mysql_collations where id in (224,33);
+-----+--------------------+---------+---------+
| Id  | Collation          | Charset | Default |
+-----+--------------------+---------+---------+
| 33  | utf8_general_ci    | utf8    | Yes     |
| 224 | utf8mb4_unicode_ci | utf8mb4 |         |
+-----+--------------------+---------+---------+

[renecannao]

We haven't verified it yet ( @pondix will do that ), but I believe I know where the issue is.
If client and backend do not have the same charset/collation, ProxySQL changes the backend charset using mysql_set_character_set(). When this function is executed:

The connection collation becomes the default collation of the character set

Because the default collation for utf8mb4 is utf8mb4_general_ci and not utf8mb4_unicode_ci, frontend and backend connections will never have the same charset/collation and ProxySQL will enter in a loop trying to change the charset/collation.

[unknown321]

Tested by building a debian 9 package from branch 1.4.4-set_collate - grafana correctly connects to the cluster as it should. Thanks!

[renecannao]

Thank you for verifying! :)