NDOUtils: duplicate rows in several tables

[Michael Friedrich]

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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20091113/91959339/attachment.html>
-------------- next part --------------
------------------------------------------------------------------------------
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
-------------- next part --------------
_______________________________________________
Nagios-users mailing list
Nagios-users at 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