1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
-- -----------------------------------------
-- upgrade path for Icinga 2.11.0
--
-- -----------------------------------------
-- Copyright (c) 2019 Icinga Development Team (https://icinga.com/)
--
-- Please check https://docs.icinga.com for upgrading information!
-- -----------------------------------------
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- --------------------------------------------------------
-- Helper functions and procedures for DROP INDEX IF EXISTS
-- --------------------------------------------------------
DELIMITER //
DROP FUNCTION IF EXISTS ido_index_exists //
CREATE FUNCTION ido_index_exists(
f_table_name varchar(64),
f_index_name varchar(64)
)
RETURNS BOOL
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE index_exists BOOL DEFAULT FALSE;
SELECT EXISTS (
SELECT 1
FROM information_schema.statistics
WHERE table_schema = SCHEMA()
AND table_name = f_table_name
AND index_name = f_index_name
) INTO index_exists;
RETURN index_exists;
END //
DROP PROCEDURE IF EXISTS ido_drop_index_if_exists //
CREATE PROCEDURE ido_drop_index_if_exists (
IN p_table_name varchar(64),
IN p_index_name varchar(64)
)
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
IF ido_index_exists(p_table_name, p_index_name)
THEN
SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`');
PREPARE stmt FROM @ido_drop_index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @ido_drop_index_sql = NULL;
END IF;
END //
DELIMITER ;
CALL ido_drop_index_if_exists('icinga_commands', 'commands_i_id_idx');
CALL ido_drop_index_if_exists('icinga_comments', 'idx_comments_object_id');
CALL ido_drop_index_if_exists('icinga_comments', 'comments_i_id_idx');
CALL ido_drop_index_if_exists('icinga_configfiles', 'configfiles_i_id_idx');
CALL ido_drop_index_if_exists('icinga_contactgroups', 'contactgroups_i_id_idx');
CALL ido_drop_index_if_exists('icinga_contacts', 'contacts_i_id_idx');
CALL ido_drop_index_if_exists('icinga_customvariables', 'idx_customvariables_object_id');
CALL ido_drop_index_if_exists('icinga_eventhandlers', 'eventhandlers_i_id_idx');
CALL ido_drop_index_if_exists('icinga_hostdependencies', 'hostdependencies_i_id_idx');
CALL ido_drop_index_if_exists('icinga_hostescalations', 'hostesc_i_id_idx');
CALL ido_drop_index_if_exists('icinga_hostescalation_contacts', 'hostesc_contacts_i_id_idx');
CALL ido_drop_index_if_exists('icinga_hostgroups', 'hostgroups_i_id_idx');
CALL ido_drop_index_if_exists('icinga_hosts', 'host_object_id');
CALL ido_drop_index_if_exists('icinga_hosts', 'hosts_i_id_idx');
CALL ido_drop_index_if_exists('icinga_objects', 'objects_objtype_id_idx');
CALL ido_drop_index_if_exists('icinga_programstatus', 'programstatus_i_id_idx');
CALL ido_drop_index_if_exists('icinga_runtimevariables', 'runtimevariables_i_id_idx');
CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'scheduleddowntime_i_id_idx');
CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'idx_scheduleddowntime_object_id');
CALL ido_drop_index_if_exists('icinga_serviceescalations', 'serviceesc_i_id_idx');
CALL ido_drop_index_if_exists('icinga_serviceescalation_contacts', 'serviceesc_contacts_i_id_idx');
CALL ido_drop_index_if_exists('icinga_servicegroups', 'servicegroups_i_id_idx');
CALL ido_drop_index_if_exists('icinga_services', 'services_i_id_idx');
CALL ido_drop_index_if_exists('icinga_services', 'service_object_id');
CALL ido_drop_index_if_exists('icinga_systemcommands', 'systemcommands_i_id_idx');
CALL ido_drop_index_if_exists('icinga_timeperiods', 'timeperiods_i_id_idx');
DROP FUNCTION ido_index_exists;
DROP PROCEDURE ido_drop_index_if_exists;
-- -----------------------------------------
-- set dbversion (same as 2.11.0)
-- -----------------------------------------
INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW();
|
