Skip to content
This repository has been archived by the owner on Jan 15, 2019. It is now read-only.

[dev.icinga.com #3022] If clean_config_tables_on_core_startup is set to 0 in ido2db.cfg, new hostgroup members are not correctly added in table icinga_hostgroup_members. #1090

Closed
icinga-migration opened this issue Aug 27, 2012 · 9 comments

Comments

@icinga-migration
Copy link

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

Created by feisenko on 2012-08-27 21:03:27 +00:00

Assignee: (none)
Status: Closed (closed on 2013-10-03 02:14:24 +00:00)
Target Version: (none)
Last Update: 2014-12-08 14:37:50 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

All hostgroup members of all hostgroups are added to hostgroup_id 0.
If "ON DUPLICATE KEY UPDATE" will be removed, like discussed in Bug #3008 for other reasons, this problem is maybe also solved.

The debug file shows the following entries:
[1346056351.674876] [002.0] [pid=13503] [tid=1078143168] INSERT INTO icinga_hostgroups (instance_id, config_type, hostgroup_object_id, alias) VALUES (1, 1, 2228, 'hg2') ON DUPLICATE KEY UPDATE config_type=1, alias='hg2'
[1346056351.676198] [001.2] [pid=13503] [tid=1078143168] ido2db_db_query(0) end
... got 0 as last_insert_id and now the program inserts the hostgroup members to hostgroup_id 0 ...
[1346056351.677077] [002.0] [pid=13503] [tid=1078143168] INSERT INTO icinga_hostgroup_members (instance_id, hostgroup_id, host_object_id) VALUES (1,0,2118),(1,0,2129)

The problem has its reason in dbhandlers.c and dbqueries.c (still the same 1.7.2):
dbhandlers.c
result = ido2db_query_insert_or_update_hostgroupdefinition_definition_add(idi, data);
...
group_id = dbi_conn_sequence_last(idi->dbinfo.dbi_conn, NULL);
dbqueries.c
dummy = asprintf(&query1, "INSERT INTO %s (instance_id, config_type, hostgroup_object_id, alias) VALUES (%lu, %d, %lu, '%s') ON DUPLICATE KEY UPDATE config_type=%d, alias='%s'",

Because no insert occurs and no column is really updated, dbi_conn_sequence_last returns 0 and as consequence all members are added to the non existing group 0.
The common problem is well discussed:
http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

With MySQL 5.5.25a the trick "ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)" works.
The statements:
mysql> INSERT INTO icinga_hostgroups (instance_id, config_type, hostgroup_object_id, alias) VALUES (1, 1, 2228, 'hg2') ON DUPLICATE KEY UPDATE hostgroup_id = LAST_INSERT_ID(hostgroup_id), config_type=1, alias='hg2';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();

------------------
| LAST_INSERT_ID() |

------------------
| 999 |

------------------
1 row in set (0.00 sec)

works like expected, it is really 999.

I have not verified it, but I think the same problem occurs also for other objects like servicegroup members and so on.

A correction should also clean the garbage data in icinga_hostgroup_members.

Best regards,
Franz Eisenkölbl


Relations:

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2012-08-28 08:11:41 +00:00

  • Status changed from New to Feedback
  • Priority changed from High to Normal

though that option is only experimental, i can see the other cause. first off, let's fix #3008 for the mysql fuckup. then recheck here.

@icinga-migration
Copy link
Author

Updated by feisenko on 2012-08-28 09:37:00 +00:00

I agree, that this problem should be rechecked after #3008 is solved.
By the way, if clean_config_tables_on_core_startup=0 is experimental, it should be marked as experimental in ido2db.cfg-sample.in like the option trim_db_interval.
Since you have resolved it as Feature #471 for Version 1.0.2 it never was marked as experimental and we use it in production for queries which expect consecutive existence of config data.

Best regards,
Franz Eisenkölbl

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2012-08-28 10:43:50 +00:00

yep should probably do that. it was done on a per users demand, and not meant for larger usage without indicating the problems possibly caused by that.

@icinga-migration
Copy link
Author

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

possibly fixed with https://dev.icinga.org/issues/3008#note-17

could you test that please, if that solves your problem as well?

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2012-10-11 15:11:54 +00:00

any updates?

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2012-11-01 13:46:44 +00:00

  • Status changed from Feedback to Closed

given what i discovered within #3408 this setting did not work before, causing all updates to fail to return the last insert id / sequence id, causing data corruption.

marking this a duplicate of #3408 as the general bug description and resolval is over there.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-01 11:33:16 +00:00

  • Status changed from Closed to Assigned
  • Assigned to set to mfriedrich

obviously not fixed and related to #4240

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-03 02:14:24 +00:00

  • Status changed from Assigned to Closed
  • Assigned to deleted mfriedrich

deprecated in #4791

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-12-08 14:37:50 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 79 to IDOUtils
  • Icinga Version changed from 1 to 1
  • OS Version set to any

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

1 participant