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

Bug #46429	use DELIMITER command in MySql.Data.MySqlClient.MySqlScript
Submitted:	28 Jul 2009 16:09	Modified:	27 Nov 2009 9:27
Reporter:	Laurent Yin
Status:	Verified
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	trunk	OS:	Microsoft Windows
Assigned to:	Reggie Burnett	Target Version:
Tags:	mysqlscript, DELIMITER
Triage:	D3 (Medium)

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.