CODEWITHSUNDEEP
mysql
Sasha
Sasha

Reputation: 8705

MYSQL - Create event to delete expired records

I have this code for MYSQL event:

CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
DELETE FROM 
`password_recovery` 
WHERE `date_expires` < 
CURRENT_TIMESTAMP();
END

When I run it, I get following error:

#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 8

date_exires is datetime with format YYYY-MM-DD HH:MM:SS What is wrong with CURRENT_TIMESTAMP().

I tried also CURRENT_TIMESTAMP but it gives me same error.

Upvotes: 0

Views: 1524

Answers (5)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Try using DELIMITER

DELIMITER $$
CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
DELETE FROM 
`password_recovery` 
WHERE `date_expires` < 
Now();
END$$
DELIMITER ;

Upvotes: 0

Jan Turoň
Jan Turoň

Reputation: 32912

You need to set delimiter in your query:

DELIMITER |

CREATE EVENT
...
CURRENT_TIMESTAMP();
END|

DELIMITER ;

If you use phpMyAdmin, set the delimiter field instead of setting delimiter manually.

Upvotes: 1

Devart
Devart

Reputation: 121922

I'd suggest you to use this query -

CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
DELETE FROM `password_recovery` WHERE `date_expires` < CURRENT_TIMESTAMP();

Otherwise, when you use BEGIN..END clause, you should use DELIMITER commands -

DELIMITER $$
CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
  DELETE
    FROM `password_recovery`
  WHERE `date_expires` < CURRENT_TIMESTAMP();
END$$
DELIMITER ;

Upvotes: 3

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

instead

ON SCHEDULE EVERY 1 MINUTE

try 

ON SCHEDULE  1 MINUTE

Complete schecule wil be like this:

CREATE EVENT DeleteToken
 ON SCHEDULE 1 MINUTE
 DO
  BEGIN
   DELETE FROM `password_recovery` WHERE `date_expires` < CURRENT_TIMESTAMP();
  END

Upvotes: 0

Hoh
Hoh

Reputation: 1184

Just use Now() instead of CURRENT_TIMESTAMP()

Upvotes: 0

Related Questions