I am trying to create a very simple aspx page that retrieves some data from a mysql database. The page is created without problems. (aspx contains only the default form and div for printing some data)
Default.aspx.vb:
Imports System.Configuration Imports System.Data Imports MySql Imports MySql.Data Imports MySql.Data.MySqlClient Partial Class _Default Inherits System.Web.UI.Page Private cnstr As String = ConfigurationManager.ConnectionStrings.Item("thisdb").ConnectionString Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load Dim cn As New MySqlConnection(cnstr) Dim cmd As New MySqlCommand("SELECT user_firstname,user_lastname FROM tb_users;", cn) cmd.CommandType = CommandType.Text Dim dt As DataTable dt = GetDataTableMySQL(cmd) If dt.Rows.Count > 0 Then testdiv.InnerHtml = dt.Rows(0).Item("user_firstname") testdiv.InnerHtml += "<br/>" & dt.Rows(0).Item("user_lastname") End If dt.Dispose() cmd.Dispose() cn.Dispose() End Sub Private Function GetDataTableMySQL(ByVal cmd As MySqlCommand) As DataTable Dim da As New MySqlDataAdapter() Dim dt As New DataTable() Try cmd.Connection.Open() da.SelectCommand = cmd da.Fill(dt) Return dt Catch ex As MySqlException Throw ex Catch ex As Exception Throw New Exception(ex.Message) Finally cmd.Connection.Close() da.Dispose() End Try End Function End Class
Web.config:
<?xml version="1.0"?> <configuration> <system.web> <compilation debug="true" strict="false" explicit="true" targetFramework="4.0"> <assemblies> <add assembly="MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/> </assemblies> </compilation> <customErrors mode="Off"/> </system.web> <connectionStrings> <add name="thisdb" connectionString="Server=localhost;Database=mydatabase;Uid=mydbuser;Pwd=dbpasswd;CharSet=UTF8; "/> </connectionStrings> </configuration>
When I look at the page URL, it works fine. If I continue to refresh the page, everything works without problems.
Now comes the annoying problem ...
If I leave the inactivity page for about 3-4 minutes and then click Refresh, I always get the following exception:
The specified key is not in the dictionary.
Description: An unhandled exception occurred during the execution of the current web request. View the stack trace for error information and where it originated in the code.
Exception Details: System.Exception: This key was not in the dictionary.
Source Error:
An unhandled exception was thrown during the execution of the current web request. Information about the origin and location of the exception can be identified using the exception stack trace below.
Stack trace:
[Exception: this key is not in the dictionary.]
_Default.GetDataTableMySQL (MySqlCommand cmd) +236 _Default.Page_Load (object sender, EventArgs e) +112 System.Web.UI.Control.OnLoad (EventArgs e) +91
System.Web.UI.Control.LoadRecursive () +74
System.Web.UI.Page.ProcessRequestMain (Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
Version Information: Microsoft.NET Framework Version: 4.0.30319; ASP.NET Version: 4.0.30319.272
If I click Refresh again, the page will be beautiful again ... etc.
After hours of searching the internet, the only thing that sounds related to my problem is related to mysql Connector / Net:
The Connector / Net documentation says:
Starting with MySQL Connector / Net 6.2, there is a background operation that runs every three minutes and removes connections from the pool that have been idle (not used) for more than three minutes. Clearing the pool frees up resources both on the client side and on the server side. This is because on the client side, each connection uses a socket, and on the server side, each connection uses a socket and stream.
Prior to this change, connections were never removed from the pool, and the pool always contained the maximum number of open connections. For example, a web application that has reached a maximum of 1000 concurrent connection databases will consume 1000 threads and 1000 open sockets on the server without freeing these resources from the connection pool.
Please note that connections, regardless of how old they are, will not be closed if the number of connections in the pool is less than or equal to the value specified by the connection parameter of the Min Pool Size connection string.
OK Even if this is my problem, How can I connect → get data → disconnect?
Any ideas? It really drives me crazy!
UPDATE After the @Andrews suggestion, I changed the GetDataTableMySQL function as follows:
Private Function GetDataTableMySQL(ByVal cmd As MySqlCommand) As DataTable Dim dt As New DataTable Using da = New MySqlDataAdapter(cmd) da.Fill(dt) End Using Return dt End Function
(This did not solve the problem, but I think it is useful to show what the code looks like now)
Exception stack trace changed to the following:
[KeyNotFoundException: this key was not in the dictionary.] System.Collections.Generic.Dictionary`2.get_Item (TKey key) +9624829
MySql.Data.MySqlClient.CharSetMap.GetCharacterSet (DBVersion version, String CharSetName) +23
MySql.Data.MySqlClient.CharSetMap.GetEncoding (DBVersion version, String CharSetName) +47
MySql.Data.MySqlClient.Driver.Configure (connection MySqlConnection) +510 MySql.Data.MySqlClient.MySqlConnection.Open () +418 System.Data.Common.DbDataAdapter.FillInternal (DataSet dataset, DataTable Intr32 [Data] Table Intable] maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +123
System.Data.Common.DbDataAdapter.Fill (DataTable [] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +166 System.Data.Common.DbDataAdapter.Fill (DataTable dataTable) +115 _Default.GetDataTableQymdm ) +86
_Default.Page_Load (object sender, EventArgs e) +112 System.Web.UI.Control.OnLoad (EventArgs e) +91
System.Web.UI.Control.LoadRecursive () +74
System.Web.UI.Page.ProcessRequestMain (Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
UPDATE 2 After reading Link to connection / network connection string parameters I checked my connection string with the option:
Pooling=false;
and then I tested changing the union option:
Pooling=no;
Checking the pool settings, the page never works! I get the exception "Each time this key was not present in the dictionary."