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?

chaos · Accepted Answer · 2009-07-09 06:27:33Z

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 9, 2009 at 6:27

chaos

125k34 gold badges306 silver badges310 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

too much php · Accepted Answer · 2009-07-09 06:25:32Z

4

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

DELIMITER ;
# not:
DELIMITER;

answered Jul 9, 2009 at 6:25

too much php

91.5k37 gold badges133 silver badges140 bronze badges

Comments

Orson · Accepted Answer · 2009-11-20 11:16:58Z

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, 2009 at 11:16

Orson

15.5k12 gold badges60 silver badges72 bronze badges

Comments

j0k · Accepted Answer · 2012-12-13 10:13:19Z

1

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
--
+

edited Dec 13, 2012 at 10:13

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Sep 2, 2010 at 22:16

user438441

111 bronze badge

Comments

fedorqui · Accepted Answer · 2013-02-27 17:59:22Z

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, 2013 at 17:59

fedorqui

293k113 gold badges592 silver badges640 bronze badges

answered Feb 27, 2013 at 17:36

lxblanco

111 bronze badge

1 Comment

Dave 5709 Over a year ago

What version would that be?

Jon · Accepted Answer · 2009-09-02 09:41:36Z

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 2, 2009 at 9:41

Jon

1 Comment

Sophia Over a year ago

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.

Himanshu Singh · Accepted Answer · 2023-06-06 09:26:23Z

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 6, 2023 at 9:26

Himanshu Singh

1

1 Comment

Community Over a year ago

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.

Collectives™ on Stack Overflow

MySQL DELIMITER syntax errors

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related