====== Spamassassin ======

From v4 Debian's spamassasin is has now dedicated package to provide persistent service:
  * spamd
so ''/etc/default/spamassasin'' should be converted to ''/etc/default/spamd''

===== missing perl modules =====

Check if some modules are missing:
<code bash>
spamassassin -D --lint 2>&1 | grep -i failed
</code>

<code bash>
sudo apt-get install libencode-detect-perl libnet-libidn-perl \
    libemail-address-xs-perl libmail-dkim-perl libmail-spf-perl \
    libio-socket-ip-perl
</code>
    
<code bash>
sudo apt install libdigest-sha-perl libnet-libidn2-perl libmaxmind-db-reader-perl libmaxmind-db-reader-xs-perl
</code>

<code bash>
sudo cpan install IP::Country::DB_File Digest::SHA1 IP::Country::Fast
</code>

===== register at DNSWL =====

Register legitimate SMTP server at: [[https://www.dnswl.org/selfservice/|dnswl.org Self Service]]


====== Issues ======

**Problem:** 
<code>info: auto-whitelist: sql-based get_addr_entry xxxxxx: SQL error: Unknown column 'last_hit' in 'order clause'</code>

**Solution:**

Configure reputation database (read ''/usr/share/doc/spamassassin/sql/README.txrep'')
for mysql, execute ''/usr/share/doc/spamassassin/sql/txrep_mysql.sql''


**Problem:**
<code>
  pts rule name              description
 ---- ---------------------- --------------------------------------------------
  0.0 URIBL_BLOCKED          ADMINISTRATOR NOTICE: The query to URIBL was
                             blocked.  See
                             http://wiki.apache.org/spamassassin/DnsBlocklists#dnsbl-block
                              for more information.
                             [URIs: grinn-global.com]
</code>

There are lots of queries to google dns 8.8.8.8, but google responds with ''NXDomain'' or ''ServFail''.
The same single query works. Probably google blocks bursts of this kind of requests.

**Solution:**
<file | local.cf>
dns_server 127.0.0.1
</file>

====== bayes: tok_get_all: SQL error: Illegal mix of collations for operation ' IN ' ======

<code bash>
spamassassin -D --lint 2>&1 | grep bayes
</code>

<code>
Aug 11 18:10:51.719 [37716] dbg: bayes: tok_get_all: SQL error: Illegal mix of collations for operation 'in'
Aug 11 18:10:51.719 [37716] dbg: bayes: cannot use bayes on this message; none of the tokens were found in the database
Aug 11 18:10:51.719 [37716] dbg: bayes: not scoring message, returning undef
</code>

It was running on very old system, upgraded multiple times. Nowadays other encodings are added to MySQL database.

<code sql>
ALTER DATABASE sa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</code>
Not possible to convert bayes databases to new charset:
<code sql>
ALTER TABLE awl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</code>

Recreate tables with new character set (prebiously set for ''sa'' database):
  * backup bayes ''sa-learn --backup >backup.txt''
  * drop 5 ''bayes_'' tables: ''DROP TABLE `bayes_expire`, `bayes_global_vars`, `bayes_seen`, `bayes_token`, `bayes_vars`;''
  * create it again from scheme: ''/usr/share/doc/spamassassin/sql/bayes_mysql.sql''
  * ''sa-learn --restore backup.txt''

Another problem:
  * [[https://lists.apache.org/thread/396rsbzngm3ydc1ol5xw4mg99d5h1q64|BayesStore MariaDB on EL9]]
  * [[https://issues.redhat.com/browse/RHEL-43418]]
  * [[https://serverfault.com/questions/1029024/can-i-usefully-convert-spamassassin-bayes-tokens-from-utf8-char5-to-binary5]]

For me it was faster to train bayes again:
<code bash>
sa-learn --ham --progress ./hamdir
sa-learn --spam --progress ./spamdir
</code>