MySQL Bugs: #74662: MySqlSimpleMembershipProvider keeps db connections open

Bug #74662	MySqlSimpleMembershipProvider keeps db connections open
Submitted:	2 Nov 2014 21:28	Modified:	18 Feb 2015 20:06
Reporter:	Marcel Wasserer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.5	OS:	Windows
Assigned to:	Francisco Alberto Tirado Zavala	CPU Architecture:	Any

Description:
MySql.Web.Security.MySqlSimpleMembershipProvider.ValidateUser does not close all its db connections.

How to repeat:
Call MySqlSimpleMembershipProvider.ValidateUser() repeatedly and get one more sleeping connection every time.

web.config for reference:
        <add name="MySqlSimpleMembershipProvider" type="MySql.Web.Security.MySqlSimpleMembershipProvider, MySql.Web, Version=6.9.3.0, Culture=neutral, PublicKeyToken=null" connectionStringName="DefaultConnection" autoGenerateTables="true" userTableName="UserProfile" userIdColumn="Id" userNameColumn="Email" passwordFormat="Hashed" />

Suggested fix:
The issue can easily be tracked down to a missing 'using' block in MySqlSimpleMembershipProvider.GetHashedUserPassword() - the code seems to be something like:

DataRow dataRow = new MySqlDatabaseWrapper(...).ExecuteQuerySingleRecord(...);

but it should be

using(var wrapper = new MySqlDatabaseWrapper(...)) { ... }

Hello Marcel Wasserer,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/Net 6.9.5 and EF Version : MySql.Data.Entity.EF6 (6.9.5.0) but Couldn't trace out any issue on db connections.
Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end.

Thanks,
Chiranjeevi.

Hello Chiranjeevi Battula,

thank you for the response.

I wrote a small console application, updated to connector 6.9.5 and noticed, that this is actually a race condition:
- SimpleMembershipProvider::GetHashedUserPassword() is the only method in this class, that does not dispose the created connected immediately with an 'using' statement
- Therefore MySqlConnectionWrapper instances are left over, if SimpleMembershipProvider::ValidateUser is called with an existing username
- Fortunately most of the time the MySqlConnectionWrapper instances are disposed by the garbage collector before the connection count gets critical
- but if the DB is fast and the garbage collection intervals get larger, the connection count explodes.

With the test below i get between 5 and 20 concurrent db connections on my local machine, probably because the app is small enough to allow quick garbage collection. The issue gets worth with larger projects. My production server exceeds the db connection limit (200) if ValidateUser is called about once per second.

By analyzing the method SimpleMembershipProvider::GetHashedUserPassword, it is quite obvious that the 'using' statement is missing.

--------
// console application test source, Visual Studio 2012, connector.net 6.9.5 (MySql.Data and MySql.Web)

using MySql.Web.Security;

namespace DbLeak
{
    class Program
    {
        private static MySqlSimpleMembershipProvider mProvider;

        private static void Main(string[] args)
        {
            mProvider = new MySqlSimpleMembershipProvider();
            mProvider.Initialize("MyTestProvider", new NameValueCollection
                {
                    {"connectionStringName", "DefaultConnection"},
                    {"userTableName", "UserProfile"}, 
                    {"userIdColumn", "Id"}, 
                    {"userNameColumn","Email"}
                });

            // create threads to get a more 
            for (var i = 0; i < 10; i++)
            {
                new Thread(Run).Start();
            }
        }

        private static void Run()
        {
            for (var i = 0; i < 1000; i++)
                mProvider.ValidateUser("validuser", "password");
        }
    }
}

---------
app.config:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="server=localhost;uid=client;pwd=password;database=database;" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

  <startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/></startup></configuration>

i accidentally posted an experiment with multiple threads - this one is the right test case that leads to 5-20 db connections where it should be only one in my view.

--------
// console application test source, Visual Studio 2012, connector.net 6.9.5 (MySql.Data and MySql.Web)

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Threading;
using MySql.Web.Security;

namespace DbLeak
{
    class Program
    {
        private static MySqlSimpleMembershipProvider mProvider;

        private static void Main(string[] args)
        {
            mProvider = new MySqlSimpleMembershipProvider();
            mProvider.Initialize("MyTestProvider", new NameValueCollection
                {
                    {"connectionStringName", "DefaultConnection"},
                    {"userTableName", "UserProfile"}, 
                    {"userIdColumn", "Id"}, 
                    {"userNameColumn","Email"}
                });

            for (var i = 0; i < 10000; i++)
                mProvider.ValidateUser("validuser", "password");
        }
    }
}

---------
app.config:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="server=localhost;uid=client;pwd=password;database=database;" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

  <startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/></startup></configuration>

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Net 6.9.6 release, and here's the changelog entry:

Repeatedly calling the "MySql.Web.Security.MySqlSimpleMembershipProvider.ValidateUser" 
method could leave open MySQL connections.

Thank you for the bug report.