Net packets out of order on opening a connection with connection pooling?

[pcarpenter]

Here is our connection string:

pooling=true;server=192.168.1.100;database=dbname;uid=dbuser;pwd=dbpwd;

Sometimes, very infrequently and when the system is under load, we will get errors like "Net packets out of order - expect [X], received [Y]", or similar.

I have done a little reading on the forums and was hoping some of the advice I found could be clarified. Specifically, at the bottom of the first page of this post: viewtopic.php?t=6396

1. Make sure you close all data readers explicitly.
In .NET, we sometimes explicitly call the Close method on the MySqlDataReader and sometimes we just open the MySqlDataReader in a Using block without explicitly invoking the Close method. I am guessing that the Dispose method of MySqlDataReader which Using calls when the resource goes out of scope is not sufficient - can anyone confirm this?

2. If you use Prepare() method of MySqlCommand class, call Dispose() after closing reader.
Not applicable to our case.

3. If you use connection pooling, call MySqlConnection.Ping() method before MySqlConnection.Open().
Does this mean that, with connection pooling enabled, the MySqlConnection we get from New MySqlConnection() might actually be a MySqlConnection() created on some other thread and which might have other activity outstanding (if, e.g., it had a MySqlDataReader that wasn't closed properly, etc.)? Should I take this to mean that all New'ed MySqlConnection instances should have Ping() called before attempting to Open()? If so, do I need to then Close() the MySqlConnection if the Ping() succeeds, or should I New another MySqlConnection, or both?

4. If above doesn't help and you use connection pooling, check if you can turn connection pooling off.
This is an option but something we'd like to avoid if there is a solution here.

Note: we have a web app with multiple requests in flight at a time, and each request creates multiple worker threads each with its own MySqlConnection instance. I was able to reproduce the "Net packets out of order" error by EXPLICITLY using the same instance of MySqlConnection in multiple background threads simultaneously... which is what leads me to believe that connection pooling is recycling still-active MySqlConnection instances with essentially the same effect.

Any help is appreciated.

[Pinturiccio]

I am guessing that the Dispose method of MySqlDataReader which Using calls when the resource goes out of scope is not sufficient - can anyone confirm this?

Yes, using block is good enough.

MySqlConnection instances should have Ping() called before attempting to Open()?

No, they shouldn't.

This is an option but something we'd like to avoid if there is a solution here.

Could you try to disable pooling for purposes of testing and tell us whether the issue can be reproduced with the disabled pooling?

Note: we have a web app with multiple requests in flight at a time, and each request creates multiple worker threads each with its own MySqlConnection instance.

dotConnect for MySQL is not thread safe, so you should use a separate connection for each thread or synchronize threads yourself. Maybe there is a case when you use the same connection in different threads.

[pergardebrink]

We tried to upgrade dotConnect from a really old version last year and immediately hit this bug. We couldn't rule out that we could have some kind of issue in our codebase (unsafe thread code, even though it worked perfectly with the old dotConnect) so we postponed the upgrade.

However I have now been able to reproduce the issue with just about 100% certainty if you use dotConnect 8.10.1152 or higher (lower version does not seem affected). The problem happens regardless if pooling is enabled or not, but it seems to be happening when multiple threads are calling .Open() at the same time with different connectionstrings.

Short code to reproduce the bug:

Code: Select all

class Program
    {
        static void Main(string[] args)
        {
            const int NUMBER_OF_THREADS = 100;

            var databases = new[] {
                "mysql",
                "sys",
            };
            
            var connectionStrings = databases.Select(database => {
                var builder = new MySqlConnectionStringBuilder
                {
                    Database = database,
                    Server = "REPLACE_WITH_MYSQL_SERVER",
                    Port = 3306,
                    UserId = "REPLACE_WITH_USERNAME",
                    Password = "REPLACE_WITH_PASSWORD",
                    MaxPoolSize = 100,
                    MinPoolSize = 0,
                    Pooling = true,
                    Charset = "utf8mb4",
                };
                return builder.ToString();
            }).ToArray();

            Console.WriteLine($"Running: {typeof(MySqlConnection).Assembly.FullName}");

            var tasks = Enumerable.Range(0, NUMBER_OF_THREADS).Select(t => Task.Run(() => {
                // Alternate between the connectionstrings
                var connectionString = connectionStrings[t % connectionStrings.Length];
                using (var connection = new MySqlConnection(connectionString))
                {
                    connection.Open();
                }
            }));

            Task.WaitAll(tasks.ToArray());
            Console.WriteLine("done");
        }
    }

Running 8.10.1134 works perfectly, or using the "workaround" below also works if you replace all instantiations of MySqlConnection (it might hurt performance if there's a lot of Open() happening:

Code: Select all

public class MySqlConnection2 : MySqlConnection
    {
        private static readonly object lockObject = new object();
        public MySqlConnection2(string connectionString) : base(connectionString) { }

        public override void Open()
        {
            lock (lockObject)
            {
                base.Open();
            }
        }
    }

[Pinturiccio]

We managed to reproduce the issue with dotConnect for MySQL 8.10.1152 and with your code. However, your code works correctly with the latest 8.13.1402 version of dotConnect for MySQL. Please try using the latest version of dotConnect for MySQL and post here the results.

[pergardebrink]

You are correct! I thought I had tested the latest version as well (I tested a lot of versions above 8.4.506 as we had before and actually thought 8.13.1402 was in my list of them). It seems to have been fixed in this version (bug present in previous 8.13.1366). Wonderful news!

Was it an accidential fix, or something known? It wasn't mentioned in the release notes for 8.13.1402?

[Pinturiccio]

No, we did not know about the issue before you sent us the test project for reproducing it.