Deleting of records older than a specific date & time

--=-xfliqTQkJLZJPrXtN1s4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hi Everyone

I have posted this question quite a while back and noticed now
that I haven't gotten an answer as yet and this is still on my
unresolved list.

We have 2 tables which we want to archive data from.

We need to be able to delete all data from the 2 tables that is
older than the start of yesterday.

The process of determining the rows manually is easy enough,
however we would like to cut out manual intervention and automate this
process.

I have played around with trying to create a stored routine /
function which is then passed the date as a parameter and then to use
this to determine the rows and delete them.

- This created 2 problems for me though

-- initially I struggled with the date format
because it does not want to accept the date format.
-- when at last it seemed to accept the date
format , it caused the script to not exit upon completion.

I am sure there must be a better way to do this than using
functions and linux scripts to automate the process, however my
experience is failing me in this respect as I am fairly new to the dba
scene.


I would really appreciate it if someone can assist me in this
regard please.....

Regards
Machiel

PS.... someone already suggested me using MySQL scheduled events ,
however I was not able to figure out how to use it for this
functionality though.





--=-xfliqTQkJLZJPrXtN1s4--

Re: Deleting of records older than a specific date & time

--0016363b87bacaab7204a3f08012
Content-Type: text/plain; charset=ISO-8859-1

A simple stored procedure run by the event scheduler at predetermined times
will do this quite easily. Can you post the output of SHOW CREATE TABLE for
your two tables?

John

On 23 May 2011 13:15, Machiel Richards wrote:

> Hi Everyone
>
> I have posted this question quite a while back and noticed now
> that I haven't gotten an answer as yet and this is still on my
> unresolved list.
>
> We have 2 tables which we want to archive data from.
>
> We need to be able to delete all data from the 2 tables that is
> older than the start of yesterday.
>
> The process of determining the rows manually is easy enough,
> however we would like to cut out manual intervention and automate this
> process.
>
> I have played around with trying to create a stored routine /
> function which is then passed the date as a parameter and then to use
> this to determine the rows and delete them.
>
> - This created 2 problems for me though
>
> -- initially I struggled with the date format
> because it does not want to accept the date format.
> -- when at last it seemed to accept the date
> format , it caused the script to not exit upon completion.
>
> I am sure there must be a better way to do this than using
> functions and linux scripts to automate the process, however my
> experience is failing me in this respect as I am fairly new to the dba
> scene.
>
>
> I would really appreciate it if someone can assist me in this
> regard please.....
>
> Regards
> Machiel
>
> PS.... someone already suggested me using MySQL scheduled events ,
> however I was not able to figure out how to use it for this
> functionality though.
>
>
>
>
>


--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016363b87bacaab7204a3f08012--

Re: Deleting of records older than a specific date & time

Quoting Machiel Richards :


>
> We need to be able to delete all data from the 2 tables that is
> older than the start of yesterday.
>

Hi,

I use this veryyyy simple script to purge data from a syslog DB:

#!/usr/local/bin/bash
/usr/local/bin/mysql -u syslog -pmypasswd -e 'DELETE FROM SystemEvents
WHERE ReceivedAt < date_add(current_date, interval -60 day)' Syslog

maybe that is useful for you...?

cheers Andy.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org