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

[Dmitriy Yatsenko]

I got an issue with amavisd-new-2.11.0,1(freebsd 9.3). All messages with 
score less than 0 are blocked as spam.
It happens when amavis configured to use mysql for lookups,
    @lookup_sql_dsn = ( ['DBI:mysql:database=amavis;host=localhost', 
'amavis', 'password'] );
    @storage_sql_dsn = @lookup_sql_dsn;
    $banned_files_quarantine_method = 'sql:';
    $spam_quarantine_method         = 'sql:';
but when it isn't - all work accordinly to config options.

It looks like amavisd get zero values for options
    spam_tag_level=>"0",
    spam_tag2_level=>"0",
    spam_kill_level=>"0",
    spam_dsn_cutoff_level=>"0",
    spam_quarantine_cutoff_level=>"0"
from mysql.

An "amavisd debug" log messages:

    (36754-01) sql: preparing and executing (6 args): SELECT users.*, 
policy.*, users.id FROM users LEFT JOIN policy ON 
users.policy_id=policy.id WHERE use
rs.email IN (?,?,?,?,?,?) ORDER BY users.priority DESC
    (36754-01) lookup_sql(username at mydomain.tld) matches, 
result=(id=>"51", priority=>"3", policy_id=>"2", 
email=>"username at mydomain.tld", fullname=>-, local=>
"Y", id=>"51", policy_name=>"Normal", virus_lover=>"N", spam_lover=>"N", 
banned_files_lover=>"N", bad_header_lover=>"N", 
bypass_virus_checks=>"N", bypa
ss_spam_checks=>"N", bypass_banned_checks=>"N", 
bypass_header_checks=>"N", spam_modifies_subj=>"Y", 
virus_quarantine_to=>"", spam_quarantine_to=>-, ban
ned_quarantine_to=>-, bad_header_quarantine_to=>-, 
clean_quarantine_to=>"", other_quarantine_to=>-, spam_tag_level=>"0", 
spam_tag2_level=>"0", spam_kil
l_level=>"0", spam_dsn_cutoff_level=>"0", 
spam_quarantine_cutoff_level=>"0", addr_extension_virus=>"", 
addr_extension_spam=>"", addr_extension_banned=>
"", addr_extension_bad_header=>"", warnvirusrecip=>"N", 
warnbannedrecip=>"N", warnbadhrecip=>"Y", newvirus_admin=>"", 
virus_admin=>"", banned_admin=>""
, bad_header_admin=>"", spam_admin=>"", spam_subject_tag=>"", 
spam_subject_tag2=>"[SPAMMY]", message_size_limit=>"0", 
banned_rulenames=>"", id=>"51")


And the same SQL query result made with mysql-client:

    mysql> SELECT users.*, policy.*, users.id FROM users LEFT JOIN 
policy ON users.policy_id=policy.id WHERE users.email IN 
('username at mydomain.tld', 'username', '@mydomain.tld', '@.mydomain.tld', 
'@.ru', '@.') ORDER BY users.priority DESC\G
    *************************** 1. row ***************************
                           id: 51
                     priority: 3
                    policy_id: 2
                        email: username at mydomain.tld
                     fullname: NULL
                        local: Y
                           id: 2
                  policy_name: Normal
                  virus_lover: N
                   spam_lover: N
           banned_files_lover: N
             bad_header_lover: N
          bypass_virus_checks: N
           bypass_spam_checks: N
         bypass_banned_checks: N
         bypass_header_checks: N
           spam_modifies_subj: Y
          virus_quarantine_to:
           spam_quarantine_to: NULL
         banned_quarantine_to: NULL
     bad_header_quarantine_to: NULL
          clean_quarantine_to:
          other_quarantine_to: NULL
               spam_tag_level: -999
              spam_tag2_level: 2.5
              spam_kill_level: 4.7
        spam_dsn_cutoff_level: 7
spam_quarantine_cutoff_level: 20
         addr_extension_virus:
          addr_extension_spam:
        addr_extension_banned:
    addr_extension_bad_header:
               warnvirusrecip: N
              warnbannedrecip: N
                warnbadhrecip: Y
               newvirus_admin:
                  virus_admin:
                 banned_admin:
             bad_header_admin:
                   spam_admin:
             spam_subject_tag:
            spam_subject_tag2: [SPAMMY]
           message_size_limit: 0
             banned_rulenames:
                           id: 51
    *************************** 2. row ***************************
                           id: 2
                     priority: 1
                    policy_id: 6
                        email: @mydomain.tld
                     fullname: �� �������
                        local: Y
                           id: 6
                  policy_name: High
                  virus_lover: N
                   spam_lover: N
           banned_files_lover: N
             bad_header_lover: N
          bypass_virus_checks: N
           bypass_spam_checks: N
         bypass_banned_checks: N
         bypass_header_checks: N
           spam_modifies_subj: Y
          virus_quarantine_to:
           spam_quarantine_to: NULL
         banned_quarantine_to: NULL
     bad_header_quarantine_to: NULL
          clean_quarantine_to:
          other_quarantine_to: NULL
               spam_tag_level: -999
              spam_tag2_level: 2.5
              spam_kill_level: 3.6
        spam_dsn_cutoff_level: 7
spam_quarantine_cutoff_level: 20
         addr_extension_virus:
          addr_extension_spam:
        addr_extension_banned:
    addr_extension_bad_header:
               warnvirusrecip: N
              warnbannedrecip: N
                warnbadhrecip: Y
               newvirus_admin:
                  virus_admin:
                 banned_admin:
             bad_header_admin:
                   spam_admin:
             spam_subject_tag:
            spam_subject_tag2: [SPAMMY]
           message_size_limit: 0
             banned_rulenames:
                           id: 2
    *************************** 3. row ***************************
                           id: 1
                     priority: 0
                    policy_id: 2
                        email: @.
                     fullname: NULL
                        local: Y
                           id: 2
                  policy_name: Normal
                  virus_lover: N
                   spam_lover: N
           banned_files_lover: N
             bad_header_lover: N
          bypass_virus_checks: N
           bypass_spam_checks: N
         bypass_banned_checks: N
         bypass_header_checks: N
           spam_modifies_subj: Y
          virus_quarantine_to:
           spam_quarantine_to: NULL
         banned_quarantine_to: NULL
     bad_header_quarantine_to: NULL
          clean_quarantine_to:
          other_quarantine_to: NULL
               spam_tag_level: -999
              spam_tag2_level: 2.5
              spam_kill_level: 4.7
        spam_dsn_cutoff_level: 7
spam_quarantine_cutoff_level: 20
         addr_extension_virus:
          addr_extension_spam:
        addr_extension_banned:
    addr_extension_bad_header:
               warnvirusrecip: N
              warnbannedrecip: N
                warnbadhrecip: Y
               newvirus_admin:
                  virus_admin:
                 banned_admin:
             bad_header_admin:
                   spam_admin:
             spam_subject_tag:
            spam_subject_tag2: [SPAMMY]
           message_size_limit: 0
             banned_rulenames:
                           id: 1
3 rows in set (0.00 sec)


What can be wrong? What else configuration or details should i provide?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.amavis.org/pipermail/amavis-users/attachments/20161212/63464d12/attachment.html>