Testing C# MySQL connection pooling fails with Exception: Connection must be valid and open

Question

I am trying to use connection pooling on MySQL's .net connector 6.6.4.0 to serve multiple threads using the code below:

static public uint uNonQuery(string query)
        {

                using (Connection = new MySqlConnection(ConnectionString))
                {
                    if (Connection.State == ConnectionState.Open)
                    {
                        Console.WriteLine("Its open..>!!!!");
                    }

                    Connection.Open();

                    MySqlCommand cmd = new MySqlCommand(query, Connection);
                    cmd.ExecuteNonQuery();


                    MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT LAST_INSERT_ID() AS 'identity'", Connection);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    uint ret = 0;

                    if (dt.Rows.Count > 0)
                    {
                        try
                        {
                            ret = Convert.ToUInt32(dt.Rows[0]["identity"]);
                        }
                        catch
                        {
                            ret = 0;
                        }
                    }

                    else
                        ret = 0;

                    Connection.Close();
                    return ret;
                }
        }

My connection string is

"server = 127.0.0.1; user id = ****; password = ****; database = testdb; pooling=true; maximumpoolsize=500;"

And the thread for testing is

static void DoInserts(object o)
{
    int i = (int)o;
    Console.WriteLine("Insert Thread {0} launched", i);


    for(int x = 0; x < 1000; x++)
    {
        uint insertId = DataLayer.uNonQuery(String.Format("INSERT INTO testdb.dbtest (writeNo,strNo) VALUES ({0},'x={0} thread={1}')", x, i));
    }

    Console.WriteLine("Insert Thread {0} completed", i);

}

I get the exception Exception: Connection must be valid and open. after about 6 inserts on 2 concurrent threads, is there something else i need to do enable pooling to work correctly?

Thanks

Out of interest, why do you check if the connection is open then open it regardless?? — Daniel Kelley
– Daniel Kelley, Commented Feb 7, 2013 at 20:40
@DanielKelley Hi. I tested if its open before i call Open() and it is not open before the Open() call, which is expected, although connection pooling is enabled. — John Smith
– John Smith, Commented Feb 7, 2013 at 20:43
(Connection.State == ConnectionState.Open indicates there is something wrong with the logic of your code. If you would open the connection in scope, it is no need to check whether it is open already. — Mike de Klerk
– Mike de Klerk, Commented Feb 7, 2013 at 20:45

Mike de Klerk · Accepted Answer · 2013-02-07 20:43:46Z

1

In your line using (Connection = new MySqlConnection(ConnectionString)) you are using the variable Connection. Where is this variable defined? Is it static? You should define it in scope of uNonQuery. If variable Connection is static, then it is possible the connection is closed after a new function call to uNonQuery has opened it.

Example: - Thread 1 calls uNonQuery and opens, does it things. - Thread 2 calls uNonQuery, opens the connection, - Thread 1 closes Connection - Thread 2 cannot do its thing because the connection was suddenly closed.

If the above is not the cause of your problem I would need more of your code to examine the problem.

answered Feb 7, 2013 at 20:43

Mike de Klerk

12.5k9 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Smith Over a year ago

Hi, it seems that the Connection object was defined as static, which caused the clash. I just tested it again without static and it completed 20 concurrent threads with 10 000 inserts each. Thank you very much.

Collectives™ on Stack Overflow

Testing C# MySQL connection pooling fails with Exception: Connection must be valid and open

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related