Analysis and solution of MySQL connection throwing Authentication Failed error

[Problem description]

On the application side, the following error is occasionally seen:

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 only occurs when using Connector/NET. There is no similar problem when using the JDBC driver.

2. There are multiple application servers, but only one reports this error, so the problem on the server side can be ruled out.

3. The problem is very random, restarting the server/IIS can temporarily solve the problem.

4. In some scenarios, the CPU of the application server is not very high, and this error may 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.

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

【Problem Analysis】

We capture packets on the application server and database sides. The packets captured by both sides are consistent. Network problems can be ruled out. The following are the captured packets and time points:

Judging from the interaction of the above network packets, the first three packets are the three-way handshake protocol of TCP. The problem lies in the sixth packet. The database server sends a Finish packet to the application server to terminate the database connection. The database sends a Finish packet because the database finds that the connection has timed out. This is controlled by the Connect_timeout variable on the server side. The reason is that the application has not sent a network packet to the database server for more than 10 seconds. Judging from the network 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, it should immediately send the following network packet to the database. This package mainly sends account number, driver version, operating system information, etc. to the database server. [Below are some screenshots of normal network packets]. In the event of an abnormal error, the client delays sending the packet. It is sent in Frame 8. At this time, the connection has been Finished. In Frame 9, the database sends a Reset packet to the application server, completely terminating the connection.

Let's now analyze in detail why the client is so slow in sending account, driver version, and operating system information to the database. This part of the code is in the Connector/NET MySQLAuthenticationPlugin.cs file. We modify this part of the code and perform time tracking to further locate the problem. The following is the tracking information printed out according to the time point.

According to the 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 obtains the Caption information through WMI query. That is, the version information of the operating system. Since it is a WMI call, there are many dependencies.

【Problem Verification】

Let's extract this code. Here is a short 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 can indeed find that the WMI query has timed out: The following points are the points where we captured more than 30 seconds:

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

【Solution】

Slow WMI queries can be due to a number of reasons. For example, the operating system CPU is high, or the query itself is deadlocked. This issue needs further analysis. But looking at the code, we know that this WMI query is only to obtain information about the operating system. This information can be cached. There is no need to perform a WMI query every time you connect.

The root cause of the error is that it takes too long to obtain operating system information in the MySQL C# connector, which causes the connection to the trigger server to time out. Comment out this part (which may cause long operations) and perform further verification. No timeout errors will occur.

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;
}
}

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.