Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-16
8
Medium Priority
?
486 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:Meir Rivkin
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:
Cornelia Yoder earned 1000 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:Meir Rivkin
Meir Rivkin earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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:Cornelia Yoder
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:Meir Rivkin
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

886 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