| 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: | |
| 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
[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.
