Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[dev.icinga.com #10190] Problem with customvariable table update/insert queries #3416

Closed
icinga-migration opened this issue Sep 22, 2015 · 15 comments
Closed

[dev.icinga.com #10190] Problem with customvariable table update/insert queries #3416

icinga-migration opened this issue Sep 22, 2015 · 15 comments
Labels
area/api REST API blocker Blocks a release or needs immediate attention bug Something isn't working
Milestone
2.3.11

Comments

@icinga-migration
Copy link

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

Created by mfriedrich on 2015-09-22 08:25:02 00:00

Assignee: mfriedrich
Status: Resolved (closed on 2015-09-28 11:22:03 00:00)
Target Version: 2.3.11
Last Update: 2015-10-16 08:21:03 00:00 (in Redmine)

Icinga Version: 2.3.10
Backport?: Already backported
Include in Changelog: 0

[2015-09-22 10:23:25  0200] critical/IdoMysqlConnection: Error "Duplicate entry '186-1-os' for key 'object_id_2'" when executing query "UPDATE icinga_customvariablestatus SET has_been_modified = '0',  instance_id = 1,  is_json = '0',  object_id = 186,  status_update_time = FROM_UNIXTIME(1442910205),  varname = 'os',  varvalue = 'blub' WHERE object_id = 186 AND varname = 'os';INSERT INTO icinga_customvariables (config_type, has_been_modified, instance_id, is_json, object_id, varname, varvalue) VALUES ('1', '0', 1, '0', 186, 'os', 'blub');UPDATE icinga_customvariablestatus SET has_been_modified = '0',  instance_id = 1,  is_json = '0',  object_id = 186,  status_update_time = FROM_UNIXTIME(1442910205),  varname = 'os',  varvalue = 'blub' WHERE object_id = 186 AND varname = 'os';UPDATE icinga_hosts SET action_url = '',  active_checks_enabled = '1',  address = '8.8.8.8',  address6 = '',  alias = 'google.com2',  check_command_args = '',  check_command_object_id = 21,  check_interval = '5',  config_type = '1',  display_name = 'google.com2',  event_handler_enabled = '1',  failure_prediction_enabled = '0',  flap_detection_enabled = '0',  freshness_checks_enabled = '1',  freshness_threshold = '300',  high_flap_threshold = '30',  host_object_id = 186,  icon_image = '',  icon_image_alt = '',  instance_id = 1,  low_flap_threshold = '30',  max_check_attempts = '3',  notes = '',  notes_url = '',  notification_interval = '1',  notifications_enabled = '1',  notify_on_down = '0',  notify_on_downtime = '0',  notify_on_flapping = '0',  notify_on_recovery = '0',  notify_on_unreachable = '0',  obsess_over_host = '0',  passive_checks_enabled = '1',  process_performance_data = '1',  retain_nonstatus_information = '1',  retain_status_information = '1',  retry_interval = '1' WHERE host_object_id = 186;INSERT INTO icinga_host_parenthosts (host_id, instance_id, parent_host_object_id) VALUES (4, 1, 237);INSERT INTO icinga_hostdependencies (dependent_host_object_id, fail_on_down, fail_on_up, host_object_id, inherits_parent, instance_id) VALUES (186, '0', '1', 237, '1', 1);UPDATE icinga_hoststatus SET acknowledgement_type = '0',  active_checks_enabled = '1',  check_command = 'hostalive',  check_source = 'mbmif.int.netways.de',  check_type = '0',  current_check_attempt = '1',  current_notification_number = '0',  current_state = '0',  endpoint_object_id = 1,  event_handler = '',  event_handler_enabled = '1',  execution_time = '4.0322551727294922',  flap_detection_enabled = '0',  has_been_checked = '1',  host_object_id = 186,  instance_id = 1,  is_flapping = '0',  is_reachable = '1',  last_check = FROM_UNIXTIME(1442910000),  last_hard_state = '0',  last_hard_state_change = FROM_UNIXTIME(1442844903),  last_state_change = FROM_UNIXTIME(1442844845),  last_time_down = FROM_UNIXTIME(1442844554),  last_time_up = FROM_UNIXTIME(1442910000),  latency = '0',  long_output = '',  max_check_attempts = '3',  modified_host_attributes = '0',  next_check = FROM_UNIXTIME(1442910296),  normal_check_interval = '5',  notifications_enabled = '1',  output = 'PING OK - Packet loss = 0%, RTA = 4.94 ms',  passive_checks_enabled = '1',  percent_state_change = '0',  perfdata = 'rta=4.942000ms;3000.000000;5000.000000;0.000000 pl=0%;80;100;0',  problem_has_been_acknowledged = '0',  process_performance_data = '1',  retry_check_interval = '1',  scheduled_downtime_depth = '0',  should_be_scheduled = '1',  state_type = '1',  status_update_time = FROM_UNIXTIME(1442910205) WHERE host_object_id = 186;COMMIT;BEGIN"
[2015-09-22 10:23:25  0200] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!

Changesets

2015-09-24 09:45:44 00:00 by mfriedrich 8c99725

DB IDO: Fix modified custom attribute update query problems

fixes #10190

2015-09-25 15:01:41 00:00 by mfriedrich f1567f3

Disable async IDO queries for custom var updates

refs #10190

2015-09-28 11:19:42 00:00 by (unknown) d24af1e

Fix incorrect value for IdoMysqlConnection::m_AffectedRows

refs #10190

2015-09-28 11:24:27 00:00 by mfriedrich f0bdd4c

DB IDO: Fix modified custom attribute update query problems

fixes #10190

2015-09-28 11:25:37 00:00 by (unknown) 39e92df

Fix incorrect value for IdoMysqlConnection::m_AffectedRows

refs #10190

Relations:
@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-23 16:19:40 00:00

  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
  • Priority changed from Normal to High

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-23 19:08:45 00:00

  • Tracker changed from Feature to Bug
  • Icinga Version set to 2
  • Include in Changelog changed from 1 to 0

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-24 09:47:09 00:00

  • Estimated Hours set to 2

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-24 09:47:16 00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 0 to 100

Applied in changeset 8c99725.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-24 13:58:35 00:00

  • Status changed from Resolved to Assigned
  • Done % changed from 100 to 80

Still exists under special circumstances.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-25 13:23:19 00:00

Nailed it down: We've two events fired in case of changing a custom attribute

  1. ModifyAttribute updates the entire object including the custom vars
  2. OnVarsChanged is called and tries to update the custom vars.

The mysql query merger causes the 2 insert queries happening one after the other. The first one works, the second one causes a unique constraint violation.

Since we decided to update the entire object once an attribute changes, the OnVarsChanged signal becomes obsolete. We will have a similar issue with cluster updates.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-25 14:16:46 00:00

Hm. Not related to VarsChanged.

Different scenario:

  1. API Object is dumped to the database, causing custom vars being inserted

  2. Modified Attributes are applied afterwards causing custom vars updates

    INSERT INTO icinga_customvariables (config_type, has_been_modified, instance_id, is_json, object_id, varname, varvalue) VALUES ('1', '0', 1, '0', 174, 'os', 'dumdidum2');
    INSERT INTO icinga_customvariablestatus (has_been_modified, instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES ('0', 1, '0', 174, FROM_UNIXTIME(1443187669), 'os', 'dumdidum2');
    INSERT INTO icinga_customvariables (config_type, has_been_modified, instance_id, is_json, object_id, varname, varvalue) VALUES ('1', '0', 1, '0', 174, 'os', 'dumdidum2');
    INSERT INTO icinga_customvariablestatus (has_been_modified, instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES ('0', 1, '0', 174, FROM_UNIXTIME(1443187669), 'os', 'dumdidum2')"

Pretty hard to debug with the async queries going on.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-25 16:39:33 00:00

The problem are actually the async queries for MySQL - the update query does not take effect before the inserts.

I've modified the OnQuery() calls to pass an async flag to the driver which then decides if to push to the async queue or to just execute them directly. This resolves this issue, but requires further discussion with Gunnar. We probably need to find a better way to aggregate queries into multiple sets (e.g. array inserts). We will stick with MySQL and PostgreSQL so it should be an unresolvable issue.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-28 11:21:36 00:00

  • Subject changed from Modifying existing custom attribute results in DB IDO exception to Problem with customvariable table update/insert queries
  • Parent Id deleted 9081

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-28 11:22:03 00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 80 to 100

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-28 11:26:22 00:00

  • Target Version changed from 2.4.0 to 2.3.11
  • Icinga Version changed from 2 to 2
  • Backport? changed from TBD to Yes

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-28 11:42:14 00:00

  • Duplicated set to 10227

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-10-02 07:54:05 00:00

  • Relates set to 10265

@icinga-migration
Copy link
Author

Updated by tarjei on 2015-10-07 19:04:02 00:00

Hi, is there any news on when 2.3.11 will be released? This bug is holing up my migration to I2.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-10-16 08:21:03 00:00

Normally this takes a couple of weeks, best is to watch the roadmap for being updated: https://dev.icinga.org/versions/277

@icinga-migration icinga-migration added blocker Blocks a release or needs immediate attention bug Something isn't working area/api REST API labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.3.11 milestone Jan 17, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/api REST API blocker Blocks a release or needs immediate attention bug Something isn't working
Projects
None yet
Milestone
2.3.11
Development

No branches or pull requests
1 participant
@icinga-migration