MySQL 8 default character set and collation

[cjmencias]

The default character set and collation in MySQL 8 is utf8mb4 and utf8mb4_0900_ai_ci, see https://dev.mysql.com/doc/refman/8.0/en/charset-server.html.

However, based on mysql_collations table, the default collation for the character set utf8mb4 is utf8mb4_general_ci.

SELECT * FROM mysql_collations WHERE `Charset` = 'utf8mb4' ORDER BY `Id`;

+------+------------------------------+---------+---------+
| Id   | Collation                    | Charset | Default |
+------+------------------------------+---------+---------+
| 45   | utf8mb4_general_ci           | utf8mb4 | Yes     |
| 46   | utf8mb4_bin                  | utf8mb4 |         |
| 224  | utf8mb4_unicode_ci           | utf8mb4 |         |
| 225  | utf8mb4_icelandic_ci         | utf8mb4 |         |
| 226  | utf8mb4_latvian_ci           | utf8mb4 |         |
| 227  | utf8mb4_romanian_ci          | utf8mb4 |         |
| 228  | utf8mb4_slovenian_ci         | utf8mb4 |         |
| 229  | utf8mb4_polish_ci            | utf8mb4 |         |
| 230  | utf8mb4_estonian_ci          | utf8mb4 |         |
| 231  | utf8mb4_spanish_ci           | utf8mb4 |         |
| 232  | utf8mb4_swedish_ci           | utf8mb4 |         |
| 233  | utf8mb4_turkish_ci           | utf8mb4 |         |
| 234  | utf8mb4_czech_ci             | utf8mb4 |         |
| 235  | utf8mb4_danish_ci            | utf8mb4 |         |
| 236  | utf8mb4_lithuanian_ci        | utf8mb4 |         |
| 237  | utf8mb4_slovak_ci            | utf8mb4 |         |
| 238  | utf8mb4_spanish2_ci          | utf8mb4 |         |
| 239  | utf8mb4_roman_ci             | utf8mb4 |         |
| 240  | utf8mb4_persian_ci           | utf8mb4 |         |
| 241  | utf8mb4_esperanto_ci         | utf8mb4 |         |
| 242  | utf8mb4_hungarian_ci         | utf8mb4 |         |
| 243  | utf8mb4_sinhala_ci           | utf8mb4 |         |
| 244  | utf8mb4_german2_ci           | utf8mb4 |         |
| 245  | utf8mb4_croatian_mysql561_ci | utf8mb4 |         |
| 246  | utf8mb4_unicode_520_ci       | utf8mb4 |         |
| 247  | utf8mb4_vietnamese_ci        | utf8mb4 |         |
| 255  | utf8mb4_0900_ai_ci           | utf8mb4 |         |
| 256  | utf8mb4_de_pb_0900_ai_ci     | utf8mb4 |         |
| 257  | utf8mb4_is_0900_ai_ci        | utf8mb4 |         |
| 258  | utf8mb4_lv_0900_ai_ci        | utf8mb4 |         |
| 259  | utf8mb4_ro_0900_ai_ci        | utf8mb4 |         |
| 260  | utf8mb4_sl_0900_ai_ci        | utf8mb4 |         |
| 261  | utf8mb4_pl_0900_ai_ci        | utf8mb4 |         |
| 262  | utf8mb4_et_0900_ai_ci        | utf8mb4 |         |
| 263  | utf8mb4_es_0900_ai_ci        | utf8mb4 |         |
| 264  | utf8mb4_sv_0900_ai_ci        | utf8mb4 |         |
| 265  | utf8mb4_tr_0900_ai_ci        | utf8mb4 |         |
| 266  | utf8mb4_cs_0900_ai_ci        | utf8mb4 |         |
| 267  | utf8mb4_da_0900_ai_ci        | utf8mb4 |         |
| 268  | utf8mb4_lt_0900_ai_ci        | utf8mb4 |         |
| 269  | utf8mb4_sk_0900_ai_ci        | utf8mb4 |         |
| 270  | utf8mb4_es_trad_0900_ai_ci   | utf8mb4 |         |
| 271  | utf8mb4_la_0900_ai_ci        | utf8mb4 |         |
| 273  | utf8mb4_eo_0900_ai_ci        | utf8mb4 |         |
| 274  | utf8mb4_hu_0900_ai_ci        | utf8mb4 |         |
| 275  | utf8mb4_hr_0900_ai_ci        | utf8mb4 |         |
| 277  | utf8mb4_vi_0900_ai_ci        | utf8mb4 |         |
| 278  | utf8mb4_0900_as_cs           | utf8mb4 |         |
| 279  | utf8mb4_de_pb__0900_as_cs    | utf8mb4 |         |
| 280  | utf8mb4_is_0900_as_cs        | utf8mb4 |         |
| 281  | utf8mb4_lv_0900_as_cs        | utf8mb4 |         |
| 282  | utf8mb4_ro_0900_as_cs        | utf8mb4 |         |
| 283  | utf8mb4_sl_0900_as_cs        | utf8mb4 |         |
| 284  | utf8mb4_pl_0900_as_cs        | utf8mb4 |         |
| 285  | utf8mb4_et_0900_as_cs        | utf8mb4 |         |
| 286  | utf8mb4_es_0900_as_cs        | utf8mb4 |         |
| 287  | utf8mb4_sv_0900_as_cs        | utf8mb4 |         |
| 288  | utf8mb4_tr_0900_as_cs        | utf8mb4 |         |
| 289  | utf8mb4_cs_0900_as_cs        | utf8mb4 |         |
| 290  | utf8mb4_da_0900_as_cs        | utf8mb4 |         |
| 291  | utf8mb4_lt_0900_as_cs        | utf8mb4 |         |
| 292  | utf8mb4_sk_0900_as_cs        | utf8mb4 |         |
| 293  | utf8mb4_es_trad_0900_as_cs   | utf8mb4 |         |
| 294  | utf8mb4_la_0900_as_cs        | utf8mb4 |         |
| 296  | utf8mb4_eo_0900_as_cs        | utf8mb4 |         |
| 297  | utf8mb4_hu_0900_as_cs        | utf8mb4 |         |
| 298  | utf8mb4_hr_0900_as_cs        | utf8mb4 |         |
| 300  | utf8mb4_vi_0900_as_cs        | utf8mb4 |         |
| 303  | utf8mb4_ja_0900_as_cs        | utf8mb4 |         |
| 304  | utf8mb4_ja_0900_as_cs_ks     | utf8mb4 |         |
| 305  | utf8mb4_0900_as_ci           | utf8mb4 |         |
| 306  | utf8mb4_ru_0900_as_ci        | utf8mb4 |         |
| 307  | utf8mb4_ru_0900_as_cs        | utf8mb4 |         |
| 608  | utf8mb4_croatian_ci          | utf8mb4 |         |
| 609  | utf8mb4_myanmar_ci           | utf8mb4 |         |
| 610  | utf8mb4_thai_520_w2          | utf8mb4 |         |
| 1069 | utf8mb4_general_nopad_ci     | utf8mb4 |         |
| 1070 | utf8mb4_general_nopad_bin    | utf8mb4 |         |
| 1248 | utf8mb4_unicode_nopad_ci     | utf8mb4 |         |
| 1270 | utf8mb4_unicode_520_nopad_ci | utf8mb4 |         |
+------+------------------------------+---------+---------+

I have certain applications which automatically executes SET NAMES 'utf8mb4' when establishing new connections to the proxy server.

As a result, the collation_connection becomes utf8mb4_general_ci instead of utf8mb4_0900_ai_ci.

show session variables like '%collation%';

+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb3_general_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

This causes SQL queries with string comparisons to complain about collation mismatches:

Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci, COERCIBLE) and (utf8mb4_general_ci, COERCIBLE) for operation '=' 0.079 sec

I attempted to configure some global variables, but none of them seemed to work. I believe that when SET NAMES 'utf8mb4' is executed, the default collation used is determined by the settings in the mysql_collations table.

select * from global_variables where variable_name in ('mysql-default_charset', 'mysql-default_collation_connection');

+------------------------------------+--------------------+
| variable_name                      | variable_value     |
+------------------------------------+--------------------+
| mysql-default_charset              | utf8mb4            |
| mysql-default_collation_connection | utf8mb4_0900_ai_ci |
+------------------------------------+--------------------+

I am currently using the latest version of ProxySQL.

[renecannao]

Hi @cjmencias .

Thank you for the report, and thank you for the PR, but unfortunately I don't believe this is the way to proceed. Let me explain.
The default collation for a given character set can change between MySQL versions. In fact, here you are referring to MySQL 8.0 , but actually this means from MySQL 8.0.1 till current release (8.0.0 didn't use utf8mb4_0900_ai_ci).
This collation didn't even exist in MySQL 5.7 , and I am not sure when it was introduced in MariaDB .
Also, you can have a MySQL server 8.0 configured with a default collation that is not utf8mb4_0900_ai_ci .
ProxySQL can have many backend servers, all with different versions, and they can all coexist.

The problem here is that your application issues a SET NAMES 'utf8mb4' without specifying any collation.
If your application connects directly to a MySQL server, this command could have different meaning based on which backend it connects to (which version, and how it is configured).
If the application issue a SET NAMES without specifying a collation, it relies on whatever the backend considers the default collation for that given collation.
In ProxySQL the default is utf8mb4_general_ci : this means that if the client doesn't specify any collation, this is the default collation.
And because we need to be compatible with old versions of MySQL , MariaDB , and any other backend that is not a MySQL server but uses the MySQL Protocol, it doesn't make sense to change the default.

That said, you can "fix" your application creating a query rule that rewrites the SET NAMES command adding the required collation. This should be the way to solve this issue.

A further comment: in a future release we plan to have a "special mode" where, if ProxySQL is only used for ONE Group Replication cluster (when it is expected that all the backends are the same), then ProxySQL can retrieve several defaults from the backend(s) and make them its own defaults.

[renecannao]

you can "fix" your application creating a query rule that rewrites the SET NAMES command adding the required collation

Here I was actually wrong, sorry about that: this is currently not possible. SET NAMES are processed before query rules are processed, and if proxysql is able to parse SET NAMES correctly then the query rules are skipped completely.

At this point the solution is: can your application specify the collation it wants?

[cjmencias]

Hi @renecannao,

Thanks for the review!

The idea of the "special mode" sounds great since it will pick up the underlying backend configurations. I'm looking forward to it.

I have actually been exploring the rewriting of SET NAMES using mysql_query_rule.

Here is my configuration, the regex is a bit aggressive to ensure it matches any SET NAMES statements.

SELECT hits, mysql_query_rules.rule_id, digest, match_digest, match_pattern, 
replace_pattern, re_modifiers, cache_ttl, apply 
FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules 
ORDER BY mysql_query_rules.rule_id;

+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| hits | rule_id | digest | match_digest | match_pattern | replace_pattern                                  | re_modifiers | cache_ttl | apply |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| 0    | 1       | NULL   | NULL         | .*SET NAMES.* | SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' | CASELESS     | NULL      | 1     |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+

Then tested from the client side by executing

SET NAMES 'utf8mb4';

Query log shows that it is not rewritten

{
    "client": "127.0.0.1:41740",
    "digest": "0x77F060C88B214206",
    "duration_us": 0,
    "endtime": "2023-07-25 12:11:54.790502",
    "endtime_timestamp_us": 1690287114790502,
    "event": "COM_QUERY",
    "hostgroup_id": -1,
    "query": "SET NAMES 'utf8mb4'",
    "rows_affected": 0,
    "rows_sent": 0,
    "schemaname": "information_schema",
    "starttime": "2023-07-25 12:11:54.790502",
    "starttime_timestamp_us": 1690287114790502,
    "thread_id": 63,
    "username": "user1"
}

And there's no increase in the number of hits

+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| hits | rule_id | digest | match_digest | match_pattern | replace_pattern                                  | re_modifiers | cache_ttl | apply |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| 0    | 1       | NULL   | NULL         | .*SET NAMES.* | SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' | CASELESS     | NULL      | 1     |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+

However, if I execute

/* some comments */ SET NAMES 'utf8mb4';

The query gets rewritten

{
    "client": "127.0.0.1:37348",
    "digest": "0x3DDFFFFB35C368B8",
    "duration_us": 0,
    "endtime": "2023-07-25 12:14:15.452571",
    "endtime_timestamp_us": 1690287255452571,
    "event": "COM_QUERY",
    "hostgroup_id": -1,
    "query": "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci'",
    "rows_affected": 0,
    "rows_sent": 0,
    "schemaname": "information_schema",
    "starttime": "2023-07-25 12:14:15.452571",
    "starttime_timestamp_us": 1690287255452571,
    "thread_id": 65,
    "username": "user1"
}

And there's an increase in the number of hits

+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| hits | rule_id | digest | match_digest | match_pattern | replace_pattern                                  | re_modifiers | cache_ttl | apply |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+
| 1    | 1       | NULL   | NULL         | .*SET NAMES.* | SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' | CASELESS     | NULL      | 1     |
+------+---------+--------+--------------+---------------+--------------------------------------------------+--------------+-----------+-------+

Are there any hidden rules that prevent it from matching SET NAMES ??

I did try configuring it using a digest value and match_digest, but none of them seem to be working.

[cjmencias]

you can "fix" your application creating a query rule that rewrites the SET NAMES command adding the required collation

Here I was actually wrong, sorry about that: this is currently not possible. SET NAMES are processed before query rules are processed, and if proxysql is able to parse SET NAMES correctly then the query rules are skipped completely.

At this point the solution is: can your application specify the collation it wants?

Hi @renecannao ,

I see. This was actually the behavior I encountered while exploring mysql_query_rules.

Unfortunately, I don't have access to the application that connects to the proxy server; therefore, I cannot configure it to specify the collation name.

[cjmencias]

Hi @renecannao,

I attempted to manually update the mysql_collations table; however, it seems that it's meant for reference purposes only, as ProxySQL reads from the array.

Nevertheless, I managed to resolve my issue by creating a custom build from the source temporarily, considering my backend databases consist of only one MySQL 8 database.

[renecannao]

Hi @cjmencias .

Thank you for the update.
I will re-open this issue because there is something we need to fix from our end: query rules should be able to modify SET NAMES statements.

[cjmencias]

Hi @renecannao , that's great and thanks!

[devicenull]

We just ran into this too - it took me awhile to track down why the default collation was utf8mb4_general_ci, even when the backend default was utf8mb4_0900_ai_ci. Our case is slightly different, in that our app is not doing a SET NAMES statement on connection, instead we're relying on the utf8mb4 charset set in the client handshake.

It's definitely unexpected that:

• client connecting to a mysql 8.0.33 server direct -> charset utf8mb4, collation utf8mb4_0900_ai_ci
• client connecting to proxysql -> charset utf8mb4, collation utf8mb4_general_ci

[renecannao]

Hi @devicenull ,
What you are describing is probably incorrect.
If the client specifies utf8mb4_0900_ai_ci during the handshake (value is 255) , then ProxySQL honors that.
So probably your client is not specifying utf8mb4_0900_ai_ci during the handshake.

[devicenull]

Ah you're right @renecannao - php is indeed requesting 45 during the handshake

[devicenull]

Oh wait - I looked at a PCAP, and proxysql is sending 45 back in the server greeting:

Probably because it's also identifying itself as 5.7? Is there another option I need to set to get it to identify as 8?

My client in this case is PHP, which just sends back whatever charset the server sends it: https://github.com/php/php-src/blob/73c5f36f5b7cfde189d8f72cfee4e3056d45f061/ext/mysqlnd/mysqlnd_commands.c#L614-L627