mySQL DB purging

[Magnusson, Andrew]

Give this a shot. This works only for the stock mysql logging, not the
customized mailwatch one. A simple improvement might be to delete all with
an ID smaller than the first date you want to keep, if you can be sure that
smaller IDs are ALWAYS older. It's a quick hack, and slow if you have a lot
of records to delete initially, but if run regularly (we do weekly) it
should do the trick.

--

#!/usr/bin/perl

# script to clear out old records from mysql email logs


my $daystokeep = 45;

my $flushed = 0;

use DBI;

foreach $dbname ("mailscanner", "mailscannerexternal") {

        $db = DBI->connect("DBI:mysql:$dbname:localhost", "mailscanner",
"password");

        # get date before which records are 'old' and will be deleted
          $sth = $db->prepare("SELECT DATE_ADD(CURDATE(), INTERVAL
-$daystokeep DAY);");
        $sth->execute();
        ($earliest) = $sth->fetchrow_array(); # the earliest date to keep
        $earliest =~ s/\-//g;

        # get list of message IDs to drop
        $sql = "SELECT msg_id FROM maillog_mail WHERE time <
\"$earliest\";";
        $sth = $db->prepare($sql);
        $sth->execute();

        while (@result = $sth->fetchrow_array()) {
                foreach $table ("maillog_recipient", "maillog_report",
"maillog_mail") {

                        $sql = "DELETE FROM $table WHERE msg_id =
\"@result[0]\";";
                        $sth2 = $db->prepare($sql);
                        $sth2->execute();
                }
                $flushed ++;
        }

        print "done with $dbname. flushed $flushed records.\n";
}

Andrew Magnusson
Internet Product Analyst
COCC
1-877-678-0444 extension 640



-----Original Message-----
From: Kearney, Rob [mailto:RKearney at AZERTY.COM]
Sent: Thursday, October 30, 2003 9:31 AM
To: MAILSCANNER at JISCMAIL.AC.UK
Subject: mySQL DB purging


Has anyone written any scripts to purge your growing mysql database, and
willing to share those scripts.

-rob

--
*** This message originates from COCC, Inc.

If the reader of this message, regardless of the address or routing, is not an intended recipient, you are hereby notified that you have received this transmittal in error and any review; use, distribution, dissemination or copying is strictly prohibited.  If you have received this message in error, please delete this e-mail and all files transmitted with it from your system and immediately notify COCC, Inc. by sending reply e-mail to the sender of this message.

Thank you. ***