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.
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.
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
$ 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
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.
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
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.
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 incremential 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-2010 Icinga Development Team, http://www.icinga.org