Fix "Authentication plugin 'caching_sha2_password' cannot be loaded"
If you've installed Perl and MySQL 8, then when you come to install a self-hosted MIDAS room booking system on your server, you may encounter a problem connecting to your MySQL server and an "Authentication plugin 'caching_sha2_password' could not be loaded" error.
This may be accompanied by a "cannot open shared object file: No such file or directory" error relating to a file named "caching_sha2_password.so", or a "the specified module could not be found" error.
What causes this error?
MySQL 8 supports a new authentication method based on improved stronger SH256-based password methods. When you install MySQL 8, this is the new default authentication method (although this can be changed during MySQL server installation).
As MySQL 8 is still relatively new, depending upon your Perl environment, Perl itself (or rather the driver that Perl uses to connect to a MySQL server) may not yet be compatible with the new authentication method in MySQL 8. When this occurs, an "Authentication plugin 'caching_sha2_password' cannot be loaded" error is produced.
How to fix this error?
To resolve this error, and revert your MySQL server back to using "legacy" authentication, you'll need to login to your MySQL server and issue the following SQL command:
ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
(where "yourusername" is the MySQL user account you specified your MIDAS system to use, and "yourpassword" is the corresponding MySQL user account password)
Note: You may need to restart your MySQL server for the changes to take affect.
