CGI Program To Delete Old Data From MySQL Table?

[Milti]

I have a table with hundreds of records (rows) and one field is the date of entry. I want to run a program to delete all records that are over 60 days old? What would such a program look like, including the SQL query?

Thanks for your help.

[marto]

It'd look like several lins of perl code with a SQL statement. This reads like a task you've been assigned, do you expect someone else to do your job for you? Please read and understand How do I post a question effectively?. What part(s) of the task are you stuck with? Show the code you have so far. Have you considered the security implications of having a webpage which deletes rows from a database, how will you control access?

You have several existing posts which make use of CGI and DBI. Perhaps the answers to those are worth reviewing.

If you're interested in learning how to use the tool you've either chosen or been told to use, the following links are worth working through.

• perlintro
• http://learn.perl.org
• http://perldoc.perl.org
• The tutorials section of this site has plenty of examples, Web Programming, Database Programming...

[talexb]

I'm not sure why you'd want to wrote a CGI to handle getting rid of entries that have aged out. It's probably much more appropriate to write a script that runs server side to handle that.

If you need assistance to write the SQL to handle this, you should probably hire a professional. :)

[kennethk]

What have you tried? What worked? What didn't? Please see How do I post a question effectively?. This is not a code writing service.

Why do you want to use a CGI script? This would seem to be more naturally a cron job.

mysql date older than shows stackoverflow has some potential guidance with regards to formulating the query.

[erix]

UPDATE: Oops. You *did* mention the DBMS, in the title no less. I didn't see that. So this is a little off topic -- sorry about that.

In PostgreSQL:

delete from table where now() - column > interval '60 days';

I wrote a little standalone bash to show that off. It creates 100 days worth of rows, shows some counts, DELETEs with an appropriate interval expression, then counts again:

(careful: it drops table t)

#!/bin/sh

echo "
drop table if exists t; -- dropping a table, be careful!
create table t 
as
  select d
  from generate_series(
      current_timestamp - interval '99 days'
    , current_timestamp
    , interval '1 day'
  )
     as f(d) ;

-- UPDATE: add this for index tests:
-- create index t_d_idx on t (d);  

select
       count(*)
     , sum( case
            when now() - d > interval '60 days' then 0
            else                                     1
            end
       )    
            as records_to_keep
     , sum( case
            when now() - d > interval '60 days' then 1
            else                                     0
            end
       )
            as records_to_dump
from t;

delete from t where now() - d > interval '60 days'
;

select
       count(*)
     , sum( case
            when now() - d > interval '60 days' then 0
            else                                     1
            end
       )    
            as records_to_keep
     , sum( case
            when now() - d > interval '60 days' then 1
            else                                     0
            end
       )
            as records_to_dump
from t;

" | psql -X

Output:

DROP TABLE
SELECT 100
 count | records_to_keep | records_to_dump 
-------+-----------------+-----------------
   100 |              60 |              40
(1 row)

DELETE 41
 count | records_to_keep | records_to_dump 
-------+-----------------+-----------------
    60 |              60 |               0
(1 row)

[Jenda]

Unless PostgreSQL is extremely clever, this query effectively prevents the use of an index on the date column. You should use something like (MSSQL syntax, untested)

delete from table where column < DateAdd(day,-60,getdate());

[erix]

I did think of including that optimization but I thought it was better (because conceptually easier) to show what I did show (the idea was to show the ease of use of the postgres interval datatype).

And remember that index-retrieval is not always faster. SeqScan is better than Index-retrieval when hitting a large part of the table (like when deleting 40 out of 100 rows (or keeping 60 days out of 'hundreds' of rows as the OP mentions)). Of course there is no way to know what the distribution in the OP's table is.

Indeed, for my example, it turns out seq scan is still preferred over index, with my original date data. Only if the number of deleted rows becomes small compared to the total rowcount, does Pg use the index. So yes, PostgreSQL /is/ extremely clever ;-)

The index-usable statement for postgres could be:

delete from t where d < now() - interval '2';

I tweaked my little program to accept an arg1=number of created rows and an arg2=number of rows to keep.

$ pm/1056374.sh 99 60 # create table with 99 rows, keep 60 
DROP TABLE
SELECT 100
CREATE INDEX
ANALYZE
 count | records_to_keep | records_to_dump
-------+-----------------+-----------------
   100 |              60 |              40
(1 row)

                       QUERY PLAN
--------------------------------------------------------
 Delete on t  (cost=0.00..2.75 rows=39 width=6)
   ->  Seq Scan on t  (cost=0.00..2.75 rows=39 width=6)
         Filter: (d <= (now() - '60 days'::interval))
(3 rows)

DELETE 40
 count | records_to_keep | records_to_dump
-------+-----------------+-----------------
    60 |              60 |               0
(1 row)

$ pm/1056374.sh 999 990 # create table with 999 rows, keep 990 
DROP TABLE
SELECT 1000
CREATE INDEX
ANALYZE
 count | records_to_keep | records_to_dump
-------+-----------------+-----------------
  1000 |             990 |              10
(1 row)

                                QUERY PLAN
----------------------------------------------------------------------
+-----
 Delete on t  (cost=0.28..8.45 rows=10 width=6)
   ->  Index Scan using d_date_idx on t  (cost=0.28..8.45 rows=10 widt
+h=6)
         Index Cond: (d <= (now() - '990 days'::interval))
(3 rows)

DELETE 10

 count | records_to_keep | records_to_dump
-------+-----------------+-----------------
   990 |             990 |               0
(1 row)

[Anonymous Monk]

It is also a good practice to wrap such things in BEGIN TRANSACTION / COMMIT or ROLLBACK ... not only because it's usually more-efficient that way, but also so that it is "cleanly all-or-nothing." Either the delete succeeds, completely, or it never happened at all. You can also test your work by starting a transaction, running the query, checking the result, and either (looks good!) COMMIT or (oops!) ROLLBACK.

[Jenda]

Just keep in mind that while you are checking the result, all those rows are locked. What exactly does that mean depends on the database and the table locking hints of other queries, but you may easily block everyone else!

[Anonymous Monk]

Note: requires InnoDB storage-engine in MySQL.

[McA]

My advice:

delete from <tablename>;

where <tablename> is the name of your table. Then all rows older than 60 days are deleted.

McA

[blue_cowdawg]

drop database <name>;

is much more efficient. :-D

[talexb]

I wouldn't recommend running delete from <tablename>; -- that's going to delete everything from the table, leaving you with nothing.

[Laurent_R]

that's going to delete everything from the table

Yes, including the records older than 60 days. The aim will have been reached. ;-)