How to fix 'Authentication to host for user using method 'mysql_native_password' failed with message: Reading from the stream has failed.' in .NET

1

I've created a custom windows service that spools up new jobs (processes, not threads) based on a queue in a mysql table. The 'jobs' are database-intensive applications as well as the service (I'll call it the 'manager' from now on).

During periods of high volume (many 'jobs' being initiated), this error starts to occur, somewhat frequently (an estimated ~1 in 20 jobs are affected). The "interesting" thing is that 9 times out of 10 when this error occurs it's in the very first SQL command of a new job. I don't think I've seen it happen in the manager. The rare case is when this error gets thrown in the middle of a job (dozens of SQL commands are being executed per job).

MySql.Data.MySqlClient.MySqlException (0x80004005): Authentication to host '***' for user '***' using method 'mysql_native_password' failed with message: Reading from the stream has failed. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.ReadPacket()
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.AuthenticationFailed(Exception ex)
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.ReadPacket()
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.Authenticate(Boolean reset)
   at MySql.Data.MySqlClient.NativeDriver.Authenticate(String authMethod, Boolean reset)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
   at MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at MySql.Data.MySqlClient.MySqlHelper.ExecuteReader(String connectionString, String commandText, MySqlParameter[] commandParameters)

Background on my stack:

  • Windows Server 2016 (this is what the manager and jobs run on)
  • MySql.Data 6.10.8 (nuget package for .NET SQL integration)

SQL Connection string (non-auth) settings:

  • encrypt=false;
  • AllowZeroDatetime=true;
  • CheckParameters=false;

Things I've Tried:

  • SSL: Most google searches for this problem come up with the exact same post suggesting adjusting SSL settings. I'll just include a link: http://www.voidcn.com/article/p-phfoefri-bpr.html (none of the suggested solutions worked for me)

  • Connection pooling configuration: I had success with disabling connection pooling (e.g. adding "Pooling=false;" to the connection string), but I fear this is not a good long term solution. I want to be able to use and understand connection pooling because of its performance benefits. Surely, disabling connection pooling can't be the only way to fix this. There are other settings related to connection pooling such as "connection reset", but I don't really have a good enough understanding to use them and I find the sql documentation on each parameter too brief to really get a good grasp. I tried increasing the MaxPool setting but that didn't have any effect. There is also the "ConnectionTimeout" setting which sets the amount of time a connection will wait for a server response before aborting...I have a question about this, when a sql cmd is waiting for a pooled connection to open up, does it only wait for "CommandTimeout" seconds?

All of my sql commands run use the MySqlHelper class like this (some of my commands simply use the ExecuteNonQuery in a very similar fashion):

using (MySqlDataReader sqlRdr = MySqlHelper.ExecuteReader(connString, cmdText, sqlParams.ToArray()))
{
    while (sqlRdr.Read())
    {
        ret.Add(getFromRdr(sqlRdr));
    }
}

I'm having trouble just "troubleshooting" this bug. Most of the research I've done looking up this exception just circles back to the same post about adjusting SSL settings. It does not seem like my issue is SSL-related, especially since disabling connection pooling seems to resolve this issue.

Thanks for any help in advance.

c#
sql
asp.net
ssl
connection-pooling
asked on Stack Overflow May 22, 2019 by Will Knight

1 Answer

0

You can use one of these solutions:

  1. Add ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls12; on start of toyr application.
  2. Maybe its problem of 'Remote Database Access Hosts'. "You can allow external web servers to access your MySQL databases by adding their domain name to the list of hosts that are able to access databases on your web site."
answered on Stack Overflow Dec 11, 2019 by Abd

User contributions licensed under CC BY-SA 3.0