Fluentmigrator: Mysql Execute.EmbeddedScript Failed

[biju-ps]

Hi I just wanted to create a function in Mysql.

Sql Script Content
_test.sql_

DELIMITER $$
CREATE DEFINER=`user`@`localhost` FUNCTION `fn_first_day`(
`Any_date` DATE
)
RETURNS date
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare retu varchar(20) ;
SET @started=TRUE;
set retu =DATE_FORMAT(Any_date ,'%Y-%m-01');
  RETURN cast(retu as date) ;
END$$
DELIMITER ;

I am using the Execute.EmbeddedScript as follows
Execute.EmbeddedScript("App.Migrator.Migrators.test.sql");

and I am getting the following error from Mysql

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 'DELIMITER $$

DROP PROCEDURE IF EXISTS proc_getCompany_det$$
CREATE DEFINER' at line 1

The script is working perfectly in MysqlWorkbench
I double checked, that the file is embedded in the assembly

       // checking the embeded file is there ?
    var asm = typeof(App.Migration.Migrators.Migartion201910050542).Assembly;
        foreach (var n in asm.GetManifestResourceNames())
                   Console.WriteLine("Embeded file :"+ n)

What is wrong with my code? Please help

Mysql Ver .5.7 on Ubuntu 18.04
Dotnet Framework : 4.6.1
FM Version : 3.2.1

[jzabroski]

Did you try using MySQL Profiler to see what gets sent to the server? Note, I've never profiled a MySQL database before, but it seems _Neor Profile SQL_ tool is very similar to how SQL Server Profiler works, albeit a bit idiosyncratic in that you have to point to a special port to debug. http://www.profilesql.com/use/

[biju-ps]

Installed and checked Neor Profile .But nothing is showing on it :(

[jzabroski]

What's the simplest way for me to install MySQL v5.7 so I can try to repro your issue? I'm currently a SQL Server expert but am being asked more and more by my clients for MySQL help, so I see much upside into debugging this problem with you as it will expose me to the MySQL stack for the first time since college ~15 years ago.

[fubar-coder]

My assumption is that the ADO.NET driver doesn't support DELIMITER. I checked it and we're not splitting the SQL commands for MySQL, so it shouldn't be a problem on our side.

This Stack Overflow question: C#, MySQL, ADO.NET, delimiter causing syntax error points to a bug report, but this issue is for the MySqlScript class which we're not using. You may find a workaround (albeit an ugly one) in this Stack Overflow question: Mysql delimiter syntax error only get error on c#.

The best solution would be to use MySqlScript with its MySqlScript.Delimiter property, but I'm not sure how to implement it.

EDIT: Clarification

[jzabroski]

@fubar-coder Wow! Great detective work. I agree, the issue was not our BatchParser / ISpecialTokenSearcher, as MySQL doesn't use the SqlBatchParser abstract base class, which only implements one ISpecialTokenSearcher via SearchContext class. That's where I initially looked, and did not mention it in my reply as to avoid confusion.

In terms of how to implement it, why can't we implement a SqlBatchParser for MySQL (MySqlBatchParser?) that detects a line matches ^DELIMITER .*$, and, if it does, use the MySqlScript class to execute the statement batch? It's 8:57am est so morning coffee is still kicking in, so apologize if I'm missing something.

[fubar-coder]

Might be possible. Maybe we should give it a try and release a fixed 3.x version.

[jzabroski]

P.S. - Shouldn't SqlBatchParser be an abstract base class? It's currently not. I'm not sure how useful it is to be non-abstract, as its a fairly idiosyncratic way to parse SQL statements into batches.

[fubar-coder]

The reason why it's not an abstract base class, was that it can be configured and used without the need for a derived class, but it seems that we're always inheriting from SqlBatchParser.

It was a middle ground between complex regular expressions and a full-blown lexer/parser to be able to support - for example - nested multi-line comments. It gets the job done 😁 , but I'm always willing to replace it with something simpler/better.

EDIT: Clarification

[biju-ps]

According to https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-using.html

Unlike the command-line and GUI clients, you are not required to specify a special delimiter when creating stored procedures in Connector/NET.

I will check this and update here soon

[ezsoftware]

Thank you, removing the delimiter also fixed this issue for me.