<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> </head> <body bgcolor="#ffffff" text="#000000"> <tt>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:</tt> <pre><tt>+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+ | 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 | </tt></pre> <tt>as you can see</tt> <tt>instance_id=1 - both event_type=0 - both scheduled_time='2009-11-11 16:20:58' - both object_id=19918 - both</tt> <tt>So the second query should generate an update not an insert!</tt> <tt>This fail concerns the following tables:</tt> <tt>systemcommands timedeventqueue timedevents</tt> <tt>Difference between mysql and postgres/oracle:</tt> <tt>MySQL: INSERT INTO table () VALUES () ON DUPLICATE KEY UPDATE SET foo=bar;</tt> <tt>depends on the defined unique constraint within the table creation.</tt> <tt>Oracle: MERGE INTO table USING DUAL ON (unique constraint) WHEN MATCHED THEN UPDATE SET foo=bar WHEN NOT MATCHED INSERT () VALUES ();</tt> <tt>Postgres: UPDATE table SET foo=bar WHERE (unique constraint); if nothing affected INSERT INTO table () VALUES ();</tt> <tt>Both Oracle and Postgres have defined unique constraints within the queries already. The table created unique constraints are just a doubled check.</tt> <tt>They have been deeply debugged by myself, since I have implemented their support (currently only within Icinga IDOUtils). </tt> <tt>But MySQL is missing some constraints and cannot recheck that within the query.</tt> <hr> <tt>So my fix attempted to recreate those unique keys within the table creation.</tt> <tt>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) </tt> <pre><tt>mysql> select * from icinga_timedevents where object_id=20260; </tt> </pre> <pre><tt>+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+ | 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 | </tt></pre> <pre><tt>mysql> select * from icinga_systemcommands where start_time='2009-11-11 18:25:46' and start_time_usec=178164;</tt></pre> <pre><tt>+------------------+-------------+---------------------+-----------------+---------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+-------------+--------+-------------+ | 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 | | | </tt></pre> <tt> People are wondering why timedevents are that many rows and exploding the db... well fixing that you'll get probably the half of them! </tt> <tt> Before sending a patch feedback on the servicechecks patch would be much appreciated - useful or duplicate rows are intended? </tt> <tt>This was btw introduced within the commit "Long time catchup on some minor patches" in January 2009 - for whatever reason. </tt> <tt>Kind regards, Michael</tt> <tt> PS: When will Nagios/NDOUtils move to GIT? Would be much easier to send those patches.</tt> </body> </html>