Upgrading from tutor 15 to tutor 18 breaks the MySQL connection due to the mysql_native_password authentication plugin not being found

[Danyal-Faheem]

Bug description

When you upgrade from tutor 15.x to tutor 18.x, the LMS cannot connect to the MySQL server during the launch step with the error:

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

The same error occurs if you try to enter the SQLshell using the following methods:

• do sqlshell command
• Bashing into the MySQL container
• Running ./manage.py lms dbshell in the LMS container

How to reproduce

1. Install tutor 15.3.9.
2. Run tutor local launch to launch a fresh local instance and verify that the LMS and Studio are working fine.
3. Install tutor 16.x.
4. Run tutor config save
5. Run tutor local upgrade --from=olive
6. Run tutor local launch
7. Follow step 3-6 for tutor 17 and tutor 18 replacing the version and release name.

And then the init task for tutor 18 crashes at the MySQL step with the above mentioned error.

Environment
OS: MacOS Sonoma 14.5
Tutor versions 15.3.9 and 18.1.0

Additional context

This issue was initially raised in https://discuss.openedx.org/t/error-1524-hy000-plugin-mysql-native-password-is-not-loaded/13257/9

The default authentication plugin in MySQL 8.0 and onwards has been changed to caching_sha2_password whereas it was mysql_native_password in MySQL 5.7 and earlier. If a CREATE USER query did not specify the authentication plugin explicitly, the default authentication plugin was used.

According to the MySQL docs:

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.

This means that the users that were created by Tutor in version 15 or earlier used the mysql_native_password authentication and even after upgrading to MySQL 8.0 in Palm and 8.4 in Redwood, that was not changed.

In Tutor 18.0.0, MySQL was upgraded to v8.4.0 as per #1071. In MySQL 8.4.0, mysql_native_password is disabled by default as per the docs. This means that if the DB contains users using the mysql_native_password plugin, it will refuse to connect.

A possible solution is to start the MySQL server with the parameters --mysql-native-password=ON. But this might be unsafe in the long run.

[DawoudSheraz]

Thanks for leading the investigations on this @Danyal-Faheem , much appreciated.