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 […]
This article was posted by Independent Software, a website and database application development company based in Maputo, Mozambique. Our website offers regular write-ups on technical and design issues, ranging from details at code level to 3D Studio Max rendering. Read more about Independent Software's philosophy, or get in touch with Independent Software.
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:
MySqlCommand cmd = new MySqlCommand();
cmd.CommandTimeout = 60;
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:
Server=mydatabaseserver;Database=mydb;User ID=myuser;Password=mypw;default command timeout=120
Did this article help you out? Please help us find more time to write useful guides & articles like this by donating a buck or two. It'll keep us coffee-fueled. Thanks!