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: | |
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
[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.