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:

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:

 

Comments

1 One Response to “MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”
  1. DBA100 says:

    I have a trigger like this:

    CREATE DEFINER=myung@% TRIGGER Insight_20101202.tblPCEventLog_AITRIGGER AFTER INSERT ON Insight_20101202.tblPCEventLog FOR EACH ROW
    BEGIN
    INSERT INTO Insight_20101202_DataAudit.tblPCEventLog SET
    TriggerAction_Audit=”AFTER”,
    ActionDone_Audit=”INSERT”,
    ActionTime_Audit=now(),
    ActionByHost_Audit=USER() , ID=NEW.ID, LevelInd=NEW.LevelInd, ReferenceID=NEW.ReferenceID, LogDate=NEW.LogDate, LogUser=NEW.LogUser, DocType=NEW.DocType, Action=NEW.Action, Description=NEW.Description, Attachment=NEW.Attachment, Original_file=NEW.Original_file, Filter1=NEW.Filter1, Hidden=NEW.Hidden; END;

    the problem is, if this trigger can’t write to Insight_20101202_DataAudit.tblPCEventLog tables, it will wait there forever, which is what we don’t want !

    Can I make use of this:

    MySqlCommand cmd = new MySqlCommand();
    cmd.CommandTimeout = 60;

    to instruct the trigger to show us timesout message or a man made message which means that, for example, can’t write to Insight_20101202_DataAudit.tblPCEventLog tables probably because that table has been locked for some reason ?

Leave a Reply

Your email address will not be published. Required fields are marked *