ProxySQL and MySQL 8.0.2 (la suite)

Recently I wrote a blog post on how to use ProxySQL with 8.0.2 dmr

This blog post was related to the change we implemented in the Group Replication’s monitoring (performance_schema tables) and how sys schema should be changed to modify the view used by ProxySQL according.

However, I used my labs machines that I was using for different purposes too and some changes I made hid a problem that avoid ProxySQL to work with the new defaults in MySQL 8.0.2. Thank you Andy for discovering this.

In fact the new collation default (utf8mb4_0900_ai_ci) is not yet supported by ProxySQL (1.4.1 is the latest recent release supporting MySQL Group Replication).

mysql> show global variables like '%colla%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

You can get the list of supported collations in ProxySQL using this query in ProxySQL’s admin interface:

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 |         |
+-----+-----------------------+---------+---------+
22 rows in set (0.00 sec)

If you leave the collation as it is, ProxySQL won’t see the nodes online (because it cannot connect to MySQL) and after a short time, MySQL will blacklist the connection attempt:

ProxySQL> select hostgroup_id, hostname, status  from runtime_mysql_servers; 
+--------------+--------------+---------+
| hostgroup_id | hostname     | status  |
+--------------+--------------+---------+
| 2            | 192.168.90.2 | SHUNNED |
| 2            | 192.168.90.4 | SHUNNED |
| 2            | 192.168.90.3 | SHUNNED |
+--------------+--------------+---------+
3 rows in set (0.01 sec)

In ProxySQL’s log file you will see similar entries:

2017-08-03 17:14:15 MySQL_Monitor.cpp:1347:monitor_ping(): [ERROR] Server 192.168.90.2:3306 missed 3 heartbeats, shunning it and killing all the connections
2017-08-03 17:14:15 MySQL_Monitor.cpp:1347:monitor_ping(): [ERROR] Server 192.168.90.3:3306 missed 3 heartbeats, shunning it and killing all the connections
2017-08-03 17:14:15 MySQL_Monitor.cpp:1347:monitor_ping(): [ERROR] Server 192.168.90.4:3306 missed 3 heartbeats, shunning it and killing all the connections

You can also check in the table mysql_server_ping_log in ProxySQL’s admin interface, and you will find entries like these:

*************************** 178. row ***************************
            hostname: 192.168.90.2
                port: 3306
       time_start_us: 1501818227091532
ping_success_time_us: 0
          ping_error: Host '192.168.90.2' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
*************************** 179. row ***************************
            hostname: 192.168.90.3
                port: 3306
       time_start_us: 1501818227103737
ping_success_time_us: 0
          ping_error: Can't initialize character set unknown (path: compiled_in)

So you will need to change the default collation, I added the following line in my.cnf (under [mysqld] section) and restarted the node:

collation-server = utf8mb4_general_ci

This fixed the problem !

ProxySQL> select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 2            | 192.168.90.3 | ONLINE |
| 3            | 192.168.90.4 | ONLINE |
| 3            | 192.168.90.2 | ONLINE |
+--------------+--------------+--------+
3 rows in set (0.00 sec)

In conclusion, if you need to use ProxySQL with MySQL 8.0.2 dmr (and not only for Group Replication or InnoDB Cluster), you will need to change the default collation while it is not yet supported. Of course René is already aware of this.

Again thank you René for the amazing work on ProxySQL and to Andy for trying MySQL 8.0.2 dmr, don’t forget, your feedback is important and really appreciated !