Possible bug in mysqlcommand.commandtimeout

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
NickG
Posts: 12
Joined: Mon 09 Jun 2008 13:54

Possible bug in mysqlcommand.commandtimeout

Post by 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.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by 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.

NickG
Posts: 12
Joined: Mon 09 Jun 2008 13:54

Post by 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.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by 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.

Post Reply