mysql-router creates users with deprecated mysql_native_password plugin

[Jake Nabasny]

The mysql-router charm creates users in mysql using the mysql_native_password plugin:

+------------+----------------------------+-----------------------+
| host | user | plugin |
+------------+----------------------------+-----------------------+
| % | mysql_innodb_cluster_1000 | caching_sha2_password |
| % | mysql_innodb_cluster_1001 | caching_sha2_password |
| % | mysql_innodb_cluster_1002 | caching_sha2_password |
| % | mysql_router1_rfuqp0x6yj1o | mysql_native_password |
| % | mysql_router2_vgsc4zrwqw85 | mysql_native_password |
| % | mysql_router3_l9m9jex8izly | mysql_native_password |
| % | mysql_router4_58wsl4qat9tl | mysql_native_password |
| % | mysql_router5_ypprl049r85v | mysql_native_password |
| % | mysql_router6_q8wul6xhkntj | mysql_native_password |
| % | mysql_router7_qyapa6l39dv5 | mysql_native_password |
| % | mysql_router8_g2xu1obmszt1 | mysql_native_password |
| % | mysql_router9_cedq97h9vvxy | mysql_native_password |
| 10.5.0.112 | clusteruser | caching_sha2_password |
| 10.5.0.129 | clusteruser | caching_sha2_password |
| 10.5.0.15 | cinder | caching_sha2_password |
| 10.5.0.15 | mysqlrouteruser | caching_sha2_password |
| 10.5.0.155 | horizon | caching_sha2_password |
| 10.5.0.155 | mysqlrouteruser | caching_sha2_password |
| 10.5.0.247 | clusteruser | caching_sha2_password |
| 10.5.0.40 | keystone | caching_sha2_password |
| 10.5.0.40 | mysqlrouteruser | caching_sha2_password |
| 10.5.1.13 | mysqlrouteruser | caching_sha2_password |
| 10.5.1.13 | vault | caching_sha2_password |
| 10.5.1.133 | heat | caching_sha2_password |
| 10.5.1.133 | mysqlrouteruser | caching_sha2_password |
| 10.5.1.66 | glance | caching_sha2_password |
| 10.5.1.66 | mysqlrouteruser | caching_sha2_password |
| 10.5.2.151 | mysqlrouteruser | caching_sha2_password |
| 10.5.2.151 | nova | caching_sha2_password |
| 10.5.2.172 | mysqlrouteruser | caching_sha2_password |
| 10.5.2.172 | neutron | caching_sha2_password |
| 10.5.3.255 | mysqlrouteruser | caching_sha2_password |
| 10.5.3.255 | nova | caching_sha2_password |
| 10.5.3.255 | placement | caching_sha2_password |
| localhost | clusteruser | caching_sha2_password |
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+------------+----------------------------+-----------------------+

This plugin is deprecated in favor of caching_sha2_password. Every connection attempt from every router creates a warning in the log:

2023-10-20T20:36:41.187100Z 30572 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

Within minutes this creates tens of thousands of log lines and leads to an enormous log file if not pruned or configured to ignore these warnings.

This charm should force the routers to register with caching_sha2_password instead.

My test setup:
Ubuntu Focal 20.04
mysql-innodb-cluster charm: 8.0.34 (8.0/stable, rev. 106)
*-mysql-router charms: 8.0.34 (8.0/stable, rev. 111)

[Felipe Reyes (freyes)]

Upstream doc on the subject: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

[Bartosz Woronicz (mastier1)]

This is quite severly flooding the logs
023-11-17T12:55:39.299648Z 114 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:39.462893Z 117 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:39.483534Z 116 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:39.504405Z 119 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:39.605830Z 120 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:39.621610Z 121 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.024119Z 124 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.135943Z 126 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.324572Z 127 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.488698Z 128 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.516155Z 129 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.541545Z 130 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:40.652853Z 131 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:41.035176Z 132 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-17T12:55:41.05928...

[João Pedro Seara (jpseara)]

Here's how to at least stop the flooding of logs:

juju ssh mysql-innodb-cluster/<unit>

sudo su
echo "[mysqld]" > /etc/mysql/mysql.conf.d/mysqld_lp2040018.cnf
echo "log_error_suppression_list='MY-013360'" >> /etc/mysql/mysql.conf.d/mysqld_lp2040018.cnf
chmod 640 /etc/mysql/mysql.conf.d/mysqld_lp2040018.cnf
chown root:mysql /etc/mysql/mysql.conf.d/mysqld_lp2040018.cnf
systemctl restart mysql
exit
#mysql -uroot -p<mysql_password> -e "SHOW VARIABLES" | grep supp
exit