Possible bug in mysqlcommand.commandtimeout

Possible bug in mysqlcommand.commandtimeout

Postby NickG » Thu 05 Feb 2009 19:48

If a command times out it seems this also closes the connection. This is bad because there is then no way to recover other than creating a new connection and waiting for the old command to timeout for the original connection to die.
This reproduces it
Code: Select all
        MySqlConnection1.Open()
        Try
            Dim c As New MySqlCommand("SELECT SLEEP(10)", MySqlConnection1)
            c.CommandTimeout = 5
            Debug.Print("Initial State: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
            c.ExecuteNonQuery()
        Catch ex As Exception
            Debug.Print("Timeout Exception: " & ex.ToString())
        End Try
        Debug.Print("State after timeout: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
        Try
            Dim c As New MySqlCommand("SELECT SLEEP(1)", MySqlConnection1)
            c.CommandTimeout = 5
            Debug.Print("State just before new command: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
            c.ExecuteNonQuery()
        Catch ex As Exception
            Debug.Print("New command exception: " & ex.ToString())
        End Try
        MySqlConnection1.Close()

it out puts:
Initial State: Open
A first chance exception of type 'Devart.Data.MySql.MySqlException' occurred in Devart.Data.dll
Timeout Exception: Lost connection to MySQL server during query
State after timeout: Open
State just before new command: Open
A first chance exception of type 'Devart.Data.MySql.MySqlException' occurred in Devart.Data.dll
New command exception: Lost connection to MySQL server during query

i would expect the second command to work fine as the COMMAND timed out and not the connection.
NickG
 
Posts: 12
Joined: Mon 09 Jun 2008 13:54

Postby Shalex » Fri 06 Feb 2009 13:11

Please try using the latest build of dotConnect for MySQL (5.00.20). I've just checked it. It works fine. Here is my log when using your code:
Initial State: Open
Timeout Exception: Lost connection to MySQL server during query
State after timeout: Closed
State just before new command: Closed
New command exception: System.InvalidOperationException: Connection must be opened.
at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at _0206myCommandTimeout.Form1.Form1_Load(Object sender, EventArgs e) in D:\AlexSh_testProgs\DateProviderName\0206myCommandTimeout\0206myCommandTimeout\Form1.cs:line 35

To make the second command to work open the connection object again:
Code: Select all
        MySqlConnection1.Open()
        Try
            Dim c As New MySqlCommand("SELECT SLEEP(10)", MySqlConnection1)
            c.CommandTimeout = 5
            Debug.Print("Initial State: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
            c.ExecuteNonQuery()
        Catch ex As Exception
            Debug.Print("Timeout Exception: " & ex.ToString())
        End Try
        Debug.Print("State after timeout: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
        Try
            MySqlConnection1.Open()
            Dim c As New MySqlCommand("SELECT SLEEP(1)", MySqlConnection1)
            c.CommandTimeout = 5
            Debug.Print("State just before new command: " & [Enum].GetName(GetType(System.Data.ConnectionState), MySqlConnection1.State))
            c.ExecuteNonQuery()
        Catch ex As Exception
            Debug.Print("New command exception: " & ex.ToString())
        End Try
        MySqlConnection1.Close()

Now the MySqlConnection.State becomes ConnectionState.Closed when MySqlCommand is time out to avoid the behaviour you have described.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby NickG » Mon 09 Feb 2009 14:48

Why does it close the connection on a timeout is my question. If a command times out that doesn't mean the connection died. It could simply be the command is taking to long to execute.
NickG
 
Posts: 12
Joined: Mon 09 Jun 2008 13:54

Postby Shalex » Mon 09 Feb 2009 15:44

If the connection state will not become closed and the command will not return any result set, this connection object will be not accessible because its state will be always open while waiting the result of your query. That's why we close the connection object in this case. If you want to use it again, just call the Open() method.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL