MySQL

mvdriel
mvdriel used Ask the Experts™
on
Hi Guys,

In my application I use a MySQL-connection. If this connection is not used for a period of time it times out. Is there any way to detect this, because the connection state remains open. Just sending a query to test takes a long time before it times out so I was hoping for a different approach.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
Is there any way to detect this
try to check the Connection object's State property.

Author

Commented:
Hi Ryan,

as mentioned, the connection's state remains open, even though the connection isn't active at that moment.
Ryan ChongSoftware Team Lead

Commented:
If this connection is not used for a period of time it times out
ok,... then what's the Connection State you get if it's "timed out'?

what's the problem with this connection issue?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
Hi Ryan,
The connectionstate remains open. However, when a query is executed an error is thrown (Connection is not active).
Ryan ChongSoftware Team Lead

Commented:
can you post your scripts and highlight the line causing the error (if you know this)?

Author

Commented:
Sure:
        Dim cmd As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand
        cmd.Connection = objMySQLConnection

        Try
                cmd.CommandText = strProcedureName
                If Procedure Then
                   cmd.CommandType = CommandType.StoredProcedure
                End If
                If Not Params Is Nothing Then
                    For Each tmpItem As clsParams In Params
                        cmd.Parameters.AddWithValue("@" & tmpItem.Name, tmpItem.Value)
                        cmd.Parameters("@" & tmpItem.Name).Direction = ParameterDirection.Input
                    Next
                End If
                Return cmd.ExecuteScalar
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message)
            Return Nothing
        End Try

Open in new window

Ryan ChongSoftware Team Lead

Commented:
how you define object clsParams  ? is it a class?

actually back to your original question:

>>  If this connection is not used for a period of time it times out. Is there any way to detect this, because the connection state remains open.

actually the connection object is designed to be allowed to set a timeout in the connection, so that the database is not always connected. if the connection remains as active (opened), it probably means that the connection is not yet timed out.

To set the connection's time out, you can set it via the Connection String.

try refer to this resource and look for the settings with wording "timeout":
https://www.connectionstrings.com/mysql-connector-net-mysqlconnection/

alternatively, if you mean to change the time out of a command execution, you can try look for CommandTimeout property.

so you can apply it like:
Dim cmd As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand
        cmd.CommandTimeout = 60

Open in new window

which means the command will time out after 60 secs.
Duy PhamFreelance IT Consultant

Commented:
I don't think there is a good way to detect if a connection is still alive practically. To do that, you will need to connect to the server anyway. Maybe you should think about using different approaches, for example:
- Only initiate connection when needed and close it when finish
- Use timer/thread to automatically 'refresh' the connection by sending a short query maybe 5 minutes before it's going to timeout (based on default or configured number of minutes that a connection is kept alive without any action

Author

Commented:
Hi Duy,

I've now started using a timer, and haven't had an error since. The best way to prevent this is, as you write, to close the connection when ready but for some reason the connection time is a little on the long side, which slows the application down to much. Using a timer is an acceptable workaround. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial