SpamAssassin – « TxRep » en remplaçant de « AWL »

Créer la base:

$ mysql -u root -p
mysql> create database sa_txrep;

Préparer mysql/mariadb a se manger un gros index:

mysql> SET GLOBAL innodb_file_format=Barracuda;
mysql> SET GLOBAL innodb_file_per_table=ON;
mysql> SET GLOBAL innodb_large_prefix=1;

Sinon, il pourrait y avoir une erreur comme ça au moment de « CREATE TABLE » :

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Retour sous shell est création de la table txrep :

$ dpkg -S txrep_mysql.sql
$ cat /usr/share/doc/spamassassin/sql/txrep_mysql.sql
CREATE TABLE txrep (username varchar(100) NOT NULL default '', \
      email varchar(255) NOT NULL default '', \
      ip varchar(40) NOT NULL default '', \
      count int(11) NOT NULL default '0', \
      totscore float NOT NULL default '0', \
      signedby varchar(255) NOT NULL default '', \
      last_hit timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \
      PRIMARY KEY (username,email,signedby,ip), \
      KEY last_hit (last_hit)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

ATTENTION: ajouter ROW_FORMAT=DYNAMIC après ENGINE=InnoDB .

Source: https://stackoverflow.com/questions/43379717/how-to-enable-large-index-in-mariadb-10

$ mysql -u root -p sa_txrep < txrep_mysql.sql

On donne les droits sur cette table:

mysql> GRANT SELECT, UPDATE, DELETE, INSERT ON sa_txrep.txrep TO 'sa_user'@'mx0.local.eez.fr';
mysql> GRANT SELECT, UPDATE, DELETE, INSERT ON sa_txrep.txrep TO 'sa_user'@'mx1.local.eez.fr';

Dans spamassassin …

# txrep
txrep_factory Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn DBI:mysql:database=sa_txrep;host=mail.local.eez.fr;
user_awl_sql_username sa_user
user_awl_sql_password user_awl_sql_table txrep
use_txrep 1

Sources:

https://svn.apache.org/repos/asf/spamassassin/trunk/sql/README.txrep
https://reload.eez.fr/blog:2013:09:05:spamassassin_awl_avec_mysql
https://reload.eez.fr/blog:2013:08:13:spamassassin_bayes_avec_mysql

SpamAssassin – « TxRep » en remplaçant de « AWL »

thierry