DELIMITER in mysql does not work

Question

The below code works correctly when I use command line (mysql.exe) to execute, but incorrect when I use SQL Query in phpmyadmin or using ScriptRunner (Java) to execute.

The error: Error Syntax execute DELIMITER $$

How can I solve this problem?

-- Procedures
-- 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `upload_photo`(
IN owner_id int, IN photo_name text, IN photo_url text, IN thumbnail text
)
begin
declare inserted_id int unsigned default 0;
INSERT INTO photo(`owner_id`, `photo_name`, `photo_url`, `thumbnail`) VALUES(owner_id, photo_name, photo_url, thumbnail);
 set inserted_id = last_insert_id();
 select * from photo where photo_id = inserted_id;
end$$

-- 
DELIMITER ;

Possible duplicate of [this thread](http://stackoverflow.com/questions/9017269/mysql-delimiter-syntax-error) — Rik, Aug 07 '13 at 09:39
@Rik. Thanks for supporting. But I don't think this post can solve my problem. If you can. Please help me. — Luc, Aug 07 '13 at 09:47
What version of phpmyadmin are you using? Does it have a delimiter-input-box below the sql-statement? — Rik, Aug 07 '13 at 10:10
Not all versions of phpMyAdmin recognise the delimiter command; instead, one can use the Delimiter input box beneath the SQL input box. [See here](http://stackoverflow.com/questions/2454071/how-do-i-write-an-sp-in-phpmyadmin-mysql/2498437#2498437) — Rik, Aug 07 '13 at 10:14
I don't know ScriptRunner personally but i think it also doesn't know the `DELIMITER` keyword. But can't you set the delimiter with `setDelimiter("$$", true)` before the `Runscript` and leave out the `DELIMITER` keywords? — Rik, Aug 07 '13 at 11:48

score 1 · Answer 1 · answered Apr 26 '17 at 13:50

1

PhpMyAdmin uses mysqli which doesn't support delimiter. See if delimiter is needed at all. I am running a script with multiple rows and my MySQL runs all of them with semicolons as delimiters.

answered Apr 26 '17 at 13:50

Volt

99
1
7

Just checked. Delimiter is not needed for MySQL. Only needed to pass queries. Mysqli->query only sends one query (you would need mysqli->multi_query). So since you can only pass one query, delimiter is not needed because it will always be one query regardless how many semicolons it will have. – Volt Apr 26 '17 at 15:58

lousuan · Answer 2 · 2021-08-31T16:03:47.300

1

I found a solution here and it works for me (i'm using ScriptRunner):

just use -- @DELIMITER $$ instead of DELIMITER $$

edited Aug 31 '21 at 16:03

answered Aug 18 '21 at 05:43

lousuan

79
1
6

score 0 · Answer 3 · answered Jun 17 '16 at 20:04

I just ran into a similar issue where my DELIMITER statement was breaking when run via Flyway despite working when I ran it through my sql client.

The issue was either indentation or hidden special characters because formatting the code in the IDE fixed the issue.

DELIMITER in mysql does not work

3 Answers3