MySQL Bugs: #68217: Connection String Cache corrupted when using Multithreading + MySqlScript

Bug #68217	Connection String Cache corrupted when using Multithreading + MySqlScript
Submitted:	29 Jan 2013 11:15	Modified:	20 Feb 2013 2:04
Reporter:	André Morais	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.6.4	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	cache, Connection, multithreading, mysqlscript

Description:
When running a multithreaded service exception "The given key was not present in the dictionary" is raised with stack:

«   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at MySql.Data.MySqlClient.MySqlConnectionStringBuilder.get_AllowUserVariables()
   at MySql.Data.MySqlClient.MySqlScript.Execute()
   at Clevernet.Framework.DataAccess.MySqlDataAccessHandler.Execute(MySqlScript script)
»

How to repeat:
Run several threads that invoke the following method with the same connection string. 

public object Execute(MySqlScript script, string connectionString)
{
   object result = null;
   if (script == null)
   {
     throw new ArgumentNullException("command", "The following argument has not been specified: command.");
   }
   try
   {               
     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {                    
      connection.Open();                                 
      script.Connection = connection;                   
      result = script.Execute();     
     }
    }
    catch (Exception ex)
    {
      throw ex;
    }          
    return result;
}

Suggested fix:
If cache may get corrupted, simply check if key exists before requesting its value.
Following code would suffice for this key, although the other keys need the corresponding alteration as well:

public bool get_AllowUserVariables()
{
    bool defaultValue = false; // Or true?
    if (this.values.ContainsKey("Allow User Variables"))
      return (bool)this.values.get_Item("Allow User Variables");
    else return defaultValue;
}

However, the issue seems to be related with thread safety in relation to static variables MySqlConnection and MySqlConnectionCache

I finally got it to work by locking every reference to MySqlConnectionStringBuilder object in MySql.Data. 
Had to do it:
   - within Connection "Open" method ( if(settings.Pooling) lock (settings).... )
   - in the MySqlScript "Execute" method ( lock (connection.Settings) )

It wouldn't work with any lock from the outside and I specifically tried it without Connection Pooling but it didn't work. It seems the Cache always caches the connection string and, in a multithreaded environment, it gets corrupted and causes all sorts of errors (example: "Collection was modified; enumeration operation may not execute" was the error originated when opening a new connection with the same connection string, because it iterates through all keys and these where being explicitly modified (end of Execute method in MySqlScript class).

Good to know you fixed, we will providing a fix in next release.

Added to changelog for 6.5.6, 6.6.6: 

When running a multithreaded service, you might receive the
exception:

The given key was not present in the dictionary

The issue was fixed by enhancing the locking code within the
ConnectionStringBuilder class.