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

This article mainly shows you the "MySQL connection thrown Authentication Failed error example analysis", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn the "MySQL connection thrown Authentication Failed error example analysis" this article.

[problem description]

On the application side, I occasionally see the following error:

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

Performance characteristics:

1. This problem occurs only with Connector/NET, and there is no similar problem with JDBC driver.

two。 There are multiple application servers, only one of which reports this error, so the problem on the server side can be eliminated.

3. The problem is very random and can be temporarily solved by restarting the server / IIS.

4. There are some scenarios where the application server CPU is not very high and occasionally throws this error.

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.

Let's take a look at the detailed analysis and solutions.

[problem Analysis]

We grab packets on the application server side and the database side. The bags caught on both sides are the same. You can troubleshoot network problems. The following is the package caught and the time point:

Numbering absolute time relative time (seconds) Source destination Network packet content    1      12:58:47      9.07      Application Server      Database Server      .s.      2      12:58:47      9.07      Database Server      Application Server     ... A.. S.      3      12:58:47      9:07      Application Server      Database Server     ... A... .      4      12:58:47      9:07      Database Server      Application Server      … AP...      5      12:58:47      9.27      Application Server      Database Server     ... A... .      6      12:58:57      19.12      Database Server      Application Server     ... A... F      7      12:58:57      19.12      Application Server      Database Server      … A... .      8      12:59:10      32.00      Application Server      Database Server      … AP...      9      12:59:10      32.00      Database Server      Application Server      … .. r..   

Judging from the interaction of the above network packets, the first three packets are TCP's three-way handshake protocol. The problem is the sixth package, where the database server sends a Finish package to the application server to terminate the database connection. The database sends Finish packets because the database side finds that the connection times out. This is controlled by the server-side Connect_timeout variable. The reason is that the application side did not send the network packet to the database server side for more than 10 seconds. From the perspective of network packet interaction, the interval between the fifth packet and the sixth packet 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 send the following network packet to the database side immediately. This package mainly sends the account, driver version, operating system information and so on the database server side. [below is a screenshot of some normal network packets]. In the scenario where an exception error occurs, the client delays sending the packet. It was sent on Frame 8. At this time, the connection has been Finish, in Frame 9, the database side sent a Reset packet to the application server, completely interrupted the connection.

We now analyze in detail why the client sends account, driver version, and operating system information to the database side so slowly. The code for this part is in the Connector/NET MySQLAuthenticationPlugin.cs file. We modify this part of the code to bury the time to further locate the problem. The following is the tracking information printed according to the time.

Judging from the tracked Trace, there is an operation delay of about 30 seconds. When MySQLDefs::OSDetails is returned. This part of the code is as follows:

[DisplayName ("_ os_details")] public string OSDetails {get {string os = string.Empty;try {var searcher = new System.Management.ManagementObjectSearcher ("SELECT * FROM Win32_OperatingSystem"); var collection = searcher.Get (); foreach (var mgtObj in collection) {os = mgtObj.GetPropertyValue ("Caption"). ToString (); break;} catch (Exception ex) {System.Diagnostics.Debug.WriteLine (ex.ToString ());} return os;}}

This code gets the Caption information through a WMI query. This is the version information of the operating system. Because it is a WMI call, there are many dependencies.

[problem Verification]

Let's extract this code. Here is a short piece of Repro code:

Static void Main (string [] args) {Stopwatch watch = new Stopwatch (); while (true) {watch.Restart (); var searcher = new System.Management.ManagementObjectSearcher ("SELECT * FROM Win32_OperatingSystem"); var collection = searcher.Get (); foreach (var mgtObj in collection) {string os = mgtObj.GetPropertyValue ("Caption"). ToString ();} watch.Stop (); Console.WriteLine (watch.ElapsedMilliseconds); if (watch.ElapsedMilliseconds > = 1000) {Console.WriteLine ("-") File.AppendAllText ("abc.txt", DateTime.Now.ToString ("yyyy-MM-dd HH:mm:ss.fff") + "," + watch.ElapsedMilliseconds + "\ r\ n");}}

When we run the above code on the problematic application server, we do find that the WMI query has timed out: the following points are the ones we caught for more than 30 seconds:

2017-11-21 17 1915 30. 208, 33638

2017-11-21 17 2015 09.193, 33199

2017-11-21 17 2015 53.086, 33201

2017-11-21 17 2715 05.114, 32976

2017-11-21 17 2819.178, 33635

2017-11-21 17 3015 07.130, 65977

2017-11-21 17 3015 49 051, 40478

2017-11-21 17 31R 15.126, 26072

2017-11-21 17 purl 38 purl 16.048, 66671

2017-11-21 17 38 purl 49.204, 33152

2017-11-21 17 39R 53.161, 33828

2017-11-21 17 purl 40 purl 38.121, 33549

2017-11-21 17 4715 09.179, 33775

2017-11-21 17 4715 57.174, 33164

[solution]

WMI queries are slow, possibly for a variety of reasons. Such as the operating system CPU is high, or the query itself has a deadlock. This problem needs further analysis. But looking at the code, we know that this WMI query is just to get information about the operating system. This information can be cached completely. It is not necessary to make a WMI query every time you connect.

The root cause of the error determined here is that the acquisition of operating system information in MySQL's C# connector takes too long, causing the connection to trigger the server to time out. Comment out this section (which can lead to a long operation), do further verification, and there are no more timeout errors.

Public string OSDetails {get {dbglog.dolog ("MysqlDefs::OSDetails1"); string os = string.Empty;/*try {var searcher = new System.Management.ManagementObjectSearcher ("SELECT * FROM Win32_OperatingSystem"); var collection = searcher.Get (); foreach (var mgtObj in collection) {os = mgtObj.GetPropertyValue ("Caption"). ToString (); dbglog.dolog (String.Format ("MysqlDefs::OSDetails::foreach {0}", os.ToString ()); break) }} catch (Exception ex) {System.Diagnostics.Debug.WriteLine (ex.ToString ());} * / dbglog.dolog ("MysqlDefs::OSDetails2"); return os;}} above is all the contents of the article "sample Analysis of Authentication Failed errors thrown by MySQL connections". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.