MySQL Reading from stream failed

Question

I am facing the following error intermittently. It is solved by using 'skip name resolve' option in the mysql server settings.

However, as per many suggestions found on net, using 127.0.0.1 should have solved the issue. But this too didn't help can you suggest me a workaround or a SQL command through which I can check the 'skip name resolve' option.

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

Stack Trace:
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()

Answer 1

This is a common error that happens when you update to MySQL Server 8.+. By default MySQL 8 uses caching_sha2_password which is an upgraded authentication plugin over the mysql_native_password.

A way to fix this is by either specifically setting the plugin for the user's password to caching_sha2_password

CREATE USER 'sha2user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password'

Updating the MySQL Server to accept mysql_native_passwords.

[mysqld]
default_authentication_plugin=mysql_native_password