• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Can I/How do I run a MYSQL delete once per day?

I have a table that keeps track of which pages on my site are visited by a single ip address.  I only care about data for the last 24 hours, I don't want this table to get too big, and I don't want to have to manually delete rows.  I suppose that whereever I have code that adds to this table I could have code that deletes any row with a date that is more than 24 hours ago, but I assume that is more wasteful than necessary.  Is there any way that I can write code that will only execute once per day and will delete rows in a table that are more than 24 hours old?
0
jef9c
Asked:
jef9c
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Meir RivkinFull stack Software EngineerCommented:
do u have a timespan column in the table?that could be an indication of the time new rows are inserted to the table.then you can create a task scheduler which once a day execute mysql script which delete all rows which are older than 24 hrs.
0
 
Cornelia YoderArtistCommented:
If you are on a linux system, you can write a simple delete script and run it on the Crontab.

If you are not familiar with crontab jobs, they run a specified times on specified days/weeks/hours/etc.

http://adminschoice.com/crontab-quick-reference
0
 
Meir RivkinFull stack Software EngineerCommented:
or better yet create event delete_old_rows on schedule once a day:
DELETE FROM mytable WHERE timespan > DATE_SUB( NOW(), INTERVAL 24 HOUR)

you can also use cron jobs which executes a file or script on a specific time interval you can set
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jef9cAuthor Commented:
yodercm and sedqwick:

Thanks so much for your suggestions.  I tried the MySQL event idea first.  I typed the following in as a query:

CREATE EVENT
sweep_states_visiteds
ON SCHEDULE
EVERY 1 DAY
DO
DELETE FROM states_visiteds WHERE updated_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);

It didn't work, but I think that is because my host only has MySQL 5.0.90 and I think events are only in 5.1 and onward.

So, then I tried cron jobs, with:
minute = 0
hour = 0
day = *
month = *
weekday = *
command = mysql "DELETE FROM states_visiteds WHERE updated_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);"

The example that I found also had some parameters for username, password, and ip address, but I'm not sure what those should be or whether I have to provide them, since this is all on the same server.  I wonder though why the example did not have a parameter to say which database to use.  Anyway, I should know in the next day or so if it worked, and when I get it done I'll give both of you points.  Please let me know if you think I've done something wrong.
0
 
Cornelia YoderArtistCommented:
I suggest you write a php script to do the delete, and run that on the crontab.  That way you do the mysql connection right in the php code first, just as if it were a script you were running directly.  (and in fact, you CAN run it directly as well, should you want to do the delete manually at some point).
0
 
Meir RivkinFull stack Software EngineerCommented:
@jef9c,
you right, events is new feature introduced in 5.1
0
 
dnadavisCommented:
i think you can use some cron jobs .set them @ a paricular time in a day,and that will do it.
0
 
jef9cAuthor Commented:
I used cron jobs directly, like this, and it worked:

Minute: 0
Hour: 0
Day: *
Month: *
Weekday: *
Command:  mysql -uusername -ppassword -e "DELETE FROM database_name.table_name WHERE updated_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);"
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now