Possible bug in mysqlcommand.commandtimeout

[NickG]

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.

[Shalex]

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.

[NickG]

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.

[Shalex]

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.