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

[13 Jan 2007 16:45] Yasin Hinislioglu
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'.
[18 Jan 2007 11:51] Tonci Grgin
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();
[23 Jan 2007 21:33] 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/18661
[23 Jan 2007 22:35] 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/18666
[23 Jan 2007 22:37] Reggie Burnett
Fixed in 5.0.4 and 1.0.9
[31 Jan 2007 16:03] MC Brown
A note has been added to the 1.0.9 and 5.0.4 changelogs.