mysql lookups - messages with score less than 0 are blocked as spam

[Martin Matuska]

Jozef Matický <cibula at gamepub.sk
<http://lists.amavis.org/cgi-bin/mailman/listinfo/amavis-users>> wrote:
>/[-- text/plain, encoding 8bit, charset: utf-8, 47 lines --] />//>/Hello, />//>/I have an amavisd-new (currently 2.8.1) with SA (currently 3.4.0) />/running for couple of years now and the same thing started happening to />/me today. It did work okay just couple of days ago. Not sure what the />/problem is, but it is the same as Dmitriy is describing: />//>/- Amavis is able to connect to MySQL />/- SQL query is working okay and returning correct values from database />/- Amavis is getting most of the results okay -> settings like />/spam_lover, virus_lover etc, however the values for scores are all zero
(0): />//>/sql: preparing and executing (5 args): SELECT />/*,mail_users.id,mail_users.name AS sa_username FROM />/mail_users,mail_policy WHERE (mail_users.policy_id = mail_policy.id) AND />/(mail_users.name IN (?,?,?,?,?)) ORDER BY mail_users.id />/Dec 13 13:21:51 hostname amavis[27181]: (27181-01) />/lookup_sql(redacted at redacted.tld
<http://lists.amavis.org/cgi-bin/mailman/listinfo/amavis-users>)
matches, result=(id=>"28", />/user_id=>"1001", domain_id=>"20", policy_id=>"26", name=>"Default", />/description=>"", home=>"/srv/mail", maildir=>"redacted.tld/redacted", />/password=>"hidden", quota=>"0", used=>"0", id=>"28", user_id=>"1001", />/name=>"Default", description=>"", virus_lover=>"N", spam_lover=>"Y", />/banned_files_lover=>"Y", bad_header_lover=>"Y", />/bypass_virus_checks=>"N", bypass_spam_checks=>"N", />/bypass_banned_checks=>"N", bypass_header_checks=>"N", />/spam_tag_level=>"0", spam_tag2_level=>"0", spam_kill_level=>"0", />/message_size_limit=>"0", spam_subject_tag2=>"", id=>"28", />/sa_username=>"redacted at redacted.tld
<http://lists.amavis.org/cgi-bin/mailman/listinfo/amavis-users>") />//>/This is with manual query taken from MySQL query log: />/Neznámá tabulka />/id;user_id;domain_id;policy_id;name;description;home;maildir;password;quota;used;id;user_id;name;description;virus_lover;spam_lover;banned_files_lover;bad_header_lover;bypass_virus_checks;bypass_spam_checks;bypass_banned_checks;bypass_header_checks;spam_tag_level;spam_tag2_level;spam_kill_level;message_size_limit;spam_subject_tag2;id;sa_username
/>/28;1001;20;26;redacted at redacted.tld
<http://lists.amavis.org/cgi-bin/mailman/listinfo/amavis-users>;;/srv/mail;redacted.tld/redacted;hidden;0;0;26;1001;Default;;N;Y;Y;Y;N;N;N;N;-100;6.2;20;0;;28;redacted
at redacted.tld
<http://lists.amavis.org/cgi-bin/mailman/listinfo/amavis-users> />//>/As stated before, it started today or maybe yesterday. Not sure if I />/reloaded amavisd recently. Last amavisd update was back in August and SA />/in May, however I can't tell if amavisd was reloaded back then. I did />/some Perl library updates as recommended by the packaging system couple />/of days ago, however nothing major. />/Pretty much I had to configure amavisd with SA static scores for now />/since most of the mail went to Spam folder with scores set to 0 instead />/of around 6 (X-Spam-Status: Yes, score=0.863 tagged_above=0 required=0). />//>/Any clues what might be wrong? />//This could be the commit that caused the problem:
https://github.com/perl5-dbi/DBD-mysql/commit/caea0b774028650c0cbd9d8f9c4a0b47831116df
 Giovanni
>/Thank you. />//


The problem starts much earlier - after version 4.038:
https://github.com/perl5-dbi/DBD-mysql/commit/702744529a9ff240b00af67983535e65822f3103

The change is that p5-DBD-mysql now returns mysql doubles as perl
doubles and mysql floats as perl floats (and not as a string anymore).
This should be adressed by amavisd.