How to Fix the 'mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]' Error?

Resolving "mysqli_connect: authentication method unknown to the client [caching_sha2_password]" Error for php mysqli_connect

When establishing a connection to a MySQL database using php mysqli_connect, it's possible to encounter the error "error connecting to database" with a specific message "mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]". This typically occurs due to the authentication method configured on the MySQL server being different from the client's expectations.

In this case, the PHP script uses the caching_sha2_password authentication method, which requires matching credentials defined in the MySQL user table. However, it seems that the user1 account does not have a corresponding password hash for caching_sha2_password.

To resolve this issue, you have a couple of options:

1. Use mysql_native_password Authentication: Change the default_authentication_plugin setting in the MySQL Server ini file to mysql_native_password, which is compatible with user1's password hash. This will allow user1 to log in successfully.

2. Create a caching_sha2_password Password Hash for user1: Issue the following SQL command to create a caching_sha2_password hash for user1:

   ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

   Replace 'new_password' with a secure password of your choice. This will update user1's password hash to use caching_sha2_password, allowing it to authenticate using this method.

3. Force caching_sha2_password for Existing Hashes: If you cannot create a new password hash due to account restrictions, you can force the use of caching_sha2_password for existing hashes. Issue the following SQL command:

   SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('existing_password');

   Replace 'existing_password' with the current password for user1. This will update the hash to use caching_sha2_password without changing the password itself.

Once you have performed one of these procedures, you should be able to successfully establish a connection to the database using caching_sha2_password authentication. Remember to review the MySQL documentation for additional details and options for password management.

The above is the detailed content of How to Fix the 'mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]' Error?. For more information, please follow other related articles on the PHP Chinese website!