Stored Procedure That Automatically Delete Rows Older Than 7 Days in MySQL

Stored procedure that Automatically delete rows older than 7 days in MYSQL

Mysql has its EVENT functionality for avoiding complicated cron interactions when much of what you are scheduling is sql related, and less file related. See the Manual page here. Hopefully the below reads as a quick overview of the important steps and things to consider, and verifiable testing too.

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

ooops, the event scheduler is not turned on. Nothing will trigger.

SET GLOBAL event_scheduler = ON; -- turn her on and confirm below

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

Schema for testing

create table theMessages
(   id int auto_increment primary key,
    userId int not null,
    message varchar(255) not null,
    updateDt datetime not null,
    key(updateDt)
    -- FK's not shown
);
-- it is currently 2015-09-10 13:12:00
-- truncate table theMessages;
insert theMessages(userId,message,updateDt) values (1,'I need to go now, no followup questions','2015-08-24 11:10:09');
insert theMessages(userId,message,updateDt) values (7,'You always say that ... just hiding','2015-08-29');
insert theMessages(userId,message,updateDt) values (1,'7 day test1','2015-09-03 12:00:00');
insert theMessages(userId,message,updateDt) values (1,'7 day test2','2015-09-03 14:00:00');

Create 2 events, 1st runs daily, 2nd runs every 10 minutes

Ignore what they are actually doing (playing against one another). The point is on time difference approaches and scheduling.

DELIMITER $$
CREATE EVENT `delete7DayOldMessages`
  ON SCHEDULE EVERY 1 DAY STARTS '2015-09-01 00:00:00'
  ON COMPLETION PRESERVE
DO BEGIN
   delete from theMessages 
   where datediff(now(),updateDt)>6; -- not terribly exact, yesterday but <24hrs is still 1 day
   -- etc etc all your stuff in here
END;$$
DELIMITER ;

...

DELIMITER $$
CREATE EVENT `Every_10_Minutes_Cleanup`
  ON SCHEDULE EVERY 10 MINUTE STARTS '2015-09-01 00:00:00'
  ON COMPLETION PRESERVE
DO BEGIN
   delete from theMessages 
   where TIMESTAMPDIFF(HOUR, updateDt, now())>168; -- messages over 1 week old (168 hours)
   -- etc etc all your stuff in here
END;$$
DELIMITER ;

Show event statuses (different approaches)

show events from so_gibberish; -- list all events by schema name (db name)
show events; -- <--------- from workbench / sqlyog
show events\G;` -- <--------- I like this one from mysql> prompt

*************************** 1. row ***************************
                  Db: so_gibberish
                Name: delete7DayOldMessages
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: DAY
              Starts: 2015-09-01 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: so_gibberish
                Name: Every_10_Minutes_Cleanup
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: MINUTE
              Starts: 2015-09-01 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.06 sec)

Random stuff to consider

drop event someEventName; -- <----- a good thing to know about

can't alias datediff and use in where clause in 1 line, so

select id,DATEDIFF(now(),updateDt) from theMessages where datediff(now(),updateDt)>6;

get more exact, 168 hours for 1 week old

select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as `difference` FROM theMessages;
+----+------------+
| id | difference |
+----+------------+
|  1 |        410 |
|  2 |        301 |
|  3 |        169 |
|  4 |        167 |
+----+------------+

The link to the Manual Page shows quite a bit of flexibilty with interval choices, shown below:

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
          WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
          DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Concurrency

Embed any concurrency measures necessary that multiple events (or multiple firings of the same event) don't cause data to run amok.

Set and Forget

Remember, for now, because you are going to forget it, that these events just keep firing. So build in solid code that will just keep running, even when you forget. Which you most likely will.

Your particular requirements

You need to determine which rows need to be deleted first by table, such that it honors Primary Key constraints. Just lump them all in proper order inside of the obvious area via the CREATE EVENT statement, which can be massive.

Delete rows older than 14 days in MySQL

I understand your point about using safe mode. If you try to use UPDATE or DELETE against a non-indexed expression, it complains, because it can't make an estimate of whether you will accidentally delete your whole table.

Using an expression on DATE(dateHeureModification) > ...
is naturally unindexed. MySQL can't do an index lookup against the result of a function.

You can use LIMIT in your delete query to make it satisfy the safe-updates mode. MySQL treats it as sufficient protection against accidentally deleting all the rows in the table, if you use LIMIT.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

It's a good idea to run the delete in limited-size batches anyway, so it doesn't create too many locks or add too much to the undo segment.

Just keep doing DELETE in a loop, deleting batches of 1000 rows at a time, and check rows-affected after each batch. Stop the loop when rows-affected reaches 0.

Another idea: I don't think you really need the DATE() function in your WHERE clause. So you might be able to do the DELETE like below, and it will be able to use an index. Besides, it should be faster to the query to check for any rows if you have an index on dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

Delete all rows with timestamp older than x days

DELETE FROM on_search 
WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))

Delete row after ten minutes not being used (expiry)

I would recommend all access through stored procedures that would then be in control of updating the lastUsed datetime.

Adhoc access outside of stored procedures would not enforce the lastUsed aspect being updated. In other words that would be frowned upon. It naturally could be used but as it would not have tight control, it would fall on the outside of expectations.

Any other mechanism to perform this would rely on a fullstack service that is outside of MySQL.

Automatic deletion would occur through the built-in Events functionality in MySQL. I have 3 Events related links off my profile page. One to start looking at is Here.

sql delete all rows older than 30 days

The following code will delete the records of messages that are older than 30 days

DELETE FROM messages WHERE sentOn < NOW() - INTERVAL 30 DAY;

The NOW() method in MySQL is used to pick the current date with time. INTERVAL 30 DAY used for subtracting 30 days from the current date.
After the above query, you can check the current table using the SELECT statement. Thank you!

What is the best way to delete old rows from MySQL on a rolling basis?

Check out MySQL Partitioning:

Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data.

See e.g. this section to get some ideas on how to apply it:

MySQL Partition Pruning

And this one:

Partitioning by dates: the quick how-to

Possible for MySQL to autodelete rows where DATETIME is older than _?

No, mySQL has no provision for this.

You might be able to set up a stored procedure and a TRIGGER but honestly, I think it's much easier to either set up a cron job to call a PHP script, or send a "delete everything older than x days" query from PHP whenever, for example, a user logs in or something.

That's what I would do.

Delete rows with date older than 30 days with SQL Server query

Use DATEADD in your WHERE clause:

...
WHERE date < DATEADD(day, -30, GETDATE())

You can also use abbreviation d or dd instead of day.

---

Related Topics