MariaDB and flyway syntax error but not in JAVA

Question

I'm trying to execute (migrate) SQL scripts (MariaDB flavour). I am using Flyway in order to run the scripts. However, some scripts fail...

The script which fails:

CREATE TRIGGER my_cool_trigger
BEFORE UPDATE ON my_db
FOR EACH ROW
BEGIN
    DECLARE n INT;
    SET n = (SELECT COUNT(uuid) FROM my_db WHERE uuid != NEW.uuid AND a_uuid = NEW.a_uuid AND number = NEW.number AND event_id IS NULL AND t_begin < NEW.t_end AND t_end > NEW.t_begin);
    IF n > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Oof. ERROR!!!';
    END IF;
END;

Error message:

Migration cool_script.sql failed ------------------------------------------------------ SQL State : 42000 Error Code : 1064 Message : (conn=2661) 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 6

When I try to execute the same scripts thru Java (they are run as Prepared Statements, sent over JDBC to MariaDB) everything goes as planned without any errors.

Java:

connection.prepareStatement(sql).execute();

I can really not explain to myself why that is happening.

What you mean by *execute the same scripts thru Java *? Flyway is also Java. — mentallurg
– mentallurg, Commented Jan 6, 2020 at 13:21
What "they"? What exactly is the content of prepared statement? — mentallurg
– mentallurg, Commented Jan 6, 2020 at 13:29

DR.C0de · Accepted Answer · 2020-01-07 10:52:10Z

2

Turns out the problem lies within not using delimiters. In order for the statements to parse successfully delimiters should be used as following:

DELIMITER //
CREATE TRIGGER my_cool_trigger
BEFORE UPDATE ON my_db
FOR EACH ROW
BEGIN
    DECLARE n INT;
    SET n = (SELECT COUNT(uuid) FROM my_db WHERE uuid != NEW.uuid AND a_uuid = NEW.a_uuid AND number = NEW.number AND event_id IS NULL AND t_begin < NEW.t_end AND t_end > NEW.t_begin);
    IF n > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Oof. ERROR!!!';
    END IF;
END//
DELIMITER ;

The reason why it worked in Java was that the Java prepareStatement seems to add custom delimiters or something else...

answered Jan 7, 2020 at 10:52

DR.C0de

588 bronze badges

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

Collectives™ on Stack Overflow

MariaDB and flyway syntax error but not in JAVA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related