Mysql connections not closing with VB.NET application

Question

that's my first time here, I'm going mad because of this problem I have:

I'm developing a windows form application with VB.NET using multiple types of connections(mysql,odbc and SQL server) everything works fine until I get into MySQL.. MySQL server is a physical windows 7 pc, I connect to it through IPSEC VPN TUNNEL.

I need to perform 2 MySQL connections every x seconds, if I get some type of result after the first connection then I'll open the second one, and so on every x seconds(that's all wrote in my timer.tick event handler). The problem is that quite often some connections on MySQL server keep staying alive(ESTABLISHED) on MySQL server and I can't find out why... code looks fine, there are both open and close methods declared at the right time, I've also tried Dispose,ClearPool and ClearAllPools methods but I keep having those connections up until I close my program or it reaches connection limit.

Here's the code:

Class connection:

   Public Sub connMySQL()
    Dim connstring As String
    Try
        If stabilimento = "1PR" Then
            If cesoia = "" Then
                connstring = "server=192.168.123.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
            Else
                connstring = "server=192.168.123.253;userid=xx;password=xx;database=xx;Connect Timeout=30"
            End If

        End If
        If stabilimento = "2PR" Then
            If cesoia = "" Then
                connstring = "server=192.168.1.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
            Else
                connstring = "server=192.168.123.253;userid=root;password=xx;database=xx;Connect Timeout=30"
            End If
        End If
   
        conMySql = New MySqlConnection(connstring)
        If conMySql.State = ConnectionState.Closed Then

            conMySql.Open()
              
        End If

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Class where the iteration is performed:

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

   connMySQL()
   comm = New MySqlCommand("SELECT count_1,count_2,start_stop,data_ora,id FROM plc_contatori where plc_nome='" + plc_nome + "' and data_ora > '" + data_ieri.ToString("yyyy/MM/dd") + "' order by data_ora desc limit 1", conMySql)

   dr = comm.ExecuteReader()
   While (dr.Read())
       count_1(0) = dr.GetValue(0)

       start_stop(0) = dr.GetValue(2)
       data_ora(0) = dr.GetValue(3)
       If id <> dr.GetValue(4) And count_2(0) <> dr.GetValue(1) Then
           id = dr.GetValue(4)
           count_2(0) = dr.GetValue(1)
       Else
           Exit Sub
       End If
   End While

   dr.Close()
   dr.Dispose()
   conMySql.Close()
   conMySql.Dispose()
   conMySql.ClearPool(conMySql)
   conMySql.ClearAllPools()

   If Not conMySql Is Nothing Then conMySql = Nothing
   comm.Dispose()

        
   If start_stop(0) = 1 Then
       Exit Sub
   End If
       

   Dim dum_count_2 As Integer = count_2(0) - 1       
   connMySQL()
   comm = New MySqlCommand("select count_1,count_2,start_stop,data_ora from plc_contatori where  plc_nome='" + plc_nome + "' and data_ora > '" + data_ieri.ToString("yyyy/MM/dd") + "'  AND count_2=" + dum_count_2.ToString + " ORDER BY data_ora desc limit 1", conMySql)
   dr = comm.ExecuteReader()
   While (dr.Read())
       count_1(1) = dr.GetValue(0)
       count_2(1) = dr.GetValue(1)
       start_stop(1) = dr.GetValue(2)
       data_ora(1) = dr.GetValue(3)
   End While

   dr.Close()
   dr.Dispose()
   conMySql.Close()
   conMySql.Dispose()
   conMySql.ClearPool(conMySql)
   conMySql.ClearAllPools()
   If Not conMySql Is Nothing Then conMySql = Nothing

   comm.Dispose()


   If count_1(0) = count_1(1) And start_stop(1) <> 1 And count_2(0) <> count_2(1) Then
      'sub that reads some values from an odbc connection
       CheckFermo()

   End If

End Sub

NOTE that variables that I have not declared in this portion of code are declared in the public class of the form.

I'm wondering what could be wrong... maybe the 2nd connection is being established before the 1st one gets closed by the server?

Answer 1

I changed the connMySQL method to a function that returns the connection string. I have declared several variables so the code makes sense. I made several assumptions about datatypes. You may have to change this back to String and VarChar if these values are actually stored as strings. (I hope they are not)

You could use a single connection but all the assignments and comparisons would occur with an open connection.

Private stabilimento As String
Private cesoia As String
Public Function connMySQL() As String
    Dim connstring As String
    Select Case True
        Case stabilimento = "1PR" And cesoia = ""
            connstring = "server=192.168.123.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
        Case stabilimento = "2PR" And cesoia = ""
            connstring = "server=192.168.1.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
        Case Else
            connstring = "server=192.168.123.253;userid=root;password=xx;database=xx;Connect Timeout=30"
    End Select
    Return connstring
End Function

Private count_1(10) As Integer
Private count_2(10) As Integer
Private start_stop(10) As Integer
Private data_ora(10) As Date
Private id As Integer
Private plc_nome As String
Private data_ieri As Date

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Using dt As New DataTable
        Using cn As New MySqlConnection(connMySQL),
            comm = New MySqlCommand("SELECT count_1,count_2,start_stop,data_ora,id 
                                    FROM plc_contatori 
                                    where plc_nome= @plcNome and data_ora > @dataOra 
                                    order by data_ora desc 
                                    limit 1", cn)
            comm.Parameters.Add("plcNome", MySqlDbType.VarChar).Value = plc_nome
            comm.Parameters.Add("dataOra", MySqlDbType.Date).Value = data_ieri '.ToString("yyyy/MM/dd")
            cn.Open()
            Using dr = comm.ExecuteReader
                dt.Load(dr)
            End Using 'closes and disposes reader
        End Using 'closes and dispose connection and command
        count_1(0) = CInt(dt(0)(0))
        start_stop(0) = CInt(dt(0)(2))
        data_ora(0) = CDate(dt(0)(3))
        If id <> CInt(dt(0)(4)) AndAlso count_2(0) <> CInt(dt(0)(1)) Then
            id = CInt(dt(0)(4))
            count_2(0) = CInt(dt(0)(1))
        Else
            Exit Sub
        End If
    End Using 'disposes DataTable
    If start_stop(0) = 1 Then
        Exit Sub
    End If


    Dim dum_count_2 As Integer = count_2(0) - 1
    Using dt As New DataTable
        Using cn As New MySqlConnection(connMySQL),
            comm As New MySqlCommand("select count_1,count_2,start_stop,data_ora 
                                        from plc_contatori 
                                        where  plc_nome= @plcNome 
                                        and data_ora > @dataOra 
                                        AND count_2= @count2 
                                        ORDER BY data_ora desc 
                                        limit 1", cn)
            comm.Parameters.Add("@plcNome", MySqlDbType.VarChar).Value = plc_nome
            comm.Parameters.Add("@dataOra", MySqlDbType.Date).Value = data_ieri '.ToString("yyyy/MM/dd")
            comm.Parameters.Add("@count2", MySqlDbType.Int32).Value = dum_count_2 '.ToString
            cn.Open()
            Using dr = comm.ExecuteReader()
                dt.Load(dr)
            End Using
        End Using
        count_1(1) = CInt(dt(0)(0))
        count_2(1) = CInt(dt(0)(1))
        start_stop(1) = CInt(dt(0)(2))
        data_ora(1) = CDate(dt(0)(3))
    End Using
    If count_1(0) = count_1(1) AndAlso start_stop(1) <> 1 AndAlso count_2(0) <> count_2(1) Then
        'sub that reads some values from an odbc connection
        CheckFermo()
    End If
End Sub