[SOLVED] Daily mail report problem

[k7sle]

I seem to have a problem with my daily mail reports.

Each day, I get my report in the mail and, each day, it reports:

Generating report
	From 2008-02-06 00:00:00 to 2008-02-07 00:00:00

No messages found

I know this is not true because I used to get traffic stats. One day, after my 4.5.9 had been locked up and not sending/receiving email for 4 days, I restarted Zimbra and all was well again, except the report.

I've since upgraded to "Release 5.0.2_GA_1975.UBUNTU6 UBUNTU6 FOSS edition" on Ubuntu Server 6.06.1 LTS

Any suggestions?

[dwmtractor]

When something similar happened to me (also Ubuntu) a long time ago, the logger daemon in Linux itself had stopped. Check for a process for syslogd.

You might find a reboot of the whole machine would fix this (not because it's windows, but because other services may also be stopped).

[phoenix]

Try some of the debugging here.

[k7sle]

I rebooted the server a few times already with no change in status. I also reviewed the Wiki and then looked into the zmdailyreport script and found a "debug" option, so I ran it.

Here is what I found:

zimbra@giskard:~$ /opt/zimbra/libexec/zmdailyreport -d
DBD::mysql::st execute failed: Table './zimbra_logger/mta' is marked as crashed and last (automatic?) repair failed at /opt/zimbra/libexec/zmdailyreport line 515.
'select distinct(quote(msgid)) from mta  where arrive_time >= '2008-02-06 00:00:00' and arrive_time <= '2008-02-07 00:00:00''
/opt/zimbra/libexec/zmdailyreport sql execute failed: DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at /opt/zimbra/libexec/zmdailyreport line 520.
DBD::mysql::st execute failed: Table './zimbra_logger/mta' is marked as crashed and last (automatic?) repair failed at /opt/zimbra/libexec/zmdailyreport line 515.
'select s2, count(s2) as C from (select distinct(msgid), sender as s2 from mta where sender is not null and arrive_time >= '2008-02-06 00:00:00' and arrive_time <= '2008-02-07 00:00:00') as foo group by s2 order by C desc'
/opt/zimbra/libexec/zmdailyreport sql execute failed: DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at /opt/zimbra/libexec/zmdailyreport line 520.
DBD::mysql::st execute failed: Table './zimbra_logger/mta' is marked as crashed and last (automatic?) repair failed at /opt/zimbra/libexec/zmdailyreport line 515.
'select recipient, count(distinct(msgid)) as C from mta where recipient is not null and arrive_time >= '2008-02-06 00:00:00' and arrive_time <= '2008-02-07 00:00:00' group by recipient order by C desc'
/opt/zimbra/libexec/zmdailyreport sql execute failed: DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at /opt/zimbra/libexec/zmdailyreport line 520.
DBD::mysql::st execute failed: Table './zimbra_logger/mta' is marked as crashed and last (automatic?) repair failed at /opt/zimbra/libexec/zmdailyreport line 515.
'select distinct(msgid), arrive_time, status, statusmsg from mta where status != 'sent' and arrive_time >= '2008-02-06 00:00:00' and arrive_time <= '2008-02-07 00:00:00' order by msgid'
/opt/zimbra/libexec/zmdailyreport sql execute failed: DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at /opt/zimbra/libexec/zmdailyreport line 520.
Generating report
        From 2008-02-06 00:00:00 to 2008-02-07 00:00:00

No messages found

[phoenix]

Did you also check the Tables as detailed in that wiki article?

[k7sle]

Did you also check the Tables as detailed in that wiki article?

I missed that (as I was looking for the boldface text, sorry), but did the checks of the table and tried to repair it. Since I'm not (apparently) fully-here today, I dutifully copied and pasted the wiki lines and had no success. Then I realized I repaired the wrong table. Now it seems to be ok, though zmdailyreport doesn't report anything still. Are those stats lost?

mysql> REPAIR TABLE mta
    -> ;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| zimbra_logger.mta | repair | status   | OK       |
+-------------------+--------+----------+----------+
1 row in set (0.67 sec)

mysql> quit
Bye
zimbra@giskard:~/libexec$ ./zmdailyreport -d
Generating report
        From 2008-02-06 00:00:00 to 2008-02-07 00:00:00

No messages found

[dwmtractor]

Now it seems to be ok, though zmdailyreport doesn't report anything still. Are those stats lost?

Yes, they probably are. Since the logger was not successfully recording data, there are no data for zmdailyreport to report. . .but once the clock rolls past midnight you should be able to get stats tomorrow for whatever happens the rest of today.

Cheers,

Dan

[k7sle]

That is what I was expecting, but thought I would make sure that is the case. Thanks! I ran it a bit later and it was recording traffic nicely!