I am facing issues with one MySQL temporary table, which was created in an Winforms class which was closed by user, but still in memory, because CancellationToken is not triggered yet, because there is a long running query. In this situation I am opening another instance of the same Winform with supposedly new MySQL session, but it seems that this is not exactly so, because when trying to create a new temp table there is an error that it already exists.

My Winforms app is using .Net7. There is one StartForm with Button1, which opens a child form (Form1.Show()) from where a process of charts creation is triggered after clicking a Button2. This button is operating in Async-Await scenario with defined CancellationToken. A temp table is created and after that there is another MySQL query, which uses this temp table for further processing. This query produces a datatable and takes longer time and there is a chance that the user closes the Form1 in order not to wait for the query to finish. For cases like that there is a FormClosing event, which cancels the CancellationToken. This token is passed to the Task running the query and also to the functions from Queries class, responsible for queries executions. Filling the MySQLDataAdapter and the query execution are done Async with Await Adap.FillAsync(dTab, token) and Await SQL.ExecuteNonQueryAsync(token).

In normal circumstances when the user does not close the form the long running query is executed, the temp table is dropped and charts are depicted.

If the user closes the form the token is canceled, the long running query is being waited to be finished, then the charts part is not executed because there is an If clause checking if the Winform was disposed. In this case an exception OperationCanceledException is caught and then the temp table is dropped and the MySQL connection is closed.

But if directly after Form1 was closed while the long-running query is still running the user opens another Form1 from StartForm and hits Button2 there is a great chance that an error occurs stating that temp table already exists. This is despite that a new MySQL connection was defined (Dim MysqlConn As New MySqlConnection) and opened. In my understanding this is supposed to be a new session, so the temp table from the previous session, which was not yet closed has nothing to do with the new session. Apparently I am doing somewhere something wrong and any help will be appreciated.

A simplified code is shown below.

Public Class StartForm
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Form1.Show()        
    End Sub
End Class

Public Class Form1
    Public connStr As String = "some connection parameters"

    Private cts As CancellationTokenSource
    
    Public Async Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
        Dim ret as boolean = false
        Dim TabResult as new DataTable
    
        cts = New CancellationTokenSource()
        Dim token As CancellationToken = cts.Token

        Dim getStats As New Queries
        getStats.connStr = connStr

        Dim MysqlConn As New MySqlConnection
        getStats.ConnOpen(MysqlConn)    
    
        Try         
            
            'Create temporary table
            ret = Await Task.Run(Function() getStats.ExecQueryFakeReturnNoSession("CREATE TEMPORARY TABLE tmptab", MysqlConn, token), token)
            
            'Long running query using this temporary table
            TabResult = Await Task.Run(Function() getStats.ExecQueryParNoSession("select query", MysqlConn, token), token)
            
            'Usually at this point the Winform is being closed by the user if query runs for too long
            
            'drop temporary table
            ret = Await Task.Run(Function() getStats.ExecQueryFakeReturnNoSession("DROP TEMPORARY TABLE tmptab", MysqlConn, token), token)
            
            getStats.ConnClose(MysqlConn)
            
            If Me.IsDisposed = False Then
            
                'Create charts based on result table TabResult
            
            End if
            
        Catch ex As OperationCanceledException
            getStats.ExecQueryNoSession("DROP TEMPORARY TABLE IF EXISTS tmptab;", MysqlConn)
            getStats.ConnClose(MysqlConn)

        Catch ex As Exception
  
        End Try

        cts = Nothing

    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
       If cts IsNot Nothing Then
           cts.Cancel()
       End If
    End Sub
    
End Class

Public Class Queries
    Public Property connStr As String

    Public Sub ConnOpen(MysqlConn As MySqlConnection)
        Try
            MysqlConn.ConnectionString = connStr
            MysqlConn.Open()
        Catch Err As MySqlException
            MsgBox("Error: " & Err.Number & " - " & Err.Message)
        End Try
    End Sub

    Public Sub ConnClose(MysqlConn As MySqlConnection)
        Try
            MysqlConn.Close()
            MysqlConn.Dispose()
        Catch Err As MySqlException
            MsgBox("Error: " & Err.Number & " - " & Err.Message)
        End Try
    End Sub
    
    Public Async Function ExecQueryParNoSession(queryString As String, MysqlConn As MySqlConnection, token As CancellationToken) As Task(Of DataTable)
        Dim dTab As New DataTable
        Dim SQL As New MySqlCommand
        Dim Adap As New MySqlDataAdapter

        Try
            SQL.Connection = MysqlConn
            SQL.CommandText = queryString

            Adap.SelectCommand = SQL
            Await Adap.FillAsync(dTab, token)

            token.ThrowIfCancellationRequested()

        Catch Err As MySqlException
            MsgBox("Error: " & Err.Number & " - " & Err.Message)
        End Try

        Return dTab
        
    End Function
    
    Public Async Function ExecQueryFakeReturnNoSession(queryString As String, MysqlConn As MySqlConnection, token As CancellationToken) As Task(Of Boolean)
        Dim SQL As New MySqlCommand
        Dim ret As Boolean
        
        Try
            SQL.Connection = MysqlConn
            SQL.CommandText = queryString

            Await SQL.ExecuteNonQueryAsync(token)
            ret = True

            token.ThrowIfCancellationRequested()

        Catch Err As MySqlException
            MsgBox("Error: " & Err.Number & " - " & Err.Message)
            ret = False 
        End Try

        Return ret

    End Function
    
End Class