我时常遇到以下错误。

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

我知道连接字符串是正确的 - 首先在应用程序启动后对其进行测试，如果失败，应用程序会给出一条消息并退出。

其次，大多数时候报告它的函数也在使用它来正确连接，它时不时地发生。

我试过更改为 127.0.0.1，但似乎没有什么不同。

它在 Windows Server 2012 64 位上。

我已将 127.0.0.1 localhost 添加到主机文件。

应用程序在 99% 的时间里都在正确运行并连接到数据库，只是偶尔会出现此错误。

最重要的是，我检查了 show full processlist 命令，但我始终只有几个连接。

我无法解释这个问题，也不知道如何解决。

谢谢， 吉姆

最佳答案

这是很老的了，但是因为我刚刚找到了这个问题的解决方案，所以我会分享它以防万一有人还在寻找它(据我所知，这也是当前 Mysql 版本的问题) .

此类问题记录在 this bug report 中.

我在这里引用那个错误报告中最重要的答案:

[21 May 2014 15:40] Francisco Alberto Tirado Zavala

Hello Jorge.

When you are working with a Client-Server application all the connections are performed by Sockets, when you create a connection you open a Socket between your client and your server, and an Id/ProccesId is assigned/reserved in the server for the connection requested by the client. If the server is restarted the Id given to the client is lost, so you need to create a new valid connection opening a new socket to get a valid Id from the server.

Also you must consider that the server can terminate or close a connection if the connection is inactive after some time. If you have your connection open also could affect your connections pool, because a connection open means less connections for your pool: having 10 clients connected at the same time are 10 connection less from the Server available connections.

If you can't change the design in your application (having a global connection), as a workaround you can have a method to validate that the connection is valid, for example the following method ping the server and if the ping fails it open the connection again:

....
MySqlConnection _conn = new MySqlConnection("MyConnectionString");
private void CheckConnection()
{
   if(!_conn.Ping())
   {
     _conn.Open();
   }
}
....

As you can see the code is very simple and it works in the following way: when the connection try to ping the server if the connection is invalid, internally the stream that has the socket information will be terminated and the connection will be set on status closed as well as the invalid connection will be removed from the connections pool if exists, then opening the connection again will create a new connection which means a new valid socket. If the ping succeed so you will continue using the same connection.

To use it you can call it before trying to perform an action to the database, and the connection will be re-opened just when is invalid:

....
private void button1_Click(object sender, EventArgs e)
{
  //assuming that the connection is already open
  CheckConnectionState();
  var cmd = new MySqlCommand("select 1", _conn);
  cmd.ExecuteNonQuery();
}
....

This is just a workaround, and as far I know all the connections to any database server will have the same behavior: if the server is restarted any connection will be lost, as well as the server will clean every unused connection.

Thanks for your time.

关于MySQL - 身份验证(但仅在某些时候)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25743581/