MySqlException: timed out - increasing connection timeout has no effect

Question

I have a query that takes longer to execute as the database grows in size. The request is optimized and necessary, but my C # console application recently gave me this error:

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

Increasing the connection time in the connection string does not help; I increased it with

Connection timeout = 28800

to

Connection timeout = 128800

but I still get the error despite this change.

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

Are there other things besides the “time when a request is required” that this exception may raise?

Answer 1

I had this problem before. The ConnectTimeout property applies only to timeouts that occur when connecting to the database, and not to requests.

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

Answer 2

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

If history (and SO) taught me anything, her

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

If you do not know how to use parameterized commands, here is an example (taken from one of the answers where I did not 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 it if you haven’t done it yet and add some code :)

Answer 3

You can also try adding to the connection string "default timeout = 360"; eg,

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

This option is available from version 5.1.4 Connector / NET.

[I removed this from connectionstrings.com/mysql/