Bug #46429 use DELIMITER command in MySql.Data.MySqlClient.MySqlScript

Description:
I dumped a database and I would like to restore it programmatically using .Net connector.
Restoring tables works perfectly, however, when trying to create stored procedures, a MySqlException is thrown when trying to redefine the DELIMITER

MySql.Data.MySqlClient.MySqlException was unhandled
  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 'DELIMITER' at line 1"
  Source="MySql.Data"
  ErrorCode=-2147467259
  Number=1064
  StackTrace:
       à MySql.Data.MySqlClient.MySqlStream.ReadPacket()
       à MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
       à MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
       à MySql.Data.MySqlClient.MySqlDataReader.NextResult()
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
       à MySql.Data.MySqlClient.MySqlScript.Execute()

Is it the expected behaviour? I really don't want to parse the sql file myself to find blocks with a delimiter.

How to repeat:
- create a stored procedure for any database
- store it in a sql file : storedproc.sql
- use .Net connector
- execute this code
  using (MySqlConnection connection = new MySqlConnection(cnxString))
  {
    connection.Open();
    MySqlScript script = new MySqlScript(connection, File.ReadAllText(@"storedproc.sql"));

    // throws the exception
    script.Execute();
    Console.WriteLine(rows);
  }

a dump file that doesn't work while trying to execute script

Attachment: dump.sql (application/octet-stream, text), 2.91 KiB.

DELIMITER is not a server's statement, it is client command of mysql command line client. Check http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html and http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html for the details.

Looks like you will have to parse the file, but it should be easy to do...

it shouldn't be that easy, I have to find lines in the form DELIMITER $something$, while trying to find out if it is a command that is supposed to be processed or just part of a quoted string containing a newline. I was hoping I could take advantage of the parsing that you probably already do in the library.

Is there any other way I can use the connector to execute a .sql file? I wanted to issue a "source" command, but it doesn't work if the file is not on the server.

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

698 Reggie Burnett	2009-07-28
      - fixed MySqlScript class so that it respects delimiter statements (bug #46429)

fixed in 5.2.8 and later.  Starting with 5.2.8 and 6.0.5 and 6.1.1 our MySqlScript class now supports the delimiter statement as it is found in sql scripts.

Thanks Reggie. This is much appreciated.
I was struggling with the fact that it could be part of a string, or that the delimiter could be enclosed with single-quotes, double-quotes or nothing at all. Your way looks much simpler. I'll get the connector once it is released.

An entry has been added to the 5.2.8, 6.0.5 and 6.1.1 changelogs:

When trying to create stored procedures from a SQL script, a MySqlException was thrown when attempting to redefine the DELIMITER:

MySql.Data.MySqlClient.MySqlException was unhandled
  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 'DELIMITER' at line 1"
  Source="MySql.Data"
  ErrorCode=-2147467259
  Number=1064
  StackTrace:
       à MySql.Data.MySqlClient.MySqlStream.ReadPacket()
       à MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64&
lastInsertId)
       à MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
       à MySql.Data.MySqlClient.MySqlDataReader.NextResult()
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
       à MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
       à MySql.Data.MySqlClient.MySqlScript.Execute()

Note: The MySqlScript class has been fixed to support the delimiter statement as it is found in SQL scripts.

Sorry, does not work on my platform.
Connector c# 6.1.2
VS2005, .NET 2.0 SP1, C#

DROP PROCEDURE IF EXISTS "GetConsultantInfo";
DELIMITER $$
CREATE PROCEDURE "GetConsultantInfo"(OUT pResult VARCHAR(500))
BEGIN
  select 'abc' into pResult;
END $$
DELIMITER ;

See bug: http://bugs.mysql.com/bug.php?id=48548.

Not repeatable with latest sources from trunk:
  using (MySqlConnection cn = new MySqlConnection("DataSource=xx;Database=test;UserID=xx;Password=xx;PORT=xx;logging=True;charset=utf8;"))
  {
      cn.Open();
      MySqlScript script = new MySqlScript(cn, File.ReadAllText(@"bug46429.sql"));
      try
      {
           script.Execute();
      }
      catch (Exception ex)
      {
          Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
          throw;
      }
  }

Script bug46429.sql:
DROP PROCEDURE IF EXISTS `bug46429`;
DELIMITER $$
CREATE PROCEDURE `bug46429`(OUT pResult VARCHAR(500))
BEGIN
  select 'abc' into pResult;
END $$
DELIMITER ;

Output:
WindowsFormsApplication1.vshost.exe Information: 0 : [06.11.09 20:29:56] - 
====== Query logged ======
Time of query: 06.11.09 20:29:56
host: xx
time of execution: 0.0004623 seconds (0.4623 milliseconds
-- Result --
Rows affected: 0
Inserted Id (if any): 0
Command text:
DROP PROCEDURE IF EXISTS `bug46429`
====== End of Query ======

WindowsFormsApplication1.vshost.exe Information: 0 : [06.11.09 20:29:56] - 
====== Query logged ======
Time of query: 06.11.09 20:29:56
host: xx
time of execution: 0.0007034 seconds (0.7034 milliseconds
-- Result --
Rows affected: 0
Inserted Id (if any): 0
Command text:
CREATE PROCEDURE `bug46429`(OUT pResult VARCHAR(500))
BEGIN
  select 'abc' into pResult;
END
====== End of Query ======

The problem is fixed in source trees, versions 5.2.8, 6.0.5 and 6.1.3.

Problem still exists in 6.1.3.0. Please take a look to the attached project, which 100% reproduces the problem.
The bug in the 'BreakIntoStatements' method, exactly in the "startPos = endPos + delimiter.Length;" line. There should be 'currentDelimiter' (instead of 'delimiter' which is actually member field).

Test VS2008 project to reproduce the problem on 6.1.3 version

Attachment: TryDelimiter.zip (application/zip, text), 123.54 KiB.

Sergei, will retest soon.

Sergei's test case (attached) fails against every tree in source repository. Problem seems to be in parsing second SP:
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 'ySuperDelimiter
CREATE PROCEDURE TestProcedure2()
BEGIN
  SELECT * FROM mysql' at line 1

However, this is partially related to protocol:
mysql> DELIMITER MySuperDelimiter
mysql>
mysql> CREATE PROCEDURE TestProcedure1()
    -> BEGIN
    ->   SELECT * FROM mysql.proc;
    -> END MySuperDelimiter
Query OK, 0 rows affected (0.00 sec)

    ->
    -> CREATE PROCEDURE TestProcedure2()
    -> BEGIN
    ->   SELECT * FROM mysql.proc;
    -> END MySuperDelimiter
ERROR 1064 (42000): 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 'r

CREATE PROCEDURE TestProcedure2()
BEGIN
  SELECT * FROM mysql.proc;
END' at line 1
    ->
    -> DELIMITER ;
    -> \c
mysql>

Protocol depends on the length of actual delimiter used:
mysql> DELIMITER MyDelimiter
mysql>
mysql> CREATE PROCEDURE TestProcedure1()
    -> BEGIN
    ->   SELECT * FROM mysql.proc;
    -> END MyDelimiter
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE PROCEDURE TestProcedure2()
    -> BEGIN
    ->   SELECT * FROM mysql.proc;
    -> END MyDelimiter
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

Shortening the delimiter to "MyDelimiter" or even "MyDeli" in Sergei's test, doesn't help c/NET though so we do have a bug here:
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 'yDeli(miter)
CREATE PROCEDURE TestProcedure2()
BEGIN
  SELECT * FROM mysql.proc' at line 1

The bug in c/NET is that it can't deal with *two* consecutive CREATE PROCEDURE statements using custom delimiter. This is *not* the original bug reported! Original bug is fixed.

Bug in server docs is in that it does not describe maximum length for custom delimiter (or I can not find it).

As a workaround, I suggest to execute SP creation DML's one by one.

Assigning Reggie and rising Priority to get full fix for this.

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

793 Reggie Burnett	2010-02-15
      - fixed issue with script execution & multi-char delimiters (bug #46429)

Fixed in 6.0.6, 6.1.4, 6.2.3, and 6.3.1+

Changelog entry updated.

This bug seems to have regressed in version 6.9.7.0 of the ADO.Net driver for MySQL

On 6.9.8, too.

I have the problem on Win 7x64, VS2013 C#, Server 5.6.29

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;                                                                                                                   
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;                                                                                                    
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';                                                                                                  

DELIMITER $$\r\n                                                                                                                                                           
                                                                                                                                                                           
USE `elite_db`$$                                                                                                                                                           
CREATE DEFINER = CURRENT_USER TRIGGER `elite_db`.`tbCommodityData_AFTER_INSERT` AFTER INSERT ON `tbCommodityData` FOR EACH ROW                                             
BEGIN                                                                                                                                                                      
	INSERT INTO `elite_db`.`tbPriceHistory`                                                                                                                                 
	(`station_id`, `commodity_id`, `Sell`, `Buy`, `Demand`, `DemandLevel`, `Supply`, `SupplyLevel`, `Sources_id`, `timestamp`)                                              
	VALUES                                                                                                                                                                  
	(NEW.`station_id`, NEW.`commodity_id`, NEW.`Sell`, NEW.`Buy`, NEW.`Demand`, NEW.`DemandLevel`, NEW.`Supply`, NEW.`SupplyLevel`, NEW.`Sources_id`, NEW.`timestamp`);	    
END$$                                                                                                                                                                      
                                                                                                                                                                           
USE `elite_db`$$                                                                                                                                                           
CREATE DEFINER = CURRENT_USER TRIGGER `elite_db`.`tbCommodityData_AFTER_UPDATE` AFTER UPDATE ON `tbCommodityData` FOR EACH ROW                                             
BEGIN                                                                                                                                                                      
    IF (NEW.Sell <> OLD.Sell) OR (NEW.Buy <> OLD.Buy) OR (NEW.Sources_id <> OLD.Sources_id) OR                                                                             
       (TIMESTAMPDIFF(hour, OLD.timestamp, NEW.timestamp) > 24) THEN                                                                                                       
		INSERT INTO `elite_db`.`tbPriceHistory`                                                                                                                             
		(`station_id`, `commodity_id`, `Sell`, `Buy`, `Demand`, `DemandLevel`, `Supply`, `SupplyLevel`, `Sources_id`, `timestamp`)                                          
		VALUES                                                                                                                                                              
		(NEW.`station_id`, NEW.`commodity_id`, NEW.`Sell`, NEW.`Buy`, NEW.`Demand`, NEW.`DemandLevel`, NEW.`Supply`, NEW.`SupplyLevel`, NEW.`Sources_id`, NEW.`timestamp`);	
	END IF;                                                                                                                                                                 
END$$                                                                                                                                                                      
                                                                                                                                                                           
DELIMITER ;\r\n                                                                                                                                                            
                                                                                                                                                                           
SET SQL_MODE=@OLD_SQL_MODE;                                                                                                                                                
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;                                                                                                                            
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;