vb net set timeout period in connection string mysql

Question

i have a program that proccess a huge database, it's around 4,8k records.. i'm populating the data into two 2d array, comparing it, then insert the result into database in form of new table..

and i also have two set of database, the smaller one with only 40 records and the biggest one with 4,8k records.. when i tested the smaller database, program runs correctly

but when i'm using the biggest one, the error occured.

i've already try to identified the connection leak, but it turned out that there isn't any.

i've already closed all the connection properly with dispose or close

but the first error message was the infamous ** timeout period period. . . max pool size was reached**

later after i dismiss the error message, another error message popped out the connection must be valid and open though i've already re-checked that problem

i'm using vs 2005 and i search to google that this error was the bug that occured in vs 2005, but i couldn't never properly find the solution that microsoft provided

so i'm thinking is there a way to fix this, like modified the connection function. what should i change in my code below?

Imports MySql.Data.MySqlClient
Public Class koneksi
  Private Conn As MySqlConnection = Nothing
  Public Function konek() As MySqlConnection
     Dim ConnString As String
     ConnString = ";server=localhost;user=root;password=;database=skripsi2;"
     Try
         Conn = New MySqlConnection(ConnString)
         Conn.Open()

     Catch ex As Exception
        MessageBox.Show("Koneksi Error " + ex.Message)
     End Try
     Return Conn
  End Function

End Class

*note: what i've already done yet is set the connection timeout to 0/300. set the pooling to true and give the max pool size = 200

but all that attempt was in vain.. nothing worked..

so could you please tell me how to fix this? i 've worked my brains out over one week and still got no solution for this

i'm using vb.net 2005 and mysql as database

'UPDATE CODE EXAMPLE CALLING THE CONNECTION FUNCTION

            Dim resnode1 As String = "..."
            CMD_resnode = New MySqlCommand(resnode1, conn.konek)
            resnode = CMD_resnode.ExecuteReader()

            Dim getmaxrow2 As String = "..."
            CMD_maxrow2 = New MySqlCommand(getmaxrow2, conn.konek)
            maxrow2 = Convert.ToInt32(CMD_maxrow2.ExecuteScalar())
            CMD_maxrow2.Connection.Dispose()

            maxrow22 = maxrow2 - 1

            IF....THEN
            ....
            resnode.read()
            ....
            END IF

            CMD_resnode.connection.dispose()

Answer 1

From my experience the timeout error is hardly about the connection. It rather has to do with the command the data adapter creates before it executes. If you're creating a data adapter by passing a sql query in a String then the adapter will create its own command. You have to get that command object from the SelectCommand property of the adapter and set its CommandTimeout.

Try:

mydataAdaptor.SelectCommand.CommandTimeout = xxxx

xxxx being some long time interval such as 60000 (1 hour)

Answer 2

As per the this stack-overflow thread . Its good practice to use close() method.

        Dim resnode1 As String = "..."
        CMD_resnode = New MySqlCommand(resnode1, conn.konek)
        resnode = CMD_resnode.ExecuteReader()

        Dim getmaxrow2 As String = "..."
        CMD_maxrow2 = New MySqlCommand(getmaxrow2, conn.konek)
        maxrow2 = Convert.ToInt32(CMD_maxrow2.ExecuteScalar())
        CMD_maxrow2.Connection.Close()

        maxrow22 = maxrow2 - 1

        IF....THEN
        ....
        resnode.read()
        ....
        END IF

        CMD_resnode.connection.Close()

If still facing the problem, try running SQL Queries on command line and check the execution time of your query.

Answer 3

It is difficult to diagnose this kind of problems, however, I can see in your example code the usual pattern with connections beeing created and leaked because they are not properly closed and returned to the connection pool. Your code force the creation of a new connection every time you call conn.konek and you seems to forget to close and dispose the instance created in the call. Until the pool is exausted and you receive the fatal error.

A simple approach is to check your code where you call the conn.konek method and change it to something like this

' Just one call to konek and keep the instance for using in the enclosed block '
Using con = conn.konek
    Dim resnode1 As String = "..."
    CMD_resnode = New MySqlCommand(resnode1, con)
    resnode = CMD_resnode.ExecuteReader()
    ....
    Dim getmaxrow2 As String = "..."
    CMD_maxrow2 = New MySqlCommand(getmaxrow2, con)
    maxrow2 = Convert.ToInt32(CMD_maxrow2.ExecuteScalar())
    maxrow22 = maxrow2 - 1

' Here the connection is closed and disposed.
End Using

In this way you create only one connection and encapsulate it in a Using Statement, at the End Using point the connection is automatically closed and disposed ALSO IF THE block of code internal at the using block raises an exception.

If you need to keep the DataReader open while you execute other commands then you could try

' Just one call to konek and keep the instance for using in the enclosed block '
Using con = conn.konek
    Dim resnode1 As String = "..."
    CMD_resnode = New MySqlCommand(resnode1, con)
    resnode = CMD_resnode.ExecuteReader()
    ....
    Using con1 = conn.konek
        Dim getmaxrow2 As String = "..."
        CMD_maxrow2 = New MySqlCommand(getmaxrow2, con1)
        maxrow2 = Convert.ToInt32(CMD_maxrow2.ExecuteScalar())
        maxrow22 = maxrow2 - 1
        ' Here the connection for the command is closed and disposed.

    End Using

' Here the connection for the datareader is closed and disposed.
End Using