Hi,
Øyvind Nordang wrote:
>
> When I check the servicechecks table each check of a service is listed
> twice, but not with the same information in both rows.
>
I've noticed that myself and that seems to be a bug. Maybe it concerns
the unique constraint where the UPDATE should happen. But it could also
be that some data is sent incorrectly and therefore no matching can be done.
> In dbhandlers.c on line 1518 all I can think of is that ON DUPLICATE
> KEY UPDATE does not work as it should. I haven't had time to look at
> the code and programming isn't my strong side.
>
> if(asprintf(&buf,"INSERT INTO %s SET %s, command_object_id='%lu',
> command_args='%s', command_line='%s' ON DUPLICATE KEY UPDATE %s"
> ,ndo2db_db_tablenames[NDO2DB_DBTABLE_SERVICECHECKS]
> ,buf1
> ,command_id
> ,es[0]
> ,es[1]
> ,buf1
> )==-1)
> buf=NULL;
>
Also the creation of "buf1" is interesting. The approach of building the
query buffer for both INSERT and UPDATE might lead into a real error
too. The only difference is that the INSERT statement gets
command_object_id, command_args and command_line
The defined keys in db/mysql.sql
KEY `instance_id` (`instance_id`),
KEY `service_object_id` (`service_object_id`),
KEY `start_time` (`start_time`)
Kind of strange, no unique definition ... but let's step further into
analysis.
Problem regarding that routine is, that the constraint never matches.
I'm trying to resolve this error from Icinga IDOUtils where the error
exists too. The main differences are that the queries are normalized
(INSERT INTO .. () VALUES() instead of INSERT INTO .. SET ..=..) but the
constraints are the same as in NDOUtils. Also the debugoutput is quite
enhanced to see what's going on.
I've run several tests with Postgres and Oracle, and regarding
especially right now Oracle, the defined constraint also matches and
entries do not get duplicated. Postgres also works fine.
The reason why this is happening is quite simple - the queries point
exactly to the unique constraint.
Postgres:
UPDATE %s SET ... WHERE instance_id=%lu AND service_object_id=%lu AND
start_time=%s AND start_time_usec=%lu
Oracle:
MERGE INTO %s USING DUAL ON (instance_id=%lu AND service_object_id=%lu
AND start_time=%s AND start_time_usec=%lu) WHEN MATCHED THEN UPDATE SET
... WHEN NOT MATCHED THEN INSERT ... VALUES ...
Conclusion to that is that it is a MySQL problem regarding the defined
keys and the ON DUPLICATE KEY statement.
To step further into that, I've only selected the columns which are
necessary to see the difference::
mysql> select servicecheck_id, service_object_id, start_time,
start_time_usec, end_time, end_time_usec, command_object_id,
execution_time from icinga_servicechecks;
+-----------------+-------------------+---------------------+-----------------+---------------------+---------------+-------------------+----------------+
| servicecheck_id | service_object_id | start_time |
start_time_usec | end_time | end_time_usec |
command_object_id | execution_time |
+-----------------+-------------------+---------------------+-----------------+---------------------+---------------+-------------------+----------------+
This block should be have been updated e.g.
| 521 | 6421 | 2009-11-03 10:34:58 |
245848 | 1970-01-01 01:00:00 | 0 | 6193
| 0 |
| 522 | 6422 | 2009-11-03 10:34:58 |
339129 | 1970-01-01 01:00:00 | 0 | 6193
| 0 |
| 523 | 6423 | 2009-11-03 10:35:02 |
188397 | 1970-01-01 01:00:00 | 0 | 6193
| 0 |
| 524 | 6424 | 2009-11-03 10:35:02 |
329014 | 1970-01-01 01:00:00 | 0 | 6193
| 0 |
| 525 | 6425 | 2009-11-03 10:35:03 |
91160 | 1970-01-01 01:00:00 | 0 | 6193
| 0 |
| 526 | 6307 | 2009-11-03 10:35:03 |
305725 | 1970-01-01 01:00:00 | 0 |
...[email truncated]...
This post was automatically imported from historical nagios-devel mailing list archives
Original poster: [email protected]