Incorrect collation returned for VARCHAR columns

[abonander]

In SQLx (database client for Rust) we got the following bug report related to Planetscale: launchbadge/sqlx#1690

If you follow the discussion, it appears that collations are getting mixed up for VARCHAR columns. The user's schema declares utf8mb4_bin as the collation and SQLx sets the default collation on the connection to be utf8mb4_unicode_ci, but the returned ColumnDefinitions for a query against the table report the collation as utf8m4_0900_ai_ci.

SQLx's support for collations isn't great and the logic we use to differentiate character strings from binary strings could be better, but it also seems like Planetscale is ignoring both the requested collation on the connection as well as the nominal collation on the source column.

[mattlord]

Hi @abonander !

Thank you for letting us know about this! We are always striving to improve PlanetScale's support for various language drivers/connectors and frameworks. We appreciate you creating this issue, so please keep that in mind as I attempt to walk through the details for everyone. 🙂

The collation defined for the table and/or columns is not ignored, as shown in the linked ticket (unless I missed something?):

• MySQL VARCHAR is mapped to Vec<u8> instead of String launchbadge/sqlx#1690 (comment)
• MySQL VARCHAR is mapped to Vec<u8> instead of String launchbadge/sqlx#1690 (comment)

But if SQLx gets around supporting all MySQL compatible collations by attempting to set the collation_connection variable on the session then we can indeed encounter problems because that session variable is indeed ignored in PlanetScale today. So the combination of that and SQLx not yet having support for the newer MySQL 8 collations means that the result set uses a collation SQLx does not understand and thus treats it as a byte sequence (as far as I can tell).

FWIW MySQL 8.0 (which PlanetScale uses) has been GA for 4 years now and 5.7 is slated for EOL next year, and more importantly that old utf8mb4_unicode_ci "unicode" collation is ironically not a valid utf-8 (1-4 byte characters) compliant collation as it only supports the BMP (1-3 bytes) and thus e.g. 👍 == 👎 if sorting and comparisons need to be done as part of forming the final result set in the session (because those characters are in the supplemental planes and anything beyond the BMP is treated as equal). To demonstrate what I mean about the lack of correctness of that old collation (not to mention it's quite slow compared to the newer unicode 9.0 collations that use no padding and have other perf improvements as well):

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@session.collation_connection;
+--------------------------------+
| @@session.collation_connection |
+--------------------------------+
| utf8mb4_0900_ai_ci             |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select '👍' = '👎' from dual;
+-----------+
| '?' = '?' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> set @@session.collation_connection="utf8mb4_unicode_ci";
Query OK, 0 rows affected (0.01 sec)

mysql> select '👍' = '👎' from dual;
+-----------+
| '?' = '?' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

It's important to note that collations are used for comparisons and sorting (character set determines the bytes stored whereas the collation determines what logical value is equal to or greater/less than another following linguistic rules rather than using byte comparisons), so they are crucial, and that old collation is not a good default — at least the older but not-so-old utf8mb4_unicode_520_ci collation is a valid utf-8 (unicode 5.2) collation and would make a better default for older MySQL, and MariaDB, releases:

mysql> set @@session.collation_connection="utf8mb4_unicode_520_ci";
Query OK, 0 rows affected (0.01 sec)

mysql> select '👍' = '👎' from dual;
+-----------+
| '?' = '?' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

BUT, setting all of that aside this is a valid report (don't want to imply otherwise) and this is actually due to underlying Vitess behavior. We're looking into that on the Vitess OSS project side and kicked off a discussion on that. If the collation_connection was not ignored then the result set would be using the collation SQLx does understand, utf8mb4_unicode_ci, and things would work as expected, at least for the Rust native type conversions.

Thank you for the issue! Even if SQLx did support the newer MySQL collations this is likely to cause issues with other frameworks and we need to think about how best to address this.

[vmg]

PlanetScale follows Vitess' behavior for connection collations, which is documented in the official Vitess docs.

Quoting from the docs:

The default connection collation and charset for a Vitess cluster is configured in your VTTablet instances via the -db_collation and -db_charset flags. These flags modify the behavior of the connections that the tablet creates, not the underlying MySQL instance: it defines the collation that VTTablet uses when opening connections to MySQL, in the same way that @collation_connection works in a normal MySQL instance.

The @collation_connection of a VTTablet is automatically propagated to all the VTGates that connect to it, and hence to all the MySQL clients connected to the VTGates. It is a configuration error to deploy several VTTablets in the same Vitess cluster with different connection collations: it will cause warning messages in the VTGates and lead to inconsistent behaviors.

The @collation_connection of a Vitess cluster is constant: it cannot be changed at runtime via SQL (e.g. by issuing a SET @collation_connection = utf8mb4 statement). VTGates will reject such queries.

This may appear inconvenient, but here's a few points I'd like to highlight:

• Vitess (and hence PlanetScale) is not out of spec here: the requested collation when opening a connection to a MySQL server is just that, a request. The server will negotiate on that request and decide which collation to actually use in the connection. Vitess by design ignores this request and defaults the collation to the default collation for the cluster. A properly implemented MySQL driver should not assume that the requested collation is the one that the connection will use: it needs to parse the answer to the handshake, which contains the collation ID that the server decided to use.

• Vitess (and hence PlanetScale) cannot allow the users to choose a collation for their connection, because our database is a hyper-scalar system. Queries performed on a PlanetScale database are sharded and will be served by N different MySQL instances. In order for this sharding to be efficient, we must maintain connection pools to all MySQL instances, and these pools have a default connection collation. We cannot support an arbitrary collation from the client because such queries couldn't be served from any of our pools.

• Generally speaking, the default collation for connections we provide, utf8mb4_0900_ai_ci, is a very sensible default, and changing that (particularly to older collations) will lead to unexpected behaviors, like @mattlord has pointed out on his reply.

• Having said that, PlanetScale does not provide a configuration option to choose the default collation for all the connections in your database right now. We understand that customers have constraints, particularly those porting existing legacy applications to our platform, so we're definitely adding this a feature request and we'll try to get to it ASAP.

Let me know if you have any other questions about collations. Cheers!