MySQL Bugs: #62090: Syntax Error using delimiter with MySqlCommand

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

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();
}

Suggested fix is a workaround. Not a suggested fix. Mixed that up. Sorry!
Suggested fix would be to make DELIMITER work in MySqlCommand. ;)

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.