Ronan McGlue <[EMAIL PROTECTED]> wrote on 03/09/2006 05:05:15 AM:
> i have a little quirck with my current Mysql setup. I log all messages
> from my mtas into Mysql and then use a simple query to generate stats
> from them... as below:
>
> mysql> select exim.hour,sum(exim.count),stats.spam,stats.ham from
> servers,exim,stats where exim.date=stats.day and exim.date =curdate()
> and exim.hour=stats.hour and exim.machine=servers.id and
> servers.type='MX' group by exim.hour;
> +------+-----------------+------+------+
> | hour | sum(exim.count) | spam | ham |
> +------+-----------------+------+------+
> | 0 | 1636 | 826 | 775 |
> | 1 | 1243 | 708 | 515 |
> | 2 | 1565 | 1018 | 536 |
> | 3 | 2274 | 637 | 492 |
> | 4 | 1325 | 760 | 547 |
> | 5 | 1177 | 768 | 398 |
> | 6 | 1266 | 718 | 530 |
> | 7 | 1382 | 883 | 477 |
> | 8 | 2026 | 899 | 1020 |
> | 9 | 5856 | 838 | 1879 |
> | 10 | 80 | 29 | 50 |
> +------+-----------------+------+------+
> 11 rows in set (0.01 sec)
>
>
> however i have noticed that at hour 3 every day the DB duplicates the
> entries for the 3 MTAs... as below
>
>
> mysql> select servers.id,exim.hour,exim.count from servers,exim,stats
> where exim.hour=3 and exim.date=stats.day and exim.date =curdate() and
> exim.hour=stats.hour and exim.machine=servers.id and servers.type='MX';
> +----+------+-------+
> | id | hour | count |
> +----+------+-------+
> | 0 | 3 | 819 |
> | 0 | 3 | 819 |
> | 1 | 3 | 189 |
> | 1 | 3 | 189 |
> | 2 | 3 | 129 |
> | 2 | 3 | 129 |
> +----+------+-------+
> 6 rows in set (0.00 sec)
>
> hours 0-2 and 4-23 are fine and report everything as expected...
>
>
> Now the data must be getting in somehow I just dont know how or why?
> The inserts are done by an exim acl entry as follows..
> warn condition = ${lookup \
> mysql {insert into exim SET \
> machine='MACHINE', \
> date='${substr_0_10:$tod_log}',\
> hour='${substr_11_2:$tod_log}'\
> ON DUPLICATE KEY UPDATE count = count + 1\
> }{0}{0}}
>
>
> anythoughts on the matter?
>
> thanks
> Ronan
>
> --
> Ronan McGlue
> Analyst / Programmer
> CMC Systems Group
>
> Queens University Belfast
>
Do you have two entries in `stats` that correspond to hour 3?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
