MySQL Bugs: #15092: delimiter // has not effect command-line

Bug #15092	delimiter // has not effect command-line
Submitted:	21 Nov 2005 8:27	Modified:	21 Nov 2005 11:09
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Linux (Linux Fedora 2)
Assigned to:		CPU Architecture:	Any

Description:
I use the following code:

DELIMITER // 

CREATE PROCEDURE payment
()
BEGIN
SELECT "test";
END;
//

This is the error message:

ERROR 1064 : 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 PROCEDURE payment
()
BEGIN
SELECT "test"' at line 1
ERROR 1064 : 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 'END' at line 1
  

How to repeat:
DELIMITER // 

CREATE PROCEDURE payment
()
BEGIN
SELECT "test";
END;
//

Suggested fix:
Test the Delimiter statement

Please see below the syntax: ...payment()

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER // 
mysql> 
mysql> CREATE PROCEDURE payment()
    -> BEGIN
    -> SELECT "test";
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call payment();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Hi,
I am also suffering with the same problem using 5.0.15 on Redhat 7.2 and Enterprise.

mysql> DELIMITER //
    ->
    ->
    ->
    -> ;
ERROR 1064: 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

I would suggest testing on 5.0.15 installation to try and replicate the problem.

A little bit more info...
both my servers previously had MySQL versions 3.23.54 and MySQL 4.1.1-alpha installed.

noticed that doing -
[root@dandelion php-libs]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 167 to server version: 5.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> help

MySQL commands:
Note that all text commands must be first on line and end with ';'
help    (\h)    Display this help.
?       (\?)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server. Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server, display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)    Print current command.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute a SQL script file. Takes a file name as an argument.
status  (\s)    Get status information from the server.
tee     (\T)    Set outfile [to_outfile]. Append everything into given outfile.
use     (\u)    Use another database. Takes database name as argument.

Connection id: 167  (Can be used with mysqladmin kill)

does not give the correct help file as listed at http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

**NOTE the lack of \d option i.e. delimiter!!!

library issue maybe? do I need to upgrade libmysqlclient ? tried the MySQL-client-5.0.15-0.i386.rpm but to no avail...

Please try:

mysql --version

and see what it prints.

was a configuration issue this is what was happening...

Installed MySQL 5 into /usr/local/mysql.
Left the existing MySQL 3 binaries/libraries installed by default untouched.
On startup it would start the 5.0.15 server but the mysql command was still using the version 3 mysqlclient (mysql --version confirmed this)

Fixed my servers by modifying the PATH to use /usr/local/mysql/bin (MySQL 5) before /usr/bin (MySQL 3 binaries).

Thanks for all your help.