MySQL DELIMITER syntax errors

Question

This MySQL script installs multiple triggers.

It works on one machine running MySQL 5.0.51b-community. On another machine running MySQL 14.12 Distrib 5.0.45, for redhat-linux-gnu (i386) it fails, with this error message, which seems to be related to the DELIMITER // ... // DELIMITER; syntax :

ERROR 1064 (42000) at line 272: 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; DROP TRIGGER IF EXISTS trigger_name; DELIMITER' at line 1

The script syntax (summarised) is:

DROP TRIGGER IF EXISTS trigger_name;
DELIMITER //
CREATE TRIGGER trigger_name BEFORE UPDATE ON table
FOR EACH ROW BEGIN
  -- Trigger logic goes here
END //
DELIMITER;

-- More trigger drop/create statements follow

What is wrong with the script, and how can I correct it?

score 26 · Accepted Answer · answered Jul 09 '09 at 06:27

26

Try

DELIMITER ;

not

DELIMITER;

You're actually specifying ; as an argument to the DELIMITER command, so not having the space there may be confusing it.

answered Jul 09 '09 at 06:27

chaos

122,029
33
303
309

score 4 · Answer 2 · answered Jul 09 '09 at 06:25

4

You need a space between 'DELIMITER' and ';'

DELIMITER ;
# not:
DELIMITER;

answered Jul 09 '09 at 06:25

too much php

88,666
34
128
138

score 3 · Answer 3 · answered Nov 20 '09 at 11:16

3

Just as an add-on, for someone else:

The delimiter is required to enable the entire definition to be passed to the server as a single statement.

answered Nov 20 '09 at 11:16

Orson

14,981
11
56
70

score 1 · Answer 4 · edited Feb 27 '13 at 17:59

1

In the version of MySql I use the same error occurs when using the delimiter command, but this version handles the delimiter ";" for statements and delimiter "|" for stored procedures and functions, which i think solves the problem; try this:

DROP TRIGGER IF EXISTS trigger_name;

CREATE TRIGGER trigger_name BEFORE UPDATE ON table FOR EACH ROW BEGIN -- Trigger logic goes here END |

-- other statements or functions here

edited Feb 27 '13 at 17:59

fedorqui

275,237
103
548
598

answered Feb 27 '13 at 17:36

lxblanco

11
1

1

What version would that be? – Dave 5709 Jun 15 '17 at 17:07

score 1 · Answer 5 · edited Dec 13 '12 at 10:13

Try the below.

I am sure it should solve the purpose.

DELIMITER +
CREATE TRIGGER STUDENT_INSERT_TRIGGER BEFORE INSERT ON FSL_CONNECTIONS 
FOR EACH ROW BEGIN 
INSERT INTO STUDENT_AUDIT 
SET STUDENT_ID = NEW.STUDENT_ID, 
MAC_ADDRESS = NEW.MAC_ADDRESS,
IPADDRESS = NEW.IPADDRESS, 
EMAIL_ID = NEW.EMAIL_ID , 
START_TIME=NEW.START_TIME, 
END_TIME=NEW.END_TIME, 
STATUS=NEW.STATUS; 
END; +

From the above when we use a DELIMITER. It should be in the form of

DELIMITER +
--
BLOCK OF SQL WHATEVER YOU WANT TO MENTION
--
+

score 0 · Answer 6 · answered Sep 02 '09 at 09:41

0

Hmm I'm having similar problems. I do a mysqldump from Debian Lenny running 5.0.51 and try importing to OpenSolaris running 5.0 and get the same error. And I have DELIMITER ;

Version conflict?

answered Sep 02 '09 at 09:41

It seemed to be related to versions for us, one version worked with no semicolon, another worked with just a semicolon, and another required a semicolon and no space. – Sophia Sep 03 '09 at 03:04

score 0 · Answer 7 · answered Jun 06 '23 at 09:26

0

DELIMITER // CREATE TRIGGER age_verified BEFORE INSERT ON customers FOR EACH ROW IF new.age<0 THEN SET new.age=0 END IF;//

answered Jun 06 '23 at 09:26

Himanshu Singh

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 06 '23 at 09:41

MySQL DELIMITER syntax errors

7 Answers7

Linked