Unsupported collation utf8mb4_unicode_520_ci causes monitoring to kill connections

[stefanproell]

Hello,

I am using ProxySQL to rewrite queries to my database and it was working very well until I started to test the setup with larger queries. In fact, I use mysqldump to export large tables and I use multiple mysqldump calls subsequently and suddenly saw non-reproducible aborts of the data export when using ProxySQL. The process would just stop after a few seconds.

The problem seemed to happen randomly at first. The only thing written in the log was missed heart beats:

2018-04-04 07:45:03 MySQL_Monitor.cpp:1408:monitor_ping(): [ERROR] Server 10.20.0.11:6034 missed 3 heartbeats, shunning it and killing all the connections

After setting up the monitoring, I noticed that there still was an error:

SELECT * FROM mysql_server_ping_log;

Shows

Can't initialize character set unknown (path: compiled_in)

Then I investigated a little further and found this blog post, where I learned that the character set or collation that my server uses obviously is not supported by ProxySQL:

These are the settings of my MySQL 5.6 instance:

character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    binary
character_set_results       utf8
character_set_server        utf8mb4
character_set_system        utf8
character_sets_dir          /usr/share/mysql/charsets/

The collation I use:

collation_connection    utf8mb4_unicode_520_ci
collation_database      utf8mb4_unicode_520_ci
collation_server        utf8mb4_unicode_520_ci

This seems to cause the issue, because it is not in the list of supported collations:

proxysql > select * from mysql_collations where charset='utf8mb4';
+-----+-----------------------+---------+---------+
| 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 |         |
| 255 | utf8mb4_0900_ai_ci    | utf8mb4 |         |
+-----+-----------------------+---------+---------+
23 rows in set (0.00 sec)

Is there any chance that the collation utf8mb4_unicode_520_ci will be supported by ProxySQL in the near future? Can I make the monitoring use a compatible collation like utf8mb4_general_ci instead? Or any strategy around the issue? Just manually adding the collation to the table does not have the desired effect.

INSERT INTO mysql_collations VALUES (256,'utf8mb4_unicode_520_ci','utf8mb4','Yes');

For now I just disable monitoring and it works without issues:

proxysql> update global_variables set variable_value = 'false' where variable_name = 'mysql-monitor_enabled';
proxysql> LOAD MYSQL VARIABLES TO RUNTIME;

Thanks for your great work! Please let me know if I can provide further details.

[renecannao]

Good analysis! ProxySQL 1.4 , based on MariaDB Client Library 2.3.1 , does not support utf8mb4_unicode_520_ci. ProxySQL 2.0 , based on MariaDB Client Library 3.0.2 , already supports utf8mb4_unicode_520_ci. This will be fixed in 1.4.8 .

Thank you for the feedback!

[stefanproell]

Great, looking forward to 1.4.8 and will be using my little work around for now. Have a great day!

[renecannao]

Opening it again until it get fixed. Thanks

[pondix]

Closing as this is fixed in 1.4.8