Syntax error when using Delimiters with Aurora Serverless MySQL 5.6

Question

I'm using Aurora Serverless MySQL 5.6 to create the following trigger which will update one table when data is inserted into another table but am receiving syntax errors, specifically around the Delimiter keyword.

DELIMITER $$
CREATE TRIGGER Create_Media_Like_Trigger AFTER INSERT ON MediaLike
FOR EACH ROW
BEGIN 
    IF NEW.likeType = 'LIKE' THEN
        UPDATE Media 
        SET Media.numLikes = Media.numLikes + 1 
        WHERE Media.mediaId = NEW.mediaId;
    ELSEIF NEW.likeType = 'DISLIKE' THEN
        UPDATE Media 
        SET Media.numLikes = Media.numLikes - 1 
        WHERE Media.mediaId = NEW.mediaId;
    ENDIF;
END $$
DELIMITER ;

Like I said above, I am receiving syntax errors near DELIMITER, is this issue AWS specific and how can I fix it?

UPDATE with error messages:

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 $$ CREATE TRIGGER Create_Media_Like_Trigger 
AFTER INSERT ON MediaLike ' at line 1

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 'ELSEIF NEW.likeType = 'DISLIKE' THEN UPDATE Media SET 
Media.numLikes = Media.num' at line 1

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 'END $$' at line 1

It might be helpful if you add the actual error message. Also, how you run this code. (The delimiter is specific to the client, not the server.) — Solarflare
@Solarflare I'm running it on the RDS console, and just updated the post with the error messages — Josh Montgomery

Kristin Green Kristin Green · Accepted Answer · 2020-02-19T20:59:20

I spent a day trying to figure this out so hopefully this helps someone out there...

DELIMITER is a feature of the client, not the MySQL server. The RDS Query Editor is a client but it does not support changing the delimiter so attempting to run the script you've provided won't work since the first time it sees a semicolon it will interpret that as the end of the command and fail with a syntax error.

So, how do you create something like a stored procedure that has multiple statements and semicolons in it? You must create it as an .sql file and send it using the Data API from either a Lambda function or the CLI.

First, create your script in a .sql file without any DELIMITER commands or alternate delimiters.

For example: function.sql

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM t;
END

Then, run the script using the CLI like this:

cat function.sql | xargs -0 aws rds-data execute-statement \
    --resource-arn arn:aws:rds:eu-west-1:xxx:cluster:cluster-name \
    --secret-arn arn:aws:secretsmanager:eu-west-1:xxx:secret:secret-name-xxx \
    --database "database_name" \
    --sql

Alternatively, you can create a Lambda function that reads the file and uses rds_client.execute_statement() to send the script to the server via the Data API. But again, do NOT use the DELIMITER statement. The server sees the BEGIN and END lines and acts accordingly without the need to change the delimiter.

Syntax error when using Delimiters with Aurora Serverless MySQL 5.6

2 Answers