For MYSQL 5.7.31 - Which Timeout variable should be changed for a transport connection: Connection timed out


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

  1. I added this to my connection string

Connect Timeout=60

  1. I added this to my mysqld.cnf, Restarted and checked it show variables. The log file shows it timed-out after 42 seconds.

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?


asked on Stack Overflow Nov 15, 2020 by mbalsam • edited Nov 15, 2020 by mbalsam

1 Answer


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.

You might need to tune net_read_timeout or net_write_timeout. But I wouldn't expect either of these to be necessary.

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.

answered on Stack Overflow Nov 15, 2020 by Bill Karwin

User contributions licensed under CC BY-SA 3.0