S h o r t S t o r i e s

// Tales from software development

MySQL query fails with "Query execution was interrupted"

with 4 comments

The project I’m currently working on has a Windows Service that loads some static data from a MySQL database when it starts. The query that returns the data has always executed slowly in my development environment and occassionally the query has reached the default timeout value of 30 seconds before completing. I hadn’t bothered to add some code override the default value as in the production environment the query executes fairly quickly and is never in danger of timing out.

When the query times out the service fails to start. Usually, just starting the service a second time will be successful although I’d sometimes get a second timeout with an appropriate timeout error message in the exception, one further attempt to start the service would succeed.

Not today though… And, the error message in the execption looked much more serious: “Query execution was interrupted”. After attempting to start the service about five or six times it was clear that it wasn’t going to succeed this time.

It took a few minutes to realise that this is probably a bug in MySQL. The error always occurred exactly 30 seconds after the call to MySQL to execute the query. So, this almost certainly is a timeout error but the wrong message is being passed back in the exception.

What seems to be happening is that the query times out and MySQL cancels it. The correct error message is passed back to indicate what happened. When the same query is executed again, MySQL is killing the query when the timeout is reached but is appears get confused about why the query is being cancelled. Possibly, because the same query was previously killed, it’s deciding that someone has requested the cancellation of the query execution without checking to see that it’s actually MySQL’s timeout handler that previously cancelled the query and is doing so this time too.

Setting an explicit, and longer, timeout value in the Command object’s CommandTimeout property fixed the problem.

Advertisements

Written by Sea Monkey

December 4, 2009 at 1:00 pm

Posted in Debugging, Development

Tagged with ,

4 Responses

Subscribe to comments with RSS.

  1. Got somehting similar today ( using the dotnet connector / MySql.Data.dll )

    It’s seems to be a bug in the MySql connector.

    What threw me off a bit at first was – not only I recieved somewhat misleading exception ( “Sort aborted” / “Query execution interrupted”) – as you did – but I did specified a (fairly large) timeout on the connection object.
    As for you, after ~ 30seconds, an exception was raised (although the connection timeout was set to 60 seconds).

    I was assuming that a command would pick up the default of its associated connection if no value was specified.
    Wrong.
    After specifying a timeout bigger thant the 30s default, it worked as expected.

    Marc Cauchy

    December 30, 2009 at 11:05 pm

  2. I have something similar using Drupal views. I can’t simplify the query though.

    Glumbo

    August 1, 2011 at 2:31 pm

  3. Similar problem while using mysqldump! But intermittant, sometimes works, sometimes doesn’t

    Steve

    September 10, 2011 at 4:15 pm

    • try using this jar file mysql-connector-java-5.0.4.jar

      fool

      July 2, 2012 at 8:08 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: