Skip to content
This repository has been archived by the owner. It is now read-only.

[dev.icinga.com #3008] insert on duplicate key with multiple unique keys marked unsafe in mysql >= 5.5.24; rewrite all queries #1085

Closed
icinga-migration opened this issue Aug 23, 2012 · 19 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Aug 23, 2012

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3008

Created by tgelf on 2012-08-23 15:30:09 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-09-23 09:57:36 +00:00)
Target Version: 1.8
Last Update: 2014-12-08 14:35:03 +00:00 (in Redmine)

Icinga Version: 1.7.1
OS Version: Any

The SQL statements executed by ido2db are ugly but replication safe, even with statement-based replication. Unfortunately MySQL starting with version 5.5.24 flags the INSERT... ON DUPLICATE KEY UPDATE queries executed by Icinga being "unsafe", as there is more than one UNIQUE KEY in effect. MySQL changelog says:

INSERT ON DUPLICATE KEY UPDATE is now marked as unsafe for
statement-based replication if the target table has more than
one primary or unique key.

Details are available at http://bugs.mysql.com/bug.php?id=58637. While their "solution" is disputable, the sad side of this "enhancement" is that Icinga floods your mysql.err logs with bazillions of warnings: 200 MB of warnings during a single Icinga startup and 20 GB in less than two days on the platform I'm currently working on. This can quickly fill your disk - and a full MySQL data partition can freeze your Icinga process (not only the IDO).

There are two quick workarounds right now:

  • Turn off log_warnings. But I REALLY want to see them :(
  • Use mixed replication. Not, I don't like mixed replication :(

IDO could try to avoid being flagged as unsafe by adjusting it's indexes. For example the icinga_hoststatus table looks like this right now:

PRIMARY KEY (hoststatus_id),
UNIQUE KEY hoststatus_id (hoststatus_id),
UNIQUE KEY object_id (host_object_id)

The second row (UNIQUE KEY hoststatus_id) is completely useless, the primary key is already unique. The unique object_id absolutely makes sense. The hoststatus_id is completely useless, but removing it would probably require changes to the IDO.

A solution for this table could look as follows:

ALTER TABLE icinga_hoststatus
DROP PRIMARY KEY,
DROP KEY hoststatus_id,
DROP KEY object_id,
ADD PRIMARY KEY (hoststatus_id, host_object_id),
ADD KEY host_object_id (host_object_id);

MySQL then no longer complains about this unsafe statement. The same constraints as before remain in effect. However, there are many other tables with the same problem. We all know that INSERT... ON DUPLICATE KEY UPDATE is an awful construct, but it has the advantage of allowing developers being lazy ;-) Unfortunatly in combination with excessive unique keys as used in the IDO schema it becomes a nightmare. Cleaning them up might partly fix this issue, but I doubt this will be not that easy for some of the affected tables.

Regards,
Thomas Gelf

NB: Top talker is icinga_customvariablestatus. Is it really necessary to refresh them with each host/service status update?

Changesets

2012-08-25 15:23:35 +00:00 by mfriedrich b317a69

idoutils: replace insert on duplicate foo with update/insert strategy

mysql sucks. rot in hell.

refs #3008

2012-08-25 16:10:05 +00:00 by mfriedrich f78b5b0

idoutils: explicitely set 0 time values to NULL as from_unixtime only supports that

refs #3008

2012-08-31 19:47:24 +00:00 by mfriedrich cd6e70d

idoutils: go on with mysql insert-on-duplicate-key replacement, add safety SELECT before INSERTS (libdbd-mysql seems broken)

 i guess the faulty return of "affected rows" is just plain mysql dbi driver specific.
because in doing so on a mysql shell, it works - everything changed, and affected is
also 1. in doing so with postgresql, it also works.

probably the driver handlers affected and matched differently within the driver layer
itsself. on debian wheezy i got

ii  libdbd-mysql 0.8.2-1-4.1+b1

need to recheck on wheezy/sid if the error is consistent.

i don't have time to debug the shitty dbd driver though.

in order to stay safe, and not to lose too much performance (because status UPDATES
happen n-1 of the time, while the initial insert only happens once on initial startup
- since we do not clean any *status tables at all), we'll double check this for sanity.

- run the UPDATE
- check if num_rows_affected turns out to be 0 (for whatever reasons later on)
- if it is really 0, do a SELECT on the unique constraint
- the the select is 0, INSERT a new row

this will move the SELECT (which is another performance killing query) out of the
standard-to-be-executed queries, and adds it as safety check before inserting anything
into the table which would violate the unique constraints.

currently done for

- comments
- commenthistory
- hoststatus
- servicestatus
- programstatus

though there might be more tables and queries affected by that, so we should possibly add
safety calls to each query former known as "insert-on-duplicate-key-update-mysql-foobar".

MySQL, you are teh suxx!

refs #3008

2012-09-02 14:35:45 +00:00 by mfriedrich e04c554

add safety SELECT for mysql scheduleddowntime and downtimehistory queries

due to the broken return value of the num_rows_affected, we'll
need to add a safety SELECT too.

refs #3008

2012-09-15 15:31:53 +00:00 by mfriedrich 544662a

idoutils: more mysql safety rewrites #3008

fuck you, libdbi and mysql.

refs #3008

2012-09-15 17:31:22 +00:00 by mfriedrich 6f50920

idoutils: mysqllibdbifoo safety for customvars+status #3008

refs #3008

2012-09-16 13:52:01 +00:00 by mfriedrich 5c200e7

idoutils: put all existing update/insert mysql specific queries into safety select blocks #3008

still, we got some shared queries with pgsql left, need to duplicate
those as well as add select blocks into them.

refs #3008

2012-09-16 15:24:57 +00:00 by mfriedrich 6450695

idoutils: rewrite ALL insert/update queries to use safety select for libdbi mysql #3008

i hate mysql, and libdbi.
i will never forget.

refs #3008

2012-09-16 15:34:04 +00:00 by mfriedrich f21bbfb

idoutils: fix insert on duplicate key with multiple unique keys marked unsafe in mysql >= 5.5.24; rewrite all queries #3008 - MF

Changelog update

refs #3008
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 23, 2012

Updated by tgelf on 2012-08-23 15:35:49 +00:00

NB: Please forget the example for the icinga_hoststatus table - unique constraints will no longer work as before when doing so. However, the problem of course remains as is...

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 23, 2012

Updated by mfriedrich on 2012-08-23 15:45:28 +00:00

mysql .............

we should drop support for that foo entirely.
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 23, 2012

Updated by mfriedrich on 2012-08-23 15:45:38 +00:00

  • Category changed from 79 to 57
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 25, 2012

Updated by robe on 2012-08-25 14:10:35 +00:00

I just looked into this with dnsmichi - given the current situation of IDO the best way to move forward would be to remove the non-standard "ON DUPLICATE" queries and merge the MySQL and Postgres queries. The latter use an "UPDATE, if no rows changed, INSERT" which is implemented in the ido code.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 25, 2012

Updated by mfriedrich on 2012-08-25 15:51:46 +00:00

problem is rather to be safe:

this update query will result in a warning.

mysql> UPDATE icinga_comments SET comment_type=1, entry_type=3, object_id=19, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(100\.0% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(0) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1345909341) AND internal_comment_id=530
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

and not changing anything.

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'expiration_time' at row 1 |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

which basically leads to the question, why from_unixtime(0) causes an error.

mysql> UPDATE icinga_comments SET comment_type=1, entry_type=3, object_id=19, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(100\.0% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(-1) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1345909341) AND internal_comment_id=530
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select from_unixtime(-1);
+-------------------+
| from_unixtime(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

hey, cool, -1 now works. or NULL too. but NOT 0. hey mysql, you suck.

another example which fails

Aug 25 17:08:23 nbfr ido2db: Error: database query failed for 'INSERT INTO icinga_programstatus (instance_id, status_update_time, program_start_time, is_currently_running, process_id, daemon_mode, last_command_check, last_log_rotation, notifications_enabled, active_service_checks_enabled, passive_service_checks_enabled, active_host_checks_enabled, passive_host_checks_enabled, event_handlers_enabled, flap_detection_enabled, failure_prediction_enabled, process_performance_data, obsess_over_hosts, obsess_over_services, modified_host_attributes, modified_service_attributes, global_host_event_handler, global_service_event_handler, disable_notif_expire_time) VALUES (1, FROM_UNIXTIME(1345907295), FROM_UNIXTIME(1345907154), '1', 11652, 1, FROM_UNIXTIME(1345907287), FROM_UNIXTIME(0), 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, '', '', FROM_UNIXTIME(0))' - '1062: Duplicate entry '1' for key 'instance_id''

see, from_unixtime(0) is also there.

so, basically, once the query is correct (say the from_unixtime(0) are revoked/rewritten), the updates will finally run through.

question - write a stored procedure which wraps 0 values around from unix_time()? catch all 0s in the code (horrible). huh?

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 25, 2012

Updated by mfriedrich on 2012-08-25 16:17:28 +00:00

the function warnings can be resolved by implicitely fetching that within ido2db_db_timet_to_sql(), and making 0 the NULL string on the function.

but that does not solve the problem with affected rows == 0 ... strangely enough. that query needs a deeper analysis. probably we're getting 2 updates in a row, where one is affecting, and one is not, causing the wrong insert.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 26, 2012

Updated by mfriedrich on 2012-08-26 00:08:39 +00:00

  • Target Version set to 1.8
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 26, 2012

Updated by mfriedrich on 2012-08-26 00:09:11 +00:00

  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 29, 2012

Updated by mfriedrich on 2012-08-29 17:04:09 +00:00

next round - we get 2x the same comments, one time ADD one time LOAD. this is due to the fact, that i have kept the flapping comment inside the retention.dat, as well as the core automatically detects the flapping once more, and adds that comment as well. both actions trigger a nebcallback on core startup.

previously, the ido2db insert on duplicate key update procedure would have just thrown an exception, which was handled internally, and none would have seen this. now, we have the problem that the insert should not happen - so we possibly need to add an expensive SELECT before actually trying to insert the comment. or we use the shitty replace into functionality?

Wed Aug 29 18:38:23 2012 .778104 [001.2] [pid=27417] [tid=140029713512192] ido2db_start_input_data() end
Wed Aug 29 18:38:23 2012 .778133 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input() end
Wed Aug 29 18:38:23 2012 .778152 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input start
Wed Aug 29 18:38:23 2012 .778202 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input instance_name=icinga-dev
Wed Aug 29 18:38:23 2012 .778219 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input() input_section
Wed Aug 29 18:38:23 2012 .778232 [001.2] [pid=27417] [tid=140029713512192] ido2db_end_input_data() start
Wed Aug 29 18:38:23 2012 .778254 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_commentdata() start
Wed Aug 29 18:38:23 2012 .778267 [001.2] [pid=27417] [tid=140029713512192] ido2db_convert_standard_data_elements() start
Wed Aug 29 18:38:23 2012 .778281 [001.2] [pid=27417] [tid=140029713512192] ido2db_convert_standard_data_elements() end
Wed Aug 29 18:38:23 2012 .778294 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('(Icinga Process)') start
Wed Aug 29 18:38:23 2012 .778307 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string changed string ('\(Icinga Process\)')
Wed Aug 29 18:38:23 2012 .778319 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .778331 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('Notifications for this host are being suppressed because it was detected as having been flapping between different states (21.4% change > 20.
0% threshold).  When the host state stabilizes and the flapping stops, notifications will be re-enabled.') start
Wed Aug 29 18:38:23 2012 .778347 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string changed string ('Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.')
Wed Aug 29 18:38:23 2012 .778359 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .778371 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(1346258268) start
Wed Aug 29 18:38:23 2012 .778385 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(1346258268)) end
Wed Aug 29 18:38:23 2012 .778397 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(1346258268) start
Wed Aug 29 18:38:23 2012 .778410 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(1346258268)) end
Wed Aug 29 18:38:23 2012 .778422 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(0) start
Wed Aug 29 18:38:23 2012 .778434 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(NULL)) end
Wed Aug 29 18:38:23 2012 .778452 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id_with_insert() start
Wed Aug 29 18:38:23 2012 .778469 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id() start
Wed Aug 29 18:38:23 2012 .778482 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_cached_object_id() start
Wed Aug 29 18:38:23 2012 .778494 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() start
Wed Aug 29 18:38:23 2012 .778506 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() end
Wed Aug 29 18:38:23 2012 .778518 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('test_host_056') start
Wed Aug 29 18:38:23 2012 .778531 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .778547 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .778632 [002.0] [pid=27417] [tid=140029713512192] SELECT object_id FROM icinga_objects WHERE instance_id=1 AND objecttype_id=1 AND name1='test_host_056' AND name2 IS NULL
Wed Aug 29 18:38:23 2012 .778988 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .779015 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id(0) end
Wed Aug 29 18:38:23 2012 .779042 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('test_host_056') start
Wed Aug 29 18:38:23 2012 .779056 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .779073 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .779153 [002.0] [pid=27417] [tid=140029713512192] INSERT INTO icinga_objects (instance_id, objecttype_id, name1, name2) VALUES (1, 1, 'test_host_056', NULL)
Wed Aug 29 18:38:23 2012 .779949 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .779977 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id_with_insert(1) object_id
Wed Aug 29 18:38:23 2012 .779991 [001.2] [pid=27417] [tid=140029713512192] ido2db_add_cached_object_id() start
Wed Aug 29 18:38:23 2012 .780531 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() start
Wed Aug 29 18:38:23 2012 .780547 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() end
Wed Aug 29 18:38:23 2012 .780560 [001.2] [pid=27417] [tid=140029713512192] ido2db_add_cached_object_id() end
Wed Aug 29 18:38:23 2012 .780572 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id_with_insert(object id=1) end
Wed Aug 29 18:38:23 2012 .780591 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_history_add() start
Wed Aug 29 18:38:23 2012 .780611 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .780694 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_commenthistory SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89
Wed Aug 29 18:38:23 2012 .800005 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .800046 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .800963 [002.0] [pid=27417] [tid=140029713512192] INSERT INTO icinga_commenthistory (entry_time, entry_time_usec, instance_id, comment_type, entry_type, object_id, comment_time, internal_comment_id, author_name, comment_data, is_persistent, comment_source, expires, expiration_time) VALUES (FROM_UNIXTIME(1346258268), 400490, 1, 1, 3, 1, FROM_UNIXTIME(1346258268), 89, '\(Icinga Process\)', 'Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', 0, 0, 0, FROM_UNIXTIME(NULL))
Wed Aug 29 18:38:23 2012 .806353 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .806390 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_history_add() end
Wed Aug 29 18:38:23 2012 .806431 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() start
Wed Aug 29 18:38:23 2012 .806466 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .806560 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_comments SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89
Wed Aug 29 18:38:23 2012 .806876 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .806910 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() dbi_result_get_numrows_affected=0
Wed Aug 29 18:38:23 2012 .806945 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .807060 [002.0] [pid=27417] [tid=140029713512192] INSERT INTO icinga_comments (entry_time, entry_time_usec, instance_id, comment_type, entry_type, object_id, comment_time, internal_comment_id, author_name, comment_data, is_persistent, comment_source, expires, expiration_time) VALUES (FROM_UNIXTIME(1346258268), 400490, 1, 1, 3, 1, FROM_UNIXTIME(1346258268), 89, '\(Icinga Process\)', 'Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', 0, 0, 0, FROM_UNIXTIME(NULL))
Wed Aug 29 18:38:23 2012 .807836 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .807869 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() end
Wed Aug 29 18:38:23 2012 .807885 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_commentdata() end


Wed Aug 29 18:38:23 2012 .810418 [001.2] [pid=27417] [tid=140029713512192] ido2db_start_input_data() end
Wed Aug 29 18:38:23 2012 .810430 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input() end
Wed Aug 29 18:38:23 2012 .810442 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input start
Wed Aug 29 18:38:23 2012 .810454 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input instance_name=icinga-dev
Wed Aug 29 18:38:23 2012 .810467 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_client_input() input_section
Wed Aug 29 18:38:23 2012 .810485 [001.2] [pid=27417] [tid=140029713512192] ido2db_end_input_data() start
Wed Aug 29 18:38:23 2012 .810507 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_commentdata() start
Wed Aug 29 18:38:23 2012 .810532 [001.2] [pid=27417] [tid=140029713512192] ido2db_convert_standard_data_elements() start
Wed Aug 29 18:38:23 2012 .810559 [001.2] [pid=27417] [tid=140029713512192] ido2db_convert_standard_data_elements() end
Wed Aug 29 18:38:23 2012 .810584 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('(Icinga Process)') start
Wed Aug 29 18:38:23 2012 .810607 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string changed string ('\(Icinga Process\)')
Wed Aug 29 18:38:23 2012 .810621 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .810633 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string('Notifications for this host are being suppressed because it was detected as having been flapping between different states (21.4% change > 20.
0% threshold).  When the host state stabilizes and the flapping stops, notifications will be re-enabled.') start
Wed Aug 29 18:38:23 2012 .810648 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string changed string ('Notifications for this host are being suppressed because it was detected as having been flapping between different states \(2
1\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.')
Wed Aug 29 18:38:23 2012 .810673 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_escape_string end
Wed Aug 29 18:38:23 2012 .810686 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(1346258268) start
Wed Aug 29 18:38:23 2012 .810701 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(1346258268)) end
Wed Aug 29 18:38:23 2012 .810713 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(1346258268) start
Wed Aug 29 18:38:23 2012 .810726 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(1346258268)) end
Wed Aug 29 18:38:23 2012 .810742 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(0) start
Wed Aug 29 18:38:23 2012 .810762 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_timet_to_sql(FROM_UNIXTIME(NULL)) end
Wed Aug 29 18:38:23 2012 .810787 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id_with_insert() start
Wed Aug 29 18:38:23 2012 .810811 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id() start
Wed Aug 29 18:38:23 2012 .810834 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_cached_object_id() start
Wed Aug 29 18:38:23 2012 .810856 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() start
Wed Aug 29 18:38:23 2012 .810875 [001.2] [pid=27417] [tid=140029713512192] ido2db_object_hashfunc() end
Wed Aug 29 18:38:23 2012 .810889 [001.2] [pid=27417] [tid=140029713512192] ido2db_compare_object_hashdata() start
Wed Aug 29 18:38:23 2012 .810901 [001.2] [pid=27417] [tid=140029713512192] ido2db_compare_object_hashdata() end
Wed Aug 29 18:38:23 2012 .810913 [001.2] [pid=27417] [tid=140029713512192] ido2db_compare_object_hashdata() start
Wed Aug 29 18:38:23 2012 .810925 [001.2] [pid=27417] [tid=140029713512192] ido2db_compare_object_hashdata() end
Wed Aug 29 18:38:23 2012 .810937 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_cached_object_id(1) end
Wed Aug 29 18:38:23 2012 .810949 [001.2] [pid=27417] [tid=140029713512192] ido2db_get_object_id(1) return cached object
Wed Aug 29 18:38:23 2012 .810962 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_history_add() start
Wed Aug 29 18:38:23 2012 .810982 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .811105 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_commenthistory SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89
Wed Aug 29 18:38:23 2012 .812066 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .812116 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .812218 [002.0] [pid=27417] [tid=140029713512192] INSERT INTO icinga_commenthistory (entry_time, entry_time_usec, instance_id, comment_type, entry_type, object_id, comment_time, internal_comment_id, author_name, comment_data, is_persistent, comment_source, expires, expiration_time) VALUES (FROM_UNIXTIME(1346258268), 400643, 1, 1, 3, 1, FROM_UNIXTIME(1346258268), 89, '\(Icinga Process\)', 'Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', 0, 0, 0, FROM_UNIXTIME(NULL))
Wed Aug 29 18:38:23 2012 .817577 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_db_error() start
Wed Aug 29 18:38:23 2012 .817722 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(-1) end
Wed Aug 29 18:38:23 2012 .817753 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_history_add() end
Wed Aug 29 18:38:23 2012 .817775 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() start
Wed Aug 29 18:38:23 2012 .817796 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .817879 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_comments SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89
Wed Aug 29 18:38:23 2012 .818860 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(0) end
Wed Aug 29 18:38:23 2012 .818888 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() dbi_result_get_numrows_affected=0
Wed Aug 29 18:38:23 2012 .818908 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query() start
Wed Aug 29 18:38:23 2012 .818987 [002.0] [pid=27417] [tid=140029713512192] INSERT INTO icinga_comments (entry_time, entry_time_usec, instance_id, comment_type, entry_type, object_id, comment_time, internal_comment_id, author_name, comment_data, is_persistent, comment_source, expires, expiration_time) VALUES (FROM_UNIXTIME(1346258268), 400643, 1, 1, 3, 1, FROM_UNIXTIME(1346258268), 89, '\(Icinga Process\)', 'Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', 0, 0, 0, FROM_UNIXTIME(NULL))
Wed Aug 29 18:38:23 2012 .819821 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_db_error() start
Wed Aug 29 18:38:23 2012 .819943 [001.2] [pid=27417] [tid=140029713512192] ido2db_db_query(-1) end
Wed Aug 29 18:38:23 2012 .819968 [001.2] [pid=27417] [tid=140029713512192] ido2db_query_insert_or_update_commentdata_add() end
Wed Aug 29 18:38:23 2012 .819982 [001.2] [pid=27417] [tid=140029713512192] ido2db_handle_commentdata() end
Wed Aug 29 18:38:23 2012 .819995 [001.2] [pid=27417] [tid=140029713512192] ido2db_free_input_memory() start
Wed Aug 29 18:38:23 2012 .820009 [001.2] [pid=27417] [tid=140029713512192] ido2db_free_input_memory() end
Wed Aug 29 18:38:23 2012 .820021 [001.2] [pid=27417] [tid=140029713512192] ido2db_end_input_data() end


Wed Aug 29 18:38:23 2012 .780694 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_commenthistory SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89
Wed Aug 29 18:38:23 2012 .817879 [002.0] [pid=27417] [tid=140029713512192] UPDATE icinga_commenthistory SET comment_type=1, entry_type=3, object_id=1, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(21\.4% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346258268) AND internal_comment_id=89



mysql> select * from icinga_objects where object_id = 1;
+-----------+-------------+---------------+---------------+-------+-----------+
| object_id | instance_id | objecttype_id | name1         | name2 | is_active |
+-----------+-------------+---------------+---------------+-------+-----------+
|         1 |           1 |             1 | test_host_056 | NULL  |         1 |
+-----------+-------------+---------------+---------------+-------+-----------+
1 row in set (0.00 sec)


Aug 29 18:39:37 sol icinga: HOST FLAPPING ALERT: test_host_056;STARTED; Host appears to have started flapping (21.4% change > 20.0% threshold)

add_new_service_comment
#ifdef USE_EVENT_BROKER
        /* send data to event broker */
        broker_comment_data(NEBTYPE_COMMENT_ADD, NEBFLAG_NONE, NEBATTR_NONE, SERVICE_COMMENT, entry_type, host_name, svc_description, entry_time, author_name, comment_data, persistent, source, expires, expire_time, new_comment_id, NULL);
#endif


add_comment
#ifdef NSCORE
#ifdef USE_EVENT_BROKER
        /* send data to event broker */
        broker_comment_data(NEBTYPE_COMMENT_LOAD, NEBFLAG_NONE, NEBATTR_NONE, comment_type, entry_type, host_name, svc_description, entry_time, author, comment_data, persistent, source, expires, expire_time, comment_id, NULL);
#endif
#endif


hostcomment {
host_name=test_host_056
entry_type=3
comment_id=89
source=0
persistent=0
entry_time=1346258268
expires=0
expire_time=0
author=(Icinga Process)
comment_data=Notifications for this host are being suppressed because it was detected as having been flapping between different states (21.4% change > 20.0% threshold).  When the host state stabilizes and the flapping stops, notifications will be re-enabled.
}
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 29, 2012

Updated by mfriedrich on 2012-08-29 17:12:44 +00:00

i guess we shouldn't put much effort into REPLACE, as this is also an extension to the mysql standard, and might get deprecated (namely "flagged unsafe") too.

https://dev.mysql.com/doc/refman/5.5/en/replace.html

Because the results of REPLACE ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging these statements for the master and the slave to diverge. For this reason, in MySQL 5.5.18 and later, REPLACE ... SELECT statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using the STATEMENT binary logging mode, and are logged using the row-based format when using MIXED mode.
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 31, 2012

Updated by mfriedrich on 2012-08-31 16:23:57 +00:00

better.

Fri Aug 31 18:21:06 2012 .272902 [002.0] [pid=24647] [tid=140059780835072] SELECT comment_id FROM icinga_comments WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346430064) AND internal_comment_id=304
Fri Aug 31 18:21:06 2012 .273235 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query(0) end
Fri Aug 31 18:21:06 2012 .273268 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query() start
Fri Aug 31 18:21:06 2012 .273348 [002.0] [pid=24647] [tid=140059780835072] INSERT INTO icinga_comments (entry_time, entry_time_usec, instance_id, comment_type, entry_type, object_id, comment_time, internal_comment_id, author_name, comment_data, is_persistent, comment_source, expires, expiration_time) VALUES (FROM_UNIXTIME(1346430064), 758195, 1, 1, 3, 3, FROM_UNIXTIME(1346430064), 304, '\(Icinga Process\)', 'Notifications for this host are being suppressed because it was detected as having been flapping between different states \(100\.0% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', 0, 0, 0, FROM_UNIXTIME(NULL))
Fri Aug 31 18:21:06 2012 .274523 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query(0) end

Fri Aug 31 18:21:06 2012 .286306 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query() start
Fri Aug 31 18:21:06 2012 .286409 [002.0] [pid=24647] [tid=140059780835072] SELECT comment_id FROM icinga_comments WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346430064) AND internal_comment_id=304
Fri Aug 31 18:21:06 2012 .286724 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query(0) end
Fri Aug 31 18:21:06 2012 .286761 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query() start
Fri Aug 31 18:21:06 2012 .286889 [002.0] [pid=24647] [tid=140059780835072] UPDATE icinga_comments SET comment_type=1, entry_type=3, object_id=3, author_name='\(Icinga Process\)', comment_data='Notifications for this host are being suppressed because it was detected as having been flapping between different states \(100\.0% change > 20\.0% threshold\)\.  When the host state stabilizes and the flapping stops, notifications will be re-enabled\.', is_persistent=0, comment_source=0, expires=0, expiration_time=FROM_UNIXTIME(NULL) WHERE instance_id=1 AND comment_time=FROM_UNIXTIME(1346430064) AND internal_comment_id=304
Fri Aug 31 18:21:06 2012 .288047 [001.2] [pid=24647] [tid=140059780835072] ido2db_db_query(0) end
Fri Aug 31 18:21:06 2012 .288078 [001.2] [pid=24647] [tid=140059780835072] ido2db_query_insert_or_update_commentdata_add() dbi_result_get_numrows_affected=0
Fri Aug 31 18:21:06 2012 .288093 [001.2] [pid=24647] [tid=140059780835072] ido2db_query_insert_or_update_commentdata_add() end
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 31, 2012

Updated by mfriedrich on 2012-08-31 17:27:14 +00:00

ok, there's a certain amount of data which hits ido2db in the same second, therefore we've got 2 exact same update queries, where the second update is not returning any affected values. mysql shell treats this right

Fri Aug 31 19:15:25 2012 .041502 [002.0] [pid=15393] [tid=140182821832448] UPDATE icinga_programstatus SET status_update_time=FROM_UNIXTIME(1346433325), program_start_time=FROM_UNIXTIME(1346433155), is_currently_running=1, process_id=15426, daemon_mode=1, last_command_check=FROM_UNIXTIME(1346433325), last_log_rotation=FROM_UNIXTIME(NULL), notifications_enabled=1, active_service_checks_enabled=1, passive_service_checks_enabled=1, active_host_checks_enabled=1, passive_host_checks_enabled=1, event_handlers_enabled=1, flap_detection_enabled=1, failure_prediction_enabled=1, process_performance_data=1, obsess_over_hosts=0, obsess_over_services=0, modified_host_attributes=0, modified_service_attributes=0, global_host_event_handler='', global_service_event_handler='', disable_notif_expire_time=FROM_UNIXTIME(NULL) WHERE instance_id=1
Fri Aug 31 19:15:25 2012 .291925 [002.0] [pid=15393] [tid=140182821832448] UPDATE icinga_programstatus SET status_update_time=FROM_UNIXTIME(1346433325), program_start_time=FROM_UNIXTIME(1346433155), is_currently_running=1, process_id=15426, daemon_mode=1, last_command_check=FROM_UNIXTIME(1346433325), last_log_rotation=FROM_UNIXTIME(NULL), notifications_enabled=1, active_service_checks_enabled=1, passive_service_checks_enabled=1, active_host_checks_enabled=1, passive_host_checks_enabled=1, event_handlers_enabled=1, flap_detection_enabled=1, failure_prediction_enabled=1, process_performance_data=1, obsess_over_hosts=0, obsess_over_services=0, modified_host_attributes=0, modified_service_attributes=0, global_host_event_handler='', global_service_event_handler='', disable_notif_expire_time=FROM_UNIXTIME(NULL) WHERE instance_id=1
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 31, 2012

Updated by mfriedrich on 2012-08-31 19:45:21 +00:00

  • Done % changed from 0 to 50

hm, i guess the faulty return of "affected rows" is just plain mysql dbi driver specific. because in doing so on a mysql shell, it works - everything changed, and affected is also 1. in doing so with postgresql, it also works.

probably the driver handlers affected and matched differently within the driver layer itsself. on debian wheezy i got

ii  libdbd-mysql                                           0.8.2-1-4.1+b1                                         MySQL database server driver for libdbi

need to recheck on wheezy/sid if the error is consistent.

i don't have time to debug the shitty dbd driver though.

in order to stay safe, and not to lose too much performance (because status UPDATES happen n-1 of the time, while the initial insert only happens once on initial startup - since we do not clean any *status tables at all), we'll double check this for sanity.

  • run the UPDATE
  • check if num_rows_affected turns out to be 0 (for whatever reasons later on)
  • if it is really 0, do a SELECT on the unique constraint
  • the the select is 0, INSERT a new row

this will move the SELECT (which is another performance killing query) out of the standard-to-be-executed queries, and adds it as safety check before inserting anything into the table which would violate the unique constraints.

currently done for

  • comments
  • commenthistory
  • hoststatus
  • servicestatus
  • programstatus

though there might be more tables and queries affected by that, so we should possibly add safety calls to each query former known as "insert-on-duplicate-key-update-mysql-foobar".

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Sep 2, 2012

Updated by mfriedrich on 2012-09-02 14:21:47 +00:00

the problematic queries are duplicated nebtype events to be dumped into the database at the exact timestamp (where the query does not make a difference on the nanoseconds).

        if (type == NEBTYPE_COMMENT_ADD || type == NEBTYPE_COMMENT_LOAD) {

which we solved for comments and commenthistory table.

        if (type == NEBTYPE_DOWNTIME_ADD || type == NEBTYPE_DOWNTIME_LOAD) {


Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_downtimehistory (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 348, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 13, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-348-2012-09-02 14:17:34-13' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 348, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 13, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-348-2012-09-02 14:17:34-13' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_downtimehistory (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 349, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 14, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-349-2012-09-02 14:17:34-14' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 349, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 14, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-349-2012-09-02 14:17:34-14' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_downtimehistory (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 350, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 15, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-350-2012-09-02 14:17:34-15' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 350, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 15, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-350-2012-09-02 14:17:34-15' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_downtimehistory (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 351, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 16, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-351-2012-09-02 14:17:34-16' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 351, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 16, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-351-2012-09-02 14:17:34-16' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_downtimehistory (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 352, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 17, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-352-2012-09-02 14:17:34-17' for key 'instance_id''
Sep  2 16:17:34 sol ido2db: Error: database query failed for 'INSERT INTO icinga_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time, is_in_effect, trigger_time) VALUES (1, 1, 352, FROM_UNIXTIME(1346595454), 'icingademo', 'sfsfsfsfs', 17, 0, 1, 3600, FROM_UNIXTIME(1346595450), FROM_UNIXTIME(1346599050), 0, FROM_UNIXTIME(NULL))' - '1062: Duplicate entry '1-352-2012-09-02 14:17:34-17' for key 'instance_id''

pending fix for scheduleddowntime and downtimehistory

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Sep 2, 2012

Updated by mfriedrich on 2012-09-02 15:15:54 +00:00

i want to see a list of distributions already shipping 5.5.24

  • debian wheezy got it, so possible debian 7 we will have this on all systems
  • ubuntu 12.04.1 ships 5.5.24
  • rhel6 5.1.64
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Sep 16, 2012

Updated by mfriedrich on 2012-09-16 15:33:57 +00:00

  • Subject changed from New warnings with MySQL >= 5.5.24 to insert on duplicate key with multiple unique keys marked unsafe in mysql >= 5.5.24; rewrite all queries
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Sep 16, 2012

Updated by mfriedrich on 2012-09-16 15:42:01 +00:00

  • Status changed from Assigned to 7
  • Done % changed from 50 to 100

rewrote all mysql update/insert queries to have a safety select, as it seems that at least on debian, the libdbi mysql driver is broken, always returning 0 on affected rows.

since this is now a complete rewrite, everybody test NOW! currently in test/ido.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Sep 23, 2012

Updated by mfriedrich on 2012-09-23 09:57:36 +00:00

  • Status changed from 7 to Resolved

runs fine at my stage, though the added SELECT will possibly decrease mysql performance a bit.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:35:03 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 57 to IDOUtils
  • OS Version set to Any

@icinga-migration icinga-migration added this to the 1.8 milestone Jan 17, 2017

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.