在MySQL的C＃的连接太多 [英] C# Too many connections in MySQL

问题描述

我尝试在的MySQL 在一个表上运行 SELECT 和我得到这个错误：

 在'/ MyApp的应用程序的服务器错误。
太多联系
描述：在当前Web请求的执行过程中发生未处理的异常。请查看有关错误的详细堆栈跟踪信息，以及它起源于code。

异常详细信息：MySql.Data.MySqlClient.MySqlException：太多连接

源错误：

当前Web请求的执行过程中生成了未处理的异常。有关异常原因和位置的信息可以使用下面的异常堆栈跟踪信息确定。

堆栈跟踪：


[个MySqlException（0X80004005）：连接数过多]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket（）517
   MySql.Data.MySqlClient.NativeDriver.Open（）702
   MySql.Data.MySqlClient.Driver.Open（）+245
   MySql.Data.MySqlClient.Driver.Create（MySqlConnectionStringBuilder设置）+297
   MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection（）+18
   MySql.Data.MySqlClient.MySqlPool.GetPooledConnection（）403
   MySql.Data.MySqlClient.MySqlPool.TryToGetDriver（）+228
   MySql.Data.MySqlClient.MySqlPool.GetConnection（）+106
   MySql.Data.MySqlClient.MySqlConnection.Open（）1468


版本信息：Microsoft .NET Framework版本：4.0.30319; ASP.NET版本：4.0.30319.1
 

我在IIS上与.NET和C＃运行它。 任何想法如何，我可以解决这个问题？

这是例子，如何我做选择：

MySqlDataReader将MSDR;

 的MySqlConnection连接=新的MySqlConnection（connectionStringMySql）;
    的MySqlCommand CMD =新的MySqlCommand（）;

    字符串命令行=SELECT * FROM表，其中有效= 1;

    命令行= commandLine.Remove（commandLine.Length  -  3）;
    cmd.CommandText =命令行;

    cmd.Connection =连接;
    cmd.Connection.Open（）;

    MSDR = cmd.ExecuteReader（）;

    而（msdr.Read（））
    {
        //读取数据
    }

    msdr.Close（）;
    cmd.Connection.Close（）;
 

这是我如何删除：

 的MySqlConnection连接=新的MySqlConnection（connectionStringMySql）;
                的MySqlCommand CMD =新的MySqlCommand（）;

                cmd.Connection =连接;
                cmd.Connection.Open（）;

                字符串命令行= @DELETE FROM表WHERE ID = @ ID;;

                cmd.CommandText =命令行;

                cmd.Parameters.AddWithValue（@ ID，slotID用于）;

                cmd.ExecuteNonQuery（）;
                cmd.Connection.Close（）;
 

这是我如何插入：

 的MySqlConnection连接=新的MySqlConnection（connectionStringMySql）;
                的MySqlCommand CMD =新的MySqlCommand（）;

                cmd.Connection =连接;
                cmd.Connection.Open（）;

                字符串命令行= @INSERT INTO表（ID，星期，开始，结束）值+
                    （@ ID，@weekday，@启动，@end）;;

                cmd.CommandText =命令行;

                cmd.Parameters.AddWithValue（@身份证，身份证）;
                cmd.Parameters.AddWithValue（@平日，item.weekday）;
                cmd.Parameters.AddWithValue（@启动，新的时间跨度（item.starthour，item.startmin，0））;
                cmd.Parameters.AddWithValue（@结束，新的时间跨度（item.endhour，item.endmin，0））;

                cmd.ExecuteNonQuery（）;
                长ID = cmd.LastInsertedId;
                cmd.Connection.Close（）;
                返回ID;
 

解决方案

所有上面的例子显示相同的弱点。你不使用 using语句，这将确保PROPERT关闭和配置连接等一次性物品。如果您的某个语句或多个抛出一个异常，code，它关闭不执行的连接，你可以用过多的连接错误终止

例如

 字符串命令行=SELECT * FROM表，其中有效= 1;
命令行= commandLine.Remove（commandLine.Length  -  3）;
使用（的MySqlConnection连接=新的MySqlConnection（connectionStringMySql））
使用（的MySqlCommand CMD =新的MySqlCommand（命令行，连接））
{
    connect.Open（）;
    使用（SqlDataReader的MSDR = cmd.ExecuteReader（））
    {
        而（msdr.Read（））
        {
            //读取数据
        }
    }
} //下面的连接将被关闭和处置。 （和命令也）
 

I try to run SELECT on a table in MySql and i get this error:

    Server Error in '/MyApp' Application.
Too many connections
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Too many connections

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[MySqlException (0x80004005): Too many connections]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +517
   MySql.Data.MySqlClient.NativeDriver.Open() +702
   MySql.Data.MySqlClient.Driver.Open() +245
   MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) +297
   MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() +18
   MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() +403
   MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() +228
   MySql.Data.MySqlClient.MySqlPool.GetConnection() +106
   MySql.Data.MySqlClient.MySqlConnection.Open() +1468


Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1 

I run it on iis with .net and C#. Any idea how i can fix this problem?

This is for example how i make select:

MySqlDataReader msdr;

    MySqlConnection connect = new MySqlConnection(connectionStringMySql);
    MySqlCommand cmd = new MySqlCommand();

    string commandLine = "SELECT * FROM Table WHERE active=1;

    commandLine = commandLine.Remove(commandLine.Length - 3);
    cmd.CommandText = commandLine;

    cmd.Connection = connect;
    cmd.Connection.Open();

    msdr = cmd.ExecuteReader();

    while (msdr.Read())
    {
        //Read data
    }

    msdr.Close();
    cmd.Connection.Close(); 

This is how i Delete:

                MySqlConnection connect = new MySqlConnection(connectionStringMySql);
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = connect;
                cmd.Connection.Open();

                string commandLine = @"DELETE FROM Table WHERE id=@id;";

                cmd.CommandText = commandLine;

                cmd.Parameters.AddWithValue("@id", slotId);

                cmd.ExecuteNonQuery();
                cmd.Connection.Close();

This is how i insert:

MySqlConnection connect = new MySqlConnection(connectionStringMySql);
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = connect;
                cmd.Connection.Open();

                string commandLine = @"INSERT INTO Table (id, weekday, start, end) VALUES" +
                    "(@ id, @weekday, @start, @end);";

                cmd.CommandText = commandLine;

                cmd.Parameters.AddWithValue("@ id", id);
                cmd.Parameters.AddWithValue("@weekday", item.weekday);
                cmd.Parameters.AddWithValue("@start", new TimeSpan(item.starthour, item.startmin, 0));
                cmd.Parameters.AddWithValue("@end", new TimeSpan(item.endhour, item.endmin, 0));

                cmd.ExecuteNonQuery();
                long id = cmd.LastInsertedId;
                cmd.Connection.Close();
                return id;

解决方案

All the examples above show the same weakness. You don't use the using statement that will ensure the propert closing and disposing of the connection and other disposable objects. If one or more of your statements throw an exception, the code that close the connection is not executed and you could end with the too many connections error

For example

string commandLine = "SELECT * FROM Table WHERE active=1";
commandLine = commandLine.Remove(commandLine.Length - 3);
using(MySqlConnection connect = new MySqlConnection(connectionStringMySql))
using(MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
    connect.Open();
    using(SqlDataReader msdr = cmd.ExecuteReader())
    {
        while (msdr.Read())
        {
            //Read data
        }
    }
} // Here the connection will be closed and disposed.  (and the command also)

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