MySQL Bugs: #19181: Spurious exception from datareader

Bug #19181	Spurious exception from datareader
Submitted:	18 Apr 2006 23:07	Modified:	2 May 2006 6:58
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.07	OS:	Windows (W2K SP2)
Assigned to:		CPU Architecture:	Any

Description:
(i) Drag a combobox onto a form, 

(ii) name the combobox cbDetailTable, 

(iii) drop the method below into the class, 

(iv) set USR and PWD for yourself, 

(v) set sDb and sMasterTable such that sMasterTable is an sDb table 
     for which there are other sDb tables with foreign key references, 

(vi) call the method from somewhere.

The call 

  daDetTbl.Fill(dsNew);
  
fails with an exception message which is rubbish: 

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

Here is the method:

   private void cbDetailTable_Init() {
      int i, iQry = 1;
      string sConn = 
       "server=127.0.0.1;uid=USR;pwd=PWD;database=information_schema";
      string sDb = "NWIB";
      string sMasterTable = "CUSTOMERS";
      string sSql;
      MySqlConnection oNewConn = null;
      DataSet dsNew = null;
      BindingSource bsDetTbl = null;
      MySqlDataAdapter daDetTbl = null;
      bcbDetailTableActive = false;
      try {
        oNewConn = new MySqlConnection(sConn);
        dsNew = new DataSet();
        bsDetTbl = new BindingSource();
        if( iQry == 0 )
          // WITH THIS QUERY, A CALL TO DATAADAPTER.Fill() SUCCEEDS:        
          sSql = "SELECT table_name FROM information_schema.tables " +
                    "WHERE table_schema = '" + sConnDb + 
                    "' AND table_name <> '"  + sMasterTable + "'";
        else
          // BUT WITH THIS QUERY, A CALL TO DATAADAPTER.Fill() 
          // FAILS WITH ERR MSG SHOWN
          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'",
                    sDb, 
                    sMasterTable 
                  );
        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();
          cbDetailTable.DisplayMember = 
                   dsNew.Tables[i].Columns[0].ColumnName;
          cbDetailTable.ValueMember = 
                   dsNew.Tables[i].Columns[0].ColumnName;
          cbDetailTable.Focus();
        }
      } catch (Exception ex) {
        MessageBox.Show(ex.Message);
      } finally {
        oNewConn.Close();
      }
    }

Set iQry = 0, ie for the smaller query, and no exception occurs.

How to repeat:
As above

Suggested fix:
Unknown

Hi. Thanks for your problem report. Can you please post small VS project demonstrating this error? It would also be nice to have a DDL script and some test data.

You don't have enough to replicate this?!?

-- DDL

USE test;
DROP TABLE IF EXISTS parent;
CREATE TABLE IF NOT EXISTS parent (
  id int(10) unsigned NOT NULL default '0',
  lastname char(20) default NULL,
  firstname char(20) default NULL,
  howmany smallint(6) default NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO parent VALUES (0, 'Brown', 'Jane', 0);
INSERT INTO parent VALUES (1, 'Black', 'Marie-Claire', 3);
INSERT INTO parent VALUES (2, 'White', 'Fran', 0);
INSERT INTO parent VALUES (3, 'Green', 'Max', 0);
INSERT INTO parent VALUES (4, 'Gretzky', NULL, 0);

DROP TABLE IF EXISTS child;
CREATE TABLE IF NOT EXISTS child (
  id int(10) unsigned NOT NULL auto_increment,
  parent_id int(10) unsigned NOT NULL default '0',
  lastname char(20) default NULL,
  firstname char(20) default NULL,
  test int(11) default NULL,
  nid int(11) default NULL,
  PRIMARY KEY (id),
  UNIQUE KEY test (lastname,firstname,test),
  KEY parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

INSERT INTO child VALUES (1, 3, 'Zane', 'Billy', 1, 0);
INSERT INTO child VALUES (2, 2, 'White', 'Dave', 2, 0);
INSERT INTO child VALUES (3, 2, 'White', 'Jack', 3, 0);
INSERT INTO child VALUES (4, 1, 'Black', 'Zach', 3, 1);
INSERT INTO child VALUES (5, 2, 'White', 'Larry', 5, 1);
INSERT INTO child VALUES (6, 2, 'White', 'Tim', 6, 1);
INSERT INTO child VALUES (7, 1, 'Black', 'Gregory', 7, 0);
INSERT INTO child VALUES (8, 2, 'White', 'Harvey', 8, 0);
INSERT INTO child VALUES (9, 1, 'Black', 'Norm', 9, 0);
INSERT INTO child VALUES (10, 1, 'Black', 'Ken', 2, 1);
INSERT INTO child VALUES (11, 4, NULL, NULL, NULL, 1);
INSERT INTO child VALUES (12, 4, NULL, NULL, 2, 1);
INSERT INTO child VALUES (13, 4, NULL, NULL, NULL, 0);

ALTER TABLE `child`
  ADD CONSTRAINT 0_19 FOREIGN KEY (parent_id) REFERENCES parent (id);

-- EOF

//---------------------------------------------------------
// Form1.Designer.cs

namespace WinApp27
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
          this.cbDetailTable = new System.Windows.Forms.ComboBox();
          this.SuspendLayout();
          // 
          // cbDetailTable
          // 
          this.cbDetailTable.FormattingEnabled = true;
          this.cbDetailTable.Location = new System.Drawing.Point(170, 112);
          this.cbDetailTable.Name = "cbDetailTable";
          this.cbDetailTable.Size = new System.Drawing.Size(289, 21);
          this.cbDetailTable.TabIndex = 0;
          // 
          // Form1
          // 
          this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
          this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
          this.ClientSize = new System.Drawing.Size(613, 273);
          this.Controls.Add(this.cbDetailTable);
          this.Name = "Form1";
          this.Text = "Form1";
          this.ResumeLayout(false);

        }

        #endregion

      private System.Windows.Forms.ComboBox cbDetailTable;
    }
}

// ---------------------------------------------------------

// Program.cs

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace WinApp27
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}
---------------------------------------------------------

// Form1.cs (substitute USR & PWD)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace WinApp27 {
  public partial class Form1 : Form  {
    public Form1()  {
      InitializeComponent();
      Test();
    }
    public void Test() {
      int i;
      string sConnDb = "test";
      string sMasTbl = "parent";
      string sNewConn = "server=127.0.0.1;uid=USR;pwd=PWD;database=information_schema";
      string sSql;
      MySqlConnection oNewConn = null;
      DataSet dsNew = null;
      BindingSource bsDetTbl = null;
      MySqlDataAdapter daDetTbl = null;
      try {
        int iQry = 1;
        oNewConn = new MySqlConnection(sNewConn);
        dsNew = new DataSet();
        bsDetTbl = new BindingSource();
        if( iQry == 0 ) 
          sSql = "SELECT table_name FROM information_schema.tables " +
                 "WHERE table_schema = '" + sConnDb + "' AND table_name <> '" + sMasTbl + "'";
        else
          // CONNECTOR/NET BUG: THIS CAUSES AN EXCEPTION WHEN DATAADAPTER.Fill() IS CALLED!
          sSql = String.Format( "SELECT u.table_name AS table_name " +                    
                                "FROM table_constraints AS c " +
                                "INNER JOIN 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);

        // THROWS:"There is already an open DataReader associated with this Connection which must be closed first."
        daDetTbl.Fill( dsNew );  // , srcTable ); ???

        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) {
        MessageBox.Show(ex.Message);
      } finally {
        oNewConn.Close();
    }
  }
}

}

Hi  Peter. I was unable to reproduce the error you get with sources provided.
  MySQL 5.1.7 beta-nt-max-log
  WinXP SP2
  Connector/NET 1.0.7 latest source
  Microsoft.NET Framework v2.0.50727

VS2005 project

Attachment: WinApp27.zip (application/zip, text), 112.01 KiB.

I am seeing the same bug.  I am using the .NET connector 1.0.7, .NET 1.1, MySQL 5.0.19.  I am using the enterprise library and stored procedures.  When I run the stored procedure and it gets no data everything works.  When I run it and should get data I see this error.  This doesn't happen for all stored procedures.  When I run the stored procedure from query browser it works and gives me results.

I have this line in the stored procedure:
CONVERT(esp.DateOfService, CHAR(10)) AS DateOfService,

When I remove it I don't see the error any more.

Nathaniel, you privided almost no data... Can you repeat the error with latest release of MySQL server?