Found nn messages in the processing-messages database
Julian Field
MailScanner at ecs.soton.ac.uk
Sat Apr 25 12:59:28 IST 2009
On 24/04/2009 12:13, Rick Cooper wrote:
>
>
>
>> -----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)
>
That sounds wrong, but I'm prepared to believe you. Can you show me
where in the documentation it says to do that?
> 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.
>
>
>
Jules
--
Julian Field MEng CITP CEng
www.MailScanner.info
Buy the MailScanner book at www.MailScanner.info/store
MailScanner customisation, or any advanced system administration help?
Contact me at Jules at Jules.FM
PGP footprint: EE81 D763 3DB0 0BFD E1DC 7222 11F6 5947 1415 B654
PGP public key: http://www.jules.fm/julesfm.asc
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
More information about the MailScanner
mailing list