MySQL Bugs: #32258: DAO ODBC-DRIVER leaves connection open after using dbSQLPassThrough

Bug #32258	DAO ODBC-DRIVER leaves connection open after using dbSQLPassThrough
Submitted:	11 Nov 2007 0:26	Modified:	13 Nov 2007 21:31
Reporter:	Klaus Gössl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.21	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	DAO ODBCdbSQLPassThrough

Description:
When using dao to connect to mysql-server the connections sometimes does not get closed after closing the db object
This occures only when opening a recordset with the option dbSQLPassThrough.
Ending the application closes the remaining connection(s).

How to repeat:
Create a visual basic file with following content,
and call it repeatedly from the ide (the connections will stay open as long as the ide is open)

sub test()
dim db As Database
Dim ConnectionString As String

    ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                   "SERVER=localhost;" & _
                   "DATABASE=wwH;" & _
                   "USER=root;" & _
                   "PASSWORD=;" & _
                   "OPTION=" & (3) & ";"
        Set db = DBEngine.OpenDatabase("wwH", False, False, ConnectionString)

        Dim rsa As DAO.Recordset
        Set rsa = db.OpenRecordset("SELECT * FROM mwst", dbOpenSnapshot, dbSQLPassThrough)
        rsa.MoveFirst
        Do Until rsa.EOF
            Debug.Print rsa!satz
            rsa.MoveNext
        Loop
        rsa.Close
        Set rsa = Nothing
        
        DBEngine.Idle
        db.Close
        Set db = Nothing
end sub

Suggested fix:
propper disconnect/cleanup on db.close()

Hello Klaus,

many thanks for writing a bug report.
You wrote, that you use the MyODBC version 3.51.52, we don't have such a version and I suppose you meant 3.51.21. I'll hope, that is correct.

Two questions anyway:

1. how do you figure out, that the connection is alive after db.Close function?
2. Please, can you add the trace file.

- yes, I meant 3.51.21, your are right.
- To monitor the open connections there are several posibilities:
  - shell command "netstat"
  - use Mysql Administrator
  - use any msql-Frontend and query: "show full processlist;"

All of them show, that the connection does not get closed, when a recordset is opend with dbOpenSnapshot, dbSQLPassThrough

As the open connections accumulate, new connections are not possible, when the maximum (in mysql.ini file) is reached.

further investigations showed me, that the memory requirements off the application rise to. It seems that there is memory allocated but not freed (maybe the snapshot-recordset ?)

- As the server-process does not crash, I can not send a Stack-Trace
  If you desire any other logfile, please specify which one.

someone else seemed to have had a similar problem:
http://lists.mysql.com/myodbc/9409
but he removed the dbSQLPassThrough, which is not an options for me.

Klaus, there's not much we can do without DM trace file (Control Panel/Administrative tools/Data Sources (ODBC), Tracing tab, start tracing now). 	At the first glance I can say that we don't have any high-level interaction with DAO as you suspect. The connection is closed when DAO says so, not when we do. And I fail to see the relation to http://lists.mysql.com/myodbc/9409.

Klaus, it is known that DAO can appear to have blocked ODBC connection until QueryTimeout property of the database object (default = 60 seconds) expires. Do you see connection being alive after QueryTimeout passes? If I'm right DM ODBC trace will show error 3146 "ODBC Call Failed".

thank you for investigating this,
I did the tracing myself ( thank you for the information where to enable it)
and found out that it´s not the fault of the mysql-odbc-driver, but of jet
which is not correctly calling
 
SQLDisconnect/SQLFreeConnect on db.close

The options  "dbOpenSnapshot, dbSQLPassThrough" are not the right way to get completly rid of jet,
but using dbUseODBC as workspacetype seems to do the trick,
i am checking this ....

So no bug on your side, but again m1cr0§oft#! sorry for making you trouble ...

Hi Klaus. The main thing is that the problem is located, don't worry about troubles.

Thanks for your interest in MySQL.