Shulou(Shulou.com)06/01 Report--

Unexpected server response\ u sha2 authentication: 109or (HY000/1045) when performing the cache, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

Mysqli::real_connect (): Unexpected server response while doing caching_sha2 auth: 109or Message: mysqli::real_connect (): (HY000/1045): Access denied for user 'root'@'114.254.211.9' (using password: YES).

Prerequisite: make sure that the user and password support remote connections, and the encryption rule of the password has been modified to the password supported by the password authentication plug-in mysql_native_password

Error mysqli::real_connect (): Unexpected server response while doing caching_sha2 auth: 109. source 1:mysql_native_password can be connected locally, but not online.

Analysis: after confirming that the version is consistent, it is determined that the mysql configuration problem is solved: [mysqld] default_authentication_plugin = mysql_native_password restart database result: successful

Error mysqli::real_connect (): Unexpected server response while doing caching_sha2 auth: 109. the source 2:mysql_native_password can be connected online, but not locally after being updated from php 7.3.x to php 7.4.x.

Analysis: before php 7.4.x, mysqli extension connection defaults to mysql_native_password password authentication plug-in, and after php 7.4.x, mysqli extension connection defaults to caching_sha2_password password authentication plug-in. So when php 7.4 does not default to mysql_native_password in the mysql configuration, the mysqli extension will use the caching_sha2_password password authentication plug-in to connect, so an error has been reported. Solution: 1. Reinstall the php environment to version 7. 4. 0 and below [not recommended] 2. Solve the problem according to the above source: [mysqld] default_authentication_plugin = mysql_native_password restart the database

Error Message: mysqli::real_connect (): (HY000/1045): Access denied for user 'root'@'114.254.211.9' (using password: YES) Source: in php 7.4.x environment, the default password verification plug-in for the database created by docker is caching_sha2_password, and can be connected; after that, the same password verification plug-in is caching_sha2_password, which cannot be connected after using navicate to change the password.

Analysis-1: the mysqli extension of php 7.4.x supports the new caching_sha2_password and determines the caching_sha2_password version of the password authentication plug-in used by navicat to solve the problem: use the password authentication plug-in caching_sha2_password in the latest client of mysql to modify the password ALTER USER 'root'@'%' IDENTIFIED BY' ffc' PASSWORD EXPIRE NEVER;ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY' ffc';FLUSH PRIVILEGES Result: failed, still reported error: Message: mysqli::real_connect (): (HY000/1045): Access denied for user 'root'@'114.254.211.9' (using password: YES). Conclusion: it has nothing to do with the password authentication plug-in of navicate-2: it is related to the order in which the operation is performed. Because the current user is changing the password authentication method of the current user, so that this connection is no longer available, will the change take effect? Solution: in the navicat or mysql client, after the current user logs in to connect, change the current user's password through the password authentication plug-in caching_sha2_password, and then successfully reconnect with the current user's user name and new password, and successfully connect normally in the mysqli extension of php 7.4.x. Think about it: if you are not changing the current user's password, do you need to disconnect and reconnect? Answer: yes, and you need to log back in to the mysql server with the modified user name and new password before you can connect in the mysqli extension of php 7.4.x.

After solving the problem, the password verification plug-in caching_sha2_password is a little vague, and sure enough, I found the corresponding explanation on the official website.

Cache clearing operations affect the authentication requirements for subsequent client connections. For each user account, the first client connection for the user after any of the following operations must use a secure connection (made using TCP using TLS credentials, a Unix socket file, or shared memory) or RSA key pair-based password exchange:After account creation.After a password change for the account.After RENAME USER for the account.After FLUSH PRIVILEGES.FLUSH PRIVILEGES clears the entire cache and affects all accounts that use the caching_sha2_password plugin. The other operations clear specific cache entries and affect only accounts that are part of the operation.Once the user authenticates successfully, the account is entered into the cache and subsequent connections do not require a secure connection or the RSA key pair, until another cache clearing event occurs that affects the account. When the cache can be used, the server uses a challenge-response mechanism that does not use cleartext password transmission and does not require a secure connection.

It probably means:

The cache purge operation affects the authentication requirements for subsequent client connections. For each user account, the user's first client connection must use a secure connection (secure connection using TLS credentials, Unix socket files, or TCP in shared memory) or password exchange based on RSA key pairs after any of the following actions are performed: after the account is created. After changing the account password. After RENAME USER for the account. After FLUSH PRIVILEGES. FLUSH PRIVILEGES clears the entire cache and affects all accounts that use the caching_sha2_password plug-in. Other actions clear specific cache entries and affect only the accounts that belong to that operation.

The default password authentication plug-in in the mysqli extension of php 7.4.x is: caching_sha2_password

The password authentication plug-in is caching_sha2_password. Users who change their passwords must re-use the client to connect successfully before they can connect in the mysqli extension of php 7.4.x. The password authentication plug-in is: mysql_native_password users do not need to reconnect successfully through the client to connect in the mysqli extension of php 7.4.x

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.