1 Topic by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Topic: Statistics Of Latest 24 Hours keeps showing 0

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

When I installed the server (0.9.7 MYSQL edition) the statistics were showing fine, until one day they appeared in 0

I tried looking at logs but coudn't find the culprit.

I just upgraded to 0.9.8 ( I thought maybe with the upgrade it might get fixed but no)

From what I could find there are a couple of settings that I need to have:
in /opt/www/iredadmin/settings.py 
I have:
amavisd_enable_logging = True

in /etc/amavis/conf.d/50-user
the @storage_sql_dsn is setup with the same info as in /opt/www/iredadmin/settings.py

I tried logging to mysql with the amavisd username and pwd and there is no error.
I had a peek into the tables, but I don't know in which one these values are stored. (I was trying to figure out if the problem is with the insertion of values or the displaying of them)

Also I haven't found a error. I don't know if there is a log I'm missing.

Any suggestions?

Post's attachments

CropperCapture[181].jpg
CropperCapture[181].jpg 8.93 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

----

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: 30,790

Re: Statistics Of Latest 24 Hours keeps showing 0

Do you have correct SQL username/password in Amavisd config file? parameters:

@storage_sql_dsn

3 Reply by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Re: Statistics Of Latest 24 Hours keeps showing 0

Yes I connected with the username/password in there and the connection was succssesfull.

I don't know what to look for inside the tables though.

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,790

Re: Statistics Of Latest 24 Hours keeps showing 0

1) Do you see many SQL records in sql tables: msgs, msgrcpt, maddr? Any messages created in latest 24 hours?
2) Try to do this with SQL commands if you already checked item #1:

USE amavisd;
TRUNCATE msgs;
TRUNCATE msgrcpt;
TRUNCATE maddr;
TRUNCATE quarantine;

Wait for new emails, then check iRedAdmin-Pro Dashboard again.

5 Reply by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Re: Statistics Of Latest 24 Hours keeps showing 0

I logged in using the amavisd user.

There is nothing stored in the db:

mysql> select * from msgs;
Empty set (0.01 sec)

mysql> select * from msgrcpt;
Empty set (0.00 sec)

mysql> select * from maddr;
Empty set (0.00 sec)

mysql> select * from quarantine;
Empty set (0.00 sec)

And the truncate commands give this:
mysql> TRUNCATE msgs;
ERROR 1142 (42000): DROP command denied to user 'amavisd'@'localhost' for table 'msgs'
mysql> TRUNCATE msgrcpt;
ERROR 1142 (42000): DROP command denied to user 'amavisd'@'localhost' for table 'msgrcpt'
mysql> TRUNCATE maddr;
ERROR 1142 (42000): DROP command denied to user 'amavisd'@'localhost' for table 'maddr'
mysql> TRUNCATE quarantine;
ERROR 1142 (42000): DROP command denied to user 'amavisd'@'localhost' for table 'quarantine'

I changed the user for mysql and the truncate command runs as expected.

mysql> TRUNCATE msgs;
Query OK, 0 rows affected (0.03 sec)

mysql> TRUNCATE msgrcpt;
Query OK, 0 rows affected (0.02 sec)

mysql> TRUNCATE maddr;
Query OK, 0 rows affected (0.01 sec)

mysql> TRUNCATE quarantine;
Query OK, 0 rows affected (0.01 sec)

Then I waited but nothing changed, the tables are still empty.

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,790

Re: Statistics Of Latest 24 Hours keeps showing 0

jsmontoya wrote:

There is nothing stored in the db:

This is the problem.
Do you have setting "@storage_sql_dsn =" configured in Amavisd (/etc/amavis/conf.d/*)?

7 Reply by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Re: Statistics Of Latest 24 Hours keeps showing 0

I have the following in this file: /etc/amavis/conf.d/50-user

@storage_sql_dsn = (['DBI:mysql:database=amavisd;host=127.0.0.1;port=3306', 'amavisd', '*****************']);

the password is correct.

Should I check the other files under /etc/amavis/conf.d/*  ?

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,790

Re: Statistics Of Latest 24 Hours keeps showing 0

If @storage_sql_dsn is enabled with correct sql user/password, you should see new records in sql table "amavisd.msgs" when new email in or out. Did you see it?

9 Reply by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Re: Statistics Of Latest 24 Hours keeps showing 0

ZhangHuangbin wrote:

If @storage_sql_dsn is enabled with correct sql user/password, you should see new records in sql table "amavisd.msgs" when new email in or out. Did you see it?

No, the table is still empty and there are plenty of emails being sent/received:

mysql> select * from msgs;
Empty set (0.00 sec)

10 Reply by jsmontoya

  • jsmontoya
  • Member
  • Offline
  • Registered: 2018-07-17
  • Posts: 12

Re: Statistics Of Latest 24 Hours keeps showing 0

I found the problem!

The server had these lines commented:

/etc/postfix/master.cf
-o content_filter=smtp-amavis:[127.0.0.1]:10026

/etc/postfix/main.cf
content_filter = smtp-amavis:[127.0.0.1]:10024

I removed the comment and restarted postfix.

It is working now. I guess the emails were not reaching the amavis service and that's the reason they were not being processed.

thanks for all the help!