From 9d97094390d651df3c883ed39befca2ca02dfd5f Mon Sep 17 00:00:00 2001 From: Michael Friedrich Date: Thu, 12 Feb 2015 13:16:30 +0100 Subject: [PATCH] DB IDO: Fix duplicate entries in {host,service}depencencies table fixes #7765 --- doc/1-about.md | 3 ++- doc/14-upgrading-icinga-2.md | 15 ++++++------- lib/db_ido_mysql/schema/mysql.sql | 6 +++--- lib/db_ido_mysql/schema/upgrade/2.3.0.sql | 26 +++++++++++++++++++++++ lib/db_ido_pgsql/schema/pgsql.sql | 6 +++--- lib/db_ido_pgsql/schema/upgrade/2.3.0.sql | 26 +++++++++++++++++++++++ 6 files changed, 67 insertions(+), 15 deletions(-) create mode 100644 lib/db_ido_mysql/schema/upgrade/2.3.0.sql create mode 100644 lib/db_ido_pgsql/schema/upgrade/2.3.0.sql diff --git a/doc/1-about.md b/doc/1-about.md index 6cfbc3e9e..5c5b78ef4 100644 --- a/doc/1-about.md +++ b/doc/1-about.md @@ -55,9 +55,10 @@ More details in the [Icinga FAQ](https://www.icinga.org/icinga/faq/). #### Changes +* [DB IDO schema upgrade](14-upgrading-icinga-2.md#upgrading-icinga-2) to `1.13.0` required! + TODO #### Issues TODO - diff --git a/doc/14-upgrading-icinga-2.md b/doc/14-upgrading-icinga-2.md index 329805238..c96745057 100644 --- a/doc/14-upgrading-icinga-2.md +++ b/doc/14-upgrading-icinga-2.md @@ -1,4 +1,4 @@ -# Upgrading Icinga 2 +# Upgrading Icinga 2 Upgrading Icinga 2 is usually quite straightforward. Ordinarily the only manual steps involved are scheme updates for the IDO database. @@ -20,13 +20,13 @@ The Icinga 2 DB IDO module will check for the required database schema version o and generate an error message if not satisfied. -**Example:** You are upgrading Icinga 2 from version `2.0.2` to `2.2.0`. Look into +**Example:** You are upgrading Icinga 2 from version `2.0.2` to `2.3.0`. Look into the *upgrade* directory: $ ls /usr/share/icinga2-ido-mysql/schema/upgrade/ - 2.0.2.sql 2.1.0.sql 2.2.0.sql + 2.0.2.sql 2.1.0.sql 2.2.0.sql 2.3.0.sql -There are two new upgrade files called `2.1.0.sql` and `2.2.0.sql` +There are two new upgrade files called `2.1.0.sql`, `2.2.0.sql` and `2.3.0.sql` which must be applied incrementially to your IDO database. ## Upgrading the PostgreSQL database @@ -46,12 +46,11 @@ Apply all database schema upgrade files incrementially. The Icinga 2 DB IDO module will check for the required database schema version on startup and generate an error message if not satisfied. -**Example:** You are upgrading Icinga 2 from version `2.0.2` to `2.1.0`. Look into +**Example:** You are upgrading Icinga 2 from version `2.0.2` to `2.3.0`. Look into the *upgrade* directory: $ ls /usr/share/icinga2-ido-pgsql/schema/upgrade/ - 2.0.2.sql 2.1.0.sql 2.2.0.sql + 2.0.2.sql 2.1.0.sql 2.2.0.sql 2.3.0.sql -There are two new upgrade files called `2.1.0.sql` and `2.2.0.sql` +There are two new upgrade files called `2.1.0.sql`, `2.2.0.sql` and `2.3.0.sql` which must be applied incrementially to your IDO database. - diff --git a/lib/db_ido_mysql/schema/mysql.sql b/lib/db_ido_mysql/schema/mysql.sql index 9cbaed26b..ea9a4adf6 100644 --- a/lib/db_ido_mysql/schema/mysql.sql +++ b/lib/db_ido_mysql/schema/mysql.sql @@ -535,7 +535,7 @@ CREATE TABLE IF NOT EXISTS icinga_hostdependencies ( fail_on_down smallint default 0, fail_on_unreachable smallint default 0, PRIMARY KEY (hostdependency_id), - UNIQUE KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable) + KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable) ) ENGINE=InnoDB COMMENT='Host dependency definitions'; -- -------------------------------------------------------- @@ -1026,7 +1026,7 @@ CREATE TABLE IF NOT EXISTS icinga_servicedependencies ( fail_on_unknown smallint default 0, fail_on_critical smallint default 0, PRIMARY KEY (servicedependency_id), - UNIQUE KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical) + KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical) ) ENGINE=InnoDB COMMENT='Service dependency definitions'; -- -------------------------------------------------------- @@ -1603,6 +1603,6 @@ CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, co -- ----------------------------------------- -- set dbversion -- ----------------------------------------- -INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.12.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.12.0', modify_time=NOW(); +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.13.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.13.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.3.0.sql b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql new file mode 100644 index 000000000..4ba629ca1 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql @@ -0,0 +1,26 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.3.0 +-- +-- ----------------------------------------- +-- Copyright (c) 2015 Icinga Development Team (http://www.icinga.org) +-- +-- Please check http://docs.icinga.org for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #7765 drop unique constraint +-- ----------------------------------------- + +ALTER TABLE icinga_servicedependencies DROP KEY instance_id; +ALTER TABLE icinga_hostdependencies DROP KEY instance_id; + +ALTER TABLE icinga_servicedependencies ADD KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); +ALTER TABLE icinga_hostdependencies ADD KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); + + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.13.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.13.0', modify_time=NOW(); + diff --git a/lib/db_ido_pgsql/schema/pgsql.sql b/lib/db_ido_pgsql/schema/pgsql.sql index 6ce044654..2dfb9d6ae 100644 --- a/lib/db_ido_pgsql/schema/pgsql.sql +++ b/lib/db_ido_pgsql/schema/pgsql.sql @@ -562,8 +562,8 @@ CREATE TABLE icinga_hostdependencies ( fail_on_down INTEGER default 0, fail_on_unreachable INTEGER default 0, CONSTRAINT PK_hostdependency_id PRIMARY KEY (hostdependency_id) , - CONSTRAINT UQ_hostdependencies UNIQUE (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable) ) ; +CREATE INDEX idx_hostdependencies ON icinga_hostdependencies(instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); -- -------------------------------------------------------- @@ -1053,8 +1053,8 @@ CREATE TABLE icinga_servicedependencies ( fail_on_unknown INTEGER default 0, fail_on_critical INTEGER default 0, CONSTRAINT PK_servicedependency_id PRIMARY KEY (servicedependency_id) , - CONSTRAINT UQ_servicedependencies UNIQUE (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical) ) ; +CREATE INDEX idx_servicedependencies ON icinga_servicedependencies(instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); -- -------------------------------------------------------- @@ -1633,5 +1633,5 @@ CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, co -- set dbversion -- ----------------------------------------- -SELECT updatedbversion('1.12.0'); +SELECT updatedbversion('1.13.0'); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql new file mode 100644 index 000000000..d62327399 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql @@ -0,0 +1,26 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.3.0 +-- +-- ----------------------------------------- +-- Copyright (c) 2015 Icinga Development Team (http://www.icinga.org) +-- +-- Please check http://docs.icinga.org for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #7765 drop unique constraint +-- ----------------------------------------- + +ALTER TABLE icinga_servicedependencies DROP CONSTRAINT uq_servicedependencies; +ALTER TABLE icinga_hostdependencies DROP CONSTRAINT uq_hostdependencies; + +CREATE INDEX idx_servicedependencies ON icinga_servicedependencies(instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); +CREATE INDEX idx_hostdependencies ON icinga_hostdependencies(instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); + + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.13.0'); + -- 2.40.0