Solved

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

Posted on 2010-09-16
8
477 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now