Solved

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

Posted on 2010-09-16
8
480 Views
Last Modified: 2012-05-10
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
Comment
Question by:jef9c
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 33696036
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
 
LVL 27

Accepted Solution

by:
yodercm earned 250 total points
ID: 33696103
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
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 250 total points
ID: 33696124
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:jef9c
ID: 33697110
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
 
LVL 27

Expert Comment

by:yodercm
ID: 33697209
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 33697226
@jef9c,
you right, events is new feature introduced in 5.1
0
 
LVL 3

Expert Comment

by:dnadavis
ID: 33698657
i think you can use some cron jobs .set them @ a paricular time in a day,and that will do it.
0
 

Author Comment

by:jef9c
ID: 33719226
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In Ruby, Call or invoke a API DLL library is easily via Win32API class, win32-api gem or other gems. For general DLL API call, there are quite a few references, some good tips list below: http://www.rubytips.org/2008/05/13/accessing-windows-api-fro…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question