mysqldatabasesyntax-errormysql-error-1064mysql-event

Creating an event got syntax error in MySQL


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


Solution

  • 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.