MySQL Bugs: #25609: MySqlDataAdapter.FillSchema

Bug #25609	MySqlDataAdapter.FillSchema
Submitted:	13 Jan 2007 16:45	Modified:	31 Jan 2007 16:03
Reporter:	Yasin Hinislioglu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.3.0	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	FillSchema, MySqlDataAdapter

Description:
When trying to fill table schema using stored procedures causes a runtime error.

How to repeat:
Create a catalog called "try". Create a table called "mytable". Add integer primary key column "id", add varchar(45) column "name".

Insert following stored procedure to schema.

CREATE DEFINER=`root`@`localhost` PROCEDURE `select_all`()
BEGIN
  select * from mytable;
END

In Visual Basic .Net 2005 try to run following code.

        Dim conn As New MySqlConnection("server=localhost;user id=; password=; database=try;")
        Dim da As New MySqlDataAdapter
        Dim cmd As New MySqlCommand()
        Dim schema As New DataTable

        conn.Open()

        cmd.Connection = conn
        cmd.CommandText = "select_all"
        cmd.CommandType = CommandType.StoredProcedure
        da.SelectCommand = cmd
        da.FillSchema(schema, SchemaType.Source)

        conn.Close()

Running this code snippet causes following exception

Procedure or function 'SET SQL_SELECT_LIMIT=0;select_all;SET sql_select_limit=-1;' cannot be found in database 'try'.

Hi Yasin and thanks for your report.
Verified as described by reporter on latest c/NET 5.0 sources.
command.cs:
		public new MySqlDataReader ExecuteReader(CommandBehavior behavior)
		{
			lastInsertedId = -1;
			CheckState();

			if (cmdText == null ||
				 cmdText.Trim().Length == 0)
				throw new InvalidOperationException(Resources.CommandTextNotInitialized);

			string sql = TrimSemicolons(cmdText);

			//TODO: make these work with prepared statements and stored procedures
			if (0 != (behavior & CommandBehavior.SchemaOnly))
			{
				sql = String.Format("SET SQL_SELECT_LIMIT=0;{0};SET sql_select_limit=-1;", sql);
			}

Judging from the comment, problem is probably known to devs.

Test case:
            MySqlConnection connection = new MySqlConnection();
            MySqlCommand command = new MySqlCommand("testbug25609",connection);
            command.CommandType = CommandType.StoredProcedure;

            connection.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;";//ignore prepare=True";
            connection.Open();
            //command.Prepare();

            DataSet dataSet = new DataSet();
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);

            dataAdapter.FillSchema(dataSet, System.Data.SchemaType.Source);
            commandBuilder.RefreshSchema();
            connection.Close();

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

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

Fixed in 5.0.4 and 1.0.9

A note has been added to the 1.0.9 and 5.0.4 changelogs.