MySQL Bugs: #30518: Calling MySqlDataAdapter.FillSchema leaves connection in bad state

Bug #30518	Calling MySqlDataAdapter.FillSchema leaves connection in bad state
Submitted:	20 Aug 2007 17:39	Modified:	8 Jul 2009 14:59
Reporter:	Paul Bonfanti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Windows (XP)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	FillSchema

Description:
Calling MySqlDataAdapter.FillSchema on a select statement that refers to a table that doesn't exist leaves the connection in bad state. After this call, all select statements will return an empty result set. If the select statement refers to a table that exists then everything works as expected. This bug does not exist in MySQL Connector/NET 1.0.9.

How to repeat:
Execute the following code. The select now() statement should return a result set but it doesn't in this case.

	private static void FillSchemaBug()
	{
		MySqlCommand commandA;
		MySqlDataReader rdr = null;
		int num;
		try
		{
			String connectString = "server=tennis;port=3307;database=test;user id=root;password=root;Pooling=False";

			// Open Connection A
			MySqlConnection connectionA = new MySqlConnection(connectString);
			connectionA.Open();

			commandA = new MySqlCommand();
			MySqlDataAdapter adapter = new MySqlDataAdapter();
			commandA.set_Connection(connectionA);
			commandA.set_CommandType(System.Data.CommandType.Text);
			commandA.set_CommandText("SELECT * FROM DOESNOTEXIST");
			adapter.set_SelectCommand(commandA);
			System.Data.DataSet ds = new System.Data.DataSet();
			try
			{
				System.Data.DataTable tables[] = adapter.FillSchema(ds, System.Data.SchemaType.Mapped);
			}
			catch (System.Exception t)
			{
			}
			finally
			{
				commandA.Dispose();
				adapter.Dispose();
			}

			// Use Connection A to select now()
			commandA = new MySqlCommand();
			commandA.set_Connection(connectionA);
			commandA.set_CommandType(System.Data.CommandType.Text);
			commandA.set_CommandText("select now()");
			rdr = commandA.ExecuteReader();
			System.out.println("CONNECTION A OUTPUT");
			while (rdr.Read())
			{
				System.out.println("value=" + rdr.GetString(0));
			}
			rdr.Close();
			commandA.Dispose();
			connectionA.Close();
		}
		catch (Throwable t)
		{
			t.printStackTrace();
		}
	}

Hi Paul and thanks for your report. I am sorry I missed it before but I'll try verifying it today.

Paul, your code doesn't compile on my box so I changed it a bit getting proper output:
CONNECTION A OUTPUT
value=31.12.07 16:07:03

Test case on MySQL server 5.0.54BK running on WinXP Pro SP2 localhost with c/NET 5.0.8.1 and .NET FW 2.0:
  private void bnBug30518_Click(object sender, EventArgs e)
  {
      MySqlCommand commandA = new MySqlCommand();
      MySqlDataReader rdr = null;
      int num;
      try
      {
	String connectString = "server=localhost;port=3306;database=test;user id=root;password=;Pooling=False";
	// Open Connection A
	MySqlConnection connectionA = new MySqlConnection(connectString);
	connectionA.Open();
	MySqlDataAdapter adapter = new MySqlDataAdapter();
	commandA.Connection = connectionA;
        commandA.CommandType = System.Data.CommandType.Text;
        commandA.CommandText = "SELECT * FROM DOESNOTEXIST";
        adapter.SelectCommand = commandA;
        System.Data.DataSet ds = new System.Data.DataSet();
	try
	{
            DataTable tables = new DataTable();
            adapter.Fill(tables); //FAILS HERE WITH MESSAGE THAT TABLE DOES NOT EXISTS, as it should
            adapter.FillSchema(tables, SchemaType.Mapped);
	}
	catch (System.Exception t)
	{
	}
	finally
	{
	    commandA.Dispose();
	    adapter.Dispose();
	}
	// Use Connection A to select now()
        commandA.Connection = connectionA;
        commandA.CommandType = System.Data.CommandType.Text;
        commandA.CommandText = "SELECT NOW()";
        rdr = commandA.ExecuteReader();
	Console.WriteLine("CONNECTION A OUTPUT");
	while (rdr.Read())
	{
            Console.WriteLine("value=" + rdr.GetString(0));
	}
	rdr.Close();
	commandA.Dispose();
	connectionA.Close();
    }
    catch (Exception ex)
    {
      Assert.Fail(ex.Message);
    }
  }
}

Hi Tonci,

Your example does work but you are calling FillSchema differently than me. If you change your example to just call adapter.FillSchema(ds, SchemaType.Mapped); instead of creating a DataTable, calling Fill and, calling FillSchema then you should see the problem. Note that my example was written in VJ# while yours was written in C#. 

Thanks,
Paul

Paul, I see... One remark, I can't have/know every programming language there is. Currently, I'm using 6+ languages on several frameworks, many many IDE's and numerous operating systems. And I'm matched against reporters like yourself who report problems from domains they mastered log before I even heard of them. So, I will consult c/NET team regarding this now.

Tonci, I was letting you know I wrote my example in VJ# to explain why it wouldn't compile for you. You should be able to reproduce the problem in C# too though by just changing that one line in your example as I described in my last comment. If you have problems reproducing it then just let me know and I'll send you a C# example that reproduces it.

Paul

Paul, I have consulted and decision is to reverify as it appears it should work.

Thanks for your interest in MySQL.

Hello!
I have the same problem on MySql.Data 5.2.3 and MySql server 5.1.28.
See my sample code:
		public void TestOnBug()
		{
			MySqlCommand commandA = new MySqlCommand();
			try
			{
				// Set connection string
				MySqlConnection connectionA = new MySqlConnection("server=xxx;");
				connectionA.Open();
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				commandA.Connection = connectionA;
				commandA.CommandType = System.Data.CommandType.Text;
				commandA.CommandText = "SELECT * FROM DOESNOTEXIST";
				adapter.SelectCommand = commandA;
				try
				{
					DataTable tables = new DataTable();
					adapter.FillSchema(tables, SchemaType.Mapped);
				}
				catch (Exception onFill)
				{
					Console.WriteLine("Exception on FillSchema = {0}", onFill);
				}
				finally
				{
					commandA.Dispose();
					adapter.Dispose();
				}

				//This is NOT working
				object NowTime = MySqlHelper.ExecuteScalar(connectionA, "select NOW()");
				Console.WriteLine("NowTime = {0}", (NowTime == null) ? "null" : NowTime);

				//This is working
				object SchemaName = MySqlHelper.ExecuteScalar(connectionA, "SELECT schema_name FROM information_schema.SCHEMATA S limit 1");
				Console.WriteLine("SchemaName = {0}", SchemaName);

				//This is NOT working
				NowTime = MySqlHelper.ExecuteScalar(connectionA, "select NOW()");
				Console.WriteLine("NowTime = {0}", (NowTime == null) ? "null" : NowTime);

				//This is NOT working
				object testInt = MySqlHelper.ExecuteScalar(connectionA, "select 123 union select 456");
				Console.WriteLine("testInt = {0}", (testInt == null) ? "null" : testInt);
			}
			catch (Exception ex)
			{
				Console.WriteLine("Exception = {0}", ex);
			}
		}

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/78136

Yes, 5.1.8

An entry was added to the 5.1.8 changelog:

Calling MySqlDataAdapter.FillSchema on a SELECT statement that referred to a table that did not exist left the connection in a bad state. After this call, all SELECT statements returned an empty result set. If the SELECT statement referred to a table that did exist then everything worked as expected.