MySQL Bugs: #45188: Incorrect syntax near DELIMITER at line 1 (1064) only with Mac clients

Bug #45188	Incorrect syntax near DELIMITER at line 1 (1064) only with Mac clients
Submitted:	29 May 2009 10:53	Modified:	2 Jun 2009 16:32
Reporter:	Fred Trellis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	5.1.30, 5.1.34	OS:	MacOS (10.4.latest)
Assigned to:		CPU Architecture:	Any

Description:
Same SQL consistently gives syntax error on all tried Mac clients, runs fine on WinPC.

The following syntax causes error 
"You have an error in your SQL syntax:check.....to use near 'DELIMITER' at line 1 (1064)

DROP PROCEDURE IF EXISTS `NewProc`;

DELIMITER $$

CREATE PROCEDURE NewProc()
BEGIN
# SELECT * FROM tbl1 ;
END $$

DELIMITER ;

DELIMITER 
Both are new clean installs, packaged by BitNami MampStack:
From the Mac::
MySQL 5.1.30 via TCP/IP
MySQL Client Version 5.1.8
Darwin 8.11.1
2x i386 (80486) 2160.0 MHz, 2.0 GB RAM

Same SQL, same error and error location in following clients:
MSQuery Browser 1.2.12  (mysql-gui-tools-5.0-r12-osx10.4-universal.dmg)
CocoaMySql 0.7.1
YourSQL_1.8.0h.dmg
sequel-pro-0.9.5.dmg

Possibly related to Bug #33812

How to repeat:
Try DELIMITER statement in any script on a Mac

Suggested fix:
A workaround:
Copy the SQL script from Mac to WinPC
Run the SQL script on a WimPC.
Backup the catalog.
The backup SQL runs fine on the Mac, creating the procedure correctly.

Suspect \n on Macs being misinterpreted, parser expects \r\n as on WinPC.

Applies to version MySQL 5.1.34 on Mac using same clients.

Apologies, should read ALSO applies to 5.1.34.

Thank you for the report.

But version 5.1.30 is old. Additionally I can not repeat described behavior with version 5.1.31 where bug #33812 is fixed. Please try with current version 5.1.34 and inform us if problem still exists in your environment.

My comment previous to yours indicates tested with 5.1.34, same results.
<quote>
[29 May 13:48] Fred Trellis

Applies to version MySQL 5.1.34 on Mac using same clients.
</quote>

Thank you for the feedback.

Please indicate accurate package name you use (filename you downloaded).

Tested with the following downloads:
mysql-5.1.34-osx10.4-i686.dmg (direct from MySQL website, and from a mirror)
(udates the Apple official version installed on my MacBook)
Also via two Stack products:
Bitnami MAMPStack 1.1-2 (from BitNami website - version 5.1.30)
MAMP (via http://www.mamp.info/en/index.html  - MySQL 5.0.41)

Non-useful background info,FWIW, I was using the stacks, but I cannot update the MySql part of the stacks seperately, and hence tried the OS X -installed version, which I updated tp 5.1.34.
Therefore the test was performed on, in sequence
5.1.30
5.0.41
5.1.34
These are all Community Server.

Thank you for the feedback.

I still can not repeat described behavior using package indicated:

$mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.34 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP PROCEDURE IF EXISTS `NewProc`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE NewProc()
    -> BEGIN
    -> # SELECT * FROM tbl1 ;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> 

Please provide copy-paste from your mysql command line client like I did.

The error occurs in the GUI clients, not the command line.
<quote>
Same SQL, same error and error location in following clients:
MSQuery Browser 1.2.12  (mysql-gui-tools-5.0-r12-osx10.4-universal.dmg)
CocoaMySql 0.7.1
YourSQL_1.8.0h.dmg
sequel-pro-0.9.5.dmg
</quote>

I'm a relative newcomer to the Mac, mostly WinPC in my history.  On the Mac, I'm not comfortable dropping in to the terminal (bash shell) because the underlying BSD Unix is totally foreign to me.
I've never used MySQL command line in any environment.  I'm an architect/designer who likes to play  with tools to get to grips with how to use them.  I am not a developer, just a super-super-super user.

Thank you for the feedback.

> I've never used MySQL command line in any environment. 

Is better to test with MySQL command line client, because problem described can be bug in a tool you use also. Anyway, please provide screenshot of MySQL Query Browser, so we can see what you do.

I understand testing with the command line is good for bug-hunting purposes.
The solution needs to work with a GUI client, unless you recommend all DDL, DML be entered through the Command line.
Strongly suspect this cannot really be classed as a parser 'bug' though.  If my hunch is correct, rather it is a design oversight in the parser.  Since it seems to affect ALL GUI clients on the Mac (that I have tested in any case), it may be that testing on Mac neglected this one statement - which would be a reasonable assumption. Since DELIMITER has a unique syntax of not being able to have an end of statement delimiter itself, the \r\n is used as the end of statement.  When it is only the \n, then the parser is chopping off 2 characters, and ignoring whitespace, the parser is seeing 'DELIMITER' with no new delimiter specified.

I have tried 'DELIMITER $$' thinking it may be happy to remove a redundant character, resulting in an effective delimiter of '$' - but no.  i believe it is looking for the missing \r specifically.

Herewith a screenshot of the problem in action in Query Browser.
 when the

Query Browser screenshot of bug in Mac clients.

Attachment: MySQL.jpg (image/jpeg, text), 183.17 KiB.

Thank you for the feedback.

Which version of MySQL Query Browser do you use? There were bug fixed related to DELIMITER keyword in it. Please also try "delimiter"

Regarding to third-party GUI Tools strictly say DELIMITER is client keyword and not SQL statement, so this just can be bug in the tool or you just don't need to use DELIMITER at all.

I meant bug #40172

<quote>
Same SQL, same error and error location in following clients:
MSQuery Browser 1.2.12 <<<<<<<<<<================
(mysql-gui-tools-5.0-r12-osx10.4-universal.dmg)
CocoaMySql 0.7.1
YourSQL_1.8.0h.dmg
sequel-pro-0.9.5.dmg
</quote>

Thank you for the feedback.

Regarding to MySQL Query Browser this is duplicate of bug #40172. Regarding to other programs - read documentation for them, because DELIMITER is client command and not SQL statement as described at http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html.

You are right in it being a client command. Your diagnosis clears the bug report from the server parser functionality POV, but fails to address the users' problem with mysql.

It is not a duplicate, because bug  #40172 is on WinPC.  I specifically state this is not an issue on WinPC, but only on OS X.

It is not specific to MySQL Query Browser, but generic to all Mac GUIs - if you prefer, it is platform related

Any Mac-based GUI, including MySQLs own QB, is going to fail to create multi-line procedures, rather impacting on the claimed functionality.

I still feel you need to address that issue - but more as a useability issue, rather than a bug.

As you say, any DBA can CREATE PROCEDURES with the command line client, so it isn't really a problem, real programmers don't need any fancy-pants GUI!

Best regards, Fred.