Mailwatch 0.2 sql prob

[Peter C. Ndikuwera]

Hi Daniel,

I have a fix which is really a hack. I'm sure it's the wrong way to do
it but it works for me. I've attached the relevant part of my
CustomConfig.pm.

Peter
On Wed, 2003-08-27 at 17:14, Daniel Bird wrote:
> Hi,
> I noticed in the archives the same problem I'm having, but no solution:
> I keep seeing this in the maillog:
>
> Cannot insert row: MySQL server has gone away
>
> I was wondering if anyone has had this problem and managed to find a fix?
>
> Regards
> --
> ____________________________________
>
> Daniel Bird
> Network & Systems Manager
> St. George's Hospital  Medical School
> Tooting
> London SW17 0RE
>
> P: +44 20 8725 2897
> F: +44 20 8725 3583
> E: dan at sghms.ac.uk
> ____________________________________
>
> Hex dump: Where witches put used curses...
> "#define QUESTION ((bb) || !(bb)) - Shakespeare."
-------------- next part --------------
###############
# SQL Logging #
###############

use DBI;
use Sys::Hostname;

sub InitSQLLogging {
}

sub EndSQLLogging {
}

sub SQLLogging {
                my($sth);
                my($hostname) = hostname;

                # Modify this as necessary for your configuration
                my($db_name) = "mailscanner";
                my($db_host) = "localhost";
                my($db_user) = "mailscanner";
                my($db_pass) = "mailscanner";

                MailScanner::Log::InfoLog("Initialising database connection database=$db_name;host=$db_host,$db_user,$db_pass");

                # Connect to the database
                my($dbh) = DBI->connect("DBI:mysql:database=$db_name;host=$db_host",
                                                                                                                $db_user, $db_pass,
                                                                                                                {PrintError => 0});

                # Sometimes this line needs to be uncommented. Go figure
                # MailScanner::Log::WarnLog($DBI::errstr);

                # Check if connection was successfull - if it isn't
                # then generate a warning and continue processing.
                if (!$dbh) {
                                MailScanner::Log::WarnLog("Unable to initialise database connection: %s", $DBI::errstr);
                                        return;
                        } else {
                                        # Prepare statement
                                        $sth = $dbh->prepare("INSERT INTO maillog VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") or MailScanner::Log::WarnLog($DBI::errstr);
                        }

                MailScanner::Log::InfoLog("Finished initialising database connection");
                my($message) = @_;

                # Don't bother trying to do an insert if no message
                # is passed-in or if the database connection is down.
                MailScanner::Log::InfoLog("In SQL Logging msg=$message, dbh=$dbh");
                return unless $message;
                return unless defined $dbh;

                # Get rid of control chars and tidy-up SpamAssassin report
                my $spamreport = $message->{spamreport};
                $spamreport =~ s/\n/ /g;
                $spamreport =~ s/\t//g;

                # Get timestamp, and format it so it is suitable to use with MySQL
                my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
                my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",
                                                                                                                 $year+1900,$mon+1,$mday,$hour,$min,$sec);

                # Also print 1 line for each report about this message. These lines
                # contain all the info above, + the attachment filename and text of
                # each report.
                my($file, $text, @report_array);
                while(($file, $text) = each %{$message->{allreports}}) {
                                $file = "the entire message" if $file eq "";
                                # Use the sanitised filename to avoid problems caused by people forcing
                                # logging of attachment filenames which contain nasty SQL instructions.
                                $file = $message->{file2safefile}{$file} or $file;
                                $text =~ s/\n/ /;  # Make sure text report only contains 1 line
                                $text =~ s/\t/ /; # and no tab characters
                                push (@report_array, $text);
                }

                # Sanitize reports
                my $reports = join(",", at report_array);

                # Insert the data
                $sth->execute(
                                                                        $timestamp,
                                                                        $message->{id},
                                                                        $message->{size},
                                                                        $message->{from},
                                                                        join(',', @{$message->{to}}),
                                                                        $message->{subject},
                                                                        $message->{clientip},
                                                                        join(',', @{$message->{archiveplaces}}),
                                                                        $message->{isspam},
                                                                        $message->{ishigh},
                                                                        $message->{issaspam},
                                                                        $message->{isrblspam},
                                                                        $message->{spamwhitelisted},
                                                                        $message->{sascore},
                                                                        $spamreport,
                                                                        $message->{virusinfected},
                                                                        $message->{nameinfected},
                                                                        $message->{otherinfected},
                                                                        $reports,
                                                                        $hostname)
                                or MailScanner::Log::WarnLog("Cannot insert row: %s", $DBI::errstr);

                MailScanner::Log::InfoLog("Finished SQL Logging [$DBI::errstr]");

                $dbh->disconnect if defined $dbh;
                MailScanner::Log::InfoLog("Disconnected from the database");
}