When writing a database application that accesses a remote MySQL database, some of your queries might take a long time to execute, either due to latency or simply because you’re receiving a lot of data. Though it’s always a good idea to limit the quantity of data you request to a reasonable size in other not to make the user wait too long, sometimes there’s no way around it.
Execution time is not the only problem, however. After a while, MySQL simply gives up, throwing a
MySql.Data.MySqlClient.MySqlException: Timeout expired error.
This is because MySQL will abort any command (i.e. any query) after a set period, known as the command timeout period. The default command timeout is 30 seconds, and this will hurt you if your application has to query data that may take longer than 30 seconds to arrive. In this case, it’s useful to increase the command timeout period.
There are two ways to go about this:
Set command timeout per query
For each MySQL query you execute, you can actually set the command timeout. The default command timeout is 30 seconds, but you can change this like so:
You can also set the command timeout to zero in order to make MySQL wait indefinitely for results to come back.
Set default command timeout
You may not wish to set the command timeout for each query you execute. In fact, you may not be able to if you are using an object relational mapping system such as NHibernate. In this case, you can set the default command timeout for MySQL in your connection string to apply to all queries executed by the ORM.
Your connection string might then look like this: