Solved

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

Posted on 2010-09-16
8
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Cornelia Yoder 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
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: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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

624 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