There may be a bug within the database scheme which has been fixed. If you are upgrading from an older IDOUtils version you also need to apply those fixes manually. If you are using rpm/deb packages please read the notes and/or ask the maintainer if he has added those modifications to the install routine.
Starting with 1.8, you will find newly added config options in version diffed config files in
Depending on the changes to be done and the size of your database it may take a while to update your database. Please try to be patient and don't abort the script as it may leave the data being corrupt.
The upgrade files can be found next to the database install files in
/path/to/icinga-src/module/idoutils/db/yourrdbm/upgrade/. The syntax is as follows:
where <rdbm> could be mysql, pgsql or oracle and <version> points to the version you want to upgrade to.
If you are upgrading from an older version and there are other versions in between be advised that you need to apply those upgrade files with incremental steps!
E.g. you have 1.0RC1 installed and want to upgrade to 1.0.1 - you will have to upgrade to 1.0 Stable first and then apply the upgrade to 1.0.1.
Backup your current database before upgrading!
Check current IDOUtils version and the target version. Check if there are any version in between and upgrade incremental if necessary.
Apply the upgrade(s) using a rdbm user with appropriate rights. You may use the upgradedb script, but this is not recommended (for MySQL only).
$ mysql -u root -p <dbname> < /path/to/icinga-src/module/idoutils/db/mysql/upgrade/mysql-upgrade-<version>.sql
# su - postgres $ psql -U icinga -d icinga < /path/to/icinga-src/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-<version>.sql
# su - oracle $ cd /path/to/icinga-src/module/idoutils/db/oracle/upgrade $ sqlplus dbuser/dbpass SQL> @oracle-upgrade-<version>.sql
Starting with 1.9, the directive
ido2db.cfg is deprecated.
IDOUtils introduces a socket queue and transactions for large object dumps in order to address core reload problems.
Please have a look at the section "CHANGES" in the
changelog as well.
There are some important fixes regarding on redundant indexes in MySQL, as well as missing indexes for proper performance (e.g. Icinga Web). Furthermore, there's a new column for the programstatus table for the new global disable notification expire time.
Please have a look at the section "CHANGES" in the
changelog as well.
Starting with Icinga 1.7 the location of the idomod shared library changed from
$libdir/idomod.so. Please note that the extension ".o" changed to ".so" as
well. Depending on the method you are using you have to alter the "broker_module" entry in
icinga.cfg OR the module
module/idomod.cfg to reflect the changes. The old file
be removed if found. You will receive a warning during "make install-idoutils" about this change.
Please have a look at the section "CHANGES for users" in the
changelog as well.
Using IDOUtils 1.5.0 with Oracle requires at least OCILIB 3.9.2 - don't install 3.9.0 or 3.9.1 as they remain buggy.
oracle-upgrade-1.5.0.sql and set the tablespaces for DATA, LOB, and IXT similar to the following
lines. You can provide your existing tablespace names for all defines.
/* set real TBS names on which you have quota, no checks are implemented!*/ define DATATBS='icinga'; define LOBTBS='icinga'; define IXTBS='icinga';
More detailed instructions using IDOUtils with Oracle you can find in our wiki howto series about Oracle.
The minimum supported version is Oracle 10g R2. Older versions may still work.
The optional separation of data, index and lob tablespaces is introduced. Modify
oracle-upgrade-1.4.0.sql and define your tablespaces. You can provide your existing tablespace names for
remove number limitations
drop most of existing NOT NULL constraints
recreate index and LOBs in new tablespaces
set sequences NOCACHE
changes Oracle functions to trap NO_DATA exceptions
Upgrading IDOUtils for Oracle to 1.4 requires some kind of "magic". Make sure to
IDOUtils 1.3 re-introduces the usage of the dbversion table in IDOUtils schema. The upgrade scripts will make sure that dbversion holds the current version while ido2db compares program version with database version and will print an error into syslog if not matching.
Just apply the upgrading script for IDOUtils 1.3 in
module/idoutils/db/<rdbms>/<rdbm>-upgrade-1.3.sql against your current schema (using incremental steps as
usual, not leaving an upgrade version behind!).
After you finished the upgrade you should check the database version.
SQL> SELECT * FROM icinga_dbversion;
The Oracle tablename is "dbversion" (instead of "icinga_dbversion").
There were some few minor changes:
change display_name to varchar(255) for mysql/oracle
update pgsql schema, replace varchar(n) by text
change configfile variables value to 1024 length for mysql/oracle
Apply the upgrading scripts for IDOUtils 1.0.3 as the general procedure applies for your RDBMS.
There was a significant long lasting bug in IDOUtils which is now resolved in Icinga 1.0.2: Everytime the core was restarted, the overall amount of objects was freshly inserted instead of using the old ones for actual config, status and historical relations. E.g. having 4k objects (hosts, services, contacts, etc), that meant restarting the core twice resulted in 4k + 4k + 4k = 12k objects.
For config and live status data, this is by means not really important as their relations normally get cleaned when the core gets restarted. But for historical data, e.g. hostchecks before the restart were in another relation than newer hostchecks after the restart. That has led into data inconsistency which has been worked on to resolve that in an easy way - next to the IDOUtils bugfix itself.
Therefore, next to the normal SQL upgrade scripts for 1.0.2 (e.g. mysql-upgrade-1.0.2.sql) an extended SQL script has been created.
It catches up on each table and object_id with a single clustered query in order to fix the relation historical table 1..1 objects table. It also cleans up broken data caused by the restarts.
Overall this has been tested and is now available for everyone to upgrade and fix those issues.
Please apply the script as you like to - directly or step by step as commented in the script. Those scripts are provided as is without any warranty so please use at your own risk - if you only depend on live data, dropping and recreating the database will take less effort.
The "normal" upgrade script only is available for MySQL - the binary casts for case sensitive comparison were removed because of massive performance issues. Instead a new collation is introduced.
Please make sure that you already have upgraded to Icinga IDOUtils 1.0 before reading this section! There have been several (big) changes made to all supported RDBMS to please be advised to read carefully! All database scripts are now reorganized within subfolders. Changes for all RDBMS are setting more indexes and also size modifications for the command_line column in several tables which exceeded 255 characters. RDBMS specific changes and howtos are listed below:
Change of the database engine from MYISAM to InnoDB. Reason for that mainly is row locks/transactions/rollbacks instead of a bit more speed at insert time.
The upgrade script performs an ALTER TABLE statement. If you don't like that idea, you can also do the following:
Dump your existing database e.g.
#> mysqldump -u root -p icinga > icinga.sql
Change all entries from "MYISAM" to "InnoDB"
Import the adapted dataset into a new database (if you want to use the old one make sure to drop that in the first place, and recreate only the database)
The systemcommands table was missing the column named output. This will be added during upgrading.
First of all, make sure that you set open_cursors to more than the default 50. This will increase performance at several stages. The upgrade scripts will add two new procedures needed for the delete stuff written in DML.
Furthermore there has been a rather huge change regarding the autoincrement sequence and after insert triggers (emulating the mysql autoincrement on primary key). The old routine has been completely dropped meaning all triggers and the autoincrement sequence will we removed during upgrading. As a replacement, sequences for each table will be added and used in current IDOUtils Oracle.
With existing data sets this will lead into problems during importing - the sequences start at 1 while the primary key (id) will have a maximum. For that reason there is a basic procedure available which does the following: By given a sequence name and a table name, it extracts the maximum id value +1 from imported data and alters the sequence to start with this value instead.
Please be advised to use that procedure yourself for all tables or on separated tables - it highly depends on your needs. The procedure is commented out, and provided as is without any warranty regarding data loss. Ask your DBA in case of upgrading with existing data.
There was a unique key failure in MySQL coming through the fork causing several tables to keep duplicated and useless rows. This concerns the following tables:
If you look at the table creation e.g. servicechecks:
mysql> show create table icinga_servicechecks;
you should see something like this
PRIMARY KEY (`servicecheck_id`), KEY `instance_id` (`instance_id`), KEY `service_object_id` (`service_object_id`), KEY `start_time` (`start_time`)
Changing this to
PRIMARY KEY (`servicecheck_id`), UNIQUE KEY `instance_id` (`instance_id`,`service_object_id`,`start_time`,`start_time_usec`)
will need your attention on the following procedure!
If you are upgrading from 1.0RC please be advised to use module/idoutils/db/mysql/mysql-upgrade-1.0.sql - if you are using an older version, please apply the incremental steps to get to 1.0RC first!
Please backup your database and stop ido2db before applying this patch!
#> /etc/init.d/ido2db stop #> mysql -u root -p icinga < /path/to/icinga-src/module/idoutils/db/mysql/mysql-upgrade-1.0.sql
The patch will do the following through MySQL statements:
add a temporary column named 'active' to mark the updated row
extract the needed information of two duplicate rows based on the unique constraint, update the second row and mark first=inactive, second=active
delete all inactive marked rows
drop false keys
add unique key
drop temporary column 'active'
This procedure will be executed for each table, so it might take quite a long time depending on your table size and/or db specs.
If you changed something on the keys before please make sure you'll have the same database scheme applied as in 1.0RC otherwise the script will fail.
© 2009-2013 Icinga Development Team, http://www.icinga.org