关闭/合并MySQL ODBC连接 [英] Closing/Pooling MySQL ODBC connections

问题描述

我正在重建一个在过去五年中开发的系统，最初开始于经典ASP我现在将整个事情移动到ASP.NET（使用VB.NET）

系统一直困扰着数据连接打开太多的问题，导致定期的max_connections超越错误。

问我的服务器

我目前正在打开连接，如下所示：

  Dim sql =SQL SELECT
 Dim oConnection As OdbcConnection = New OdbcConnection（ConfigurationManager.ConnectionStrings（dbConn）。ConnectionString）
 oConnection.Open（）
 openDatabase = New OdbcCommand（sql，oConnection）
  

连接字符串包含在web.config文件中，如下所示

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

使用这样的DataReader：

  Dim objDataReader As OdbcDataReader 
 objDataReader = openDatabase.ExecuteReader（CommandBehavior.CloseConnection）
 
 While（objDataReader.Read（））
 // do stuff 
 End While 
 
 objDataReader.Close（）
 （CommandBehavior.CloseConnection） c $ c>应该确保当 objDataReader.Close（）关闭Reader时，它也应该关闭连接（或者返回到池）
 
 
 
我得到这些max_connections错误。
 
 
 
查看MySQL管理员的开放流程，我可以看到连接没有关闭。
 
 
 
我对连接过程的理解很少我恐怕和非常有限地访问MySQL服务器，所以很难找到这里发生了什么... 
解决方案
我不知道很多关于DataReader，但似乎你需要找到这个漏洞。我建议首先手动设置连接字符串中的池大小使用：最大和最小池大小属性请参阅有关详细信息： http://dev.mysql.com/doc/refman/5.0/en/connector-net-connection-options.html  
 
 
 
我还建议监控连接的打开和关闭与池设置为关闭，以查看连接生命周期实际发生了什么。你可以在mysql控制台中使用
  show processlist; 
  
查看当前连接和
 显示全局状态; 
  
监视所有数据库属性。我也建议从mysql阅读这篇文章解释太多的连接问题
 
 
 
  http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html   p> 
 
 
确保mysql配置符合您的应用程序配置，以确保您不允许太多的连接超过服务器实际允许的连接。
 
 
 
干杯，
 
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!
解决方案
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,

                        
这篇关于关闭/合并MySQL ODBC连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！