Hi there,
we had an issue with servicechecks where duplicate rows where inserted
instead of updating one single row. During my analysis there was more of
that "feature"...
Looking at timedevents table ... there also is no unique constraint
since unique key != key
now it looks like this:
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| timedevent_id | instance_id | event_type | queued_time |
queued_time_usec | event_time | event_time_usec | scheduled_time
| recurring_event | object_id | deletion_time | deletion_time_usec |
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| 346416 | 1 | 0 | 2009-11-11 16:16:06 |
448613 | 0000-00-00 00:00:00 | 0 | 2009-11-11 16:20:58 |
0 | 19918 | 0000-00-00 00:00:00 | 0 |
| 346941 | 1 | 0 | 0000-00-00 00:00:00 |
0 | 2009-11-11 16:20:58 | 49905 | 2009-11-11 16:20:58 |
0 | 19918 | 0000-00-00 00:00:00 | 0 |
as you can see
instance_id=1 - both
event_type=0 - both
scheduled_time='2009-11-11 16:20:58' - both
object_id=19918 - both
So the second query should generate an update not an insert!
This fail concerns the following tables:
systemcommands
timedeventqueue
timedevents
Difference between mysql and postgres/oracle:
MySQL:
INSERT INTO table () VALUES () ON DUPLICATE KEY UPDATE SET foo=bar;
depends on the defined unique constraint within the table creation.
Oracle:
MERGE INTO table USING DUAL ON (unique constraint) WHEN MATCHED THEN
UPDATE SET foo=bar WHEN NOT MATCHED INSERT () VALUES ();
Postgres:
UPDATE table SET foo=bar WHERE (unique constraint);
if nothing affected
INSERT INTO table () VALUES ();
Both Oracle and Postgres have defined unique constraints within the
queries already. The table created unique constraints are just a doubled
check.
They have been deeply debugged by myself, since I have implemented their
support (currently only within Icinga IDOUtils).
But MySQL is missing some constraints and cannot recheck that within the
query.
------------------------------------------------------------------------
So my fix attempted to recreate those unique keys within the table creation.
Looks nice indeed, no more duplicates (tested on Icinga IDOUtils where
the exact same DB Scheme is applied and MySQL does the same on duplicate
key)
mysql> select * from icinga_timedevents where object_id=20260;
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| timedevent_id | instance_id | event_type | queued_time |
queued_time_usec | event_time | event_time_usec | scheduled_time
| recurring_event | object_id | deletion_time | deletion_time_usec |
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| 362362 | 1 | 0 | 2009-11-11 18:25:56 |
255593 | 2009-11-11 18:30:44 | 240715 | 2009-11-11 18:30:44 |
0 | 20260 | 0000-00-00 00:00:00 | 0 |
mysql> select * from icinga_systemcommands where start_time='2009-11-11
18:25:46' and start_time_usec=178164;
+------------------+-------------+---------------------+-----------------+---------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+-------------+--------+-------------+
| systemcommand_id | instance_id | start_time | start_time_usec |
end_time | end_time_usec | command_line
| timeout | early_timeout | execution_time |
return_code | output | long_output |
+------------------+-------------+---------------------+-----------------+---------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+-------------+--------+-------------+
| 126918 | 1 | 2009-11-11 18:25:46 | 178164 | 2009-11-11 18:25:46 | 207814 | /usr/bin/printf "%b" "1257960336\thostname\tUP\t1\tHARD\t0.030\tPING OK - Packet loss = 0%, RTA = 0.69 ms\t\n" >> /opt/icinga/var/host-perfdata.out | 5 | 0 | 0.029 | 0 | | |
People are wondering why timedevents are that many rows and exploding
the db... well fixing that you'll get probably the half of them!
Before sending a patch feedback on the servicechecks patch would be much
appreciated - useful or duplicate rows are intended?
This was btw introduced within the commit "Long time catchup on some
minor patches" in January 2009 - for whatever reason.
Kind regards,
Michael
PS: When will Nagios/NDOUtils move to GIT? Would be much easier to send
those patches.
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Nagios-users mailing list
Nagios-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting
any issue.
::: Messages without supporting info will risk being sent to /dev/null
