Problems with type conversions

[bigio]

Hi,
after upgrading to DBD::mysql 4.041 a problem appeared in amavisd-new:
https://lists.amavis.org/pipermail/amavis-users/2016-December/004674.html
The problem could be related to this commit:
caea0b7
Cheers
Giovanni

[bigio]

If it could be useful I am running MariaDB 10.0.28.

[pali]

Can you test last engineering version 4.041_1? Or version from git master?

[bigio]

On Fri, Jan 20, 2017 at 04:19:32PM -0800, pali wrote: Can you test last engineering version 4.041_1? Or version from git master?

I tried git master and the issue is still there, it seems that amavisd run correctly the query but does not undestand that the returned result is a float and it treats it as an int. Cheers & Thanks Giovanni

…

-- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #78 (comment)

[pali]

Can you describe problem in DBD::mysql? Sorry, but from your report I just understand that amavisd-new does not work...

[bigio]

On Sat, Jan 21, 2017 at 08:03:19AM -0800, pali wrote: Can you describe problem in DBD::mysql? Sorry, but from your report I just understand that amavisd-new does not work...

The problem happens only with DBD::mysql > 4.037, I am investigating also on amavisd-new side. Giovanni

…

-- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #78 (comment)

[pali]

Ideally if you can provide perl code which outputs that "wrong result".

[brianmay]

I believe this is the relevant code in amavisd-new (note I haven't been able to reproduce this myself):

  eval {                       
    snmp_count('OpsSqlSelect');                      
    $conn_h->execute($sel,@pos_args);  # do the query
    # fetch query results                                       
    while ( defined($a_ref=$conn_h->fetchrow_arrayref($sel)) ) {
      my(@names) = @{$conn_h->sth($sel)->{NAME_lc}};
      $match = {}; @$match{@names} = @$a_ref;                         
      if ($clause_name eq 'sel_policy' && !exists $match->{'local'} &&
          defined $match->{'email'} && $match->{'email'} eq '@.') {    
          [ ... AFAIK not executed ...]
      }                                     
      push(@result, {%$match});  # copy hash                   
      push(@matchingkey, join(", ", map { sprintf("%s=>%s", $_,                 
                                !defined($match->{$_})?'-':'"'.$match->{$_}.'"'
                                ) } @names));
      last  if !$get_all;
    }

   [ ... ]
   do_log(4,'lookup_sql(%s) matches, result=(%s)', $addr,$_) for @matchingkey;

This results in output like:

(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", bypass_spam_checks=>"N", bypass_banned_checks=>"N", bypass_header_checks=>"N", spam_modifies_subj=>"Y", virus_quarantine_to=>"", spam_quarantine_to=>-, banned_quarantine_to=>-, bad_header_quarantine_to=>-, clean_quarantine_to=>"", other_quarantine_to=>-, spam_tag_level=>"0", 
spam_tag2_level=>"0", spam_kill_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")

Where the "0" values are wrong. These aren't just rounding errors, the values in the database were very different, e.g.:

spam_tag_level: -999
spam_tag2_level: 2.5
spam_kill_level: 4.7
spam_dsn_cutoff_level: 7
spam_quarantine_cutoff_level: 20

For more details see:

• https://lists.amavis.org/pipermail/amavis-users/2016-December/004674.html
• https://lists.amavis.org/pipermail/amavis-users/2016-December/004675.html

To me it looks like the relevant part is sprintf using "%s", I don't see how this could get the wrong value, even if it is string, float, or double. So I suspect DBD-mysql might be returning bad data.

Note that that I haven't reproduced this myself, just assembling data from existing reports.

[pali]

That is strange :-( I'm still not able to reproduce this problem... I tried:

use strict;
use warnings;

use DBI;
use Data::Dumper;

my $dbh = DBI->connect("dbi:mysql:test", "user", "pass");
my $sth = $dbh->prepare("select * from t");
$sth->execute();

while ( my $ref = $sth->fetchrow_arrayref() ) {

  my @names = @{$sth->{NAME_lc}};
  my $match = {}; @$match{@names} = @$ref;

  my $str = join(", ", map { sprintf("%s=>%s", $_,                 
                                  !defined($match->{$_})?'-':'"'.$match->{$_}.'"'
                                  ) } @names);

  print Dumper [\@names, $ref, $match, $str];
  my $t = sprintf "%s", '"' . $ref->[0] . '"';
  print "$t\n";

}

and its output is correct:

$VAR1 = [
          [
            'id'
          ],
          [
            '-999'
          ],
          {
            'id' => '-999'
          },
          'id=>"-999"'
        ];
"-999"
$VAR1 = [
          [
            'id'
          ],
          [
            '2.5'
          ],
          {
            'id' => '2.5'
          },
          'id=>"2.5"'
        ];
"2.5"
$VAR1 = [
          [
            'id'
          ],
          [
            '4.7'
          ],
          {
            'id' => '4.7'
          },
          'id=>"4.7"'
        ];
"4.7"
$VAR1 = [
          [
            'id'
          ],
          [
            '7'
          ],
          {
            'id' => '7'
          },
          'id=>"7"'
        ];
"7"

Data stored in mysql are:

mysql> describe t;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| id    | float | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
| -999 |
|  2.5 |
|  4.7 |
|    7 |
+------+
4 rows in set (0.00 sec)

So I see only option now, enabling DBI tracing in that problematic amavis code and sending stderr trace output for later inspection. Tracing can be enabled by DBI->trace(2); and disabled by DBI->trace(0);.

[brianmay]

Full debug output is available here: https://bugs.debian.org/cgi-bin/bugreport.cgi?att=2;bug=847311;filename=amavisd-debug.txt;msg=54

I believe these are the most relevant lines:

T  <- fetchrow_arrayref= ( [ 4 1 1 'riud' 'riud' '' 1 5 1 'RECEIVER@DOMAIN2.COM' 'RECEIVER@DOMAIN2.COM' 'Y' 1 1 0 'riud' 'riud' 'r' 'Normalni' 'N' 'N' 'N' 'N' 'N' 'N' 'N' 'N' 'N' '' '' '' '' '' '' 0 0 0 0 0 '' '' '' '' 'N' 'N' 'N' '' '' '' '' '' '' '' 0 '' -1 24 -1 24 'N' 4 ] ) [1 items] row1 at (eval 110) line 140
    -> FETCH for DBD::mysql::st (DBI::st=HASH(0x559a620afe90)~INNER 'NAME_lc') thr#559a5c331010
    -> dbd_st_FETCH_attrib for 559a5f004e98, key NAME_lc
    -> dbd_st_FETCH_attrib for 559a5f004e98, key NAME
 T  <- FETCH= ( [ 'id' 'sys_userid' 'sys_groupid' 'sys_perm_user' 'sys_perm_group' 'sys_perm_other' 'server_id' 'priority' 'policy_id' 'email' 'fullname' 'local' 'id' 'sys_userid' 'sys_groupid' 'sys_perm_user' 'sys_perm_group' 'sys_perm_other' 'policy_name' 'virus_lover' 'spam_lover' 'banned_files_lover' 'bad_header_lover' 'bypass_virus_checks' 'bypass_spam_checks' 'bypass_banned_checks' 'bypass_header_checks' 'spam_modifies_subj' 'virus_quarantine_to' 'spam_quarantine_to' 'banned_quarantine_to' 'bad_header_quarantine_to' 'clean_quarantine_to' 'other_quarantine_to' 'spam_tag_level' 'spam_tag2_level' 'spam_kill_level' 'spam_dsn_cutoff_level' 'spam_quarantine_cutoff_level' 'addr_extension_virus' 'addr_extension_spam' 'addr_extension_banned' 'addr_extension_bad_header' 'warnvirusrecip' 'warnbannedrecip' 'warnbadhrecip' 'newvirus_admin' 'virus_admin' 'banned_admin' 'bad_header_admin' 'spam_admin' 'spam_subject_tag' 'spam_subject_tag2' 'message_size_limit' 'banned_rulenames' 'policyd_quota_in' 'policyd_quota_in_period' 'policyd_quota_out' 'policyd_quota_out_period' 'policyd_greylist' 'id' ] ) [1 items] at (eval 112) line 308

(side note: what result would you expect if the database has NULL values?)

I believe (but not confirmed) this is the database schema used:
https://git.ispconfig.org/ispconfig/ispconfig3/blob/0f9fa27a6c7026f77c7d857c971b1db1b709f583/install/sql/ispconfig3.sql

I have requested the submitter provide the following information:

desc spamfilter_users;
desc spamfilter_policy;
SELECT *,spamfilter_users.id FROM spamfilter_users LEFT JOIN spamfilter_policy ON spamfilter_users.policy_id=spamfilter_policy.id WHERE spamfilter_users.email IN ('RECEIVER@DOMAIN2.COM','@domain2.com','@.domain2.com','@.com','@.') ORDER BY spamfilter_users.priority DESC;

[pali]

Ok, so DBI trace show that DBI itself got 0 for spam_tag_level column. Strange.

Please provide that SELECT and schemas from mysql console client to verify that there is not problem on server.

I would like to see output from that SELECT by simple perl script (e.g. reuse my above example). And also please provide affected perl version (perl -V).

Also I would like to see what is internally stored in perl scalar represening that spam_tag_level. You can do that by calling function Dump from Devel::Peek:

use Devel::Peek;
Dump($ref->[3]);

(in check which column in $ref is spam_tag_level). Dump outputs scalar structure to stderr.

Also if you can it would be good to provide also TCP dump of mysql protocol communication. That could verify what is really sent from MySQL server. It should be possible e.g. via wireshark or tcpdump and setting mysql to connect via TCP on 127.0.0.1 port 3306 (beware that specifying "localhost" means to connect via UNIX filesystem socket! so IP address needs to be used).

[brianmay]

Some additional information you asked for (but not all of it):

  desc spamfilter_users

    Field  Type  Null  Key  Default  Extra                          
    id             int(11) unsigned NO  PRI NULL auto_increment     
    sys_userid     int(11) unsigned NO      0                       
    sys_groupid    int(11) unsigned NO      0                       
    sys_perm_user  varchar(5)       NO                              
    sys_perm_group varchar(5)       NO                              
    sys_perm_other varchar(5)       NO                              
    server_id      int(11) unsigned NO      0                       
    priority       tinyint(3)       NO      7                       
                   unsigned                                         
    policy_id      int(11) unsigned NO      1                       
    email          varchar(255)     NO  UNI                         
    fullname       varchar(64)      YES     NULL                    
    local          varchar(1)       YES     NULL                    

  #############################################################################################

  desc spamfilter_policy

    Field  Type  Null  Key  Default  Extra                              
    id               int(11) unsigned NO  PRI NULL auto_increment       
    sys_userid       int(11) unsigned NO      0                         
    sys_groupid      int(11) unsigned NO      0                         
    sys_perm_user    varchar(5)       NO                                
    sys_perm_group   varchar(5)       NO                                
    sys_perm_other   varchar(5)       NO                                
    policy_name      varchar(64)      YES     NULL                      
    virus_lover      enum('N','Y')    YES     NULL                      
    spam_lover       enum('N','Y')    YES     NULL                      
    banned_files_lov enum('N','Y')    YES     NULL                      
    er                                                                  
    bad_header_lover enum('N','Y')    YES     NULL                      
    bypass_virus_che enum('N','Y')    YES     NULL                      
    cks                                                                 
    bypass_spam_chec enum('N','Y')    YES     NULL                      
    ks                                                                  
    bypass_banned_ch enum('N','Y')    YES     NULL                      
    ecks                                                                
    bypass_header_ch enum('N','Y')    YES     NULL                      
    ecks                                                                
    spam_modifies_su enum('N','Y')    YES     NULL                      
    bj                                                                  
    virus_quarantine varchar(255)     YES     NULL                      
    _to                                                                 
    spam_quarantine_ varchar(255)     YES     NULL                      
    to                                                                  
    banned_quarantin varchar(255)     YES     NULL                      
    e_to                                                                
    bad_header_quara varchar(255)     YES     NULL                      
    ntine_to                                                            
    clean_quarantine varchar(255)     YES     NULL                      
    _to                                                                 
    other_quarantine varchar(255)     YES     NULL                      
    _to                                                                 
    spam_tag_level   float            YES     NULL                      
    spam_tag2_level  float            YES     NULL                      
    spam_kill_level  float            YES     NULL                      
    spam_dsn_cutoff_ float            YES     NULL                      
    level                                                               
    spam_quarantine_ float            YES     NULL                      
    cutoff_level                                                        
    addr_extension_v varchar(64)      YES     NULL                      
    irus                                                                
    addr_extension_s varchar(64)      YES     NULL                      
    pam                                                                 
    addr_extension_b varchar(64)      YES     NULL                      
    anned                                                               
    addr_extension_b varchar(64)      YES     NULL                      
    ad_header                                                           
    warnvirusrecip   enum('N','Y')    YES     NULL                      
    warnbannedrecip  enum('N','Y')    YES     NULL                      
    warnbadhrecip    enum('N','Y')    YES     NULL                      
    newvirus_admin   varchar(64)      YES     NULL                      
    virus_admin      varchar(64)      YES     NULL                      
    banned_admin     varchar(64)      YES     NULL                      
    bad_header_admin varchar(64)      YES     NULL                      
    spam_admin       varchar(64)      YES     NULL                      
    spam_subject_tag varchar(64)      YES     NULL                      
    spam_subject_tag varchar(64)      YES     NULL                      
    2                                                                   
    message_size_lim int(11) unsigned YES     NULL                      
    it                                                                  
    banned_rulenames varchar(64)      YES     NULL                      
    policyd_quota_in int(11)          NO      -1                        
    policyd_quota_in int(11)          NO      24                        
    _period                                                             
    policyd_quota_ou int(11)          NO      -1                        
    t                                                                   
    policyd_quota_ou int(11)          NO      24                        
    t_period                                                            
    policyd_greylist enum('Y','N')    NO      N                         

  #############################################################################################

  SELECT *,spamfilter_users.id FROM spamfilter_users LEFT JOIN
  spamfilter_policy ON
  spamfilter_users.policy_id=spamfilter_policy.id WHERE
  spamfilter_users.email IN
  ('RECEIVER@DOMAIN2.COM','@domain2.com','@.domain2.com','@.com','@.')
  ORDER BY spamfilter_users.priority DESC;

    id  sys_userid  sys_groupi sys_perm_u sys_perm_g sys_perm_o server_id  priority  policy_id  email  fullname  loca l  id  sys_userid  sys_groupi sys_perm_u sys_perm_g sys_perm_o policy_nam virus_love spam_lover  banned_fil bad_header bypass_vir bypass_spa bypass_bann bypass_head spam_modif virus_quar spam_quara banned_quar bad_header clean_quar other_quar spam_tag_l spam_tag2_ spam_kill_ spam_dsn_c spam_quara addr_extens addr_exten addr_exten addr_exten warnvirusr warnbanned warnbadhre newvirus_a virus_admi banned_adm bad_header spam_admin  spam_subje spam_subje message_si banned_rul policyd_qu policyd_qu policyd_qu policyd_qu policyd_gr id    
                    d          ser        roup       ther                                                                                d          ser        roup       ther       e          r                      es_lover   _lover     us_checks  m_checks   ed_checks   er_checks   ies_subj   antine_to  ntine_to   antine_to   _quarantin antine_to  antine_to  evel       level      level      utoff_leve ntine_cuto ion_virus   sion_spam  sion_banne sion_bad_h ecip       recip      cip        dmin       n          in         _admin                 ct_tag     ct_tag2    ze_limit   enames     ota_in     ota_in_per ota_out    ota_out_pe eylist           
                                                                                                                                                                                                                                                                                                                                        e_to                                                              l          ff_level                          d          eader                                                                                                                                                      iod                   riod                        
    4 1 1 riud riud  1 5 1 receiver@domain2.com receiver@domain2.com Y 1 1 0 riud riud r Normalni N N N N N N N N N       -50 4.5 15 4.5 15     N N N        0  -1 24 -1 24 N 4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

  #############################################################################################

[bigio]

On 02/13/17 23:27, pali wrote: Ok, so DBI trace show that DBI itself got |0| for spam_tag_level column. Strange. Please provide that SELECT and schemas from mysql console client to verify that there is not problem on server. I would like to see output from that SELECT by simple perl script (e.g. reuse my above example). And also please provide affected perl version (perl -V). Also I would like to see what is /internally/ stored in perl scalar represening that |spam_tag_level|. You can do that by calling function |Dump| from |Devel::Peek|: |use Devel::Peek; Dump($ref->[3]); | (in check which column in $ref is spam_tag_level). Dump outputs scalar structure to stderr. Also if you can it would be good to provide also TCP dump of mysql protocol communication. That could verify what is really sent from MySQL server. It should be possible e.g. via wireshark or tcpdump and setting mysql to connect via TCP on 127.0.0.1 port 3306 (beware that specifying "localhost" means to connect via UNIX filesystem socket! so IP address needs to be used). — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#78 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ACYK1ogJygF--sKRhNpCRsJow2sunbKfks5rcNi0gaJpZM4LjKl9>.

Some infos attached. Cheers Giovanni $VAR1 = [ [ 'id', 'sys_userid', 'sys_groupid', 'sys_perm_user', 'sys_perm_group', 'sys_perm_other', 'server_id', 'priority', 'policy_id', 'email', 'fullname', 'local', 'id', 'sys_userid', 'sys_groupid', 'sys_perm_user', 'sys_perm_group', 'sys_perm_other', 'policy_name', 'virus_lover', 'spam_lover', 'banned_files_lover', 'bad_header_lover', 'bypass_virus_checks', 'bypass_spam_checks', 'bypass_banned_checks', 'bypass_header_checks', 'spam_modifies_subj', 'virus_quarantine_to', 'spam_quarantine_to', 'banned_quarantine_to', 'bad_header_quarantine_to', 'clean_quarantine_to', 'other_quarantine_to', 'spam_tag_level', 'spam_tag2_level', 'spam_kill_level', 'spam_dsn_cutoff_level', 'spam_quarantine_cutoff_level', 'addr_extension_virus', 'addr_extension_spam', 'addr_extension_banned', 'addr_extension_bad_header', 'warnvirusrecip', 'warnbannedrecip', 'warnbadhrecip', 'newvirus_admin', 'virus_admin', 'banned_admin', 'bad_header_admin', 'spam_admin', 'spam_subject_tag', 'spam_subject_tag2', 'message_size_limit', 'banned_rulenames', 'id' ], [ 402, 1, 0, 'riud', 'riud', '', 6, 10, 11, 'giovanni@paclan.it', 'giovanni@paclan.it', 'Y', 11, 1, 1, 'riud', 'riud', 'r', 'Normal-no-banned', 'N', 'N', 'Y', 'N', 'N', undef, 'N', 'N', 'Y', undef, undef, undef, undef, undef, undef, '1', '3.5', '4.5', '0', '0', '', '', '', '', 'N', 'N', 'N', '', '', '', '', '', '', '***SPAM***', 0, '', 402 ], { 'newvirus_admin' => '', 'addr_extension_bad_header' => '', 'fullname' => 'giovanni@paclan.it', 'policy_name' => 'Normal-no-banned', 'addr_extension_spam' => '', 'sys_perm_group' => 'riud', 'clean_quarantine_to' => undef, 'email' => 'giovanni@paclan.it', 'sys_userid' => 1, 'virus_admin' => '', 'spam_kill_level' => '4.5', 'spam_tag_level' => '1', 'bypass_virus_checks' => 'N', 'banned_rulenames' => '', 'bad_header_admin' => '', 'spam_tag2_level' => '3.5', 'addr_extension_banned' => '', 'spam_lover' => 'N', 'spam_subject_tag2' => '***SPAM***', 'spam_admin' => '', 'warnvirusrecip' => 'N', 'server_id' => 6, 'spam_quarantine_to' => undef, 'message_size_limit' => 0, 'warnbannedrecip' => 'N', 'banned_files_lover' => 'Y', 'banned_quarantine_to' => undef, 'bad_header_lover' => 'N', 'sys_perm_user' => 'riud', 'banned_admin' => '', 'spam_quarantine_cutoff_level' => '0', 'policy_id' => 11, 'warnbadhrecip' => 'N', 'spam_dsn_cutoff_level' => '0', 'virus_lover' => 'N', 'bypass_header_checks' => 'N', 'bypass_banned_checks' => 'N', 'priority' => 10, 'id' => 402, 'sys_perm_other' => 'r', 'bad_header_quarantine_to' => undef, 'spam_modifies_subj' => 'Y', 'other_quarantine_to' => undef, 'sys_groupid' => 1, 'addr_extension_virus' => '', 'virus_quarantine_to' => undef, 'bypass_spam_checks' => undef, 'local' => 'Y', 'spam_subject_tag' => '' }, 'id=>"402", sys_userid=>"1", sys_groupid=>"1", sys_perm_user=>"riud", sys_perm_group=>"riud", sys_perm_other=>"r", server_id=>"6", priority=>"10", policy_id=>"11", email=>"giovanni@paclan.it", fullname=>"giovanni@paclan.it", local=>"Y", id=>"402", sys_userid=>"1", sys_groupid=>"1", sys_perm_user=>"riud", sys_perm_group=>"riud", sys_perm_other=>"r", policy_name=>"Normal-no-banned", virus_lover=>"N", spam_lover=>"N", banned_files_lover=>"Y", bad_header_lover=>"N", bypass_virus_checks=>"N", bypass_spam_checks=>-, bypass_banned_checks=>"N", bypass_header_checks=>"N", spam_modifies_subj=>"Y", virus_quarantine_to=>-, spam_quarantine_to=>-, banned_quarantine_to=>-, bad_header_quarantine_to=>-, clean_quarantine_to=>-, other_quarantine_to=>-, spam_tag_level=>"1", spam_tag2_level=>"3.5", spam_kill_level=>"4.5", 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=>"N", newvirus_admin=>"", virus_admin=>"", banned_admin=>"", bad_header_admin=>"", spam_admin=>"", spam_subject_tag=>"", spam_subject_tag2=>"***SPAM***", message_size_limit=>"0", banned_rulenames=>"", id=>"402"' ]; "402" $VAR1 = [ [ 'id', 'sys_userid', 'sys_groupid', 'sys_perm_user', 'sys_perm_group', 'sys_perm_other', 'server_id', 'priority', 'policy_id', 'email', 'fullname', 'local', 'id', 'sys_userid', 'sys_groupid', 'sys_perm_user', 'sys_perm_group', 'sys_perm_other', 'policy_name', 'virus_lover', 'spam_lover', 'banned_files_lover', 'bad_header_lover', 'bypass_virus_checks', 'bypass_spam_checks', 'bypass_banned_checks', 'bypass_header_checks', 'spam_modifies_subj', 'virus_quarantine_to', 'spam_quarantine_to', 'banned_quarantine_to', 'bad_header_quarantine_to', 'clean_quarantine_to', 'other_quarantine_to', 'spam_tag_level', 'spam_tag2_level', 'spam_kill_level', 'spam_dsn_cutoff_level', 'spam_quarantine_cutoff_level', 'addr_extension_virus', 'addr_extension_spam', 'addr_extension_banned', 'addr_extension_bad_header', 'warnvirusrecip', 'warnbannedrecip', 'warnbadhrecip', 'newvirus_admin', 'virus_admin', 'banned_admin', 'bad_header_admin', 'spam_admin', 'spam_subject_tag', 'spam_subject_tag2', 'message_size_limit', 'banned_rulenames', 'id' ], [ 1122, 1, 32, 'riud', 'riud', '', 6, 5, 5, '@paclan.it', '@paclan.it', 'Y', 5, 1, 0, 'riud', 'riud', 'r', 'Normal', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', '', '', '', '', '', '', '1', '3.5', '4.5', '0', '0', '', '', '', '', 'N', 'N', 'N', '', '', '', '', '', '', '***SPAM***', 0, '', 1122 ], { 'bypass_spam_checks' => 'N', 'virus_quarantine_to' => '', 'addr_extension_virus' => '', 'sys_groupid' => 0, 'other_quarantine_to' => '', 'spam_subject_tag' => '', 'local' => 'Y', 'priority' => 5, 'bypass_banned_checks' => 'N', 'bypass_header_checks' => 'N', 'virus_lover' => 'N', 'spam_dsn_cutoff_level' => '0', 'warnbadhrecip' => 'N', 'spam_modifies_subj' => 'Y', 'sys_perm_other' => 'r', 'bad_header_quarantine_to' => '', 'id' => 1122, 'banned_admin' => '', 'bad_header_lover' => 'N', 'sys_perm_user' => 'riud', 'policy_id' => 5, 'spam_quarantine_cutoff_level' => '0', 'warnbannedrecip' => 'N', 'message_size_limit' => 0, 'banned_files_lover' => 'N', 'banned_quarantine_to' => '', 'addr_extension_banned' => '', 'spam_tag2_level' => '3.5', 'spam_quarantine_to' => '', 'warnvirusrecip' => 'N', 'server_id' => 6, 'spam_subject_tag2' => '***SPAM***', 'spam_lover' => 'N', 'spam_admin' => '', 'virus_admin' => '', 'sys_userid' => 1, 'bad_header_admin' => '', 'bypass_virus_checks' => 'N', 'banned_rulenames' => '', 'spam_tag_level' => '1', 'spam_kill_level' => '4.5', 'sys_perm_group' => 'riud', 'clean_quarantine_to' => '', 'addr_extension_spam' => '', 'email' => '@paclan.it', 'addr_extension_bad_header' => '', 'newvirus_admin' => '', 'policy_name' => 'Normal', 'fullname' => '@paclan.it' }, 'id=>"1122", sys_userid=>"1", sys_groupid=>"0", sys_perm_user=>"riud", sys_perm_group=>"riud", sys_perm_other=>"r", server_id=>"6", priority=>"5", policy_id=>"5", email=>"@paclan.it", fullname=>"@paclan.it", local=>"Y", id=>"1122", sys_userid=>"1", sys_groupid=>"0", sys_perm_user=>"riud", sys_perm_group=>"riud", sys_perm_other=>"r", 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=>"", banned_quarantine_to=>"", bad_header_quarantine_to=>"", clean_quarantine_to=>"", other_quarantine_to=>"", spam_tag_level=>"1", spam_tag2_level=>"3.5", spam_kill_level=>"4.5", 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=>"N", newvirus_admin=>"", virus_admin=>"", banned_admin=>"", bad_header_admin=>"", spam_admin=>"", spam_subject_tag=>"", spam_subject_tag2=>"***SPAM***", message_size_limit=>"0", banned_rulenames=>"", id=>"1122"' ]; "1122"

[pali]

@brianmay so in your output is spam_tag_level=0, spam_tag2_level=-1, spam_kill_level=24 and spam_dsn_cutoff_level=-1. This is from mysql console client, right?

@bigio your output is from perl DBI code and is correct, right?

[bigio]

This is from your perl code, some more info attached.
perl.txt
sql-output.txt

[pali]

Ah... mysql (resp. mariadb) sees data correctly. Simple SELECT over perl DBI from amavisd-new code sees data correctly. And amavisd-new itself with DBI gets incorrect data, plus DBI sees incorrect data.

Has some else got similar problem with other application as amavisd-new? Otherwise conclusion is that just amavisd-new with DBD::mysql does not work and it is only one reproducer...

The last thing which can show some light into this problem is looking at Dump information from Devel::Peek.

[bigio]

Some more tests:
I tried with an OpenBSD vm and a remote MySQL (5.1.73) with same results (I think this is not MariaDB related).
Attached Devel::Peek infos.
amavisd.diff.txt
amavisd.log.txt

[pali]

Please call Dump as early as possible, ideally before other functions (like that join) which read that scalar (as they can alter it). And you do not have to check if scalar is defined. Dump correctly handle also undefs.

From your Dump we can just see that variable is tainted. Are you running perl in taint (-T) mode? (Note that it should have no effect for DBI...)

[bigio]

Now I called Dump just after "$match = {}; @$match{@NAMEs} = @$a_ref;".
amavisd-new runs all on tainted mode, I tried to run your test-script in tainted mode but it still works.
amavisd.log.txt

[bigio]

remove "perl -T" from amavisd-new first line, amavisd gives a lot of errors because it is not running in tainted mode but the query give correct results.
amavisd.log.txt

[pali]

Thank you for info! Now we know that problem is related to taint mode. I'm still not able to reproduce this problem, but from your Dump it looks like perl refused to assign value from mysql to perl scalar.

Can you try to apply this patch to DBD-mysql and recompile it?

diff --git a/dbdimp.c b/dbdimp.c
index 91cc1a8..ad4f1b7 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -4567,6 +4567,8 @@ process:
       char *col= cols[i];
       SV *sv= AvARRAY(av)[i]; /* Note: we (re)use the SV in the AV	*/
 
+      fprintf(stderr, "i=%u len=%lu flags=%u col='%s'\n", i, lengths[i], fields[i].flags, cols[i]);
+
       if (col)
       {
         STRLEN len= lengths[i];
@@ -4584,8 +4586,11 @@ process:
           if (!(fields[i].flags & ZEROFILL_FLAG))
           {
             /* Coerce to dobule and set scalar as NV */
+            sv_dump(sv);
             (void) SvNV(sv);
+            sv_dump(sv);
             SvNOK_only(sv);
+            sv_dump(sv);
           }
           break;
 

It should print additional info to stderr for all fetch commands. sv_dump is same as Dump.

[bigio]

Data attached.
amavisd.log.txt

[pali]

Thank you! From your output we can see that NV value (float) is not filled in second dump when it should be. So problem is in SvNV() call. Going to look into perl source code when and why should it happen. Which perl version are you using? And can you recheck that NV value in second dump is filled correctly?

[bigio]

Will double check tomorrow morning CEST,
latest test has been done with Perl 5.24.
perl.log.txt

[pali]

I suspect there is bug (or maybe it is feature?) in perl itself. Function sv_2nv_flags() in perl (called by SvNV()) does not upgrade scalar to NV (float) in specific conditions (and float value is lost). This is just observation from reading perl source code. First I need to be able to reproduce this bug and then I can say if problem is in amavis, DBD::mysql, DBI or perl.

[bigio]

Some more infos (maybe) with a new diff.
debug.log.txt
patch-dbdimp_c.diff.txt

[pali]

Yes, I expected that those dumps are from problematic float columns. As I wrote yesterday I will try to reproduce that scenario when SvNV() does not upgrade scalar to NV if it is really truth. And then decide next steps...

[pali]

I opened tiket in perl bug tracker for this problem: https://rt.perl.org/Public/Bug/Display.html?id=130801

[brianmay]

Thanks!

[pali]

So it is probably problem with understanding perlapi documentation and different behavior for floating point magic scalar seems to be OK... It is just (for me) strange that non-magic integer, magic integer and non-magic float is working, just magic float is problematic...

Can you try to apply this patch? It explicitly set float (NV) value via sv_setnv(). And in same case it set also integer values.

diff --git a/dbdimp.c b/dbdimp.c
index 91cc1a8..d484d80 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -4584,8 +4584,7 @@ process:
           if (!(fields[i].flags & ZEROFILL_FLAG))
           {
             /* Coerce to dobule and set scalar as NV */
-            (void) SvNV(sv);
-            SvNOK_only(sv);
+            sv_setnv(sv, SvNV(sv));
           }
           break;
 
@@ -4594,15 +4593,9 @@ process:
           {
             /* Coerce to integer and set scalar as UV resp. IV */
             if (fields[i].flags & UNSIGNED_FLAG)
-            {
-              (void) SvUV(sv);
-              SvIOK_only_UV(sv);
-            }
+              sv_setuv(sv, SvUV(sv));
             else
-            {
-              (void) SvIV(sv);
-              SvIOK_only(sv);
-            }
+              sv_setiv(sv, SvIV(sv));
           }
           break;
 

[bigio]

On 02/23/17 23:50, pali wrote: So it is probably problem with understanding perlapi documentation and different behavior for floating point magic scalar seems to be OK... It is just (for me) strange that non-magic integer, magic integer and non-magic float is working, just magic float is problematic... Can you try to apply this patch? It explicitly set float (NV) value via sv_setnv(). And in same case it set also integer values. |diff --git a/dbdimp.c b/dbdimp.c index 91cc1a8..d484d80 100644 --- a/dbdimp.c +++ b/dbdimp.c @@ -4584,8 +4584,7 @@ process: if (!(fields[i].flags & ZEROFILL_FLAG)) { /* Coerce to dobule and set scalar as NV */ - (void) SvNV(sv); - SvNOK_only(sv); + sv_setnv(sv, SvNV(sv)); } break; @@ -4594,15 +4593,9 @@ process: { /* Coerce to integer and set scalar as UV resp. IV */ if (fields[i].flags & UNSIGNED_FLAG) - { - (void) SvUV(sv); - SvIOK_only_UV(sv); - } + sv_setuv(sv, SvUV(sv)); else - { - (void) SvIV(sv); - SvIOK_only(sv); - } + sv_setiv(sv, SvIV(sv)); } break; |

This fixes the issue, thanks. Giovanni

[brianmay]

Hello.

@pali Thanks for you help with this.

Can I please confirm: Does this only cause problems when Perl is in tainted mode?

Thanks

[pali]

@brianmay Problem with floats seems to be only if DBD::mysql is filling values into magical scalars. Tainted scalars are magical. Scalars are created by DBI (not DBD::mysql) so this probably depends on DBI version...

I was not fully able to reproduce this problem even in tainted Perl mode. I just found code path in perl sources which could trigger it. So... in case DBI does not pass magical scalars to DBD::mysql then this problem could not happen. Note that tainted scalars are not the only type of magical scalars. I have no idea if DBI could not pass another magical scalars... So I cannot answer to your question.

What I can say is that @bigio already tested my change and confirmed that it fixes this problem. Function sv_setnv() really should store float value into variable. I re-checked not only documentation, but also implementation perl.

This is probably the most suspicious problem which I ever seen and I was not able to reproduce it.

Now I do not thing that SvNV() behavior would change to guarantee filling NV slot. Maybe just documentation would be extended or fixed for better understanding. For sure current and older perl versions would not be changed, so fix in DBD::mysql is needed.

[mbeijen]

@bigio You can update OpenBSD to 4.042 now and the AmavisD problem should be fixed.

[pali]

This fix was reverted in 4.043.

[Fneufneu]

can we re open this issue plz ? 2 month and the last version of DBD-mysql is still broken

[LsPjvVzszDt]

Fedora 27, for which the current shipped version is perl-DBD-MySQL-4.043-6, also still has this bug.

amavisd-new SQL lookups of float fields still return 0.

[dbielefeldt]

I know this is an old thread, but the problem with amavis and DBD-MySQL still persist - At least on FreeBSD. Is there any conclusion ? The perl bug report is marked as solved -
https://rt.perl.org/Public/Bug/Display.html?id=130801

[pali]

@dbielefeldt see my #78 (comment) fix was reverted due to issue #117

[tomsommer]

This is still a problem in 4.048, reverting to 4.037 works (4.040 does not)

UNSIGNED BIGINT(20) PRIMARY KEY is not returned "correctly" to Amavis, breaking sql_storage

[bigio]

On 6/13/19 8:34 AM, Tom Sommer wrote: It still does not work with 4.050 and latest Amavisd - foreign key constraint fails because of BIGINT type-error @bigio <https://github.com/bigio> How did you solve it? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#78?email_source=notifications&email_token=AATAVVTLVIFSPLKIGXOOI23P2HS75A5CNFSM4C4MVF62YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXSVKWI#issuecomment-501568857>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AATAVVXLJOHWLRNX6CRE2XDP2HS75ANCNFSM4C4MVF6Q>.

Atm I am using 4.050 + the following patch: https://cvsweb.openbsd.org/cgi-bin/cvsweb/ports/databases/p5-DBD-mysql/patches/patch-dbdimp_c?rev=1.4&content-type=text/x-cvsweb-markup

[tomsommer]

@bigio Do you have an idea of what an amavis-side patch/fix would look like?

[bigio]

On 6/13/19 10:37 AM, Tom Sommer wrote: @bigio <https://github.com/bigio> Do you have an idea of what an amavis-side patch would look like? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#78?email_source=notifications&email_token=AATAVVV772AORZKIG7QUU3DP2IBLZA5CNFSM4C4MVF62YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXS6XTA#issuecomment-501607372>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AATAVVVV2FK4UTUPT3CLHJDP2IBLZANCNFSM4C4MVF6Q>.

I do not think that anything could be done on amavisd side, amavisd is just able to trigger a bug in DBD::Mysql