insertion problem.

Hi everyone,

I am having some trouble with an insertion. Specifically an execution
call. I have installed MailScanner (a perl Email Defense software).
MailScanner is working, but I am setting up Real Time SQL Logging using
a customizable feature of this package. I have "SQLRealTimeLogging.pm"
placed into a CustomFunctions directory that gets parse and "require"d.

Yet I am unable to insert into mysql with my custom package. See my .pm
and syslog entry at the bottom of this message. I have marked "IT DIES HERE"
where the death occurs. This is what I have.
Also a trace is included too.

All packages are installed as RPM's.

OS: Fedora Core 2
MS: mailscanner-4.30.3-2
PERL: perl-DBI-1.40-4
perl-DBD-MySQL-2.9003-4
DB: mysql-3.23.58-9
mysql-server-3.23.58-9


I have used a test script in that directory to insert successfully, but
not through my custom.pm. (as written below)
I have tried everything that I know of. I've changed the database it only
include one table and one field. I changed from using execute to using do.
(inefficient, I know..but I am desparate. :) It just will not insert into that database.
I have scanned this mailling list and MailScanner's lists.

If anyone has had this problem before, please let me know.

Thanks..

-k

----begin trace level 9 for one transaction
DBI::db=HASH(0xae84b98) trace level set to 9 in DBI 1.40-ithread (pid
3251)
-> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98
'INSERT INTO maillog_mail (time, msg_id, size, from_user, from_domain,
subject, clientip, archives, isspam, ishighspam, sascore, spamreport)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)') thr#9f9c008
dbih_setup_handle(DBI::st=HASH(0xae84d3c)=>DBI::st=HASH(0xae 390bc),
DBD::mysql::st, ae84d48, Null!)
dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208,
0) thr#9f9c008
dbih_setup_attrib(DBI::st=HASH(0xae390bc), Err,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390bc), State,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390bc), Errstr,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390bc), TraceLevel,
DBI::db=HASH(0xae84b98)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390bc), FetchHashKeyName,
DBI::db=HASH(0xae84b98)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390bc), HandleError,
DBI::db=HASH(0xae84b98)) undef (not defined)
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0xae84d3c) at
/usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTime Logging.pm
line 48 via /usr/lib/MailScanner/MailScanner/Config.pm line 754
-> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98
'INSERT INTO maillog_report (msg_id, filename, filereport) VALUES
(?,?,?)') thr#9f9c008
dbih_setup_handle(DBI::st=HASH(0xae84dcc)=>DBI::st=HASH(0xae 390d4),
DBD::mysql::st, ae84dd8, Null!)
dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208,
0) thr#9f9c008
dbih_setup_attrib(DBI::st=HASH(0xae390d4), Err,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390d4), State,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390d4), Errstr,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390d4), TraceLevel,
DBI::db=HASH(0xae84b98)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390d4), FetchHashKeyName,
DBI::db=HASH(0xae84b98)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae390d4), HandleError,
DBI::db=HASH(0xae84b98)) undef (not defined)
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0xae84dcc) at
/usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTime Logging.pm
line 50 via /usr/lib/MailScanner/MailScanner/Config.pm line 754
-> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98
'INSERT INTO maillog_recipient (msg_id, to_user, to_domain) VALUES
(?,?,?)') thr#9f9c008
dbih_setup_handle(DBI::st=HASH(0xae89998)=>DBI::st=HASH(0xae 84dfc),
DBD::mysql::st, ae899a4, Null!)
dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208,
0) thr#9f9c008
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), Err,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), State,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), Errstr,
DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), TraceLevel,
DBI::db=HASH(0xae84b98)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), FetchHashKeyName,
DBI::db=HASH(0xae84b98)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0xae84dfc), HandleError,
DBI::db=HASH(0xae84b98)) undef (not defined)
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0xae89998) at
/usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTime Logging.pm
line 52 via /usr/lib/MailScanner/MailScanner/Config.pm line 754
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
-> execute for DBD::mysql::st (DBI::st=HASH(0xae84d3c)~0xae390bc
'2004-06-04 09:10:52' 'i54DAm3i003241' 1247 'jspanbauer' 'nfschools.net'
'Golf' '168.169.15.29' '' 0 0 0.159 'not spam, SpamAssassin (score=0.159,
required 6, BAYES_44 -0.00, NO_REAL_NAME 0.16)') thr#9f9c008
-> dbd_st_execute for 0ae84db4
Binding parameters: INSERT INTO maillog_mail (time, msg_id, size,
from_user, from_domain, subject, clientip, archives, isspam, ishighspam,
sascore, spamreport) VALUES ('2004-06-04
09:10:52','i54DAm3i003241','1247','jspanbauer','nfschools.ne t','Golf','168.169.15.29','','0','0','0.159','not
spam, SpamAssassin (score=0.159, required 6, BAYES_44 -0.00, NO_REAL_NAME
0.16)')
error 0 recorded:
<- dbd_st_execute -2 rows
<- execute= undef at
/usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTime Logging.pm
line 105 via /usr/lib/MailScanner/MailScanner/Config.pm line 119
-> $DBI::errstr (&) FETCH from lasth=HASH
<- $DBI::errstr= ''
-> $DBI::errstr (&) FETCH from lasth=HASH
<- $DBI::errstr= ''
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER)
thr#9f9c008
&imp_dbh->mysql: ae84f9c
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER)
thr#9f9c008
! <- DESTROY= undef during global destruction
---- end trace





---- my custom.pm (SQLRealTimeLogging.pm) - this is placed into
CustomFunctions directory.
package MailScanner::CustomConfig;

use DBI;
use strict 'vars';
use strict 'refs';
no strict 'subs'; # Allow bare words for parameter %'s

my $database = "mailscanner";
my $dbuser = "mailscanner";
my $dbpass = "!mailscanner32!";
my $mysqlsocket = "/db/mysql/mysql.sock";

my $dbhandle;
my $sthMail;
my $sthReport;
my $sthRecipient;

sub InitSQLRealTimeLogging {

MailScanner::Log::InfoLog("Initialising SQL Real Time Logging ");

$dbhandle =
DBI->connect("DBI:mysql:$database:localhost;mysql_socket=$my sqlsocket;",
"$dbuser", $dbpass,
{'RaiseError' => 1, 'PrintError' => 1}) or
MailScanner::Log::DieLog("Cannot connect to the database: %s",
$DBI::errstr);
$dbhandle->trace(9,"/tmp/mysql.trace2");

$sthMail = $dbhandle->prepare("INSERT INTO maillog_mail (time, msg_id,
size, from_user, from_domain, subject, clientip, archives, isspam,
ishighspam, sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)") or
die("
Prepare did not work: %s", $DBI::errstr);

$sthReport = $dbhandle->prepare("INSERT INTO maillog_report (msg_id,
filename, filereport) VALUES (?,?,?)");

$sthRecipient = $dbhandle->prepare("INSERT INTO maillog_recipient
(msg_id, to_user, to_domain) VALUES (?,?,?)"); }

sub SQLRealTimeLogging {

use DBI;
my($message) = @_;

my $id = $message->{id};
my $size = $message->{size};
my $from = $message->{from};
my ($from_user, $from_domain);

# split the from address into user and domain bits.
# This may be unnecessary for you; we use it to more easily determine
# inbound vs outbound email in a multi-domain environment.
# HINT: refine queries using SQL 'join' with a table containing local
# domains.

($from_user, $from_domain) = split /\@/, $from;

my @to = @{$message->{to}};
my $subject = $message->{subject};
my $clientip = $message->{clientip};
my $archives = join(',', @{$message->{archiveplaces}});
my $isspam = $message->{isspam};
my $ishighspam = $message->{ishigh};
my $sascore = $message->{sascore};
my $spamreport = $message->{spamreport};

# Get rid of control chars and tidy-up SpamAssassin report
$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);


# maillog_mail insert
my @fields=($timestamp, $id, $size, $from_user, $from_domain,
$subject, $clientip, $archives, $isspam, $ishighspam,
$sascore, $spamreport);

map { s/\'/\\'/g } @fields;
map { ($_ eq '')?'NULL':"$_" } @fields;

# Debug statements added for verifying date is populating fields array.
MailScanner::Log::InfoLog("=========================\nfields array -
\n0:[$fields[0]] 1:[$fields[1]] \n2:[$fields[2]] 3:[$fields[3]] \n");
MailScanner::Log::InfoLog("4:[$fields[4]] 5:[$fields[5]]
\n6:[$fields[6]] 7:[$fields[7]] \n");
MailScanner::Log::InfoLog("8:[$fields[8]] 9:[$fields[9]]
\n10:[$fields[10]] 11:[$fields[11]] \n====================\n");

# Insert @fields into a database table
$sthMail->execute($timestamp, $id, $size, $from_user, $from_domain,
$subject, $clientip, $archives, $isspam, $ishighspam, $sascore,
"$spamreport") or MailScanner::Log::DieLog("Cannot insert into
maillog_mail:
%s", $DBI::errstr);
# IT DIES HERE on the above execute statement.

my($file, $text);
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

my @fields = ($id, $file, $text);
map { s/\'/\\'/g } @fields;

$sthReport->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row into maillog_report:
[$DBI::errstr]");
}

for (@to) {
# again, split the recipient's email into user and domain halves
first.
# see comment above about splitting the email like this.

my ($to_user, $to_domain);
($to_user, $to_domain) = split /\@/, $_;
my @fields = ($id, $to_user, $to_domain);
map { s/\'/\\'/g } @fields;
$sthRecipient->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row into maillog_recipient: [%s]",
$DBI::errstr);
}

}

sub EndSQLRealTimeLogging {
MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");
# Close database connection
$dbhandle->disconnect();
}
1;


---- end custom.pm

----one entry of one message
Jun 3 16:28:03 hostname MailScanner[12208]: MailScanner E-Mail Virus
Scanner version 4.30.3 starting...
Jun 3 16:28:03 hostname MailScanner[12208]: Config: calling custom init
function SQLRealTimeLogging Jun 3 16:28:03 hostname MailScanner[12208]:
Initialising SQL Real Time Logging Jun 3 16:28:03 hostname
MailScanner[12208]: Config: calling custom init function MultipleQueueDir
Jun 3 16:28:03 hostname MailScanner[12208]: Using locktype = flock Jun 3
16:28:04 hostname MailScanner[12208]: New Batch: Forwarding 1 unscanned
messages, 6679 bytes Jun 3 16:28:04 hostname MailScanner[12208]: MCP
Checks completed at 6679 bytes per second Jun 3 16:28:04 hostname
MailScanner[12208]: Spam Checks: Found 1 spam messages Jun 3 16:28:04
hostname MailScanner[12208]: Spam Checks completed at 6679 bytes per
second Jun 3 16:28:04 hostname MailScanner[12208]: Unscanned: Delivered 1
messages

Jun 3 16:28:04 hostname MailScanner[12208]: Virus and Content Scanning:
Starting
Jun 3 16:28:04 hostname MailScanner[12208]: Virus Scanning completed at
6679 bytes per second
Jun 3 16:28:04 hostname MailScanner[12208]: Virus Processing completed at
6679 bytes per second
Jun 3 16:28:04 hostname MailScanner[12208]: Disinfection completed at
6679 bytes per second Jun 3 16:28:04 hostname MailScanner[12208]: Batch
completed at 6679 bytes per second (6679 / 0) Jun 3 16:28:04 hostname
MailScanner[12208]: ========================= Jun 3 16:28:04 hostname
MailScanner[12208]: fields array - Jun 3 16:28:04 hostname
MailScanner[12208]: 0:[2004-06-03 16:28:04] 1:[i53KRoO7012126] Jun 3
16:28:04 hostname MailScanner[12208]: 2:[6679] 3:[fakeuser] Jun 3
16:28:04 hostname MailScanner[12208]: 4:[fakedomain.com] 5:[Don, Extend
Your Auto Warranty, Extend Your Peace of Mind.] Jun 3 16:28:04 hostname
MailScanner[12208]: 6:[192.168.0.1] 7:[] Jun 3 16:28:04 hostname
MailScanner[12208]: 8:[1] 9:[0] Jun 3 16:28:04 hostname
MailScanner[12208]: 10:[8.528] 11:[spam, SBL+XBL, SpamAssassin
(score=8.528, required 6, BAYES_90 2.10, BUY_DIRECT 1.82, HTML_50_60 0.10,
HTML_MESSAGE 0.10, HTML_WEB_BUGS 0.34, NO_OBLIGATION 1.46,
RCVD_IN_BL_SPAMCOP_NET 1.50, RCVD_IN_SBL 1.11)] Jun 3 16:28:04 hostname
MailScanner[12208]: ==================== Jun 3 16:28:04 hostname
MailScanner[12208]: Cannot insert into
maillog_mail:



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org