Create Transaction on MariaDB to set expiry time for ip blacklist

Question

The question I used to help build this transaction is here:
How to add 1 hour to currrent_timestamp in mysql which is the default value?

I'm trying to create a transaction in my database to fill in the allowed column in my mariadb database table blacklisted_ips. I want the allowed column to be an hour after my added column which has a default value of CURRENT_TIMESTAMP. Here is my transaction so far:

CREATE TRIGGER before_insert_on_blacklisted_ips BEFORE INSERT ON blacklisted_ips FOR EACH ROW BEGIN
SET NEW.allowed=NOW()+INTERVAL 1 HOUR;
END;

The error message I'm getting is the following:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

Here is my table schema:

CREATE TABLE blacklisted_ips (
    ip_id int(11) NOT NULL AUTO_INCREMENT,
    ip_add varchar(15) NOT NULL,
    added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    allowed timestamp NOT NULL,
    PRIMARY KEY (ip_id)
);

Possible duplicate of https://stackoverflow.com/questions/8154158/mysql-how-do-i-use-delimiters-in-triggers — Bill Karwin, Jul 18 '18 at 22:34
Possible duplicate of https://stackoverflow.com/questions/10259504/delimiters-in-mysql — Bill Karwin, Jul 18 '18 at 22:34
@BillKarwin You're absolutely right, it was my lack of delimiters, but that was not immediately clear because it's hard to see when none of the examples show triggers. I would love to give you the answer on this one if you want to answer it — Adam McGurk, Jul 18 '18 at 22:44

score 1 · Accepted Answer · answered Jul 18 '18 at 22:49

In MySQL, there's an ambiguity between the ; that terminates a CREATE TRIGGER statement, and the possible ; characters that terminate individual statements in the body of the trigger.

The error you got is confusing:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

Normally a syntax error includes the text of the statement following the position where the syntax parser got confused. But in your case, it got confused at ;, thinking it was the end of the CREATE TRIGGER statement. Therefore the error occurs at the termination; there is no text following the error as far as the parser is concerned.

The same issue affects CREATE PROCEDURE and CREATE FUNCTION.

To fix the ambiguity, MySQL client supports a builtin command to change the DELIMITER, so you can change it to something distinct from any sequence of characters that appear in the body of your routine.

DELIMITER ;;
CREATE TRIGGER before_insert_on_blacklisted_ips BEFORE INSERT ON blacklisted_ips 
FOR EACH ROW BEGIN
  SET NEW.allowed=NOW()+INTERVAL 1 HOUR;
END ;;

Alternatively, since in your case the trigger is a single-statement trigger, you don't need a BEGIN...END block at all. This way you can skip changing the DELIMITER, because the ; that terminates your CREATE TRIGGER is the same ; that terminates the single statement of the trigger.

CREATE TRIGGER before_insert_on_blacklisted_ips BEFORE INSERT ON blacklisted_ips 
FOR EACH ROW
  SET NEW.allowed=NOW()+INTERVAL 1 HOUR;

P.S.: This is documented with an example here: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

Create Transaction on MariaDB to set expiry time for ip blacklist

1 Answers1