Stop log?

[Scott Galambos]

I keep getting this in my /var/log/mysql.log
"[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'"

how do I stop it? I'm running MysQL 8.0.34.

I do see this on my new installation of cPanel:
mysql> use mysql
mysql> select plugin from user;
+-----------------------+
| plugin |
+-----------------------+
| mysql_native_password |
| mysql_native_password |
| caching_sha2_password |
| caching_sha2_password |
| caching_sha2_password |
| mysql_native_password |
| mysql_native_password |
| mysql_native_password |
| mysql_native_password |
| mysql_native_password |
+-----------------------+
10 rows in set (0.00 sec)

is that normal - mysql_native_password?

 

[cPRex]

Hey there! The best way to find out why this is happening is to follow this guide:

MySQL: who’s filling my error log?

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one: 2020-07-24T06:54:00.877128Z 46 [W…

lefred.be

This will let you see who is trying to log in, which triggers that error.

 

[Scott Galambos]

Hey there! The best way to find out why this is happening is to follow this guide:

MySQL: who’s filling my error log?

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one: 2020-07-24T06:54:00.877128Z 46 [W…

lefred.be

This will let you see who is trying to log in, which triggers that error.

this didn't help. I think its because my problem is different. its not logging in at all to log anything with that plugin it talks about.

I have this in my /etc/my.cnf:
[mysqld]
default-authentication-plugin=mysql_native_password

that might be wrong. what should it be? cPanel installation did that I think.

 

[cPRex]

That is the default that we include on all servers and the issue is actually with PHP. PHP's mysqli connector doesn't have support for the more modern sha2 tool, so if you change it, you'll break your PHP sites.

For the time being, I would just ignore these entries if they aren't part of malicious login attempts.

More details here: MySQL :: Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations

 

[vacancy]

Yes, in the latest version of mysql 8, hundreds of thousands of these log records started to occur, I deleted the default-authentication-plugin= record or edited it as caching_sha2_password, but nothing changed, mysql has created 28 gb of logs in 3 days, it does not recognize the settings we made on my.cnf.

An urgent fix is needed.

 

[Scott Galambos]

Yes, in the latest version of mysql 8, hundreds of thousands of these log records started to occur, I deleted the default-authentication-plugin= record or edited it as caching_sha2_password, but nothing changed, mysql has created 28 gb of logs in 3 days, it does not recognize the settings we made on my.cnf.

An urgent fix is needed.

so its not just me. this would be brutal on larger servers.

 

[cPRex]

I did a bit more research on this. The earlier MySQL development link I posted was from 2018, and is no longer accurate. mysqli does support caching_sha2_password as of PHP 7.2.4:

PHP: Requirements - Manual

www.php.net

so you can update the passwords to use the more modern hash requirements.

However, this would mean editing every MySQL password on the server. Here is a specific writeup from MySQL with more details (taken from a section of MySQL :: MySQL 8.0.4 : New Default Authentication Plugin : caching_sha2_password)

What if I upgraded my database to MySQL 8.0.4?

User accounts already created in existing instances are not altered as a part of an upgrade. So, all existing user accounts continue to use the same authentication plugin that they used in e.g. MySQL 5.7. However, if you create a new user without changing ––default-authentication-plugin explicitly, caching_sha2_password is used as authentication mechanism for such users.

Support for caching_sha2_password was added in MySQL 8.0.3. Older versions of libmysqlclient do not support this plugin. So, although client tools that use libmysqlclient older than one available with MySQL 8.0.3 can connect to MySQL 8.0.4 server using users that use other authentication plugins such as mysql_native_password or sha256_password, such client cannot connect to MySQL 8.0.4 server using users which require caching_sha2_password support. For an upgraded database, it means connecting using an existing user account should not face any issues.

Further, it is always possible to change ––default-authentication-plugin to a non-default value of your choice (At present, options other than caching_sha2_password are: mysql_native_password and sha256_password). For example, if ––default-authentication-plugin is set to mysql_native_password, the behavior of the MySQL 8.0.4 server is similar to MySQL 8.0.3 and prior. Correspondingly, libmysqlclient supports the MYSQL_DEFAULT_AUTH options for mysql_options() C API function. (For libmysqlclient based client tools available in MySQL packages, the ––default-auth command-line options achieves the same purpose.) The client-side option is a performance tool and saves an extra round-trip when used in tandem with ––default-authentication-plugin option on server side.

However, we recommend use of caching_sha2_password because of the enhanced security that it offers. We also recommend updating libmysqlclient to MySQL 8.0.4 or later so that new default authentication plugin is supported by clients that use it.

The log entry itself is being added by MySQL, so I don't have a way to suppress that. This is very remiscent of the MySQL 4 -> 5 changes with the "old_passwords" so removing the "default-authentication-plugin=mysql_native_password" from /etc/my.cnf and manually changing the user's passwords is the only way to completely resolve the issue.

I'm sorry I don't have better news or a more automated solution.

 

[cPRex]

One option may be to change the logging verbosity. Setting log_error_verbosity to 1 will make the system only log errors, so you don't get the deprecation warning.

 

[Scott Galambos]

One option may be to change the logging verbosity. Setting log_error_verbosity to 1 will make the system only log errors, so you don't get the deprecation warning.

this works well.

 

[cPRex]

I'm glad that's working well for you. It would still be a good idea to start making the transition to the more modern password scheme soon so you're ready for future updates.

 

[Scott Galambos]

I'm glad that's working well for you. It would still be a good idea to start making the transition to the more modern password scheme soon so you're ready for future updates.

right, and I do this by simple re-setting each users password to be caching_sha2_password instead of mysql_native_password, correct? And if so will it break cPanel at all? I mean it's cPanel that sets these users to mysql_native_password when I add them.

 

[cPRex]

cPanel has no control over the MySQL password, and adjusting that won't break cPanel at all. The only issue is if you actually change the password to something different, you would need to update it in any connection strings in your software, such as the WordPress configuration.

 

[totalchoice]

Alternatively, you can add the following to /etc/my.cnf without affecting the entire verbosity of your log.

log_error_suppression_list=MY-013360