0

I am rebuilding a system that has developed over the past five years, initially started with Classic ASP I am now moving the whole thing to ASP.NET (using VB.NET)

The system has been plagued by a persistent problem of having too many data connections open, resulting in periodic "max_connections is exceeeded" errors.

Having asked my server hosts many times about this, I am still having troubles so thought I'd open it up to SO.

I am currently opening connections as follows:

 Dim sql = "SQL SELECT"
 Dim oConnection As OdbcConnection = New OdbcConnection(ConfigurationManager.ConnectionStrings("dbConn").ConnectionString)
 oConnection.Open()
 openDatabase = New OdbcCommand(sql, oConnection)

The connection string is contained with the web.config file and looks like this

<add name="dbConn" connectionString="DRIVER={MySQL ODBC 3.51 Driver}; SERVER=mysql.dc-servers.com; DATABASE=dbName; UID=user; PASSWORD=pwd; OPTION=3; pooled=true" providerName="System.Data.Odbc"/>

I am getting actual data using a DataReader like this:

 Dim objDataReader As OdbcDataReader
 objDataReader = openDatabase.ExecuteReader(CommandBehavior.CloseConnection)

 While (objDataReader.Read())
        // do stuff
 End While

 objDataReader.Close()

It is my understanding that, assuming there are no errors with the data or database (CommandBehavior.CloseConnection) should ensure that when the line objDataReader.Close() closes the Reader, it should close the connection too (or return it to the pool)

I'm getting these max_connections errors though.

Looking at the open processes on MySQL admin, I can see that the connections are not being closed.

I have minimal understanding of the connection process I am afraid and very limited access to the MySQL server, so struggling to find out what is going on here... unless of course... I have misunderstood something, which I hope is the case and you guys can point me to it!

Jamie Hartnoll
  • 7,231
  • 13
  • 58
  • 97

1 Answers1

0

I do not know a lot about the DataReader but it seems as you need to find this leak. I would suggest first to manually set the pool size in your connection string using: Max and Min Pool Size attributes see for details: http://dev.mysql.com/doc/refman/5.0/en/connector-net-connection-options.html

I would also suggest monitoring the opening and closing of connection with the pooling set to off to see what actually happens to the connection lifecycle. You can use in the mysql console 

show processlist;

To see the current connections and 

show global status;

To monitor all the db attributes. I would also suggest reading this article from mysql explaining the "too many connection problem"

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Ensure that mysql configuration are in line with your application configuration to make sure that you do not allow too many connection than the server actually allows.

Cheers,

Stainedart
  • 1,929
  • 4
  • 32
  • 53
  • Thanks for the tips. Setting the Max/Min pools sizes made no difference at all though. I am using MySQL PHP admin to view what I can with the connections and the Process List gives me no information about what the connections are doing, or why they are open so it's hard to tell even which "open database" events are causing it. I think I should just manually close all connections and give up with `(CommandBehavior.CloseConnection)` which I thought was supposed to do that for me! – Jamie Hartnoll Dec 23 '11 at 18:27
  • Have you tried performing a single operation with the pool turned off to see if the connection that operation uses gets closed ? Personally I always close explicitly my connections to avoid these issue. Make sure you turn off the pool to perform this test because the pool will never really close the connection it will only save it for the next transaction. – Stainedart Dec 23 '11 at 18:56
  • Yeh, initially when I began testing the pool was off, I only added it to see if this would help! Didn't seem to make any discernible difference! – Jamie Hartnoll Dec 23 '11 at 22:19