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 -u root -p <dbname> < /path/to/icinga-src/module/idoutils/db/mysql/mysql-upgrade-<version>.sqlPostgresql
# su - postgres $ psql -U icinga -d icinga < /path/to/icinga-src/module/idoutils/db/pgsql/pgsql-upgrade-<version>.sqlOracle
# su - oracle $ sqlplus dbuser/dbpass SQL> @oracle-upgrade-<version>.sqlUpgrading 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:
MySQLChange 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.
OracleFirst 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 seperated 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.0There 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