How to upgrade Icinga2 from 2.3.4 to 2.5 (or 2.6)?

This forum was archived to /woltlab and is now in read-only mode.
  • HI All,
    I have Icinga 2.3.4 running with Icinga2Web, IDO and Director on CentOS 6.8. I'm a lightweight using Linux but I can usually find my way around.


    I want to upgrade to Icinga2 v2.5 (or 2.6, see below) but the only thing I can find in the Icinga docs is this:
    "Upgrading Icinga 2 is usually quite straightforward. Ordinarily the only manual steps involved are scheme updates for the IDO database."


    I can't find any instructions on how to actually upgrade it. Is it as easy as this?
    - Stop Icinga2, Mysql (and Apache?)
    - yum upgrade icinga2 to apply schema changes
    - Run the mysql script
    - Start everything back up?


    Please let me know if I've missed anything.


    Also, I see the current release version installed by yum is 2.6. I've read of some stability issues with this version. Is your suggestion to upgrade to 2.5 or 2.6?


    Thanks so much for any help!

  • Only the most recent version of icinga 2 is supported, i.e. there is no lts, which is one of the reasons why we recommend always using the most recent version of Icinga 2.


    Regarding the upgrade procedure, it is as simple as it sounds. But keep in mind that the schema upgrades are incremental (run the 2.4 upgrade, then the 2.5 etc.)

  • Last things first:


    If you do not have a complex setup with lots of satellites / clients or a load sharing setup then i can say that 2.6 is stable for me (running one master 2 clients and 1 satellite; Master is linux; Rest is Windows).


    I would suggest:

    • Backup the complete machine, do not forget an sql dump.
    • Stop icinga2 but let mysqld run.
    • Do a yum check-update and see what icinga related packages come out.
    • Do your yum update icinga2 [other related packages follow] and see if that works.
    • I have been told that with RHEL / CENTOS there *may* be dependency problems, so if you get that i would rpm -e these rpms followed by a rpm install packagename
    • Do your mysql database schema update then: https://docs.icinga.com/icinga…hapter/upgrading-icinga-2 step by step 2.4 to 2.5 to 2.6


    • restart icinga2
    • Do not forget to update icingaweb2 to 2.4 as that is needed by icinga 2.6.
  • I've started the upgrade.
    I used yum to upgrade Icinga2.
    Now the /usr/share/icinga2-ido-mysql/schema/upgrade/ directory contains the following files (all timestamped Dec 13 05:34):


    2.0.2.sql
    2.1.0.sql
    2.2.0.sql
    2.3.0.sql
    2.4.0.sql
    2.5.0.sql
    2.6.0.sql
    mysql.sql


    I have a single record in the icinga_dbversion table that contains 1.14.1 in the Version column.


    I'm upgrading from Icinga2 v 2.3.4 to 2.6. I assumed I need to run the following scripts:
    2.4.0.sql
    2.5.0.sql
    2.6.0.sql


    Is that correct?


    The reason I ask is that 2.4.0.sql sets the dbversion value to 1.14.0 and 2.5.0 sets it to 1.14.1. This seems to imply that these scripts have already been run and that I should just run 2.6.0.sql. Spot checking the tables vs the scripts it appears that 2.5.0 has been run because the icinga_commands table already has the config_hash column which is added by 2.5.0.sql.


    Question 1:
    Can you give me some guidance on which script(s) to run?


    Question 2:
    I am getting this error when attempting to run the scripts: "SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALTER TABLE icinga_acknowledgements
    MODIFY COLUMN entry_time timestamp NULL D' at line 14"


    Line 140is the first exectable line (i.e. non comment) contains this:
    ALTER TABLE icinga_acknowledgements
    MODIFY COLUMN entry_time timestamp NULL DEFAULT NULL,
    MODIFY COLUMN end_time timestamp NULL DEFAULT NULL;


    Do you have any guidance on this Mysql error?


    Thanks!

  • Using my 2.6.0-1, i reapplied 2.4.0, 2.5.0, 2.6.0 for you. That is what came out:

    1. Assuming that you *have* a mariadb backup dump, i would suggest that.
    2. Entered that command in a mysql shell and it worked out great. Would suggest to do the same and re-apply 2.6.0 but can not reproduce that.

  • Thanks for all the help. It seems to be running fine now. I am a little worried because the only sql script I ran was 2.6.0 because the other changes seemed to already be in the db and the db version matched the version in the script named 2.5.0 even though I did not run that script as part of this upgrade.


    I suppose it's possible I ran it in the past but I'm not sure how I would have gotten it since the version I was running was 2.3.4.


    So, I will keep an eye on it to see if any errors are produced and I'll read through every modification in the earlier sql scripts to make sure every one really has been applied.


    Is there a command (CLI maybe?) or script that can verify the db structure against the requirements of the current version?


    Thanks again to all.

  • Let us do that together.


    Concept:
    I created a dump of the structure 2.6.0 from my production environment for you (see below attachment). Alternatively, you may use /usr/share/icinga2-ido-mysql/schema/mysql.sql if that is more comfortable for you.

    • You will need to create that same dump on your machine.
    • To be able to compare both files, it is needed that you create a test database and import the below structure to it.
    • Next, you dump that test-database and now have 2 database dumps (production and test) that can be compared.


    Step by Step:

    • copy below file to the linux machine (winscp might be comfortable)
    • run the following commands (you can copy / paste that into a script and run it):

    That script produces:


    You see that the only differences are comments and autoincrement counters.
    Hope that helps.

  • Worked like a charm! Most of the differences in my output were comments and auto increment counters.


    There are three instances below where my structure has AUTO_INCREMENT and the base 2.6 structure does not:


    Code
    1. 41c41< ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='Current and historical host and service acknowledgements';---> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Current and historical host and service acknowledgements';
    2. 95c95< ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='Historical host and service comments';---> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Historical host and service comments';
    3. 610c610< ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Current and historical record of host and service flapping';---> ) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1 COMMENT='Current and historical record of host and service flapping';


    Is that an issue?



    Thanks again for your help!


    Jim

  • if you look into mysql.sql, you see that it has none of these either, but the resulting production database shows autoincrement values once these tables hold records.


    So: No, that is not an issue.

  • Hi all,


    This is more of any information post for anyone wondering about the updating process of Icinga2 and seeing whether some other people run into the same issues.


    I was having some trouble upgrading Icinga2 v2.3.4 to v2.6.x.


    I had some problems with upgrading the IDO-mysql database. Without this upgrade, I couldn't start Icinga2 and the log showed this:


    Code
    1. [2017-02-09 11:34:01 -1000] information/DbConnection: Resuming IDO connection: ido-mysql
    2. [2017-02-09 11:34:01 -1000] critical/IdoMysqlConnection: Schema version '1.13.0' does not match the required version '1.14.2' (or newer)! Please check the upgrade documentation.
    3. Context:
    4. (0) Reconnecting to MySQL IDO database 'ido-mysql'


    I followed this guide. As discussed in the guide, I have the following upgrade files in the /schema/upgrade/ folder:


    2.0.2.sql

    2.1.0.sql

    2.2.0.sql

    2.3.0.sql

    2.4.0.sql

    2.5.0.sql

    2.6.0.sql (this one appeared after running 'yum update')


    When I try to run this command: mysql -u root -p icinga2 < /usr/share/icinga2-ido-mysql/schema/upgrade/2.0.2.sql, I enter my password but get no output.


    I try using the other upgrade files and get mixed results:


    - using 2.1.0.sql, I receive this error: ERROR 1060 (42S21) at line 10: Duplicate column name 'endpoint_name'

    - using 2.2.0.sql, I receive this error: ERROR 1060 (42S21) at line 10: Duplicate column name 'program_version'

    - using 2.3.0 sql, I receive no output.

    - using 2.4.0 sql, I receive this error: ERROR 1060 (42S21) at line 10: Duplicate column name 'zone_object_id'

    - using 2.5.0 sql, I receive this error: ERROR 1060 (42S21) at line 10: Duplicate column name 'idx_comments_object_id'

    - using 2.6.0 sql, I receive a long pause, then no output.


    So, I tried to upgrade the mysql database incrementally, but it didn't seem to work. After running the command using the 2.6.0 sql file, I attempted to restart Icinga2 and it worked.


    My question is why did this work? It showed (in the log above) that my schema version was 1.13.0, and I seemingly jumped to 2.6.0.


    Since this worked, I also am wondering why you would have to run the upgrades incrementally as well.

  • Quote from the docs: Example:You are upgrading Icinga 2 from version 2.0.2 to 2.3.0

    Quotes from above: upgrading Icinga2 v2.3.4 to v2.6.x and When I try to run this command: ... schema/upgrade/2.0.2.sql


    Trying to downgrade the schema to 2.0.2,2.1.0,2.2.0 was clearly not what was described in the docs, it was wrong.


    Quotes from above: After running the command using the 2.6.0 sql file, I attempted to restart Icinga2 and it worked

    You are lucky that it did.

    If i recognize that i make a perfect mess, it is time to restore the database from a backup and start over with upgrading a consistent state.


    Incremental upgrades are done to ensure that existing data will be transformed into the new schema. It is important that this is done

    step by step with the correct starting point, with no leftouts and to the correct endpoint to let this work.


    The file mysql.sql in contrast is meant to init a fresh database, it does not need to care about preserving or transforming data.

    To put it another way: If you are fine with loosing all your data but want your system back running asap , mysql.sql might be a chance.


    To be honest, that goes for every kind of relational database:

    • Have backups and know how to restore them
    • Apply each transformation set carefully, perhaps create intermediate backups
    • If problems arise, do *not* blindly continue.
    • Get support and fix the issue until that special step was successfull or
    • Restore the last state from the intermediate backup and re-apply the problematic transformation.
    • Do not care about migration time. Users might start howling but they will calm down. But *be* sure the job is done.


    • With lost data, there is no "tomorrow is another day". You will be blamed for a lifetime.
  • I took a snapshot of the VM before proceeding with updating the schema, but I do not see the need to do so yet.


    Everything seems to work fine. All my information still exists and is functioning correctly. Since I have verified this, what problems could possibly arise?

  • We compared the structure of what is running at your machine with the structure that mysql.sql creates and found no differences.

    The only problem that *could * have arrised would have been a data loss.

    If you are fine after that long time, you should not run into any problems.


    In #12 you asked "Why did that worked" and i answered that in #13.

    I took a snapshot of the VM before proceeding with updating the schema, but I do not see the need to do so yet

    Do at least weekly mysqldump's of the icinga database and backup the relevant folders in the file system.

    Everybody running icinga2 should do that to ensure that the monitoring system can be restored if the need arrises.