0

I have the following table created :

 CREATE TABLE Trailtbl( userid char(50), action varchar(150), timestamp 
 varchar(30), service char(20), resources varchar(200), accountid varchar(30), 
 awsregion varchar(20), roleid varchar(100), eventid varchar(100));

The table is supposed to contain AWS Cloudtrail logs. I set up a MySQL event like this :

CREATE EVENT AutoDeleteOldNotifications
 -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
 -> ON COMPLETION PRESERVE
 -> DO  
 -> DELETE FROM testdb.Trailtbl WHERE datetime <DATE_SUB(NOW(), 
    INTERVAL 10 MINUTE);

What am I doing wrong here ? Why is the log data older than 10 minutes not getting deleted from the table ? Please explain. Any help is greatly appreciated.

FCoding
  • 121
  • 1
  • 4
  • 13
  • Check if MySQL events are enabled within your MySQL server. `SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler'` should display ON – Raymond Nijland Mar 26 '18 at 13:55
  • Yes it was set as OFF. I turned it ON now. Thanks pal. However there is one more doubt I am having.. I have set the field 'timestamp' in my table as varchar type instead of datetime type because I was facing a timestamp error .. Will that affect my event in any way ? Also, in the line 'DELETE FROM testdb.Trailtbl WHERE datetime – FCoding Mar 26 '18 at 14:01

0 Answers0