[opendmarc-users] Bug in opendmarc-expire script

[Todd Lyons]

Murray, I started running opendmarc about 2 days ago, so it should not
expire request data b/c there shouldn't have been anything that age.

CentOS58[root at lunar ~]# opendmarc-expire --verbose --dbhost=ivwm52
--dbname=dmarc --dbpasswd=DMARCopendmarc --dbuser=dmarc
opendmarc-expire: started at Thu Aug  9 21:40:10 2012
opendmarc-expire: connected to database
opendmarc-expire: expiring messages older than 180 day(s)
opendmarc-expire: no rows deleted
opendmarc-expire: expiring signatures on expired messages (id < 1)
opendmarc-expire: no rows deleted
opendmarc-expire: expiring request data older than 180 days
opendmarc-expire: 371 row(s) deleted
opendmarc-expire: terminating at Thu Aug  9 21:40:10 2012

I ran this expire script and it did the above, now my system has no
domains that it wants to send out information for.  All my data is in
a mysql database, so maybe this issue is specific to mysql.  Basically
I'm doing in crontab:
@hourly opendkim-import < $statsfile
@hourly opendkim-reports
# @daily opendkim-expire

But the expire action is deleting the data from the requests table, so
it has no reports to send.  I ran the query from the script and I
figured it out.  This is what it's doing:

mysql> SELECT * FROM requests WHERE lastsent <=
DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY);
+-----+--------+--------+-------+------+--------+---------+-----+---------------------+---------------------+
| id  | domain | repuri | adkim | aspf | policy | spolicy | pct |
firstseen           | lastsent            |
+-----+--------+--------+-------+------+--------+---------+-----+---------------------+---------------------+
| 372 |    489 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 373 |    490 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 374 |    233 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 375 |     12 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 376 |    201 |        |   114 |  114 |    114 |       0 | 100 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 377 |    133 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 378 |    492 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 379 |    485 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
| 380 |    493 |        |     0 |    0 |      0 |       0 |   0 |
2012-08-10 05:00:02 | 0000-00-00 00:00:00 |
+-----+--------+--------+-------+------+--------+---------+-----+---------------------+---------------------+
9 rows in set (0.00 sec)

Since lastsent starts at zero, it always matches it.  Changing the
query to exclude values of 0 fixes it:

mysql> SELECT * FROM requests WHERE lastsent <=
DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY) AND lastsent > 0;
Empty set (0.00 sec)

Second, looking at indexes, an EXPLAIN shows that it's doing a full table scan:

mysql> explain SELECT * FROM requests WHERE lastsent <=
DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY) AND lastsent > 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: requests
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
        Extra: Using where
1 row in set (0.00 sec)

There should be an index on the field that is being used in the WHERE:
ALTER TABLE `requests` ADD KEY `lastsent_idx` (`lastsent`);

Now it uses that index instead of a full table scan:
mysql> explain SELECT * FROM requests WHERE lastsent <=
DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY) AND lastsent > 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: requests
         type: range
possible_keys: lastsent_idx
          key: lastsent_idx
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

...Todd
-- 
The total budget at all receivers for solving senders' problems is $0.
 If you want them to accept your mail and manage it the way you want,
send it the way the spec says to. --John Levine