Bug #46429 use DELIMITER command in MySql.Data.MySqlClient.MySqlScript
Submitted: 28 Jul 2009 14:09 Modified: 17 Feb 2010 15:32
Reporter: Laurent Yin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:trunk OS:Windows
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: DELIMITER, mysqlscript

[28 Jul 2009 14:09] Laurent Yin
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);
  }
[28 Jul 2009 14:10] Laurent Yin
a dump file that doesn't work while trying to execute script

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

[28 Jul 2009 14:21] Valeriy Kravchuk
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...
[28 Jul 2009 14:58] Laurent Yin
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.
[29 Jul 2009 1:43] Bugs System
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)
[29 Jul 2009 1:44] Reggie Burnett
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.
[29 Jul 2009 8:29] Laurent Yin
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.
[4 Aug 2009 9:58] Tony Bedford
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.
[3 Nov 2009 21:56] Werner Wolf
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 ;
[4 Nov 2009 23:33] MySQL Verification Team
See bug: http://bugs.mysql.com/bug.php?id=48548.
[6 Nov 2009 19:34] Tonci Grgin
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 ======
[23 Nov 2009 8:56] Tonci Grgin
The problem is fixed in source trees, versions 5.2.8, 6.0.5 and 6.1.3.
[25 Nov 2009 14:14] Sergei Shirokov
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).
[25 Nov 2009 14:15] Sergei Shirokov
Test VS2008 project to reproduce the problem on 6.1.3 version

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

[25 Nov 2009 14:21] Tonci Grgin
Sergei, will retest soon.
[27 Nov 2009 8:27] Tonci Grgin
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.
[15 Jan 2010 7:30] Tonci Grgin
Assigning Reggie and rising Priority to get full fix for this.
[15 Feb 2010 21:27] Bugs System
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)
[15 Feb 2010 21:27] Reggie Burnett
Fixed in 6.0.6, 6.1.4, 6.2.3, and 6.3.1+
[17 Feb 2010 15:32] Tony Bedford
Changelog entry updated.
[21 Jul 2015 15:38] Jimmy Bosse
This bug seems to have regressed in version 6.9.7.0 of the ADO.Net driver for MySQL
[22 Mar 2016 19:35] Kai Neumann
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;