Bug #57516 DELIMITER must be followed by a 'delimiter' character or string
Submitted: 18 Oct 2010 10:48 Modified: 22 Oct 2010 10:06
Reporter: Kedar Vaijanapurkar Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1.50 OS:MacOS (Darwin Kernel Version 8.11.1)
Assigned to: CPU Architecture:Any
Tags: DELIMITER

[18 Oct 2010 10:48] Kedar Vaijanapurkar
Description:
(root@localhost) [(none)] mysql> delimiter ;
ERROR:
DELIMITER must be followed by a 'delimiter' character or string

This also restricts to load stored sqls from mysql-dump.
I have installed MySQL on MAC-OS-X with tarball mysql-5.1.50-osx10.4-i686.tar.gz

How to repeat:
mysql> delimiter ;
[18 Oct 2010 10:58] Peter Laursen
I think the answer is that DELIMITER is not a SQL statement in the strict sense. It is handled client-side - not server side.  So in principle every client may use any implementation of their own.  GUI clients could even use coloring, indentation or other 'styling'. The server does not forbid that. DELIMITER as such is never sent to the server.

Also I have seen quite a lot of (perl?) scripts where 'delimiter <token>' was concluded with 'current delimiter'. So this looks like a client/console issue with Mac.

But it causes inconsistencies in particular with constructs like

DELIMITER ;;  -- (what I think I have seen used in 'mysqldumps')

Peter
(not a MySQL person)
[18 Oct 2010 11:19] Kedar Vaijanapurkar
While you load dump with  "delimiter ;;" it still gives the same error.
[18 Oct 2010 11:39] Peter Laursen
yes .. 

DELIMITER ;; 

was introduced in mysqldump at some time (before I think they used $$ or something similar). It will fail with several clients. Actually we (SQLyog) rewrote our editor code to handle it a few years ago.  Before that we also needed 'DELIMITER to be delimited'.  It had the advantage that you could continue on the same line.

But a lot of special characters (like &, $, |, ?) may be interpreted as a command or instruction of some kind in some clients and I could image that that was why it was changed.
[18 Oct 2010 11:45] Peter Laursen
But category is wrong.  It is not a server/parser issue - it is a client issue.  DELIMITER is handled client side and no DELIMITER statement is ever sent to the server (verify in general log if you want).
[18 Oct 2010 11:49] Kedar Vaijanapurkar
okay.
I installed latest version MySQL 5.1.51 (From mysql-5.1.51-osx10.4-i686.tar.gz)
It still giving the same error.

+I already have another Mac Box(Darwin) same configuration installed with MySQL 5.1.50-log [select version();] for which "DELIMITER ;" works well!!
[18 Oct 2010 11:49] Peter Laursen
And there is no --delimiter option in mysqldump according to:
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
[19 Oct 2010 5:38] Kedar Vaijanapurkar
Its troublesome while creating Stored routines / loading dump etc.
[19 Oct 2010 9:29] Kedar Vaijanapurkar
Well I moved back to 5.1.50 and replaced mysqldump file's 'DELIMITER ;;' with 'DELIMITER |'.
But that's a work-around,not a solution!
[20 Oct 2010 20:02] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior:

$tar -xzf mysql-5.1.51-osx10.4-i686.tar.gz

$cd mysql-5.1.51-osx10.4-i686

$cd mysql-test/

$./mtr alias --start &
[1] 9239

$Logging: ./mtr  alias --start
101020 22:59:44 [Warning] Setting lower_case_table_names=2 because file system for /tmp/Qwuj6na17J/ is case insensitive
101020 22:59:44 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.1.51
Checking supported features...
 - skipping ndbcluster, mysqld not compiled with ndbcluster
 - SSL connections supported
Collecting tests...
vardir: /Users/apple/Documents/_installers/mysql-5.1.51-osx10.4-i686/mysql-test/var
Removing old var directory...
Creating var directory '/Users/apple/Documents/_installers/mysql-5.1.51-osx10.4-i686/mysql-test/var'...
Installing system database...
Using server port 55480

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1] 
Started [mysqld.1 - pid: 9249, winpid: 9249]
worker[1] Using config for test main.alias
worker[1] Port and socket path for server(s):
worker[1] mysqld.1  13000  /Users/apple/Documents/_installers/mysql-5.1.51-osx10.4-i686/mysql-test/var/tmp/mysqld.1.sock
worker[1] Waiting for server(s) to exit...

$../bin/mysql -uroot -S  /Users/apple/Documents/_installers/mysql-5.1.51-osx10.4-i686/mysql-test/var/tmp/mysqld.1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> DELIMITER ;
mysql> 

Do you run this command in the command line or just from dump file?
[21 Oct 2010 5:09] Kedar Vaijanapurkar
try:

mysql> delimiter ;
mysql> delimiter ;
ERROR:
DELIMITER must be followed by a 'delimiter' character or string

When current delimiter is specified again, it errors.
When you load mysqldump this errors.
[21 Oct 2010 9:03] Sveta Smirnova
Thank you for the feedback.

I still can not get this error:

$../bin/mysql -uroot -S  /tmp/UYzBnYpyHq/mysqld.1.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> delimiter ;
mysql> delimiter ;
mysql> delimiter ;
mysql> 

Please send us your configuration file.
[21 Oct 2010 9:27] Kedar Vaijanapurkar
Be
[21 Oct 2010 9:28] Kedar Vaijanapurkar
contents of my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
lower_case_table_names = 2
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log             = /var/log/mysqlgeneral.log
server-id               = 11
lower_case_table_names = 2
skip_slave_start=1
lower_case_table_names=2
skip-federated
innodb_file_per_table = 1
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
no-auto-rehash
prompt=(\\u@\\h) [\\d] mysql>\\_
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

Server OS is Mac OS X 10.4 (Darwin Kernel Version 8.11.1)
[21 Oct 2010 10:32] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior. Which SHELL and locale do you use? What commands `echo $SHELL` and `locale` output?
[21 Oct 2010 10:43] Kedar Vaijanapurkar
locale::
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL="C"

bash::
/bin/bash
[21 Oct 2010 21:22] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior. What command \s outputs?
[22 Oct 2010 5:19] Kedar Vaijanapurkar
(root@localhost) [(none)] mysql> \s
--------------
mysql  Ver 14.7 Distrib 4.1.22, for apple-darwin8.0 (powerpc) using  EditLine wrapper

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.50-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 21 hours 54 min 20 sec

Threads: 1  Questions: 21008  Slow queries: 68  Opens: 2672  Flush tables: 2  Open tables: 247  Queries per second avg: 0.266
--------------
[22 Oct 2010 9:58] Sveta Smirnova
Thank you for the feedback.

There is inconsistency with:

mysql  Ver 14.7 Distrib 4.1.22, for apple-darwin8.0 (powerpc) using 
EditLine wrapper

While in mysql command line client which came from 5.1.51 tar.gz package for me I see:

./bin/mysql  Ver 14.14 Distrib 5.1.51, for apple-darwin8.11.1 (i386) using readline 5.1

Please use command line client from 5.1.51 distribution.
[22 Oct 2010 10:00] Sveta Smirnova
For 4.1 client this behavior is not a bug.
[22 Oct 2010 10:06] Kedar Vaijanapurkar
I replaced /user/bin/mysql with new mysql client. Fixes issue.
I appreciate your support. Thanks.