I'm using mysql 5.7.31 with C# on Linux ubuntu
When I programmatically delete a large table or drop a database I get this timeout
Stack=MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.IOException: Unable to read data from the transport connection: Connection timed out. ---> System.Net.Sockets.SocketException (110): Connection timed out 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() at MySql.Data.MySqlClient.MySqlStream.LoadPacket() at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
To fix this I've attempted
connect_timeout=60000 interactive_timeout=60000 wait_timeout=60000
I think the issue is "Unable to read data from the transport connection: Connection timed out."
But not sure which variable needs to be changed to fix this?
connect_timeout is used only when the client and server are performing a handshake, during the initial client connection. Increasing this helps if the client gets errors like
Lost connection to MySQL server.... The default is 10 seconds.
I've never had a reason to tune this variable, because if the client can't connect within 10 seconds, it's probably blocked by a firewall, or routing, or else the MySQL Server is simply not running or is listening on another port. Increasing the timeout will not help in these cases.
wait_timeout helps the client is idle for a long time between queries. The client is not considered idle if the query executes for a long time. The idle time starts when a query finishes. The default is 8 hours (28800 seconds). If your app is timing out during an SQL statement execution, then this variable is not relevant.
interactive_timeout is just like wait_timeout, but for "interactive" clients. The only client that is in this category is the mysql command-line client. You can ignore this timeout variable, unless you're working on the mysql CLI.
Tip: Use XFS as the filesystem for the MySQL datadir on Linux. It's much faster to drop tables or schemas, regardless of how large they are. XFS gives better performance for other work too, not just dropping tables.
User contributions licensed under CC BY-SA 3.0