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]
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
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]
