Mailwatch 0.2 sql prob

[Daniel Bird]

Peter C. Ndikuwera wrote:

>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
>

Peter / Steve,
I've tried this one and it works for me (although the DB username and
password appear in the maillog!!! ;-)

Dan

>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."
>>
>>
>>------------------------------------------------------------------------
>>
>>###############
>># 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");
>>}
>>
>>

--
____________________________________

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 --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030829/7398406d/attachment.html