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?

Answer 1

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

DELIMITER ;
# not:
DELIMITER;

Answer 2

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.

Answer 3

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?

Answer 4

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.

Answer 5

Hi All,

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

+

HOPE YOU GOT IT NOW.