Statistics problem: Database usage and Mailbox used space show wrong info

Discussion in 'General' started by linus, Mar 17, 2020.

  1. linus

    linus Member

    [EDIT2] The problem still persists.
    [EDIT] This was solved by updating from source reconfiguring everything including services and cron.


    After (I think) the update to 3.1.15p3 , the database usage and mail stats have stopped working both on the splash page and on the respective stats pages in the ispconfig gui. Mailbox Quota all show Used Space: 96 KB. I've tested to add accounts, change quota, etc.
    Website Harddisk Quota looks ok. Database Quota all show Used Space 0 KB except for one 3.8 MB.

    [INFO] OS version is Debian GNU/Linux 8.11 (jessie)
    [INFO] ISPConfig is installed.
    ##### ISPCONFIG #####
    ISPConfig version is 3.1.15p3
    ##### VERSION CHECK #####
    [INFO] php (cli) version is 5.6.40-0+deb8u9
    [INFO] php-cgi (used for cgi php in default vhost!) is version 5.6.40-0+deb8u9
    ##### PORT CHECK #####
    ##### MAIL SERVER CHECK #####
    ##### RUNNING SERVER PROCESSES #####
    [INFO] I found the following web server(s):
    Apache 2 (PID 2458)
    [INFO] I found the following mail server(s):
    Unknown process (smtpd) (PID 2538)
    [INFO] I found the following pop3 server(s):
    Dovecot (PID 461)
    [INFO] I found the following imap server(s):
    Unknown process (init) (PID 1)
    Dovecot (PID 461)
    [INFO] I found the following ftp server(s):
    PureFTP (PID 584)


    # crontab -l
    * * * * * /usr/local/ispconfig/server/server.sh 2>&1 | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.log; done
    * * * * * /usr/local/ispconfig/server/cron.sh 2>&1 | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.log; done

    I've repaired the mysql tables. REPAIR TABLE `aps_instances`, `aps_instances_settings`, `aps_packages`, `aps_sett ....

    I've run (and added mail_plugins=quota to the /etc/dovecot/dovecot.conf):
    doveadm quota recalc -A
    Yesterday it seemed it fixed the issue but today Mail Usage showed again 96KB.

    email_quota for server_id=1:
    a:127:{s:17:"[email protected]";a:1:{s:4:"used";s:9:"256185872";}s:19:"[email protected]";a:1:{s:4:"used";s:10:"1167788669";}s:20:"[email protected]";a:1:{s:4:"used";s:9:"195357160";}s:19:"[email protected]";a:1:{s:4:"used";s:9:"293937343";}s:19:"[email protected]";a:1:{s:4:"used";s:7:"2070235";}s:26:"[email protected]";a:1:{s:4:"used";s:8:"10150919";}s:18:"[email protected]";a:1:{s:4:"used";s:7:"4841492";}s:12:"[email protected]";a:1:{s:4:"used";s:6:"270324";}s:28:"[email protected]";a:1:{s:4:"used";s:8:"58232286";}s:19:"[email protected]";a:1:.....

    There are some email_quotas for other server_ids:
    ....
    4 email_quota 1486320302 a:0:{}
    5 email_quota 1486487702 a:0:{}
    6 email_quota 1489240802 a:0:{}
    7 email_quota 1489240803 a:0:{}

    /usr/local/ispconfig/server/lib/config.inc.php
    $conf['server_id'] = '1';
    /usr/local/ispconfig/interface/lib/config.inc.php
    $conf['server_id'] = '1';

    There's a slave that has mirror: DNS.
    I've resynced email also and reinstalled Ispconfig by downloading and letting it reconfigure.

    Enabling debug /usr/local/ispconfig/server/server.sh
    17.03.2020-11:48 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    17.03.2020-11:48 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock

    I also removed old entries from the courier time (not modified since 2017)
    find /var/vmail -name ispconfig_mailsize | xargs rm
     
    Last edited: Mar 18, 2020
  2. Jesse Norell

    Jesse Norell Well-Known Member

    By that I infer you may have had courier installed in the past, and converted to dovecot? You might verify your settings under System > Server Config > {server} > Mail, in particular 'Maildir Format' and 'POP3/IMAP Daemon' and ensure 'Mailbox quota statistics' is enabled.

    IIRC, /var/log/ispconfig/cron.log should have some output about the quota checks at least when debugging is enabled. (Or there's a small chance I had added my own debugging messages when working on a bugfix, which are not upstream.)
     
  3. linus

    linus Member

    Thank you Jesse, I have now verified the Maildir is of Maildir format and pop/imap is Dovecot. Also quota stats are enabled with a checkbox. So the settings were ok.

    Yesterday night it worked one time and this morning once, but 50 times it didn't work today.

    In the cron.log I don't see any email logs only entries like these:

    Tue Mar 17 17:40:01 EET 2020 17.03.2020-17:40 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    Tue Mar 17 17:40:01 EET 2020 17.03.2020-17:40 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    Tue Mar 17 17:40:01 EET 2020 finished.
    Tue Mar 17 17:40:01 EET 2020 database casass size does not exceed quota: unlimited (quota) > 15269888 (used)
    Tue Mar 17 17:40:01 EET 2020 database casassa size does not exceed quota: unlimited (quota) > 2055 (used)
    Tue Mar 17 17:40:01 EET 2020 database casaaa size does not exceed quota: unlimited (quota) > 21107802 (used)

    Any clues?
     
  4. Jesse Norell

    Jesse Norell Well-Known Member

    That's the quota message I was vaguely remembering, so not email quota, not helpful here. Though according to /usr/local/ispconfig/server/lib/classes/cron.d/100-monitor_email_quota.inc.php there should be a DEBUG level log for 'Mail storage $email: $value' logged for each account. Maybe make sure you have DEBUG level logging enabled.

    This is a single server setup? (ie. mail quotas are on same server as the control panel)

    Seems like that could be somewhat telling of where the problem lies. Does `doveadm quota get -A` look right? dovecot.conf should have already had quota enabled, what all is in your /etc/dovecot/dovecot.conf?

    Looking at 100-monitor_email_quota.inc.php a bit .. what is in .quotausage for one of these inaccurate accounts (ie. /var/vmail/domain.tld/user/.quotausage file)?
    You could edit your 100-monitor_email_quota.inc.php and add some more debugging info to print what's found/going on as it runs.
     
  5. linus

    linus Member

    Thank you Jesse for helping out. Yes it's a single server (except for the DNS only being mirrored).
    Dovecot's quota stats seem to work very well (doveadm quota get -A)

    Username Quota name Type Value Limit %
    [email protected] user STORAGE 19276 - 0
    [email protected] user MESSAGE 628 - 0
    [email protected] user STORAGE 1821026 - 0
    [email protected] user MESSAGE 19211 - 0

    # doveadm quota get -u [email protected]
    Quota name Type Value Limit %
    user STORAGE 5543 - 0
    user MESSAGE 55 -

    I think the values are stored correctly (from the ispconfigdb) but they are not shown for some reason.
    email_quota for server_id=1:
    a:127:{s:17:"[email protected]";a:1:{s:4:"used";s:9:"256185872";}s:19:"[email protected]";a:1:{s:4:"used";s:10:"1167788669";}s:20:"[email protected]";a:1:{s:4:"used";s:9:"195357160";}s:19:"[email protected]";a:1:{s:4:"used";s:9:"293937343";}s:19:"[email protected]";a:1:{s:4:"used";s:7:"2070235";}s:26:"[email protected]";a:1:

    When I run this manually for some reason the email quotas appear correct in the gui now:
    # php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_email_quota.inc.php
    finished.
    But it stopped working after some time (more than several minutes, also the cron.sh and server.sh was run without breaking it). And I can fix it by running the above command and then again it shows the mail usage correctly. But then it's replaced.

    The databases only show empty sizes:
    casdsd 0 KB unlimited 0 KB unlimited
    csdsad 0 KB unlimited 0 KB unlimited
    cdssda 3.8 MB unlimited 3.8 MB unlimited
    cadssd101_db1 0 KB unlimited

    So I thought maybe a similar run like above would help:
    php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_database_size.inc.php

    PHP Warning: require_once(lib/mysql_clientdb.conf): failed to open stream: No such file or directory in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 667
    PHP Fatal error: require_once(): Failed opening required 'lib/mysql_clientdb.conf' (include_path='.:/usr/share/php:/usr/share/pear') in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 667

    But it didn't run correctly. There's one conf file in: /usr/local/ispconfig/server/lib/mysql_clientdb.conf

    [EDIT2]
    After a while the problem still appears :(

    [EDIT]
    I run the :/tmp/ispconfig3_install/install# php update.php once again and reconfigured everything, including the cron (I think I had skipped that) and now also databases are working. I hope it holds. Thank you for your help!
     
    Last edited: Mar 18, 2020
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    If not, empty (truncate) the sys_cron database table in the ISPConfig database. This will reset the whole internal ispconfig cron system.
     
  7. linus

    linus Member

    Thank you, till!
    I emptied the sys_cron 40 minutes ago.
    Now it shows:
    Edit Edit Copy Copy Delete Delete cronjob_mailbox_stats NULL 2020-03-19 00:00:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_clamav_log 2020-03-18 10:35:01 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_cpu 2020-03-18 10:35:01 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_database_size 2020-03-18 10:35:02 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_disk_usage 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_email_quota 2020-03-18 10:30:06 2020-03-18 10:45:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_fail2ban 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_hd_quota 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_iptables 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_ispconfig_log 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_ispconfig_version 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_kernel_version 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_mail_log 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_mail_queue 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_mem_usage 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_openvz 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_os_version 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_raid 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_rkhunter 2020-03-18 09:59:06 2020-03-19 00:00:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_server 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_services 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_syslog 2020-03-18 10:35:06 2020-03-18 10:40:00 0
    Edit Edit Copy Copy Delete Delete cronjob_monitor_system_update 2020-03-18 10:02:57 2020-03-18 11:00:00 0
    Edit Edit Copy Copy Delete Delete cronjob_awstats NULL 2020-03-19 00:00:00 0
    Edit Edit Copy Copy Delete Delete cronjob_webalizer NULL 2020-03-19 00:00:00

    Still no improvements, until i run: php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_email_quota.inc.php

    How do I manually run the database quotas?
    php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_database_size.inc.php
     
    Last edited: Mar 18, 2020
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    No, the table will fill itself automatically again. This may take some time, depending on when the cronjobs are scheduled.
     
  9. linus

    linus Member

    Ok, thank you. Now all the mail usages have returned to 96KB and db to 0KB. But I'll check again tomorrow.
     
  10. Jesse Norell

    Jesse Norell Well-Known Member

    Try 'cd /usr/local/ispconfig/server' first.
     
  11. linus

    linus Member

    Thanks Jesse! That works and the db's are showing correctly too, unfortunately so far everything is resets to same wrong info after a while, but I'll come back tomorrow if clearing the cron table would help.
     
  12. linus

    linus Member

    Unfortunately waiting didn't help, both the mail and db usage stats resets to 96 KB respectively 0 KB.
     
  13. linus

    linus Member

    I made a script run from cron that uses Jesse's help.
    #!/bin/bash
    cd /usr/local/ispconfig/server
    php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_database_size.inc.php
    php /usr/local/ispconfig/server/cron_debug.php --cronjob=100-monitor_email_quota.inc.php

    This fixed the problem for now, it seems.
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Really strange. May you please post the result of the command, run as root:

    crontab -l
     
  15. Jesse Norell

    Jesse Norell Well-Known Member

    In light of that, the statement in your original post here seems odd:
    What is the history of this install? How is the dns "mirror" setup? Did you by chance setup (or attempt to setup) a mirrored server, where maybe the other server could be misconfigured with server_id=1 and it is updating the quota values? Or any database-level replication?
     
  16. linus

    linus Member

    crontab -e

    * * * * * /usr/local/ispconfig/server/server.sh 2>&1 | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.log; done
    * * * * * /usr/local/ispconfig/server/cron.sh 2>&1 | while read line; do echo `/bin/date` "$line" >>
    * * * * * /root/scripts/fix-ispconfig-stats.sh 2>&1 | while read line; do echo /bin/date "$line" >> /var/log/ispconfig/cron-test.log; done

    I checked the cron logs and they have recent content.

    # /usr/local/ispconfig/server/cron.sh
    # /usr/local/ispconfig/server/server.sh

    22.03.2020-10:32 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    22.03.2020-10:32 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.


    The DNS mirror has, no other services should be active:
    /usr/local/ispconfig/server/lib/config.inc.php
    $conf['server_id'] = '3';
    /usr/local/ispconfig/interface/lib/config.inc.php
    $conf['server_id'] = '3';

    The content has been migrated to the server a couple years ago with regular updates to ispconfig.
     

Share This Page