From ce3d7ab973e319283f3aa3fd8f610e90b293d565 Mon Sep 17 00:00:00 2001 From: Michael Friedrich Date: Thu, 21 Jul 2016 17:37:41 +0200 Subject: [PATCH] DB IDO: Do not clear {host,service,contact}group_members tables on restart fixes #12210 --- lib/db_ido/dbconnection.cpp | 72 +++++++++++------------ lib/db_ido/dbquery.hpp | 2 +- lib/db_ido/hostdbobject.cpp | 24 +++----- lib/db_ido/servicedbobject.cpp | 24 +++----- lib/db_ido/userdbobject.cpp | 18 +++--- lib/db_ido_mysql/idomysqlconnection.cpp | 32 ++++++---- lib/db_ido_mysql/idomysqlconnection.hpp | 3 +- lib/db_ido_mysql/schema/mysql.sql | 15 +++-- lib/db_ido_mysql/schema/upgrade/2.5.0.sql | 18 ++++++ lib/db_ido_pgsql/idopgsqlconnection.cpp | 32 ++++++---- lib/db_ido_pgsql/idopgsqlconnection.hpp | 3 +- lib/db_ido_pgsql/schema/pgsql.sql | 17 ++++-- lib/db_ido_pgsql/schema/upgrade/2.5.0.sql | 18 ++++++ 13 files changed, 163 insertions(+), 115 deletions(-) diff --git a/lib/db_ido/dbconnection.cpp b/lib/db_ido/dbconnection.cpp index 186e78c7c..2b48fcc89 100644 --- a/lib/db_ido/dbconnection.cpp +++ b/lib/db_ido/dbconnection.cpp @@ -182,53 +182,47 @@ void DbConnection::UpdateProgramStatus(void) DbQuery query1; query1.Table = "programstatus"; - query1.Type = DbQueryDelete; + query1.IdColumn = "programstatus_id"; + query1.Type = DbQueryInsert | DbQueryUpdate; query1.Category = DbCatProgramStatus; + + query1.Fields = new Dictionary(); + query1.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ + query1.Fields->Set("program_version", Application::GetAppVersion()); + query1.Fields->Set("status_update_time", DbValue::FromTimestamp(Utility::GetTime())); + query1.Fields->Set("program_start_time", DbValue::FromTimestamp(Application::GetStartTime())); + query1.Fields->Set("is_currently_running", 1); + query1.Fields->Set("endpoint_name", IcingaApplication::GetInstance()->GetNodeName()); + query1.Fields->Set("process_id", Utility::GetPid()); + query1.Fields->Set("daemon_mode", 1); + query1.Fields->Set("last_command_check", DbValue::FromTimestamp(Utility::GetTime())); + query1.Fields->Set("notifications_enabled", (IcingaApplication::GetInstance()->GetEnableNotifications() ? 1 : 0)); + query1.Fields->Set("active_host_checks_enabled", (IcingaApplication::GetInstance()->GetEnableHostChecks() ? 1 : 0)); + query1.Fields->Set("passive_host_checks_enabled", 1); + query1.Fields->Set("active_service_checks_enabled", (IcingaApplication::GetInstance()->GetEnableServiceChecks() ? 1 : 0)); + query1.Fields->Set("passive_service_checks_enabled", 1); + query1.Fields->Set("event_handlers_enabled", (IcingaApplication::GetInstance()->GetEnableEventHandlers() ? 1 : 0)); + query1.Fields->Set("flap_detection_enabled", (IcingaApplication::GetInstance()->GetEnableFlapping() ? 1 : 0)); + query1.Fields->Set("process_performance_data", (IcingaApplication::GetInstance()->GetEnablePerfdata() ? 1 : 0)); query1.WhereCriteria = new Dictionary(); query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ + query1.Priority = PriorityHigh; queries.push_back(query1); DbQuery query2; - query2.Table = "programstatus"; - query2.IdColumn = "programstatus_id"; - query2.Type = DbQueryInsert; - query2.Category = DbCatProgramStatus; - - query2.Fields = new Dictionary(); - query2.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ - query2.Fields->Set("program_version", Application::GetAppVersion()); - query2.Fields->Set("status_update_time", DbValue::FromTimestamp(Utility::GetTime())); - query2.Fields->Set("program_start_time", DbValue::FromTimestamp(Application::GetStartTime())); - query2.Fields->Set("is_currently_running", 1); - query2.Fields->Set("endpoint_name", IcingaApplication::GetInstance()->GetNodeName()); - query2.Fields->Set("process_id", Utility::GetPid()); - query2.Fields->Set("daemon_mode", 1); - query2.Fields->Set("last_command_check", DbValue::FromTimestamp(Utility::GetTime())); - query2.Fields->Set("notifications_enabled", (IcingaApplication::GetInstance()->GetEnableNotifications() ? 1 : 0)); - query2.Fields->Set("active_host_checks_enabled", (IcingaApplication::GetInstance()->GetEnableHostChecks() ? 1 : 0)); - query2.Fields->Set("passive_host_checks_enabled", 1); - query2.Fields->Set("active_service_checks_enabled", (IcingaApplication::GetInstance()->GetEnableServiceChecks() ? 1 : 0)); - query2.Fields->Set("passive_service_checks_enabled", 1); - query2.Fields->Set("event_handlers_enabled", (IcingaApplication::GetInstance()->GetEnableEventHandlers() ? 1 : 0)); - query2.Fields->Set("flap_detection_enabled", (IcingaApplication::GetInstance()->GetEnableFlapping() ? 1 : 0)); - query2.Fields->Set("process_performance_data", (IcingaApplication::GetInstance()->GetEnablePerfdata() ? 1 : 0)); - query2.Priority = PriorityHigh; + query2.Type = DbQueryNewTransaction; queries.push_back(query2); - DbQuery query3; - query3.Type = DbQueryNewTransaction; - queries.push_back(query3); - DbObject::OnMultipleQueries(queries); - DbQuery query4; - query4.Table = "runtimevariables"; - query4.Type = DbQueryDelete; - query4.Category = DbCatProgramStatus; - query4.WhereCriteria = new Dictionary(); - query4.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ - DbObject::OnQuery(query4); + DbQuery query3; + query3.Table = "runtimevariables"; + query3.Type = DbQueryDelete; + query3.Category = DbCatProgramStatus; + query3.WhereCriteria = new Dictionary(); + query3.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ + DbObject::OnQuery(query3); InsertRuntimeVariable("total_services", std::distance(ConfigType::GetObjectsByType().first, ConfigType::GetObjectsByType().second)); InsertRuntimeVariable("total_scheduled_services", std::distance(ConfigType::GetObjectsByType().first, ConfigType::GetObjectsByType().second)); @@ -433,7 +427,7 @@ void DbConnection::PrepareDatabase(void) //ClearConfigTable("comments"); ClearConfigTable("contact_addresses"); ClearConfigTable("contact_notificationcommands"); - ClearConfigTable("contactgroup_members"); + //ClearConfigTable("contactgroup_members"); //ClearConfigTable("contactgroups"); //ClearConfigTable("contacts"); //ClearConfigTable("contactstatus"); @@ -445,7 +439,7 @@ void DbConnection::PrepareDatabase(void) ClearConfigTable("host_contacts"); ClearConfigTable("host_parenthosts"); ClearConfigTable("hostdependencies"); - ClearConfigTable("hostgroup_members"); + //ClearConfigTable("hostgroup_members"); //ClearConfigTable("hostgroups"); //ClearConfigTable("hosts"); //ClearConfigTable("hoststatus"); @@ -453,7 +447,7 @@ void DbConnection::PrepareDatabase(void) ClearConfigTable("service_contactgroups"); ClearConfigTable("service_contacts"); ClearConfigTable("servicedependencies"); - ClearConfigTable("servicegroup_members"); + //ClearConfigTable("servicegroup_members"); //ClearConfigTable("servicegroups"); //ClearConfigTable("services"); //ClearConfigTable("servicestatus"); diff --git a/lib/db_ido/dbquery.hpp b/lib/db_ido/dbquery.hpp index a1fd50333..9e3751ca1 100644 --- a/lib/db_ido/dbquery.hpp +++ b/lib/db_ido/dbquery.hpp @@ -77,7 +77,7 @@ struct I2_DB_IDO_API DbQuery static void StaticInitialize(void); DbQuery(void) - : Type(0), Category(DbCatInvalid), ConfigUpdate(false), StatusUpdate(false), Priority(PriorityLow) + : Type(0), Category(DbCatInvalid), ConfigUpdate(false), StatusUpdate(false), Priority(PriorityNormal) { } static const std::map& GetCategoryFilterMap(void); diff --git a/lib/db_ido/hostdbobject.cpp b/lib/db_ido/hostdbobject.cpp index 47fd7d244..b6422a0a3 100644 --- a/lib/db_ido/hostdbobject.cpp +++ b/lib/db_ido/hostdbobject.cpp @@ -189,29 +189,21 @@ void HostDbObject::OnConfigUpdate(void) BOOST_FOREACH(const String& groupName, groups) { HostGroup::Ptr group = HostGroup::GetByName(groupName); - std::vector queries; - DbQuery query1; query1.Table = DbType::GetByName("HostGroup")->GetTable() + "_members"; - query1.Type = DbQueryDelete; + query1.Type = DbQueryInsert | DbQueryUpdate; query1.Category = DbCatConfig; + query1.Fields = new Dictionary(); + query1.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ + query1.Fields->Set("hostgroup_id", DbValue::FromObjectInsertID(group)); + query1.Fields->Set("host_object_id", host); + query1.Fields->Set("session_token", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria = new Dictionary(); query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria->Set("hostgroup_id", DbValue::FromObjectInsertID(group)); query1.WhereCriteria->Set("host_object_id", host); - queries.push_back(query1); - - DbQuery query2; - query2.Table = DbType::GetByName("HostGroup")->GetTable() + "_members"; - query2.Type = DbQueryInsert; - query2.Category = DbCatConfig; - query2.Fields = new Dictionary(); - query2.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ - query2.Fields->Set("hostgroup_id", DbValue::FromObjectInsertID(group)); - query2.Fields->Set("host_object_id", host); - queries.push_back(query2); - - DbObject::OnMultipleQueries(queries); + + DbObject::OnQuery(query1); } } diff --git a/lib/db_ido/servicedbobject.cpp b/lib/db_ido/servicedbobject.cpp index 2806e1c70..7f2c6211f 100644 --- a/lib/db_ido/servicedbobject.cpp +++ b/lib/db_ido/servicedbobject.cpp @@ -188,29 +188,21 @@ void ServiceDbObject::OnConfigUpdate(void) BOOST_FOREACH(const String& groupName, groups) { ServiceGroup::Ptr group = ServiceGroup::GetByName(groupName); - std::vector queries; - DbQuery query1; query1.Table = DbType::GetByName("ServiceGroup")->GetTable() + "_members"; - query1.Type = DbQueryDelete; + query1.Type = DbQueryInsert | DbQueryUpdate; query1.Category = DbCatConfig; + query1.Fields = new Dictionary(); + query1.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ + query1.Fields->Set("servicegroup_id", DbValue::FromObjectInsertID(group)); + query1.Fields->Set("service_object_id", service); + query1.Fields->Set("session_token", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria = new Dictionary(); query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria->Set("servicegroup_id", DbValue::FromObjectInsertID(group)); query1.WhereCriteria->Set("service_object_id", service); - queries.push_back(query1); - - DbQuery query2; - query2.Table = DbType::GetByName("ServiceGroup")->GetTable() + "_members"; - query2.Type = DbQueryInsert; - query2.Category = DbCatConfig; - query2.Fields = new Dictionary(); - query2.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ - query2.Fields->Set("servicegroup_id", DbValue::FromObjectInsertID(group)); - query2.Fields->Set("service_object_id", service); - queries.push_back(query2); - - DbObject::OnMultipleQueries(queries); + + DbObject::OnQuery(query1); } } diff --git a/lib/db_ido/userdbobject.cpp b/lib/db_ido/userdbobject.cpp index 5adfd099e..ae6987465 100644 --- a/lib/db_ido/userdbobject.cpp +++ b/lib/db_ido/userdbobject.cpp @@ -95,23 +95,19 @@ void UserDbObject::OnConfigUpdate(void) DbQuery query1; query1.Table = DbType::GetByName("UserGroup")->GetTable() + "_members"; - query1.Type = DbQueryDelete; + query1.Type = DbQueryInsert | DbQueryUpdate; query1.Category = DbCatConfig; + query1.Fields = new Dictionary(); + query1.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ + query1.Fields->Set("contactgroup_id", DbValue::FromObjectInsertID(group)); + query1.Fields->Set("contact_object_id", user); + query1.Fields->Set("session_token", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria = new Dictionary(); query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */ query1.WhereCriteria->Set("contactgroup_id", DbValue::FromObjectInsertID(group)); query1.WhereCriteria->Set("contact_object_id", user); - OnQuery(query1); - DbQuery query2; - query2.Table = DbType::GetByName("UserGroup")->GetTable() + "_members"; - query2.Type = DbQueryInsert; - query2.Category = DbCatConfig; - query2.Fields = new Dictionary(); - query2.Fields->Set("instance_id", 0); /* DbConnection class fills in real ID */ - query2.Fields->Set("contactgroup_id", DbValue::FromObjectInsertID(group)); - query2.Fields->Set("contact_object_id", user); - OnQuery(query2); + OnQuery(query1); } } diff --git a/lib/db_ido_mysql/idomysqlconnection.cpp b/lib/db_ido_mysql/idomysqlconnection.cpp index 6e66075e9..9e0ac7a5c 100644 --- a/lib/db_ido_mysql/idomysqlconnection.cpp +++ b/lib/db_ido_mysql/idomysqlconnection.cpp @@ -414,9 +414,7 @@ void IdoMysqlConnection::Reconnect(void) UpdateAllObjects(); - /* delete all customvariables without current session token */ - ClearCustomVarTable("customvariables"); - ClearCustomVarTable("customvariablestatus"); + m_QueryQueue.Enqueue(boost::bind(&IdoMysqlConnection::ClearTablesBySession, this), PriorityLow); m_QueryQueue.Enqueue(boost::bind(&IdoMysqlConnection::FinishConnect, this, startTime), PriorityLow); } @@ -437,9 +435,21 @@ void IdoMysqlConnection::FinishConnect(double startTime) Query("BEGIN"); } -void IdoMysqlConnection::ClearCustomVarTable(const String& table) +void IdoMysqlConnection::ClearTablesBySession(void) { - Query("DELETE FROM " + GetTablePrefix() + table + " WHERE session_token <> " + Convert::ToString(m_SessionToken)); + /* delete all customvariables and group members without current session token */ + ClearTableBySession("customvariables"); + ClearTableBySession("customvariablestatus"); + ClearTableBySession("hostgroup_members"); + ClearTableBySession("servicegroup_members"); + ClearTableBySession("contactgroup_members"); +} + +void IdoMysqlConnection::ClearTableBySession(const String& table) +{ + Query("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " + + Convert::ToString(static_cast(m_InstanceID)) + " AND session_token <> " + + Convert::ToString(m_SessionToken)); } void IdoMysqlConnection::ClearConfigTable(const String& table) @@ -929,12 +939,12 @@ void IdoMysqlConnection::InternalExecuteQuery(const DbQuery& query, DbQueryType if ((type & DbQueryInsert) && (type & DbQueryUpdate)) { bool hasid = false; - ASSERT(query.Object); - - if (query.ConfigUpdate) - hasid = GetConfigUpdate(query.Object); - else if (query.StatusUpdate) - hasid = GetStatusUpdate(query.Object); + if (query.Object) { + if (query.ConfigUpdate) + hasid = GetConfigUpdate(query.Object); + else if (query.StatusUpdate) + hasid = GetStatusUpdate(query.Object); + } if (!hasid) upsert = true; diff --git a/lib/db_ido_mysql/idomysqlconnection.hpp b/lib/db_ido_mysql/idomysqlconnection.hpp index fdc54c660..ee7cb9e83 100644 --- a/lib/db_ido_mysql/idomysqlconnection.hpp +++ b/lib/db_ido_mysql/idomysqlconnection.hpp @@ -116,7 +116,8 @@ private: void InternalNewTransaction(void); virtual void ClearConfigTable(const String& table) override; - void ClearCustomVarTable(const String& table); + void ClearTableBySession(const String& table); + void ClearTablesBySession(void); void ExceptionHandler(boost::exception_ptr exp); diff --git a/lib/db_ido_mysql/schema/mysql.sql b/lib/db_ido_mysql/schema/mysql.sql index 45b87a234..24c8be8a8 100644 --- a/lib/db_ido_mysql/schema/mysql.sql +++ b/lib/db_ido_mysql/schema/mysql.sql @@ -196,6 +196,7 @@ CREATE TABLE IF NOT EXISTS icinga_contactgroup_members ( instance_id bigint unsigned default 0, contactgroup_id bigint unsigned default 0, contact_object_id bigint unsigned default 0, + session_token int default NULL, PRIMARY KEY (contactgroup_member_id) ) ENGINE=InnoDB COMMENT='Contactgroup members'; @@ -625,6 +626,7 @@ CREATE TABLE IF NOT EXISTS icinga_hostgroup_members ( instance_id bigint unsigned default 0, hostgroup_id bigint unsigned default 0, host_object_id bigint unsigned default 0, + session_token int default NULL, PRIMARY KEY (hostgroup_member_id) ) ENGINE=InnoDB COMMENT='Hostgroup members'; @@ -1119,6 +1121,7 @@ CREATE TABLE IF NOT EXISTS icinga_servicegroup_members ( instance_id bigint unsigned default 0, servicegroup_id bigint unsigned default 0, service_object_id bigint unsigned default 0, + session_token int default NULL, PRIMARY KEY (servicegroup_member_id) ) ENGINE=InnoDB COMMENT='Servicegroup members'; @@ -1641,10 +1644,6 @@ CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1); -- #4985 CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id); --- #10436 -CREATE INDEX cv_session_del_idx ON icinga_customvariables (session_token); -CREATE INDEX cvs_session_del_idx ON icinga_customvariablestatus (session_token); - -- #10070 CREATE INDEX idx_comments_object_id on icinga_comments(object_id); CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); @@ -1662,6 +1661,14 @@ CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); +-- #12210 +CREATE INDEX idx_hg_session_del ON icinga_hostgroup_members (instance_id, session_token); +CREATE INDEX idx_sg_session_del ON icinga_servicegroup_members (instance_id, session_token); +CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (instance_id, session_token); + +CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token); +CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token); + -- #12107 CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); diff --git a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql index 5f6b47c8c..89fc0d873 100644 --- a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql +++ b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql @@ -60,6 +60,24 @@ CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_ob -- ----------------------------------------- CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); +-- ----------------------------------------- +-- #12210 +-- ----------------------------------------- + +ALTER TABLE icinga_hostgroup_members ADD COLUMN session_token int default NULL; +ALTER TABLE icinga_servicegroup_members ADD COLUMN session_token int default NULL; +ALTER TABLE icinga_contactgroup_members ADD COLUMN session_token int default NULL; + +CREATE INDEX idx_hg_session_del ON icinga_hostgroup_members (instance_id, session_token); +CREATE INDEX idx_sg_session_del ON icinga_servicegroup_members (instance_id, session_token); +CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (instance_id, session_token); + +DROP INDEX cv_session_del_idx ON icinga_customvariables; +DROP INDEX cvs_session_del_idx ON icinga_customvariablestatus; + +CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token); +CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token); + -- ----------------------------------------- -- set dbversion -- ----------------------------------------- diff --git a/lib/db_ido_pgsql/idopgsqlconnection.cpp b/lib/db_ido_pgsql/idopgsqlconnection.cpp index c9e1ab0fc..7b8117d45 100644 --- a/lib/db_ido_pgsql/idopgsqlconnection.cpp +++ b/lib/db_ido_pgsql/idopgsqlconnection.cpp @@ -387,9 +387,7 @@ void IdoPgsqlConnection::Reconnect(void) UpdateAllObjects(); - /* delete all customvariables without current session token */ - ClearCustomVarTable("customvariables"); - ClearCustomVarTable("customvariablestatus"); + m_QueryQueue.Enqueue(boost::bind(&IdoPgsqlConnection::ClearTablesBySession, this), PriorityLow); m_QueryQueue.Enqueue(boost::bind(&IdoPgsqlConnection::FinishConnect, this, startTime), PriorityLow); } @@ -408,9 +406,21 @@ void IdoPgsqlConnection::FinishConnect(double startTime) Query("BEGIN"); } -void IdoPgsqlConnection::ClearCustomVarTable(const String& table) +void IdoPgsqlConnection::ClearTablesBySession(void) { - Query("DELETE FROM " + GetTablePrefix() + table + " WHERE session_token <> " + Convert::ToString(m_SessionToken)); + /* delete all customvariables and group members without current session token */ + ClearTableBySession("customvariables"); + ClearTableBySession("customvariablestatus"); + ClearTableBySession("hostgroup_members"); + ClearTableBySession("servicegroup_members"); + ClearTableBySession("contactgroup_members"); +} + +void IdoPgsqlConnection::ClearTableBySession(const String& table) +{ + Query("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " + + Convert::ToString(static_cast(m_InstanceID)) + " AND session_token <> " + + Convert::ToString(m_SessionToken)); } void IdoPgsqlConnection::ClearConfigTable(const String& table) @@ -786,12 +796,12 @@ void IdoPgsqlConnection::InternalExecuteQuery(const DbQuery& query, DbQueryType if ((type & DbQueryInsert) && (type & DbQueryUpdate)) { bool hasid = false; - ASSERT(query.Object); - - if (query.ConfigUpdate) - hasid = GetConfigUpdate(query.Object); - else if (query.StatusUpdate) - hasid = GetStatusUpdate(query.Object); + if (query.Object) { + if (query.ConfigUpdate) + hasid = GetConfigUpdate(query.Object); + else if (query.StatusUpdate) + hasid = GetStatusUpdate(query.Object); + } if (!hasid) upsert = true; diff --git a/lib/db_ido_pgsql/idopgsqlconnection.hpp b/lib/db_ido_pgsql/idopgsqlconnection.hpp index 180d16f30..0be3a8eaf 100644 --- a/lib/db_ido_pgsql/idopgsqlconnection.hpp +++ b/lib/db_ido_pgsql/idopgsqlconnection.hpp @@ -101,7 +101,8 @@ private: void InternalCleanUpExecuteQuery(const String& table, const String& time_key, double time_value); virtual void ClearConfigTable(const String& table) override; - void ClearCustomVarTable(const String& table); + void ClearTableBySession(const String& table); + void ClearTablesBySession(void); void ExceptionHandler(boost::exception_ptr exp); diff --git a/lib/db_ido_pgsql/schema/pgsql.sql b/lib/db_ido_pgsql/schema/pgsql.sql index 1f524a0df..7f63fbc5c 100644 --- a/lib/db_ido_pgsql/schema/pgsql.sql +++ b/lib/db_ido_pgsql/schema/pgsql.sql @@ -652,6 +652,7 @@ CREATE TABLE icinga_hostgroup_members ( instance_id bigint default 0, hostgroup_id bigint default 0, host_object_id bigint default 0, + session_token INTEGER default NULL, CONSTRAINT PK_hostgroup_member_id PRIMARY KEY (hostgroup_member_id) ) ; @@ -1146,6 +1147,7 @@ CREATE TABLE icinga_servicegroup_members ( instance_id bigint default 0, servicegroup_id bigint default 0, service_object_id bigint default 0, + session_token INTEGER default NULL, CONSTRAINT PK_servicegroup_member_id PRIMARY KEY (servicegroup_member_id) ) ; @@ -1670,10 +1672,6 @@ CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1); -- #4985 CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id); --- #10436 -CREATE INDEX cv_session_del_idx ON icinga_customvariables (session_token); -CREATE INDEX cvs_session_del_idx ON icinga_customvariablestatus (session_token); - -- #10070 CREATE INDEX idx_comments_object_id on icinga_comments(object_id); CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); @@ -1691,6 +1689,17 @@ CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); +-- #12210 +CREATE INDEX idx_hg_session_del ON icinga_hostgroup_members (session_token); +CREATE INDEX idx_sg_session_del ON icinga_servicegroup_members (session_token); +CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (session_token); + +DROP INDEX cv_session_del_idx; +DROP INDEX cvs_session_del_idx; + +CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token); +CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token); + -- #12107 CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql index 25e0bb968..db05ca4f6 100644 --- a/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql +++ b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql @@ -38,6 +38,24 @@ CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); +-- ----------------------------------------- +-- #12210 +-- ----------------------------------------- + +ALTER TABLE icinga_hostgroup_members ADD COLUMN session_token INTEGER default NULL; +ALTER TABLE icinga_servicegroup_members ADD COLUMN session_token INTEGER default NULL; +ALTER TABLE icinga_contactgroup_members ADD COLUMN session_token INTEGER default NULL; + +CREATE INDEX idx_hg_session_del ON icinga_hostgroup_members (session_token); +CREATE INDEX idx_sg_session_del ON icinga_servicegroup_members (session_token); +CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (session_token); + +DROP INDEX cv_session_del_idx; +DROP INDEX cvs_session_del_idx; + +CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token); +CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token); + -- ----------------------------------------- -- #12107 -- ----------------------------------------- -- 2.40.0