Creating a StoreProc Syntax Error

[mmTomArcher]

Afternoon,

I'm trying to use DbUp to create a new Store Proc and I'm getting a syntax error when execute PerformUpgrade... My script works directly on a query window on the MySQL database.

here is the query

DROP PROCEDURE IF EXISTS My_StoreProc;

CREATE PROCEDURE My_StoreProc;(strEmail VARCHAR(255))

BEGIN SELECT id FROM table WHERE email = strEmail ORDER BY id DESC limit 1; END;

Here is the error: Script block number: 1; Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command) at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_2.b__1() at DbUp.MySql.MySqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)

Any suggestions, is there a work arround, is this a know bug?

[jafin]

@mmTomArcher I think the issue is with the MySQL parser splitting on the limit1; inside the BEGIN..END

--cmd1
DROP PROCEDURE IF EXISTS My_StoreProc

--cmd2
CREATE PROCEDURE My_StoreProc(strEmail VARCHAR(255))
BEGIN
SELECT id FROM table_name WHERE email = strEmail
ORDER BY id DESC limit 1

--cmd3
END

As a workaround you could try changing the delimier

DELIMITER $$

DROP PROCEDURE IF EXISTS My_StoreProc$$

CREATE PROCEDURE My_StoreProc(strEmail VARCHAR(255))
BEGIN
SELECT id FROM table_name WHERE email = strEmail
ORDER BY id DESC limit 1;
END$$

This test fails but passes if setting a DELIMITER:

  public class MySqlParserTests
    {
        private int commandCount = 0;

        [Fact]
        public void Parse()
        {
            MySqlCommandReader reader = new MySqlCommandReader(@"
DROP PROCEDURE IF EXISTS My_StoreProc;

CREATE PROCEDURE My_StoreProc(strEmail VARCHAR(255))
BEGIN
SELECT id FROM table_name WHERE email = strEmail
ORDER BY id DESC limit 1;
END;
");
            reader.ReadAllCommands(CommandHandler);
            commandCount.ShouldBe(2);
        }

        private void CommandHandler(string val)
        {
            Console.WriteLine(val);
            commandCount++;
        }
    }