Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Feb 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem With Closing MySQL Connections

    I am having a real problem with an ASP application I am building. The main page is forced to connect to 14 different tables in 2 different databases (it's checking multiple news sources for updates). Once the site moved to production, during peaks times, we are getting "Too Many Connections" errors. I believe I've properly closed all the connections.

    I run "show processlist" from the MySQL command line prompt and I discover that all the connections are in sleep mode. So, they don't seem to be closed correctly, though closing a connection isn't rocket science and I can't for the life of me see what could possibly be wrong.

    I cannot post my actual code here due to security concerns by my company, but here is the basic structure.

    -default.asp consists of three includes. {header.asp / body.asp / footer.asp}

    -I open my connections in the header.asp file like so:

    Code:
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
    Set objConn = objConn.Execute(SELECT * FROM tablexxx)
    
    Set objConn2 = Server.CreateObject("ADODB.Connection")
    objConn2.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
    Set objConn2 = objConn2.Execute(SELECT * FROM tablexxx)
    
    Set objConn3 = Server.CreateObject("ADODB.Connection")
    objConn3.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
    Set objConn3 = objConn3.Execute(SELECT * FROM tablexxx)
    
    etc....
    I then output my body, and then the footer. It is within the footer that I close my connections, like so:

    Code:
    objConn.Close
    Set objConn = Nothing
    
    objConn2.Close
    Set objConn2 = Nothing
    
    objConn3.Close
    Set objConn3 = Nothing
    Any ideas what I could be doing wrong? Any help or advice would be greatly appreciated.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Are you making sure to trap errors and close connections when they occur?
    Because if the code never hits the close statements, the connections might stay alive.
    I had this problem with one of my java apps.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    I'm sure someone will correct me if I'm wrong but I'm confused as to why you're doing this:
    Code:
    Set objConn = objConn.Execute(SELECT * FROM tablexxx)
    Its quite possible (even proabably ) that you know more about this than me but is it not more usual to have a connection object and a recordset object and then
    Code:
    set recordset = connection.execute(SQL)
    'do stuff with recordset
    recordset.close
    connection.close
    ...also why do you need 3 connections to get data from 2 databases?

  • #4
    New Coder
    Join Date
    Feb 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you making sure to trap errors and close connections when they occur?
    Because if the code never hits the close statements, the connections might stay alive.
    I had this problem with one of my java apps.
    I'm not sure what you mean by this. Since the close statements are held in the footer, and the footer is included in every page, wouldn't it guarentee that the connections are closed with every page load? An honest question, because I am obviously missing something somewhere.

    NancyJ, I have a typo there, I am connecting to 14 different databases to retrieve news from each independant source here on our local intranet.

  • #5
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Since the close statements are held in the footer, and the footer is included in every page, wouldn't it guarentee that the connections are closed with every page load?
    Honestly, with ASP, I am not positive. I don't like it for anything important, since error trapping is such a pain. (who invented on error resume next? I'd like to smack him or her!)
    I play with it and use it for small things.
    And I am in the process of converting an existing ASP app to ASP.NET (which is MUCH better)

    With JSP, nope. In my JSP app, if an error occurs that halts execution ("exceptions"), unless you use try/catch/finally and set connection close in the finally so it is always executed, the connection is not closed by the JSP app. The database was set to timeout connections after a certain amount of time had elapsed with no activity, but if a lot of users were on, that didn't always help.
    We ended up using a combo of error trapping as well as using connection pooling and a custom class that kept track of open connections.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    How high is your peak traffic? It takes 2-4 minutes for a connection port to become available again after being closed (when running on windows)
    If you're exceeding the number of connections allowed (check what the limit on the dbs are) in a 5 minutes period then it may be that there is nothing wrong with your code... though I still dont understand why you're using your connection object as a recordset

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @Spire2000,
    The problem is that you have too many connections opened for too long of a time. You really only need one and should close it after you get the data from each database. This is the way that I would do it.
    Code:
    <%
    Dim aryNews1, aryNews2, etc.
    Dim objConn, objRS, iRow, iCol
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRS = Server.CreateObject("ADODB.Recordset")
    'First database
    objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
    Set objRS = objConn.Execute("SELECT * FROM tablexxx")
    If NOT objRS.EOF AND NOT objRS.BOF Then
    	aryNews1 = objRS.GetRows
    	objRS.Close
    	objConn.Close
    End If
    'Second database
    objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
    Set objRS = objConn.Execute("SELECT * FROM tablexxx")
    If NOT objRS.EOF AND NOT objRS.BOF Then
    	aryNews2 = objRS.GetRows
    	objRS.Close
    	objConn.Close
    End If
    ...
    Set objRS = Nothing
    Set objConn = Nothing
    
    'To print out
    If IsArray(aryNews1) Then
    	For iRow = 0 To Ubound(aryNews1, 2)
    		For iCol = 0 To Ubound(aryNews1, 1)
    			Response.Write "..."
    		Next
    	Next
    End If
    ...
    %>
    Last edited by neocool00; 09-23-2005 at 07:57 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •