Intermittent connection issues to DB server

[ikonrao]

Hello,

Our application team is intermittently facing below error. Initially mysql setup was on windows server and we used to face this error there as well. Later we moved to linux server and still facing same error. There are no specific pattern, it justs occurs randomly.

App is still hosted on Windows setup, it used .NET connector for Db connection. Could you guys please help with the same.

ERROR:
Authentication to host IP for user dbuser 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 software in your host machine
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
— End of inner exception stack trace —
at MySql.Data.Common.MyNetworkStream.HandleOrRethrowException(Exception e)
at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
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.GetPooledConnection()
at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
at MySql.Data.MySqlClient.MySqlPool.GetConnection()
at MySql.Data.MySqlClient.MySqlConnection.Open()
at MM.Backend.Common.Repository.BaseRepository`1.GetRecords(MySqlCommand command)
at MM.Backend.Common.Repository.StockDetailsRepository.GetAllActiveStocks()
at MM.Backend.Scorecard.Valuation.ValuationInitialiser.Main(String[] args)

[avi_vainshtein]

Hello,

Although the post is not fresh, but that behavior is still here in almost 2019.
We use a domestic Windows application using a MySQL 6.8.3 .Net connector to work with a 5.7.19 MySQL DB at a centos host.
And occasionally, and different hours of a day, and not every day, we get similar messages.

What is the proper way to resolve/avoid/workaround that problem ?

Best regards and looking forward the community assistance,
Avi Vainshtien

[apapsch]

Do you by chance use connections for a rather long time? I had a similar problem in a daemon that employed a connection for hours (if not days). Once the connection becomes unused, the connection is closed after a timeout. This should not be an issue specific to the .NET connector. You’ll also experience this e.g. in Go programs or even long running PHP scripts (given the connection becomes idle every once in a while).

You have several options:
[LIST]
[]Increase the timeouts (there may also be a driver specific way to do this). You’ll want to set this higher than the expected idle times. A downside of this is that connections can accumulate (depending how your app is structured, for a lone daemon with just one connection it may not have much of an impact).
SET SESSION wait_timeout=x
SET SESSION net_read_timeout=x
SET SESSION net_write_timeout=x
[]Use a connection pool that’s responsible for maintaining connections. The pool should be a component of your app. If your driver is really good, it provides a pool for you.
[*]Somewhat related to the pool: Implement automated reconnects. I’d recommend the pool however.
[/LIST]

[avi_vainshtein]

To add more information : when those messages appear - the status values of “Aborted_connects” are increased.

And also, in the “show processlist” appear a number of sessions with :

• “User” = “unauthenticated user”
• “Command” = “Connect”
• “Time” values of few seconds
• “State” = “Receiving from client”

For example :
28433485 unauthenticated user vm-cmint4.bezeq.com:53175 NULL Connect 9 Receiving from client NULL

[avi_vainshtein]

The current values of mentioned timeout variables are :
| wait_timeout | 3600 |
| net_read_timeout | 900 |
| net_write_timeout | 900 |

[avi_vainshtein]

We don’t use long connections.
The problem is not that the connection is closed, but that a new connection can’t be established.