MySqlDataReader : I close the connection but get : There is already an open DataReader associated with this Connection which must be closed first

Question

    private void startPoll()
    {

        System.Diagnostics.Debug.WriteLine("dbForm created");
         timer = new Timer(int.Parse(Properties.Settings.Default.DbPoll) * 1000);
        // ElapsedEventHandler OnEventExecution = null;
        timer.Elapsed += OnEventExecution;
        timer.Start();
        System.Diagnostics.Debug.WriteLine("Timer Started " + Properties.Settings.Default.DbPoll + " secs");

        string connectionString;
        // MySqlConnection conn;
        connectionString = "server=" + Properties.Settings.Default.DbServer + ";database=digital_passports;uid=" + Properties.Settings.Default.DbUser + ";pwd=" + Properties.Settings.Default.DbPass ;
        try
        {
            using (conn = new MySqlConnection(connectionString))
            {
                try
                {

                    conn.Open();
                    System.Diagnostics.Debug.WriteLine("Database Connection Connected");

                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("Database Connection Failed..." + ex);
                }

                var stm = "SELECT * from vtokenpool limit 10";


                using (var cmd = new MySqlCommand(stm, conn))
                {

                    // var version = cmd.ExecuteScalar().ToString();
                    //  System.Diagnostics.Debug.WriteLine($"MySQL version: {version}");
                    stateText.Text = "Running";
                    stateText.ForeColor = System.Drawing.Color.Green; ;

                    try
                    {
                        using (MySqlDataReader rdr = cmd.ExecuteReader())
                        {

                            while (rdr.Read())
                            {

                               System.Diagnostics.Debug.WriteLine(rdr[0] + " -- " + rdr[1]);

                                /*
                                object[] tempRow = new object[rdr.FieldCount];
                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    tempRow[i] = rdr[i];
                                }
                                dataList.Add(tempRow);
                                */
                            }
                            rdr.Close();
                            System.Diagnostics.Debug.WriteLine("Reader Closed");
                            conn.Close();
                            System.Diagnostics.Debug.WriteLine("Connection Closed");

                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex);
                    }

                }
            }
        }
        catch(Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex);
        }

    }

Hi

Im polling a database view and Im getting this error along with
The ReadAsync method cannot be called when another read operation is pending.

Im closing the datareader and the connection in my code and using using statements but Im getting these errors on repeat queries.

Regards Aidan

Where is the connection declared? I see you've commented out `MySqlConnection conn`. Don't reuse the connection. Sidenote: `conn.Close();` is unnecessary with `using`. — Tim Schmelter, Jun 19 '23 at 16:16
Where's `conn` declared? A static field? `using (conn = ...`) is a bug. I suspect the bug is that two threads are executing `startPoll` at the same time and the second one is replacing the connection started by the first one. The connection should be declared in the using block itself: `using(var conn=new MySqlConnection(...)` — Panagiotis Kanavos, Jun 19 '23 at 16:26
Has the Timer anything todo with this? For example does OnEventExecution call startPoll? Or is startPoll called multiple times (you didn't dispose the old timer in the shown code)? You might also think about your exceptionhandling. Letting the code move on when you couldn't open a connection seems wrong. — Ralf, Jun 19 '23 at 16:55

Joel Coehoorn · Accepted Answer · 2023-06-19T19:07:46.687

The (or at least "a") problem is here:

using (conn = new MySqlConnection(connectionString))

This creates a new connection object, but it doesn't create a new variable. This code just assigns over top of an existing conn variable declared elsewhere, at a higher scope that is potentially shared with other code. The ReadAsync remark also indicates we have asynchronous code in play (even if it's not part of this sample), which means the other code could run interspersed with and at the same time as this code — all looking at the same conn variable, but with the referred connection object changing out from under us.

Worse, the orphaned connection objects could be left hanging open until the GC catches up with them at some undetermined time in the future.

Instead, you should create a new variable each time:

using (var conn = new MySqlConnection(connectionString))

And when this is done, you don't even need to call conn.Close() manually. The using block will take care of it for you.

MySqlDataReader : I close the connection but get : There is already an open DataReader associated with this Connection which must be closed first

1 Answers1