Description:
When 'CacheServerProperties' connectionstring setting is true, serverproperties are cahced, fine.
I really think that if a user wants to cache serverproperties, he/she definately also wants to cache collations. They normally don't change in the server's lifetime, and certainly noone removes collations, that might be used.
I have implemented this caching, so that when 'CacheServerProperties' is true, both ServerProperties and Collations are cached, and when false, nothing is cached.
There's also a problem when several connections are created at the same time. Then they will all send queries for serverproperties even when 'CacheServerProperties' is true (requieres a lock).
How to repeat:
Turn on tracing, and note all the queries for collations.
Also try setting 'MinimumPoolSize' to some value. This will create connections right at startup. Note that they will ALL send queries for serverproperties (and collations) even if 'CacheServerProperties' is true.
After applying the change, try again and notice how only the first connection queries for serverproperties and collations. The lock makes other connections simply wait, causing less traffic and faster creation of connections.
Suggested fix:
First we need a property in MySqlPool to cache the charactersets:
internal Hashtable CharacterSets
{
get;
set;
}
Second, we need a lock, also in MySqlPool, so we lock on a per pool basis:
internal object lockServerProperties = new object();
Then we change (in Driver.cs) 'LoadCharacterSets' to return a HashTable:
private Hashtable LoadCharacterSets(MySqlConnection connection)
{
MySqlCommand cmd = new MySqlCommand("SHOW COLLATION", connection);
// now we load all the currently active collations
try
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Hashtable chSets = new Hashtable();
while (reader.Read())
{
chSets[Convert.ToInt32(reader["id"], NumberFormatInfo.InvariantInfo)] =
reader.GetString(reader.GetOrdinal("charset"));
}
return chSets;
}
}
catch (Exception ex)
{
MySqlTrace.LogError(ThreadID, ex.Message);
throw;
}
}
Finally we change 'Configure' to use MySqlPool's caching if the user wants it (here the first half of 'Configure', which is what is changed:
public virtual void Configure(MySqlConnection connection)
{
bool firstConfigure = false;
// if we have not already configured our server variables
// then do so now
if (serverProps == null)
{
firstConfigure = true;
// if we are in a pool and the user has said it's ok to cache the
// properties, then grab it from the pool
if (Pool != null && Settings.CacheServerProperties)
{
if (Pool.ServerProperties == null)
{
lock (Pool.lockServerProperties)
{
if (Pool.ServerProperties == null)
{
Pool.ServerProperties = LoadServerProperties(connection);
Pool.CharacterSets = LoadCharacterSets(connection);
}
}
}
serverProps = Pool.ServerProperties;
charSets = Pool.CharacterSets;
}
else
{
serverProps = LoadServerProperties(connection);
charSets = LoadCharacterSets(connection);
}
}