I recently upgraded from MySQL version 4.0 to 5.1 and from .NET 1.1 to
2.0 and since then I've noticed that when I try to run queries or
commands that take longer than a minute I get the following timeout:

MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.

Maybe I'm missing something but I haven't found a timeout variable
that I can set for queries or commands executed within a transaction.
I'm confident that this is absolutely nothing else going on with the
database so it's not timing out because another connection has a lock
on the table.
I'm trying to delete about 300k rows out of a table with around 500k
rows. The WHERE clause looks for double values less than a set amount
on a column which has an index.

This is the SQL:
DELETE FROM `MyTable` WHERE `MyColumn` < 1.234567;

The only variable that seems at all related is this one:
innodb_lock_wait_timeout but I know there's no other locks on the
table and even if I change it to something large it has no effect.

I can run the exact same query in the query browser in the middle of a
transaction and it works fine.

On Thu, 20 Mar 2008 12:31:27 -0700 (PDT), Israel wrote:
> I recently upgraded from MySQL version 4.0 to 5.1 and from .NET 1.1 to
> 2.0 and since then I've noticed that when I try to run queries or
> commands that take longer than a minute I get the following timeout:
>
> MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout
> period elapsed prior to completion of the operation or the server is
> not responding.

This looks like an error from .NET, not MySQL.
>
> Maybe I'm missing something but I haven't found a timeout variable
> that I can set for queries or commands executed within a transaction.
> I'm confident that this is absolutely nothing else going on with the
> database so it's not timing out because another connection has a lock
> on the table.
> I'm trying to delete about 300k rows out of a table with around 500k
> rows. The WHERE clause looks for double values less than a set amount
> on a column which has an index.
>
> This is the SQL:
> DELETE FROM `MyTable` WHERE `MyColumn` < 1.234567;
>
> The only variable that seems at all related is this one:
> innodb_lock_wait_timeout but I know there's no other locks on the
> table and even if I change it to something large it has no effect.

Is the a configuration for how long an SQL command might take in .NET? I
know there is one for php that kills scripts that take more than N
seconds to run.

> I can run the exact same query in the query browser in the middle of a
> transaction and it works fine.

How long does it take to run?

--
Is it just me, or is it a clear indication that a thread is ending its
useful life is when people start debating the merits of the analogies
that have been posed rather than the original subject matter of the
thread? --Rhetorical question by Rainer Atkins founding "Atkins' Law"

On Mar 20, 4:31*pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> > MySql.Data.MySqlClient.MySqlException: Timeout expired. *The timeout
> > period elapsed prior to completion of the operation or the server is
> > not responding.
>
> This looks like an error from .NET, not MySQL.

But it's a "MySqlException" not a .NET generic exception so it would
seem that the .NET connector is throwing this based off of something.
The exception stack looked like all stuff within the connector.

> Is the a configuration for how long an SQL command might take in .NET? I
> know there is one for php that kills scripts that take more than N
> seconds to run.

None that I know of but if there is I'd bet it only applies to SQL
server database connections.

> > I can run the exact same query in the query browser in the middle of a
> > transaction and it works fine.
>
> How long does it take to run?

In the query browser it takes ~30 seconds. Which leads to two
questions, 1) why does it take longer to execute within code than it
does from the query browser (in both I'm executing within a
transaction)? 2) why does something timeout after a minute anyway?

The full exception stack looks like this:
MySql.Data.MySqlClient.MySqlException: Timeout expired.
The timeout period elapsed prior to completion of the operation or the
server is not responding.
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader( CommandBehavior
behavior) at
MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuer y()

The next line being my original line of code that calls
ExecuteNonQuery().

I'm doing this within a transaction that has an isolation level of
ReadCommitted and the one and only command that is being executed
within the transaction is this delete statement.

On Mon, 24 Mar 2008 07:21:55 -0700 (PDT), Israel wrote:
> On Mar 20, 4:31*pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>> > MySql.Data.MySqlClient.MySqlException: Timeout expired. *The timeout
>> > period elapsed prior to completion of the operation or the server is
>> > not responding.
>>
>> This looks like an error from .NET, not MySQL.
>
> But it's a "MySqlException" not a .NET generic exception so it would
> seem that the .NET connector is throwing this based off of something.
> The exception stack looked like all stuff within the connector.

But timeouts on commands are *usually* set by the calling
language/program, not the routine itself. Especially since MySQL is a
package that can expect that some queries may take minutes or hours to
run.

>> Is the a configuration for how long an SQL command might take in .NET? I
>> know there is one for php that kills scripts that take more than N
>> seconds to run.
>
> None that I know of but if there is I'd bet it only applies to SQL
> server database connections.

Looks like (from websearching) that there's a sqlcommand.commandtimeout
on the .NET sql abstraction layer that fixes this.

http://www.google.com/search?q=%22Th...s ponding.%22

shows this happening with MySQL, Oracle, SQL Server, etc.

http://msdn2.microsoft.com/en-us/lib...timeout.as px

shows that this thing defaults to 30 seconds. If you haven't changed it,
it's still 30 seconds. If you don't KNOW about this setting, my instinct
is that you haven't changed it.

>> > I can run the exact same query in the query browser in the middle of a
>> > transaction and it works fine.
>>
>> How long does it take to run?
>
> In the query browser it takes ~30 seconds.

Weeeeelll doggie... Look at that. Right about what that timeout defaults
to...

> Which leads to two
> questions, 1) why does it take longer to execute within code than it
> does from the query browser (in both I'm executing within a
> transaction)?

Beats me. Run an EXPLAIN over both places and see what looks different.
The post the results here, and the relevant question becomes "Is there
any room for improving the run-time of this easily?" That's a great
question for this group. .NET errors belong in .NET groups.

As for me, I don't know .NET, have never touched it, have no expertise
with it. I know a little about MySQL. Anyone thinking that they know
anything about .NET should be able to find these answers even faster
than I did.

> 2) why does something timeout after a minute anyway?

So that your program can report a error to the user or log instead of
waiting forever for something that may never come back. It's the
programmer's resposibility to set the timeout appropriately, and a
single timeout will not be appropriate for all SQL operations.

--
For why should my freedom be judged by another's conscience?
-- Paul (I Corinthians 10:29)

On Mar 24, 11:04*am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> Looks like (from websearching) that there's a sqlcommand.commandtimeout
> on the .NET sql abstraction layer that fixes this.
Yeah, I just stumbled upon the timeout property and found that
changing it seemed to work. Sometimes you can't see the forest
through the trees since I was looking at the server settings vs. the
command itself.
I'm still confused as to why it *seemed* to timeout after 60 seconds
yet said it was 30 and also why it worked perfectly fine in the last
release of the .NET connector unless that defaulted to a longer time.
I swear we were running even longer queries and statements in the past
but maybe that's just my impression.