Bug #7248 There is already an open DataReader associated with this Connection which must
Submitted: 14 Dec 2004 0:42 Modified: 22 Sep 2006 14:03
Reporter: Malcolm Crowe Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.3 gamma OS:Windows (Windows XP SP2 .NET 1.1)
Assigned to: Reggie Burnett CPU Architecture:Any

[14 Dec 2004 0:42] Malcolm Crowe
Description:
I get the above message from code that works with other database products: I am certain I close all my readers before opening another.
From the newsgroups I see lots of advice to close Connections immediately, and comments that a connection must not have been used before ExecuteReader is called.
But I am following a specification which involves quite long transactions, and .NET providers for other database products work fine. The transactions have a dozen or so SELECTS mixed in with various updates and inserts.
It seems to me that I can't simply keep reopening the connections since closing a connection will lose the transaction?

How to repeat:
Open a connection, start a transaction, carry out several operations such as
SELECT
close the reader
SELECT
close the reader
Things will have failed by then...

Suggested fix:
Nothing springs to mind
[14 Dec 2004 20:53] Malcolm Crowe
Recompilation from the sources helps. Once this is done the behaviour is a bit different: now we only get the error message if an ExecuteNonQuery() is called before the MySqlDataReader is closed.
IMHO this is still a bug, but I suspect that removing or modifying the call to CheckState() from ExecuteNonQuery() will be unwise.
To work around, adjust code as required to collect information from the open data reader, and close it before calling ExecuteNonQuery().
I recommend that the distribution is recompiled.
[16 Dec 2004 18:36] Reggie Burnett
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

I am marking this not a bug since you said compiling from BK changed the behavior.  You said the new behavior is that if you try to call ExecuteNonQuery before closing the reader, it fails.  This *IS* expected behavior.  Try the same thing with SqlClient and you'll see it fails in exactly the same way.  The reason is that the MySqlDataReader (and the SqlDataReader) are used to implement ExecuteScalar and ExecuteNonQuery, therefore anytime you execute a SQL command you are using a data reader.
[20 Dec 2004 20:35] Malcolm Crowe
It may be the behaviour that you expect but it is not in accordance with IDbConnection documentation or with the behaviour of other DBMS.
[20 Dec 2004 21:00] Reggie Burnett
In what way does it disagree with existing database implemtations or with IDbConnection documentation?  The SQL Server  driver will not let you execute a command while a reader is open and there is nothing in the docs that I have found that says this should be allowed.
[21 Dec 2004 6:29] Malcolm Crowe
You are right of course. The documentation says
"Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed."
Humble apologies.
[10 Feb 2005 9:08] Tanveer Ahmed
Dim sqlDr As MySqlDataReader
        sSQL = "Select * from site Where SiteCode='" & Trim(txtSiteCode.Text) & "' And CompanyCode='" & gsCoCode & "' And Deleted='N'"
        dbCommand.CommandText = sSQL
        sqlDr = dbCommand.ExecuteReader()
        If Not sqlDr.Read Then
            MessageBox.Show("Entered site code is invalid.", “”, MessageBoxButtons.OK, MessageBoxIcon.Information)
            sqlDr.Close()		-This code works fine
            Exit Sub
        End If
        sqlDr.Close()			-This code works fine

  daI.SelectCommand = New MySqlCommand("Select * from sitecharges”, Connection)
        daI.Fill(dsI, "tblSC")

        Dim R As DataRow
        For Each R In dsI.Tables(0).Rows
dbCommand.CommandText = "Select Query ………………………………."
sqlDr = dbCommand.ExecuteReader
            If sqlDr.Read Then
sSQL = "Update " & TmpInvoice & " Set SundayHrs=" & sqlDr("SundayHrs") & " ,SundayRate=" & R("Sunday") & ",SundayOTHrs= " & sqlDr("SundayOTHrs") & ",SundayOTRate = " & R("SOT") & " Where RateCode='" & R("RateCode") & "'"
            If Not sqlDr.IsClosed Then
      	          sqlDr.Close()				- This Code Gives the error
End If
                dbCommand.CommandText = sSQL
                dbCommand.ExecuteNonQuery()
            End If
            sqlDr.Close()
  Next 

This above code uses a datareader to open and check data twice 
but in one of the instances the error mentioned in the post appears. Closing the connection is also refused saying open datareader to be closed before closing the connection.
This same code works fine in MSSQL server.
[11 Feb 2005 11:17] Tanveer Ahmed
This error is occuring only if the where clause contains unformatted date format
(not supported by mysql). But this has got nothing to do with the error message reported.
So is that a bug or not is not clear...
[20 Jul 2005 1:28] Marcelo del Pozo
Hi, I confirmed that it in a 'SELECT' involving dates witch are not in "yyyy-MM-dd'" format produce de error.
the following code produces the error:
public function isBirthday() as boolean
      Dim cn As MySqlConnection = GetConnection()
      Dim cmd As New MySqlCommand("SELECT Cust_ID FROM Customers WHERE DateOfBirth = " & Date.Today , cn)
      Dim dr As MySqlDataReader

      Try
         dr = cmd.ExecuteReader
         If dr.HasRows Then
            return= True
         Else
            return = False
         End If
      Catch ex As Exception
         MessageBox.Show(ex.Message, "GeHos", MessageBoxButtons.OK, MessageBoxIcon.Information)
      Finally
         dr.Close()
         cmd.Dispose()
         cn.close
         cn.dispose
end function

the same code by using the following line works fine

      Dim cmd As New MySqlCommand("SELECT Cust_ID FROM Customers WHERE DateOfBirth = '" & Date.Today.ToString("yyyy'-'MM'-'dd") & "'" , cn)

Marcelo
[19 Dec 2005 5:48] Doug Grogan
My question regarding this.

I am executing the following code:

// Add Parameters to SPROC
MySqlParameter parameterEmail = new MySqlParameter("?inEmail", MySqlDbType.VarChar, 50);
parameterEmail.Value = email;
			
MySqlParameter parameterPassword = new MySqlParameter("?inPasswrd", MySqlDbType.VarChar, 50);
parameterPassword.Value = password;
			
// Execute Dataset
DataSet ds = MySqlHelper.ExecuteDataset(Web.Global.CfgKeyConnString, SQLProc.ProcCollection["procCustomerLogin"], parameterEmail, parameterPassword);

Very straight forward.  I am getting the open DataReader error.
Not sure what else I could change in this code.   Looking for any assistance I could get with this.
[18 Apr 2006 21:56] Peter Brawley
Reggie's denials notwithstanding, this has every appearance of being a bug. Drag a combox onto a form, name the combobox cbDetailTable, drop this method into the class, and call the method from somewhere:

   private void cbDetailTable_Init() {
      int i;
      string sConn = "server=127.0.0.1;uid=USR;pwd=PWD" ; 
      MySqlConnection oNewConn = null;
      DataSet dsNew = null;
      BindingSource bsDetTbl = null;
      MySqlDataAdapter daDetTbl = null;
      try {
        oNewConn = new MySqlConnection(sConn);
        dsNew = new DataSet();
        bsDetTbl = new BindingSource();
     // WITH THIS QUERY, A CALL TO DATAADAPTER.Fill() SUCCEEDS:        
     // string sSql = "SELECT table_name FROM information_schema.tables " +
     //               "WHERE table_schema = '" + sConnDb + "' AND table_name <> '" + sMasTbl + "'";
     // BUT WITH THIS QUERY, A CALL TO DATAADAPTER.Fill() FAILS WITH ERR MSG SHOWN
        string sSql = String.Format( "SELECT u.table_name AS table_name " +                    
                                     "FROM information_schema.table_constraints AS c " +
                                     "INNER JOIN information_schema.key_column_usage AS u " +
                                     "USING( constraint_schema, constraint_name ) " +
                                     "WHERE u.referenced_table_schema = '{0}' " +
                                     "AND u.referenced_table_name = '{1}' " +
                                     "AND c.table_schema = '{0}' " + 
                                     "AND c.constraint_type = 'FOREIGN KEY'",
                                     sConnDb, 
                                     sMasTbl 
                                   );

        i = dsNew.Tables.Count;
        cbDetailTable.DataSource = bsDetTbl;
        daDetTbl = new MySqlDataAdapter(sSql, oNewConn);

        // WITH 2ND QUERY ONLY, THROWS EXCEPTION:
        // "There is already an open DataReader associated with this Connection which must be closed first."
        daDetTbl.Fill(dsNew);

        if (dsNew.Tables[i].Rows.Count > 0) {
          bsDetTbl.DataSource = dsNew;
          bsDetTbl.DataMember = dsNew.Tables[i].ToString();     // "Tables" + i.ToString();
          cbDetailTable.DisplayMember = dsNew.Tables[i].Columns[0].ColumnName;
          cbDetailTable.ValueMember = dsNew.Tables[i].Columns[0].ColumnName;
          cbDetailTable.Focus();
        }
      } catch (Exception ex) {
        ErrMsg(ex);
      } finally {
        oNewConn.Close();
      }
    }
[30 Jun 2006 17:25] Jason Jaskolka
The source code for the MySql .Net connector does not refer to the data reader at all in the data adapter class. I assume this is because it implements the inherited Fill method. I also receive this error, but I only get it on one of two of my development computers. Should not the connector implement its own Fill method or is it unnecessary? I suspect the inherited Fill method may not be closing the data reader however it needs to be with MySql. Please help us!
[9 Sep 2006 17:48] xx xxxx
As of 2006-09-06, this remains unfixed with the mysql connector. 
Kindly treat this as a bug and fix the issue. OdbcAdapter does not throw an error in the same situation, described by others. 
THIS SHOULD BE a BUG!
[18 Sep 2006 20:00] Ozgur YASAR
This should be a bug because i have inspected and debugged the MySQL Client Source Code.

When you fill data adapter by using some sql statement or stored procedure which has not errors but warnings, The MySql Command tries to report warnings.
But the MySqlDataAdapter which is using MySqlDataReader is opened on active connection, prevents the second MySqlDataReader to be opened and executed on the same connection. So, i think MySql Driver ReportWarnings method should have something like below to close existing data reader;

if (connection != null && connection.Reader != null && !connection.Reader.IsClosed)
  connection.Reader.Close();

Or you should write clean statements which generates no warnings at all. In my situation there was;
DROP TABLE IF EXISTS tmp; 
which generates a warning when you have tmp table available :)

Have a nice day.

~
Ozgur YASAR
MCAD.NET
[19 Sep 2006 21:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12241
[19 Sep 2006 21:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12242
[19 Sep 2006 21:44] Reggie Burnett
I verified this bug and it will be fixed in 1.0.8 an 5.0.1
[22 Sep 2006 14:03] MC Brown
A note has been added to the 5.0.1 and 1.0.8 changelogs.
[6 Aug 2008 14:01] Israel DiPeri
I know this says that this bug is fixed but is there a possible regression?
I’m using .NET 2.0 with the .NET connector 5.1.4 and MySQL 5.0.
I ran into an issue where I’m trying to insert a row into the database via calling ExecuteNonQuery() on a MySqlCommand. It times out due to another connection having a long transaction trying to delete many rows in the same database.  The command is within a using statement so it should be properly disposed when the timeout exception is thrown.  When it comes back in for a second try it again creates a new command and then hits the error:

MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.   at MySql.Data.MySqlClient.MySqlCommand.CheckState()   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()   

I am 100% confident that this code is the only one using that connection and the only thing it does is insert records via this same method.
Does anyone have any suggestions or thoughts?  I’m probably just going to rework the code to start a new connection every time but I wanted to re-use the same one because it’s used very often (possibly 100 times per second or more).  We have thread pooling turned on so creating connections should be a little more efficient.
[18 Jan 2010 13:24] asdf as
i am getting same error how to solve this issue
[19 Jan 2010 22:03] J D
I thought I might add the comment... when dealing with a OdbcDataConnection, it does allow multiple dbreaders to be outstanding.  I beleive the sqlite connector also allows multiple dbreaders outstanding....

I have a few places where I had nested queries cause I was just working against the ODBC connector, and I had a select, that for each result I iterated to do another select against another table (yes this is better served by a join, but the data was not really related in such a way that I could have done that so simply)  I ended up sucking the whole resultset into a DataTable and iterating that with the remaining select query.