MySQL Bugs: #46429: use DELIMITER command in MySql.Data.MySqlClient.MySqlScript

Bug #46429	use DELIMITER command in MySql.Data.MySqlClient.MySqlScript
Submitted:	28 Jul 16:09	Modified:	6 Nov 20:34
Reporter:	Laurent Yin
Status:	Can't repeat
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	6.0.3.0	OS:	Microsoft Windows
Assigned to:		Target Version:
Tags:	DELIMITER, 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 ======