duplicate entry (same time every day)
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]
Re: duplicate entry (same time every day)
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
Re: duplicate entry (same time every day)
[EMAIL PROTECTED] wrote:
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?
well yes, thats the problem for hour=3 there are duplicate entries for
my 3 mail MX machines...
the inserts are done on the fly whenever a mail is accepted to our
domain as explained, by an exim acl which increments the hours count for
mail accepted from that specific machine.
I just dont understand why it is being duplicated at the hour of 3 o clock.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
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]
