| Bug #12274 | dont'close the connection with ADODB in ASP | ||
|---|---|---|---|
| Submitted: | 29 Jul 2005 16:58 | Modified: | 10 Aug 2005 13:09 |
| Reporter: | Paolo Tozzo | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51 11-2 | OS: | Windows (Windows XP PRO) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[31 Jul 2005 5:25]
Vasily Kishkin
I was not able to reproduce the bug. Could you please say me what version of mysql ?
[31 Jul 2005 19:42]
Paolo Tozzo
Hello, my MYSQL version is 4.1.12a-nt my ODBC driver is 3.51.11-2 version
[1 Aug 2005 7:52]
Vasily Kishkin
I was able to reproduce the bug on Windows. MYSQL version is 4.1.14. ODBC driver is 3.51.11 Additional note: When server is shutting down I got the follow message: 050801 13:46:42 [Warning] mysqld-debug.exe: Forcing close of thread 5 user: 'root'
[4 Aug 2005 7:23]
Vasily Kishkin
I stated IIS on Windows XP and connect to MySQL server on Linux (Suse 9.3). The bug is still there.
[4 Aug 2005 7:57]
Paolo Tozzo
....mmm!! It's just a ODBC driver bug!!
[10 Aug 2005 13:09]
Sergey Vlasenko
Connection is not closed in given scenario due to using one of pooling types - oledb resource pooling or odbc connection pooling.
Connection & resource pooling are recommended techniques, especially for web applications, since they allow to serve multiple clients by opening minimum number of DB connections (less then clients) and saving time on creating connection to DB.
In given scenario connection will we closed 60 seconds after the last retrieval of .asp page. notice that multiple retrievals will not create additional connections, but will reuse initial one.
Pooling can be disabled if necessary in 2 stages:
1. Disable oledb resource pooling for MSDASQL provider (OLEDB provider for ODBC drivers). Set HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\OLEDB_SERVICES equal to 0xfffffffe.
After this pooling is totally controlled on ODBC level.
2. To disable ODBC connection pooling, open ODBC Data Source Manager (odbcad32.exe). Go to Connection pooling tab. Double click a driver in the list and disable pooling.
To check that pooling is disabled. Restart IIS and open the same .asp page. Connection to database will be closed immidiatelly after page is generated.
[10 Aug 2005 16:55]
Paolo Tozzo
OK, thank you
[5 Nov 2009 10:40]
Neil
I appear to be experiencing the same problems. Here is my set-up :
In our local office we have a custom developed C++ application, which connects to our MySQL server (externally hosted) using ODBC 3.51. When we close this application, the connection_id generated from this application is instantly closed.
However, on our website running IIS6 and also on IIS7 (hosted externally) connecting to our MySQL server, when a classic ASP page is opened the process list in MySQL shows a new connection. However even though we issue the close command in ASP, the connection remains open for up to 60 seconds.
My classic ASP Code is as follows :
Function OpenConnection()
OpenConnection = "DSN=MyDSN_SSL"
End Function
DSNTemp = OpenConnection()
Set Conn = Server.CreateObject("ADODB.Connection")
Set RecordsetTest = Server.CreateObject("ADODB.RecordSet")
response.write Now() & " connecting... <BR> Connected at : "
Conn.Open DSNTemp
response.write Now()
if Conn.errors.count = 0 then
response.write " - Connection Successful!"
else
response.write " - ERROR: Couldn't connect to database"
end if
RecordsetTest.open "SELECT CONNECTION_ID() As MySQLConnectionID" ,Conn 'query database for data
if not RecordsetTest.eof then
response.write "<br>" & RecordsetTest.fields("MySQLConnectionID").value & "<br>"
end if
RecordsetTest.close
set RecordsetTest = nothing
Conn.close
set Conn = nothing
The problem we have is that when we have lots of connections to our database which aren't closed - it appears to slow everything down.
How should I deal with this ? Should we make the registry change to polling as mentioned above ?
Description: Hello, in MYSQL administrator I found that the opened connections with ADODB, also with a simple page with the "open" statement, and the "close" statement are not closed, but they remain in state of "sleep". How to repeat: <% Dim CNN, ConnString,Connstring_log Connstring = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=mydb; Uid=root; Pwd=" set Con =Server.CreateObject("ADODB.Connection") Con.Open Connstring Con.close() set Con=Nothing %>