Bug #66477 Also Cache 'SHOW COLLATION' when 'CacheServerProperties' is true
Submitted: 21 Aug 2012 3:29 Modified: 28 Sep 2012 15:19
Reporter: Poul Bak Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.6.1 OS:Any
Assigned to: Francisco Alberto Tirado Zavala CPU Architecture:Any
Tags: Cache collation CacheServerProperties', Contribution

[21 Aug 2012 3:29] Poul Bak
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);
                }
            }
[21 Aug 2012 3:30] Poul Bak
Driver.cs after change

Attachment: Driver.cs (text/plain), 18.68 KiB.

[21 Aug 2012 3:31] Poul Bak
MySqlPool.cs after change

Attachment: MySqlPool.cs (text/plain), 11.68 KiB.

[21 Aug 2012 3:33] Poul Bak
The change should be able to go into earlier versions of .Net connector too.
[28 Aug 2012 14:49] Poul Bak
Please note that 'Driver.cs' still contains http://bugs.mysql.com/bug.php?id=66578

Use the version from that bug to fix both.
[28 Aug 2012 14:55] Poul Bak
I should mention that I have run this code for a week now in production.