Error with MySQL syntax in event sql-script. Delimeter already used

Question

I have this script for update table monthly:

DELIMITER |
CREATE
    EVENT `kpiparams_scheduled_update`
    ON SCHEDULE
        EVERY 1 MONTH
            STARTS '2020-02-01 02:59:59'
    ON COMPLETION PRESERVE
    COMMENT 'KPIParams was updated by event_kpiparams_scheduled_update'
    DO
    BEGIN
        UPDATE kpiparams INNER JOIN kpiparams_update
            ON kpiparams.param_name = kpiparams_update.param_name
        SET kpiparams.good        = kpiparams_update.good,
            kpiparams.bad         = kpiparams_update.bad,
            kpiparams.weight_gold = kpiparams_update.weight_gold,
            kpiparams.weight_tech = kpiparams_update.weight_tech,
            kpiparams.is_for_calc = kpiparams_update.is_for_calc
        WHERE kpiparams.param_name = kpiparams_update.param_name;
    END |
DELIMITER ;

This code drop exception:

Caused by: java.sql.SQLSyntaxErrorException: 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 EVENT kpiparams_scheduled_update ON SCHEDULE EVERY 1 MONTH ST' at line 1

And this:

nested exception is java.sql.SQLSyntaxErrorException: 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 EVENT kpiparams_scheduled_update ON SCHEDULE EVERY 1 MONTH ' at line 1

I serched some resources that said, I need use Delimeter, don't write amount of months in '' and use BEGIN-END. By the way, it didn't help me.

spring.datasource.schema=classpath*:mysql-scripts/event_update_kpiparams.sql

EDITED SCRIPT:

CREATE
    EVENT kpiparams_scheduled_update 
        ON SCHEDULE EVERY 1 MONTH STARTS '2020-02-01 02:59:59'
    ON COMPLETION PRESERVE
    COMMENT 'KPIParams was updated by event_kpiparams_scheduled_update'
    DO
    BEGIN
        UPDATE kpiparams INNER JOIN kpiparams_update
            ON kpiparams.param_name = kpiparams_update.param_name
        SET kpiparams.good        = kpiparams_update.good,
            kpiparams.bad         = kpiparams_update.bad,
            kpiparams.weight_gold = kpiparams_update.weight_gold,
            kpiparams.weight_tech = kpiparams_update.weight_tech,
            kpiparams.is_for_calc = kpiparams_update.is_for_calc
        WHERE kpiparams.param_name = kpiparams_update.param_name;
    END

Answer 1

I guess you tried to run this using JDBC?

You don't need DELIMITER | at all. That's a mysql client builtin command. Client builtins are not recognized by the SQL parser.

You can just execute the CREATE EVENT statement as a single statement and then you don't need to have a delimiter at the end of the statement. Delimiters are only important in interfaces that support multiple statements (e.g. the mysql client).

---

Okay it seems you are using multiple statements in an .sql file and you need some way of separating the statements. Normally this is ; but you have some statements that contain ; as part of the statement, not as the separator.

I'm not a Spring developer, but I found Spring Boot Database initialization MySQLException for Trigger which describes the use of:

spring.datasource.separator

This is also documented: https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html

spring.datasource.separator

(default) ;

Statement separator in SQL initialization scripts.