MySQL Bugs: #61529: Cannot create Stored Procedures from my .Net Application

Bug #61529	Cannot create Stored Procedures from my .Net Application
Submitted:	15 Jun 2011 19:23	Modified:	28 Jun 2011 14:29
Reporter:	Erwin Kryszat	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.3.6	OS:	Windows (Windows 7)
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	.net, Parameter @var_Bookmarked, stored procedure

Description:
I thinks this is a bug. My software creates tables and views. When creatuing the stored procedure i get an error since I upgraded from .Net-Connector 5.0.9.0 to 6.3.6. I reversed back to 5.0.9.0 and my software has no problems to create databases in MySQL including stored procedures.

As a database server I use MySQL on my winodws laptop and linux MySQL on my NAS harddisc. This issue occurs in both cases.

So somehow crreating of procedures does not work. The error message is like this:

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> 
MySql.Data.MySqlClient.MySqlException: @var_Bookmarked Parameter must be defined.

How to repeat:
Sample Stored procedure:

CREATE PROCEDURE AddBand(
    IN var_Name nvarchar(100),
    OUT var_ID int
    )
BEGIN

SET @var_Bookmarked = 0;
SELECT * FROM Bookmarks 
WHERE Reference = var_Name AND BookmarkType = 0 ;
IF  found_rows() > 0 THEN
    BEGIN
    SET @var_Bookmarked = 1;
END;
END IF;

SET @RETURN_VALUE = -1;
SELECT  ID FROM Bands
WHERE var_Name = Name
INTO @RETURN_VALUE;
IF  found_rows() = 0  THEN
    BEGIN
        INSERT INTO Bands
            (
                Name, 
                Bookmarked
            )

        VALUES
            (
                var_Name,
                @var_Bookmarked
            );
SET @RETURN_VALUE = last_insert_id();
END;
ELSE
    BEGIN
        UPDATE Bands
        SET Name = var_Name,
        Bookmarked = @var_Bookmarked
        WHERE ID = @RETURN_VALUE;
END;
END IF;
SET  var_ID = @RETURN_VALUE;
END;

Suggested fix:
revert back to .Net Connector 5.0.9.0

Hello Erwin,

Unfortunately CREATE PROCEDURE statement is not enough to repeat the bug and it is not quite clear what data set/SP parameter set causes the exception to be thrown.

In order to continue we need the following details from you:

1. The table dump with data for `Bookmarks` and `Bands` tables (use mysqldump).

2. A repeatable C#/C++/VB test case, which shows how the parameters are
   used. In other words, is the application running stored procedure as
   a string with all parameters inserted (something like "CALL AddBand(1, 'a')")
   or it is using the Command class with parameters:

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "AddBand";

        cmd.Parameters.AddWithValue("@a", "some string");
        cmd.Parameters["@a"].Direction = ParameterDirection.Input;

Thanks.

hi,

this error does not occure if I use stored procedures with .Net and MySQL. The error occurs when my software creates the whole database including the SP.

I use the command object and call ExecuteNonQuery to create the stored procedure. AllSqlCommand is my wrapper that allows my software to run with SQL-Server and MySQL. An AllSqlCommand is actualy an MySqlCommand.

A table dump is not useful since the error occurs when I create the database. All tables are empty that time. I hope this helps. Try to create the stored procedure I send you with the command object. But I think you need the tables and views first. I can send you all scripts for them if you like.

if (Global.dbServerType == DatabaseServer.MySql)
{
		try
		{
				AllSqlCommand cmd1 = new AllSqlCommand(strSQL, ((AllSqlConnection)con));
				cmd1.CommandType = CommandType.Text;
				cmd1.ExecuteNonQuery();
		}
		catch (Exception Err)
		{
				Logger logger = new Logger();
				logger.logError(Err.ToString());
				return -1;
		}
}

Erwin,

Thanks for clarifications.
So, first I need to create the database and then the stored procedure.
Will try that and come back to you with the results.

There is a simpler test, no need to create the database or something.
To repeat the problem it is enough to init a session variable @a using SET statement as follows:

        cmd.CommandText = @"CREATE PROCEDURE get_a()
                               BEGIN
                               SET @a = 1;
                               SELECT @a;
                            END";
        cmd.ExecuteNonQuery();

The problem affects Connector/NET 6.4 too

Do you not need to DECLARE the variable before you SET it?

Thank you for taking the time to write to us, but this is not a bug. 

In order to use user variables in your stored procedures please add the "Allow User Variables" option to your connection string. More info on this can be found in the Connector/Net documentation: 

http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-connecting-connection-str...