MySqlCommand timeout

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hi,

I use the native OleDb driver for MySql from ByteFx in my c# application.
When i try to set the command timeout on a MySqlCommand object I get the
following exception:

Unhandled Exception: System.NotSupportedException: Specified method is not
supported.
at ByteFX.Data.MySqlClient.MySqlCommand.set_CommandTimeout(Int32 value)

Any idea why this functionality isn't implemented or alternatives to shorten
the command timeout?

Thanks in advance
 
Hi Eric

Implementing CommandTimeout on MySql is not straightforward because MySql
doesn't support canceling of in-progress commands. If you give a query that
is going to take 2 minutes to complete and you give the commandtimeout as 60
seconds, what should the library do after 60 seconds? The only option is to
close and reopen the connection. What if you are inside a transaction when
this happens? Closing the connection will rollback your transaction which
could be disastrous. Given this, I thought it better to just let the
developer know that you can't stop the command once issued. BTW, the JDBC
driver from MySql doesnt' support command timeouts either for this reason.
It is something I have been thinking about and have not come up with a good
solution.

Reggie
ByteFX, Inc.
 
Thanks for the clear answer!

Reggie Burnett said:
Hi Eric

Implementing CommandTimeout on MySql is not straightforward because MySql
doesn't support canceling of in-progress commands. If you give a query that
is going to take 2 minutes to complete and you give the commandtimeout as 60
seconds, what should the library do after 60 seconds? The only option is to
close and reopen the connection. What if you are inside a transaction when
this happens? Closing the connection will rollback your transaction which
could be disastrous. Given this, I thought it better to just let the
developer know that you can't stop the command once issued. BTW, the JDBC
driver from MySql doesnt' support command timeouts either for this reason.
It is something I have been thinking about and have not come up with a good
solution.

Reggie
ByteFX, Inc.
Click to expand...
 
A solution for anyone with the same problem:

My problem was a freezing GUI when two users want to edit the same fields.
When the edit button is clicked a 'select for update' is performed. To avoid
a long wait time i used the Commandtimeout property and set it to 1. This is
working fine with MSSql but unfortunatly doesn't work with MySql.
Alternativly i set the mysql variable innodb_lock_wait_timeout to a desired
value with the same result.

Eric
 
A solution for anyone with the same problem:

My problem was a freezing GUI when two users want to edit the same fields.
When the edit button is clicked a 'select for update' is performed. To avoid
a long wait time i used the Commandtimeout property and set it to 1. This is
working fine with MSSql but unfortunatly doesn't work with MySql.
Alternativly i set the mysql variable innodb_lock_wait_timeout to a desired
value with the same result.

Eric
 
Back
Top