MySQL Bugs: #74992: Authentication mysql to .Net

Bug #74992	Authentication mysql to .Net
Submitted:	25 Nov 2014 4:44	Modified:	25 Dec 2014 11:39
Reporter:	Ali Nirabi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.1	OS:	Windows (Authentication mysql to .Net)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	C#, database, MySQL

Description:
 0 down vote favorite
	

I have Mysql DB connected to .NET MVC. When i connect remote it some times works fine and some times i have this error msg:

    MySql.Data.MySqlClient.MySqlException (0x80004005): Authentication to host 'ServerName' for user 'UserName' 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.IOException: Unable to read data from the transport connection: An established connection was aborted by the software in your host machine. ---> System.Net.Sockets.SocketException: An established connection was aborted by the

Please how can i make Authentication ? Any ideas to solve it?

How to repeat:
    MySql.Data.MySqlClient.MySqlException (0x80004005): Authentication to host 'ServerName' for user 'UserName' 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.IOException: Unable to read data from the transport connection: An established connection was aborted by the software in your host machine. ---> System.Net.Sockets.SocketException: An established connection was aborted by the

Hello Ali Nirabi,

Thank you for the bug report.
This is a duplicate of Bug #72110, please see the explaination by dev's in #72110.

Quoting from #72110 - [21 May 15:40] Francisco Alberto Tirado Zavala:

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. Also you must consider that the server can terminate or close a connection if the connection is inactive after some time, so you need to create a new valid connection opening a new socket to get a valid Id from the server.

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, 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.

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

sda

Attachment: mysql.data.dll (application/x-msdownload, text), 447.00 KiB.