2.10. Upgrading IDOUtils Database
Prev | Chapter 2. Getting Started | Next

## 2.10. Upgrading IDOUtils Database

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.

| Note
Starting with 1.8, you will find newly added config options in version diffed config files in `module/idoutils/config/updates` e.g. `ido2db.cfg_added_1.7_to_1.8.cfg`.

| Note
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/`. The syntax is as follows:

`<rdbm>-upgrade-<version>.sql`

where <rdbm> could be mysql, pgsql or oracle and <version> points to the version you want to upgrade to.

| Note
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.

1. Backup your current database before upgrading!

2. Check current IDOUtils version and the target version. Check if there are any version in between and upgrade incremental if necessary.

3. 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

       $ mysql -u root -p <dbname> < /path/to/icinga-src/module/idoutils/db/mysql/mysql-upgrade-<version>.sql

- Postgresql

       # su - postgres
       $ psql -U icinga -d icinga < /path/to/icinga-src/module/idoutils/db/pgsql/pgsql-upgrade-<version>.sql

- Oracle

       # su - oracle
       $ sqlplus dbuser/dbpass
       SQL> @oracle-upgrade-<version>.sql

### 2.10.1. Upgrading IDOUtils to 1.8

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.

### 2.10.2. Upgrading IDOUtils to 1.7

Starting with Icinga 1.7 the location of the idomod shared library changed from `$bindir/idomod.o` to `$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 definition in `module/idomod.cfg` to reflect the changes. The old file `idomod.o` will 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.

### 2.10.3. Upgrading IDOUtils to 1.5

- Oracle

  | Note
  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.

  Modify `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';

  | Note
  More detailed instructions using IDOUtils with Oracle you can find in our wiki howto series about Oracle.

### 2.10.4. Upgrading IDOUtils to 1.4

- 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 all defines.

    Actions:

    - remove number limitations

    - drop most of existing NOT NULL constraints

    - label constraints

    - recreate index and LOBs in new tablespaces

    - set sequences NOCACHE

    - changes Oracle functions to trap NO_DATA exceptions

  | Caution
  Upgrading IDOUtils for Oracle to 1.4 requires some kind of "magic". Make sure to copy the complete `upgrade` folder edit `oracle-upgrade-1.4.0.sql` and set the tablespaces for DATA, LOB and IXT run the upgrade script

### 2.10.5. Upgrading IDOUtils to 1.3

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;

| Note
The Oracle tablename is "dbversion" (instead of "icinga_dbversion").

### 2.10.6. >Upgrading IDOUtils to 1.0.3

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.

### 2.10.7. Upgrading IDOUtils to 1.0.2

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.

* <rdbms>-upgrade-1.0.2-fix-object-relations.sql

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.

* mysql-upgrade-1.0.2.sql

### 2.10.8. Upgrading IDOUtils to 1.0.1

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:

- MySQL

  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)

- PostgreSQL

  The systemcommands table was missing the column named output. This will be added during upgrading.

- Oracle

  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.

### 2.10.9. Upgrading IDOUtils to 1.0

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:

- timedevents, timedeventqueue

- servicechecks

- systemcommands

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.

Prev | Up | Next
2.9. Upgrading (to) Icinga | Home | 2.11. Monitoring Overview

© 2009-2012 Icinga Development Team, http://www.icinga.org