I created an EVENT basically does two things:
SELECT
the rows that are more than 30 days old andINSERT
into theachieve
table.DELETE
the rows that are older than 30 days old in thetrends
table.
I separately tested the two scripts and they both work fine but I got a syntax error when put them in the EVENT.
If I only user INSERT INTO
or DELETE
script in the event body, it saves fine.
The scripts are:
CREATE DEFINER=`root`@`localhost`
EVENT `trend_decayer` ON SCHEDULE EVERY 1 DAY
STARTS '2016-08-30 00:00:00' ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Decay trends that are older than 30 days'
DO
INSERT INTO `achieve`
SELECT *, FROM_UNIXTIME(`trends`.`timestamp`) AS `datetime`
FROM `trends`
WHERE `t`.`timestamp` < UNIX_TIMESTAMP(CAST(DATE_SUB(NOW(), INTERVAL 30 DAY) AS DATETIME))
ORDER BY `datetime` DESC;
DELETE
FROM `trends`
WHERE `timestamp` < UNIX_TIMESTAMP(CAST(DATE_SUB(NOW() , INTERVAL 30 DAY) AS DATETIME));
The error message is :
MySQL said: #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
'INTO `achieve`
SELECT *, FROM_UNIXTIME(`timestamp`) AS `datetime` FR' at line 2`