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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
