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

is the hash sign (# 04:00:00 ...) recognized as a comment sign by mysql? — Ronald
– Ronald, Commented Jan 13, 2020 at 15:27
What have you tried to debug this? Shouldn't it be easy to remove parts here and there until something works? — Nico Haase
– Nico Haase, Commented Jan 13, 2020 at 15:35

Bill Karwin · Accepted Answer · 2020-01-13 15:56:52Z

4

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.

edited Jan 13, 2020 at 15:56

answered Jan 13, 2020 at 15:34

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

8 Comments

Bill Karwin Over a year ago

The above was paraphrased from an answer I posted in 2013: stackoverflow.com/a/19772738/20860

Mykola Over a year ago

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

Bill Karwin Over a year ago

I can't infer how you're calling this. But I tested your CREATE EVENT in the mysql client and I got no error.

Bill Karwin Over a year ago

Generally when you get a syntax error complaining about '' it means the parser reached the end of your SQL statement and expected more. Like a close-parenthesis, or a final END or something like that.

Mykola Over a year ago

I added line to Answer how I call this sql-script. I can't understand what exactly it expected...

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related