MySqlException: Timeout expired - Increasing Connection Timeout Has Had No Effect

Question

I have a query that is taking longer to execute as the database increases in size. The query is optimized and necessary, but my C# Console Application has recently been giving me this error:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Timeout expired.

Increasing the connection time out in the connection string doesn't help; I increased it from

Connect Timeout=28800

to

Connect Timeout=128800

but I'm still getting the error despite this change.

If I run the query from MySQL workbench it only take about 10 seconds, so I'm not sure how to prevent this Unhandled Exception.

Are there other things, besides "the time a query takes", that can produce this exception?

Answer 1

I've had this problem before. ConnectTimeout property only applies to time outs that occur when connecting to the database, not for queries.

CommandTimeout however specifies how long it should wait for the query to return. I believe the default is 30 seconds. Double check the documentation for your MySql library, but for SqlCommand the CommandTimeout is in Seconds not milliseconds.

Answer 2

If you can show us your method, we can help find the bugs.

If history (and SO ) has taught me anything, its

"You better be using Paramaterized SQL statements before posting any code" 

If you are unsure on how to use parameterized commands here is an example ( taken from one of the answers where I didn't use parameterized SQL )

var command = new MySqlCommand(
    "SELECT * FROM tblPerson WHERE LastName = @Name AND Height > @Height AND BirthDate < @BirthDate", connection);

command.Parameters.AddWithValue("@Name", lastname);
command.Parameters.AddWithValue("@Height", height);
command.Parameters.AddWithValue("@Name", birthDate);

Try that if you haven't already and post some code :)

Answer 3

You can also try adding "default command timeout=360" in your connection string; eg

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;default command timeout=20;

This option is available from Connector/NET version 5.1.4.

[I lifted this from connectionstrings.com/mysql/