Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL DELIMITER syntax errors

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?

like image 893
Sophia Avatar asked Jul 09 '09 06:07

Sophia


People also ask

How do I get out of delimiter in MySQL?

You can redefine the delimiter to a string other than // , and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash ( \ ) character because that is the escape character for MySQL.

What does delimiter mean in MySQL?

You define a DELIMITER to tell the mysql client to treat the statements, functions, stored procedures or triggers as an entire statement. Normally in a . sql file you set a different DELIMITER like $$. The DELIMITER command is used to change the standard delimiter of MySQL commands (i.e. ;).

How do I change the delimiter in Mariadb?

The solution is to specify a distinct delimiter for the duration of the process, using the DELIMITER command. The delimiter can be any set of characters you choose, but it needs to be a distinctive set of characters that won't cause further confusion. // is a common choice, and used throughout the knowledgebase.

What is the error in MySQL syntax?

During application update an error message containing "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 ..." appears in the log. It means your database is outdated and it can't work with the request our application sends to it.


2 Answers

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.

like image 67
chaos Avatar answered Oct 19 '22 04:10

chaos


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

DELIMITER ;
# not:
DELIMITER;
like image 26
too much php Avatar answered Oct 19 '22 06:10

too much php