This is a multi-part message in MIME format.
--------------090908060700020107050600
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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;
+------------------+-------------+---------------------+-----------------+-----------------
...[email truncated]...
This post was automatically imported from historical nagios-devel mailing list archives
Original poster: [email protected]