mysql instances logging excessive error messages

[Matt Verran]

While investigating the issue with mysql instances going offline regularly the error.log is constantly reporting the auth plugin is deprecated.

For example:

$ kubectl exec -n openstack -c mysql octavia-mysql-0 -- tail -f /var/log/mysql/error.log
2024-01-30T11:13:03.932043Z 202 [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'
2024-01-30T11:13:08.976306Z 203 [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'
2024-01-30T11:13:14.020689Z 204 [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'
2024-01-30T11:13:19.066841Z 205 [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'
2024-01-30T11:13:24.105660Z 206 [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'
2024-01-30T11:13:29.144597Z 207 [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'
2024-01-30T11:13:34.180207Z 208 [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'
2024-01-30T11:13:39.212075Z 209 [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'

Looking into the container directly it appears the mysql-router password is causing this.

mysql> select user,plugin from user;
+---------------------------------+-----------------------+
| user | plugin |
+---------------------------------+-----------------------+
| backups | caching_sha2_password |
| clusteradmin | caching_sha2_password |
| monitoring | caching_sha2_password |
| mysql_innodb_cluster_1373734039 | caching_sha2_password |
| mysql_innodb_cs_51e18497 | caching_sha2_password |
| mysql_router8_25azerrdq2s3 | mysql_native_password |
| relation-186 | caching_sha2_password |
| relation-186-190 | caching_sha2_password |
| root | caching_sha2_password |
| serverconfig | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | caching_sha2_password |
+---------------------------------+-----------------------+

[Matt Verran (mv-2112)]

This looks to be one to raise internally inside Canonical. Fix likely around https://github.com/canonical/mysql-router-k8s-operator/blob/fad69b1423582ebcaf579a5b2c0be4979283838a/src/mysql_shell.py#L107 change the user creation to include IDENTIFIED WITH caching_sha2_password BY 'auth_string'... or to set caching_sha2_password as the default in the mysql container.

[Jake Nabasny (slapcat)]

The same issue has been reported here: https://bugs.launchpad.net/charm-mysql-router/+bug/2040018

Workaround in comment #4 until a patch is made.