Bug #62090 Syntax Error using delimiter with MySqlCommand
Submitted: 5 Aug 2011 9:25 Modified: 8 Aug 2011 17:03
Reporter: Simon Höddels Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.4.3 OS:Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: DELIMITER, MySqlCommand

[5 Aug 2011 9:25] Simon Höddels
Description:
I'm on Windows XP SP3, .NET 4.0 and using Connector/NET 6.4.3.
MySQL version are 5.6.2 and 5.1.57. (Tested on two different machines.)
When executing and MySqlCommand with a delimiter, an MySqlException is thrown.

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 $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(IN row_ID INT)' at line 1

I've also tried different variations, nothing worked. It ran fine when I used mysql.exe or HeidiSQL:
mysql> DROP PROCEDURE IF EXISTS p_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS t_test (ID INT PRIMARY KEY AUTO_INCREMENT, som
etext VARCHAR(100));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(IN row_ID INT) BEGIN
    ->          DELETE FROM t_test WHERE ID > 0;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

How to repeat:
Execute this:
using (MySqlCommand com = new MySqlCommand())
{
	String sql = "";
	sql = "DROP PROCEDURE IF EXISTS p_test; " +
	"CREATE TABLE IF NOT EXISTS t_test (ID INT PRIMARY KEY AUTO_INCREMENT, sometext VARCHAR(100));" +
	"DELIMITER $$\n" +
	"CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(IN row_ID INT) " +
	"BEGIN " +
	"	DELETE FROM t_test WHERE ID > 0; " +
	"END " +
	"$$";
	com.CommandText = sql;
	com.ExecuteNonQuery();
}

Suggested fix:
Work with MySqlScript instead:
using (MySqlCommand com = new MySqlCommand())
{
	String sql = "";
	sql = "DROP PROCEDURE IF EXISTS p_test; " +
	"CREATE TABLE IF NOT EXISTS t_test (ID INT PRIMARY KEY AUTO_INCREMENT, sometext VARCHAR(100));" +
	"DELIMITER $$\n" +
	"CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(IN row_ID INT) " +
	"BEGIN " +
	"	DELETE FROM t_test WHERE ID > 0; " +
	"END " +
	"$$";
	MySqlScript script = new MySqlScript(db.Connection, sql);
	//script.Delimiter = "$$";
	script.Execute();
}
[5 Aug 2011 9:27] Simon Höddels
Suggested fix is a workaround. Not a suggested fix. Mixed that up. Sorry!
Suggested fix would be to make DELIMITER work in MySqlCommand. ;)
[8 Aug 2011 17:03] Fernando Gonzalez.Sanchez
Hi Simon,

Thanks for your report, actually MySqlCommand lack of support of custom delimiter is by design, and it won't be supported.

As you suggested (and as stated by the comment in class MySqlScript: Provides a class capable of executing a SQL script containing multiple SQL statements including CREATE PROCEDURE statements that require changing the delimiter.), 
if you need to use Delimiter, you have stick to MySqlScript.

Thanks.