Creating an event got syntax error in MySQL

Question

I'm trying to create this event but I can't find the syntax error.

CREATE 
EVENT respaldoRegRawInformeAperturaLocal
ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00' 
DO BEGIN

    -- INSERT INTO BACKUP TABLE
    INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId) 
    -- GET DATA
    SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId  
    FROM    regRawInformeAperturaLocal 
    WHERE regRawInformeAperturaLocal.date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

    -- DELETE DATA FROM ORIGINAL TABLE
    DELETE FROM regRawInformeAperturaLocal WHERE regRawInformeAperturaLocal.date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

END;

[CODE EDIT 1]

CREATE 
EVENT respaldoRegRawInformeAperturaLocal
ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00' 
DO BEGIN

    -- INSERT INTO BACKUP TABLE
    INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId) 
    -- GET DATA
    SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId  
    FROM    regRawInformeAperturaLocal 
    WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

    -- DELETE DATA FROM ORIGINAL TABLE
    DELETE FROM regRawInformeAperturaLocal WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

END;

[EDIT] This is the syntax error message:

[Err] 1064 - 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 11

Ghiberti

post the error .. – Guinevere

Updated. It says it's on this string: "WHERE regRawInformeAperturaLocal.date < DATE_SUB(NOW(), INTERVAL 5 MONTH);" – Ghiberti

why is regRawInformeAperturaLocal.date, not only date ? – Guinevere

Not that it matters, but why are you referencing the table name in the Where clause when you don't need to? – Sweetbread

I updated the error. I always used by this way Is it a bad practice? – Ghiberti

Just delete it and see – Guinevere

Remove it from the Delete statement as well. Just be consistent throughout your code. – Sweetbread

Ok, I updated the code, The delete should work fine because If I use the same structure as a Select works perferct. – Ghiberti

still getting same error? – Guinevere

Yes, I'm still getting the same error. There could be some issues with Mysql events + DATE_SUB function? – Ghiberti

you want select data with 5 month ago ? – Guinevere

Yes, I need to backup the data older than 5 months and then delete it. – Ghiberti

Have you tried using DELIMITER?. – Whitcher

Oh, no. I'm going to search about it – Ghiberti

The DELIMITER was the answer. – Ghiberti

Answer 1

I could make it work:

I have to add the DELIMITER and change the syntaxis from END; to END|

Anyway here's the code:

DELIMITER |
CREATE 
    EVENT IF NOT EXISTS respaldoRegRawInformeAperturaLocal
    ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00' 
    DO BEGIN

    -- INSERT INTO BACKUP TABLE
    INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId) 
    -- GET DATA
    SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId  
    FROM    regRawInformeAperturaLocal 
    WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

    -- DELETE DATA FROM ORIGINAL TABLE
    DELETE FROM regRawInformeAperturaLocal WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);

END|
DELIMITER ;

Now I have to test it but I could create it so I guess It's fine.

[EDIT] The event works like a charm.

Ghiberti

Using a delimiter solved the issue for me on Amazon RDS with MySQL v5.7 – Pontormo

Answer 2

I got the same error below:

ERROR 1064 (42000): 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 4

When I didn't set DO statement in the event plus_one as shown below:

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00';

Or, when I didn't set any SQL statements in DO statement in the event plus_one as shown below:

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO; -- Here

So, I set DO statement with a SQL statement as shown below, then the error was solved:

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO UPDATE test SET num = num + 1; -- Here

Gussi