Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand MySQL Authentication Failed problem analysis and solutions, many people may not know, in order to let everyone know more, Xiaobian summarized the following content for everyone, I hope you can harvest according to this article.

problem description

Authentication to host 'xxxx' for user 'yy' using method 'mysql_native_password' failed with message: Reading from the stream has failed.

Performance characteristics:

This problem occurs only with Connector/NET and not with the JDBC driver.

Multiple application servers, only one reported this error. So you can eliminate server-side problems.

The questions are very random. Restart the server/IIS to resolve the problem temporarily.

There are some scenarios where the application server CPU is not very high, and this error will occasionally be thrown.

The client is a Windows machine, the driver is MySQL Connector ADO. NET Driver for MySQL (Connector/NET), and the version used is 6.9.9, which is a relatively new version.

problem analysis

We capture packets on the application server side and on the database side. The bags caught on both sides match. Network packet loss can be eliminated. Here are the bags caught and the time points:

From the above network packet interaction, the first three packets are TCP's three-way handshake protocol. The problem with the sixth packet is that the database server sends a Finish packet to the application server to terminate the database connection. The database sends a Finish packet, which is sent due to a database-side discovery connection timeout. This is controlled by the server-side Connect_timeout variable. The reason is that the application side does not send network packets to the database server side for more than 10 seconds. In terms of packet interaction, the time interval between the fifth and sixth packets is exactly 10 seconds.

Compare the normal database connection with the abnormal database connection above. After the application server sends the fifth packet to the database side, it should immediately send the following network packet to the database side. This package mainly sends account, driver version, operating system information to the database server. [Below is a screenshot of some normal network packets]. In the exception error scenario, the client delays sending the packet. Sent in Frame 8. At this point, the connection has been finished. In Frame 9, the database sends a Reset packet to the application server, completely interrupting the connection.

We now analyze why the client sends account, driver version, and operating system information to the database so slowly. This part of the code is in Connector/NET's MySQLAuthenticationPlugin.cs file. We modify this part of the code to perform time burials to further locate the problem. Below is the trace information printed out according to the time buried point.

From the trace, there is a delay of about 30 seconds, all of which is concentrated on obtaining the properties of Mysqldefs:: OSDetails. This code is as follows:

This code uses WMI queries to obtain Caption information. This is the version of the operating system. Because it is a WMI call, there are many dependencies and are related to the state of the operating system.

question verification

To verify if WMI's occasional and frequent delays are causing communication exceptions on some machines in the production environment, we extracted this code. Here's a short reproduction code:

On the application server in question, we ran the above code and indeed found that WMI queries timed out. The following log messages are WMI message queries that we caught for more than 30 seconds. This fully confirms that the operation caused the MySQL authentication failed error.

2017-11-21 17:19:30.208， 33638 2017-11-21 17:20:09.193， 33199 2017-11-21 17:20:53.086， 33201 2017-11-21 17:27:05.114， 32976 2017-11-21 17:28:19.178， 33635 2017-11-21 17:30:07.130， 65977 2017-11-21 17:30:49.051， 40478 2017-11-21 17:31:15.126， 26072 2017-11-21 17:38:16.048， 66671 2017-11-21 17:38:49.204， 33152 2017-11-21 17:39:53.161， 33828 2017-11-21 17:40:38.121， 33549 2017-11-21 17:47:09.179， 33775 2017-11-21 17:47:57.174， 33164

solutions

WMI queries are slow and can be caused by a variety of reasons. If the operating system CPU is high, or the query itself has deadlocks. This issue requires further analysis. But looking at the code, we know to do this WMI query just to get OS information. This information can be cached. It is not necessary to perform WMI queries every time you connect.

The root cause of this error is determined here to be that MySQL's C#connector takes too long to obtain operating system information, resulting in a connection timeout that triggers the server. Commenting out this section may result in long operation, further verification on the problem machine, no more timeout errors.

Because application-side system information is relatively static, Connector/Net can retrieve this information through environment variables, bypassing WMI queries. This avoids query timeouts every time a connection is made and improves Connector/Net efficiency.

After reading the above, do you have any further understanding of how to understand MySQL Authentication Failed problem analysis and resolution? If you still want to know more knowledge or related content, please pay attention to 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.