Found nn messages in the processing-messages database

[Rick Cooper]

> -----Original Message-----
> From: mailscanner-bounces at lists.mailscanner.info 
> [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf 
> Of Julian Field
> Sent: Friday, April 24, 2009 4:41 AM
> To: MailScanner discussion
> Subject: Re: Found nn messages in the processing-messages database
> 
> 
> 
> On 23/04/2009 20:57, Rick Cooper wrote:
> >
> >
> >    
> >> -----Original Message-----
> >> From: mailscanner-bounces at lists.mailscanner.info
> >> [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf
> >> Of Mark Sapiro
> >> Sent: Thursday, April 23, 2009 2:29 PM
> >> To: MailScanner List
> >> Subject: Re: Found nn messages in the processing-messages database
> >>
> >> Julian Field wrote:
> >>      
> >>> On 23/04/2009 15:12, Mark Sapiro wrote:
> >>>        
> >>>> On Thu, Apr 23, 2009 at 09:31:45AM +0100, Julian Field wrote:
> >>>>
> >>>>          
> >>>>> On 23/04/2009 02:57, Mark Sapiro wrote:
> >>>>>
> >>>>>            
> >>>>>> I have looked a bit more clusely at the messages that
> >>>>>>              
> >> were left in the
> >>      
> >>>>>> database. and it is not only that the id is all-decimal;
> >>>>>>              
> >> the entropy
> >>      
> >>>>>> fragment must also be all-decimal or contain only a
> >>>>>>              
> >> single 'E', so in
> >>      
> >>>>>> every case, they are being interpred as a floating point
> >>>>>>              
> >> number rather
> >>      
> >>>>>> than a string.
> >>>>>>
> >>>>>>
> >>>>>>              
> >>>>> Which is a Perl bug. Perl should carry them around as
> >>>>>            
> >> both a number and
> >>      
> >>>>> a string, and the final destination type is a string
> >>>>>            
> >> (that's the type of
> >>      
> >>>>> the field in the SQL query), so someone is squashing it
> >>>>>            
> >> to just a number
> >>      
> >>>>> before checking the destination type. That's either a bug
> >>>>>            
> >> in Perl or in
> >>      
> >>>>> the implementation of DBD::SQLite.
> >>>>>
> >>>>>            
> >>>>>> E.g., the latest list from running for a while with the
> >>>>>>
> >>>>>>       $sth->execute("$id");
> >>>>>>
> >>>>>> patch is
> >>>>>>
> >>>>>> 74221690410.052E4
> >>>>>> 36888690435.06105
> >>>>>> 73061690441.05915
> >>>>>> 15461690460.09210
> >>>>>>
> >>>>>> all of which are valid floating point number
> >>>>>>              
> >> representations. If you
> >>      
> >>>>>> look at the lists in the messages I previously posted 
> such as at
> >>>>>>
> >>>>>>              
> >> <http://lists.mailscanner.info/pipermail/mailscanner/2009-Apri
> >> l/091022.html>,
> >>      
> >>>>>> you will see that those too are all decimal to the left
> >>>>>>              
> >> of the period
> >>      
> >>>>>> and all decimal or contain at most one 'E' to the right
> >>>>>>              
> >> of the period
> >>      
> >>>>>> so they are all valid floating point number
> >>>>>>              
> >> representations, and were
> >>      
> >>>>>> apparently treated as floating point numbers when passed to the
> >>>>>> statement handler method with perl-DBD-SQLite-1.21, but
> >>>>>>              
> >> apparently not
> >>      
> >>>>>> with the prior perl-DBD-SQLite-1.13.
> >>>>>>
> >>>>>>
> >>>>>>              
> >>>>> Well diagnosed, sir  :-)
> >>>>> So an alternative solution would be to put a "P" (for
> >>>>>            
> >> Postfix, but is
> >>      
> >>>>> arbitrary) on the front of the message id.
> >>>>> Would you like me to do that, or just live with the
> >>>>>            
> >> slight inefficiency
> >>      
> >>>>> in the database deletion?
> >>>>>
> >>>>>            
> >>>> I had an idea, and I am trying
> >>>>
> >>>>       $sth->execute("\'$id\'");
> >>>>
> >>>>          
> >>> That shouldn't work, as you are trying to delete the message
> >>>        
> >> id, not the
> >>      
> >>> message id with quotes around it.
> >>>        
> >>
> >> You are correct. That doesn't work at all.
> >>
> >> I then tried installing MailScanner 4.76.17-1, but that 
> seems to have
> >> some other problem in that it is not picking up new messages from
> >> Postfix at all. Postfix receives messages and puts them in the Hold
> >> queue, and they just stay there. MailScanner doesn't pick 
> them up and
> >> doesn't log anything.
> >>
> >> So I have reverted to 4.76.15-1 with the additional replacement of
> >>
> >>    $sth->execute($id);
> >>
> >> with
> >>
> >>    $MailScanner::ProcDBH->do("DELETE FROM processing WHERE
> >> (id='$id')");
> >>
> >>      
> >
> > Wouldn't
> >
> > 	my $sth = $MailScanner::ProcDBH->prepare(q{DELETE FROM 
> processing
> > WHERE (id='?')});
> > Then
> > 	$sth->execute($id);
> >
> > Work?
> >    
> Yes, but it's slower as you aren't taking advantage of 
> pre-preparing the 
> statement and using placeholders to bind values to it.
> 

No, you misunderstand, you do the prepare in the same spot as now but change
that line to

my $sth = $MailScanner::ProcDBH->prepare(q{DELETE FROM processing WHERE
(id='?')});

You use the q{...} around the complete statement and change $id=? To $id='?'
(this is actually talked about in the module documentation)

So your sql will have single quotes around the value of $id as part of the
sql (which would the proper way to do it, generally speaking)

Then $sth->execute($id); ought to work as intended. Of course you could
always use CAST in the prepare and there would be no doubt that it would be
interrprested as the type you cast the data as ( ex: id = CAST( ?, CHAR) )

Rick


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.