1 Topic by cvcvelo

  • cvcvelo
  • Member
  • Offline
  • Registered: 2014-06-16
  • Posts: 151

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.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

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.

3 Reply by cvcvelo

  • cvcvelo
  • Member
  • Offline
  • Registered: 2014-06-16
  • Posts: 151

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 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Reverting to non-SQL Bayes data

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

5 Reply by cvcvelo

  • cvcvelo
  • Member
  • Offline
  • Registered: 2014-06-16
  • Posts: 151

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 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

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.

7 Reply by cvcvelo

  • cvcvelo
  • Member
  • Offline
  • Registered: 2014-06-16
  • Posts: 151

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.