I have below database tables,
Tables Definition
A – order_dateils :
- id
- order_bar
- description
- payment_status
- created_at
B – invoices :
- id
- invoice_id
- status
- created_at
and created below event to delete data older than 2 months
DELIMITER //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00'
DO
BEGIN
DELETE
FROM bariq_DB.invoice
where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
delete from bariq_DB.order_details
where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;
and I checked : SHOW PROCESSLIST
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 61763 | Waiting for next activation | NULL |
| 34948 | root | localhost | baridi_DB | Sleep | 984 | | NULL |
| 34949 | root | localhost | baridi_DB | Query | 0 | starting | SHOW PROCESSLIST |
+-------+-----------------+-----------+-----------+---------+-------+---------------------------
But the events is not deleting any thing that matches the conditions, what Im missing here?
Any help will be much appreciated
Best Answer
You have to make the event stay put using
ON COMPLETION PRESERVE
See my examples in the following posts
Jun 08, 2015
: Use event schedule and stored procedure to automatically average every hour MySQLJun 08, 2015
: Choosing what table fields to partition onSep 19, 2014
: Creating Event that execute at end of each monthAug 28, 2014
: Adding time to an event that runs on a weekly basisThis will kickoff the event starting midnight tomorrow
You never start an event in the past. You must always start an event in the future.