1

Topic: Reverting to non-SQL Bayes data

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: FreeBSD 11.2-RELEASE-p1
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? Yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

The other day our iRedMail server crashed, leading to corruption of the sa_bayes database (we have been storing Bayes data using MySQL). I am still investigating the root cause, and I was able to recreate the database, but a friend who runs a much larger email server said his testing showed that SQL performance for Bayes data is actually worse than plain text.

If I want to revert to storing Bayes data without SQL, is it really as simple as commenting out these lines in /usr/local/etc/mail/spamassassin/local.cf and restarting amavisd?

bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn      DBI:mysql:sa_bayes:127.0.0.1:3306

bayes_sql_username sa_user
bayes_sql_password wouldnt-you-like-to-know

bayes_sql_override_username vscan

I set up a test machine with a vanilla installation of iRedMail 0.9.8 (no iRedAdmin-Pro). It appears that these are the only deltas from the default install, but just checking. Is it really this simple to go back to no SQL for Bayes data?

Thanks.

2

Re: Reverting to non-SQL Bayes data

Yes, it's that simple. But you will lose data stored in SQL db unless you export it first, then import it after switch.

----

Does my reply help a little? How about buying me a cup of coffee ($5) as an encouragement?

buy me a cup of coffee

3

Re: Reverting to non-SQL Bayes data

Thanks ZHB!

Dumb question, but where do I import the data from the database, and what is the procedure for doing that?

Thanks again.

4

Re: Reverting to non-SQL Bayes data

FYI: https://docs.iredmail.org/backup.restore.html

----

Does my reply help a little? How about buying me a cup of coffee ($5) as an encouragement?

buy me a cup of coffee

5

Re: Reverting to non-SQL Bayes data

OK, thanks. I read that document which is about backing up and restoring to SQL among other things.

In this case, I am switching away from storing Bayes data in SQL and going back to whatever iRedMail does by default with Bayes data.

Other than disabling the SQL storage, as described in the original post above, is there anything else needed to keep using Bayes data? Your first response above said something about losing anything in the Bayes database, and the docs do not cover the case of reverting to non-SQL storage of Bayes data.

Thanks!

6

Re: Reverting to non-SQL Bayes data

cvcvelo wrote:

Other than disabling the SQL storage, as described in the original post above, is there anything else needed to keep using Bayes data? Your first response above said something about losing anything in the Bayes database, and the docs do not cover the case of reverting to non-SQL storage of Bayes data.

My mistake, sorry.

You can find 2 arguments of 'sa-learn' in SpamAssassin document:
https://spamassassin.apache.org/full/3. … -learn.txt

sa-learn --backup > backup.txt
sa-learn --restore backup.txt

I suggest testing the restore on a testing machine first.

Note: while backing up, you may need the '-u <user>' option to backup/dump from SQL db.

----

Does my reply help a little? How about buying me a cup of coffee ($5) as an encouragement?

buy me a cup of coffee

7

Re: Reverting to non-SQL Bayes data

Thanks, ZHB.

For future reference: Those sa-learn commands seem to work just fine, both on a test machine and a production server.