MySQL Bugs: #115225: mysql_native_password: internal users not always migrated on upgrade

Bug #115225	mysql_native_password: internal users not always migrated on upgrade
Submitted:	5 Jun 2024 9:52	Modified:	30 Apr 9:22
Reporter:	Bernd Wahlen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	8.4 9.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	caching_sha2_password, mysql_native_password, mysql.session, mysql.sys, plugin dir

Description:
mysql_native_password authentication is turned off by default on mysql 8.4.
i upgraded some installations (from 8.3 to 8.4) and not always all of the internal users are changed.

select user,host,plugin from mysql.user;
...
| mysql.infoschema | localhost      | caching_sha2_password |
| mysql.session    | localhost      | mysql_native_password |
| mysql.sys        | localhost      | mysql_native_password |

this does not happen on fresh setup.

How to repeat:
now i upgraded 5 setups with 2 of them left internal users with old mysql_native_password plugin.

1. source/replica setup on ubuntu 22.04 => all users migrated
2. source/replica setup on ubuntu 24.04 => all users migrated
3. source/replica setup on ubuntu 22.04 => not all users migrated
4. standalone docker => not all users not migrated
5. standalone on ubuntu 22.04 => users migrated

so i have no idea when it works and when not.
maybe depending on how old the installations is.

Hi Mr. Wahlen,

Thank you for your bug report.

We have created users on 8.3.0 with native password and upgraded to 8.4.0 and noticed the same behaviour.

This is now a verified bug for 8.4 only.

Thank you for your contribution.

any workaround to recreate these users with the same password?

8.4 causes warning:
024-07-01T07:15:16.177194Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'mysql.session'@'localhost' is not loaded. Nobody can currently login using this account.
2024-07-01T07:15:16.177241Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'mysql.sys'@'localhost' is not loaded. Nobody can currently login using this account.

and with 9.0 the plugin seems to be removed and cannot be activated via settings.

Thank you, Mr. Wahlen ..........

still not solved in 9.0.1

mysql 9.3.0 no longer affected