Bug 7235 - Plugin::TxRep: txrep.count field reaching 32-bit maxint
Summary: Plugin::TxRep: txrep.count field reaching 32-bit maxint
Status: NEW
Alias: None
Product: Spamassassin
Classification: Unclassified
Component: Plugins (show other bugs)
Version: 3.4.1
Hardware: All All
: P2 minor
Target Milestone: Undefined
Assignee: SpamAssassin Developer Mailing List
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-08-10 19:15 UTC by Mark Martinec
Modified: 2018-10-29 12:56 UTC (History)
4 users (show)


Attachment Type Modified Status Actions Submitter/CLA Status
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Mark Martinec 2015-08-10 19:15:38 UTC 
Found the following in our logs after using the TxRep plugin
for a couple of months:

SA info: auto-whitelist: sql-based add_score/update 4294967295|-1.709|vscan|no-reply@dropbox.com|amazonses.com|dropbox.com|none: SQL error: Out of range value for column 'count' at row 1
SA info: auto-whitelist: sql-based add_score/update 4294967295|-1.709|vscan|amazonses.com dropbox.com|amazonses.com|dropbox.com|none: SQL error: Out of range value for column 'count' at row 1
SA info: auto-whitelist: sql-based add_score/update 4294967295|2.265|vscan|messages-noreply@fitbit.com|email.fitbit.com|fitbit.com|none: SQL error: Out of range value for column 'count' at row 1
SA info: auto-whitelist: sql-based add_score/update 4294967295|2.265|vscan|email.fitbit.com fitbit.com|email.fitbit.com|fitbit.com|none: SQL error: Out of range value for column 'count' at row 1
SA info: auto-whitelist: sql-based add_score/update 4294967295|2.786|vscan|messages-noreply@fitbit.com|email.fitbit.com|fitbit.com|none: SQL error: Out of range value for column 'count' at row 1
SA info: auto-whitelist: sql-based add_score/update 4294967295|2.786|vscan|email.fitbit.com fitbit.com|email.fitbit.com|fitbit.com|none: SQL error: Out of range value for column 'count' at row 1

Seems the txrep.count in MySQL table is limited to a 32-bit integer.

Can't imagine what chain of events lead to this large value in a
couple of months, we are not receiving hundreds of messages per second.

The info() is reported by SQLBasedAddrList.pm,
called from TxRep.pm line 1569:

sub add_score
[...]
  } else {
    $self->{checker}->add_score($self->{entry}, $score);
  }



mysql> select count(*) from txrep where count=2147483647;
+----------+
| count(*) |
+----------+
|      330 |
+----------+


mysql> select count(*) from txrep where count>2147483647;
+----------+
| count(*) |
+----------+
|        0 |
+----------+


mysql> select count(*) from txrep;
+----------+
| count(*) |
+----------+
|  1374582 |
+----------+

mysql> select email,ip,count,totscore,signedby from txrep order by count desc limit 10;
+---------------------------------------+------+------------+----------+--------------------+
| email                                 | ip   | count      | totscore | signedby           |
+---------------------------------------+------+------------+----------+--------------------+
| 500px.com mandrillapp.com             | none | 2147483647 |     88.6 | 500px.com          |
| amazon-offers@amazon.co.uk            | none | 2147483647 |    23.79 | amazonses.com      |
| 30.memberemail.com novusbio-email.com | none | 2147483647 |  -10.291 | 30.memberemail.com |
| amazonses.com 1nadan.si               | none | 2147483647 |  331.591 | amazonses.com      |
| amazonses.com 1nadan.si               | none | 2147483647 |  331.591 | 1nadan.si          |
| 30.memberemail.com novusbio-email.com | none | 2147483647 |  -10.291 | novusbio-email.com |
| 500px.com mandrillapp.com             | none | 2147483647 |     88.6 | mandrillapp.com    |
| amazon-offers@amazon.co.uk            | none | 2147483647 |    23.79 | amazon.co.uk       |
| amavis.org gmail.com                  | none | 2147483647 |  -171.07 | gmail.com          |
| amavis.org gmail.com                  | none | 2147483647 |  -171.07 | amavis.org         |
+---------------------------------------+------+------------+----------+--------------------+


After some 350 records the sorted txrep.count slowly starts to fall
to lower (but still large) values.
Comment 1 Kevin A. McGrail 2015-08-11 14:01:00 UTC 
Yep, gotta be a bug.  I see it as well.
Comment 2 Joe Quinn 2015-08-18 13:39:31 UTC 
This is caused by the following lines in lib/Mail/SpamAssassin/SQLBasedAddrList.pm

    while ( defined($aryref = $sth->fetchrow_arrayref()) ) {
      if (defined $entry->{count} && defined $aryref->[1]) {
        $entry->{count} += $aryref->[0];
        $entry->{totscore} += $aryref->[1];
      }

When a message is signed by multiple domains, $entry->{count} is the total of all rows for those different signers. Then when they get written back to the table, each row's new count value is $entry->{count} + 1. With two signers, this changes the behavior from (n + 1) to (2n + 1), meaning you get to that max value after just 31 iterations.

For a reproduction, test any message that has multiple valid DKIM signatures from different domains.

Not sure how to fix it yet, since it's pretty deep into SQLBasedAddrList and that count value is used in many places throughout txrep.
Comment 3 RW 2015-08-18 14:57:47 UTC 
Presumably $entry->{count} is needed for the calculation, but the individual counts should be separately incremented in the database. The currently behaviour is only right for a single row.
Comment 4 Joe Quinn 2015-08-27 14:23:03 UTC 
Due to the number of places that would have to be touched in order to properly handle the fact that multiple reputation rows match the message, as a workaround for now we'll just use the newest record's data.

If anyone wants to put the time into a better fix, feel free.

Committed revision 1698165.