1 Topic by robynhub (edited by robynhub 2011-03-04 17:07:17)

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Topic: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

After upgrade to 1.4.1 from 1.4.0 i've lost all statistics in dashboard.
Downgrade fixed that. Something has changed in dashboard stats handling?

Thanks in advance.

----

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,082

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

robynhub wrote:

Downgrade fixed that. Something has changed in dashboard stats handling?

Did you check Amavisd related settings in v1.4.1 config file "settings.ini"?

3 Reply by robynhub

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

ZhangHuangbin wrote:
robynhub wrote:

Downgrade fixed that. Something has changed in dashboard stats handling?

Did you check Amavisd related settings in v1.4.1 config file "settings.ini"?

During the upgrade I've copyed entire settings.ini from 1.4.0 as the upgrade documentation said.
No changes were applied.

4 Reply by ZhangHuangbin

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

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

That could be bug in v1.4.1, i will check it later. sorry.

5 Reply by robynhub (edited by robynhub 2011-03-04 18:14:42)

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

ZhangHuangbin wrote:

That could be bug in v1.4.1, i will check it later. sorry.

Seems to be an error in the sql:

1.4.1:

     COUNT(*) as total,
                       -- msgs.mail_id,
                    -- msgs.subject,
                         -- msgs.time_iso,
                         -- recip.email as recipient,
                        sender.email as mail
                     FROM msgs RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                     RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                     RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                     WHERE
                       msgs.content IS NOT NULL
                       AND sender.domain IN (...snip....)
                        AND msgs.time_num < DATE_SUB(NOW(), INTERVAL 86400 SECOND)

                 GROUP BY mail
                    ORDER BY total DESC
                     LIMIT 10

Return 0 result

1.4.0

     COUNT(*) as total,
                       -- msgs.mail_id,
                    -- msgs.subject,
                         -- msgs.time_iso,
                         -- recip.email as recipient,
                        sender.email as mail
                     FROM msgs RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                     RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                     RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                     WHERE
                       msgs.content IS NOT NULL
                       AND sender.domain IN (...snip....)
                           AND UNIX_TIMESTAMP()-msgs.time_num < 86400
                 GROUP BY mail
                    ORDER BY total DESC
                     LIMIT 10

Return right things.
This becouse the table msg in amavisd database has time_num defined as INT(10) and not as TIMESTAMP
It's strange altought. With quarantine script it works like a sharm.
I'll try to debug it.

STFU

6 Reply by robynhub

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

Got IT!

SELECT
      COUNT(*) as total,
                       -- msgs.mail_id,
                    -- msgs.subject,
                         -- msgs.time_iso,
                         -- recip.email as recipient,
                        sender.email as mail
                     FROM msgs RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                     RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                     RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                     WHERE
                       msgs.content IS NOT NULL
                       AND sender.domain IN (...snip...)
                          AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL 86400 SECOND)
                 GROUP BY mail
                    ORDER BY total DESC
                     LIMIT 10

time_num need to be converted with FROM_UNIXTIME function.
My bad.

7 Reply by ZhangHuangbin

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

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

robynhub wrote:

AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL 86400 SECOND)

It works, thanks very much for sharing with us. smile

8 Reply by robynhub

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

ZhangHuangbin wrote:
robynhub wrote:

AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL 86400 SECOND)

It works, thanks very much for sharing with us. smile

NP. Just a note.

I've see that in the code there are some queryes with errors.
For example:

lib/amavisd/log.py
row 93

        if isinstance(timeLength, types.IntType):
            self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) < DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

Should be > and not < if you want to select the number of incoming mails of last 24 hour (for example).

This error is pretty often in the code.

9 Reply by veldsink (edited by veldsink 2011-03-04 19:14:58)

  • veldsink
  • Member
  • Offline
  • Registered: 2010-04-09
  • Posts: 50

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

Should this be changed in libs/amavisd/log.py?

I find this on the following lines:

93, 132, 176 and 325

and this should then be changed from:

self.sql_append_where += ' AND msgs.time_num < DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

to

self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

Am I right about this?

With kind regards,
Luc

10 Reply by robynhub

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

veldsink wrote:

Should this be changed in libs/amavisd/log.py?

I find this on the following lines:

93, 132, 176 and 325

and this should then be changed from:

self.sql_append_where += ' AND msgs.time_num < DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

to

self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

Am I right about this?

With kind regards,
Luc

Yes but it need to be compiled.

py_compilefiles log.py

I think should be right. Need a confirm by ZhangHuangbin i think.

11 Reply by ZhangHuangbin

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

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

It's correct. You can also remove "libs/amavisd/log.pyc" if it exists.

I will upload a patched version later, so that you can simply replace "libs/amavisd/log.py" with new version.

Seems there's another bug while viewing quarantined mail logs which reported by veldsink here:
http://www.iredmail.org/forum/topic1721 … right.html

So it will be a little delay for this patched version. Sorry for this trouble.

12 Reply by robynhub

  • robynhub
  • Member
  • Offline
  • Registered: 2010-12-23
  • Posts: 31

Re: [BUG] iRedMail-Pro 1.4.1 Dashboard Stats

Need to be changed also in quarantine.py row 157.

 where="""content IN ('S', 's', 'V', 'Y') \
                            AND quar_type IN ('Q', 'F') \
                            AND FROM_UNIXTIME(msgs.time_num) < DATE_SUB(NOW(), INTERVAL %d DAY)