EC2 MySQL Connection Drops using Private IP Works with Public IP

0

We recently split our single EC2 (t2.small) instance which was running (IIS and MySQL) into 2 - (t2.small) instances. We moved all the IIS logic to the new instance and updated the WebConfigs to connect to the old machine which is running MySQL. After firing everything up we noticed very random / sporadic exceptions from MySQL related to connection issues. Overall our connection to the DB works but it seems to drop under certain cases. We tried tweaking timeouts on both IIS and MySQL side but didn't help. The last thing we tried was instead of using the private (Internal) IP for the connection to the MySQL Db we used the public (Elastic IP). With the public IP everything works perfect and we do not get any connection exceptions

Does anyone have any insight into why using the private IP between the servers is inconsistent, but the public IP works very reliably?

MySQL Exception:
MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.MySqlStream.LoadPacket()

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

asked 4 years ago437 views
1 Answer
0

We ended up solving this issue by following this guide for windows. Setting the below TCP settings in the Registry.

https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters:
KeepAliveTime: 30000
KeepAliveInterval: 1000
TcpMaxDataRetransmissions: 10

answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions