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