MySQL Bugs: #15834: DataReader already exists?

Bug #15834	DataReader already exists?
Submitted:	17 Dec 2005 23:12	Modified:	23 Mar 2006 18:54
Reporter:	Aaron Freeman	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows XP Professional)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I saw a particular bug displayed about getting an error from:

"There is already an open DataReader associated with this Connection which must closed first."

There are 2 problems:

1) If I avoid use of stored procedures and embed the SQL (assuming it is a single select statement) it will work.  But to keep sql statements out of a compiled EXE is my priority.

2) I am not using any DataReaders in my procedures.

How to repeat:
Code VB.NET that calls the stored proc:

    Public Function getDBField(ByVal sSQL As String, ByVal sDataField As String) As String
        Dim ds As New DataSet, dt As New DataTable, da As New MySqlDataAdapter
        Dim con As New MySqlConnection(mConnectionString)
        Dim dr As DataRow

		Dim cmd As New MySqlCommand(sSQL, con), sValue As String

		da.SelectCommand = cmd
		da.Fill(ds)

        Try
			dt = ds.Tables(0)

			If dt.Rows.Count > 0 Then
				dr = dt.Rows(0)
				sValue = dr.Item(sDataField)

				If sValue <> "" Then
					Return sValue
				Else
					Return Nothing
				End If
			Else
				Return Nothing
			End If
        Catch ex As Exception
			Return Nothing
		Finally
			con.Close()
			dt.Dispose()
			ds.Dispose()
			da.Dispose()
			con.Dispose()
		End Try
	End Function

The procedure being called:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbschema`.`usp_PlayerShopCardChange` $$
CREATE PROCEDURE `usp_PlayerShopCardChange`(sNick VARCHAR(45), sCard VARCHAR(60), iAmount INT, iPrice INT)
BEGIN

DECLARE iID INTEGER DEFAULT 0;
DECLARE iBIT INTEGER DEFAULT 0;

SELECT id INTO iID FROM playershop_cards where player = sNick and cardname = sCard and price = iPrice;

if iAmount > 0 THEN
  INSERT INTO playershop_cards(player, cardname, price) VALUES (sNick, sCard, iPrice);
ELSEIF iAmount < 0 THEN
  DELETE FROM playershop_cards where id = iID;

  if iID = 0 THEN
    SET iBIT = 0;
  END IF;
END IF;

SELECT 1 as Success, iBIT as Removed;

END $$

DELIMITER ;

Suggested fix:
Only workaround is having the application do what the stored procedure is.  I am wanting to avoid this.

Thank for the bug report. I was able to reproduce the bug. My test case is attached.

Test case

Attachment: 15834.zip (application/force-download, text), 5.37 KiB.

Hello,

I solved/worked around this bug by changing the following method in Driver.cs and recompiling the MySql connector 1.0.7:

		public void ReportWarnings() 
		{
			ArrayList errors = new ArrayList();
			
			MySqlConnection _warningConnection = new MySqlConnection(this.connectionString.GetConnectionString(true));
			MySqlCommand cmd = new MySqlCommand("SHOW WARNINGS", _warningConnection);
			MySqlDataReader reader = null;
			try 
			{
				_warningConnection.Open();
				reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					errors.Add(new MySqlError(reader.GetString(0),
						reader.GetInt32(1), reader.GetString(2)));
				}
				reader.Close();

				hasWarnings = false;
				// MySQL resets warnings before each statement, so a batch could indicate
				// warnings when there aren't any
				if (errors.Count == 0) return;   

				MySqlInfoMessageEventArgs args = new MySqlInfoMessageEventArgs();
				args.errors = (MySqlError[])errors.ToArray(typeof(MySqlError));
				if (connection != null)
					connection.OnInfoMessage( args );
			
			}
			catch (Exception) 
			{
				throw;
			}
			finally 
			{
				if (reader != null) reader.Close();
				if (_warningConnection.State == System.Data.ConnectionState.Open) _warningConnection.Close();
			}
		}

Regards,

Peter Vyvey
-
http://www.chiliware.be

This bug appears in my application, too.

My workaround: de-connecting and re-connecting to mySQL

Will it be fixed in the next version of Connector/Net and when will it be available ?

Thanks !

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Aaron

I'm unable to reproduce either using your code or the test case from Vasiliy.  I tried with the table empty and it worked.  I tried with a row in the table and get a server error indicating that the result inside the sproc resulted in more than one row.

Can you attach to this bug report a test case with create table and create procedure scripts that show the problem?  

Reggie

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".