Broadcom API Gateway 10 : MySQL log overfilled with WARNING "sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

Products

• CA API Gateway

Issue/Introduction

Under certain circumstances, Mysql logs (/var/log/mysqld.log) may be over filled with a high number of WARNING entries as the one below:

[Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

The above can cause the log file size to drastically increase and unnecessary log noise.

Environment

Gateway 10 - Mysql 8.x

Cause

MySQL will deprecate sha256_password authentication method in future release and the vendor recommend to use caching_sha2_password instead.

The scenarios where warnings are recorded in the log are :

• A DB user creation is executed using sha256_password authentication method 
• A remote or local connection attempt is done using a sha256_password authentication method 

 

Generally speaking, by default, Gateway 10 Appliance do not use "sha256_password". In fact the default authentication plugin is "mysql_native_password" and all built-in Database's Users are set to use either that or "caching_sha2_password".  

 

Resolution

• Verify no new users were set with the deprecated authentication method, by running the following query:

mysql> select host, user, plugin from mysql.user;

Here an example of the output that shows a "test" user that was created using "sha256_password":

mysql>  select host, user, plugin from mysql.user;
+-------------------------------------------+------------------+-----------------------+
| host                                      | user             | plugin                |
+-------------------------------------------+------------------+-----------------------+
| localhost                                 | test             | sha256_password       |

 

• Isolate DB User(s) and Host(s) that were created with sha256_password
• Change the plugin type from sha256_password to caching_sha2_password by using the following MySQL statement (replace <username>, <remote host or ip> and <password> with the correct entries):

alter user '<username>'@'<remote host or ip>' identified with caching_sha2_password by '<password>'; 

If you desire instead to completely suppress the warning for being recorded in the log, you can do the following:

• Edit /etc/my.cnf and add below line under [mysqld] section:

log_error_suppression_list='MY-013360'

Here an example of how it should like after change:

• Then restart mysql service to allow new settings to take effect.