IcingaDB 1.2.0 - schema upgrade fails

Icinga 2 Icinga DB
1

Hello,
after recklessly running OS update on my Icinga master running on Ubuntu 22.04.03 using “apt upgrade” I realised a minor update for Icinga was released(Releasing Icinga DB 1.2.0 and Icinga DB Web 1.1.2). It includes an update to IcingaDB which requires a schema update to 1.2.0 - I am coming from 1.1.1 .
Sadly the schema update fails:
mysql -u root -p icingadb < 1.2.0.sql
ERROR 1061 (42000) at line 3: Duplicate key name ‘idx_history_event_time_event_type’

What is causing this issue ? How can it be solved ?

Any hint appreciated - atm my icingadb is down :frowning:

Thanks in advance.
Kind regards,
Jan

Update: I managed to manually fix this by dropping the index and rerunning the script.
Dunno the impact, but icingadb is running now.

Could sb comment on impact of my workaround ?

2

That should be fine. I’m wondering how this happened though. Did you experience performance issues with the history views in Icinga Web before and already added that index earlier yourself? Or did you maybe try to apply the schema upgrade a second time?

3

Thank you for reporting this issue and we apologize for the
inconvenience. However, this index should not be there if you have not
already performed the schema upgrade [1][2].

Unless you have manually created an index with this exact name, I would
guess that the upgrade script was already executed. Have you interrupted
the schema upgrade before or do you have any automation in place or did,
e.g., a college of yours did an update in parallel?

Edit: The email thingy didn’t liked my message’s format (and hold it in greylisting for quite some time).

  1. https://github.com/Icinga/icingadb/commit/2a0da3dec1f999b4e2b50284acf31eb178e41f03 ↩︎

  2. https://github.com/Icinga/icingadb/blob/v1.2.0/schema/mysql/upgrades/1.2.0.sql#L3 ↩︎

4

@jbrost @apenning Actually I am the only Admin working on the environment. I did not run the script before, at least I do not remember :wink: I wasn’t even aware an update was available. I just noticed that IcingaDB printet the warning that my database has schema “4” instead of expected “5”. Then I ran the script which caused the error message. I am sorry I can not tell you how my database got into this state.

5

Hi,
i’ve got the same error: Duplicate key name ‘idx_history_event_time_event_type’

I don’t have much knowledge in DBs. Should i use the workaround from janHH?

Update: I managed to manually fix this by dropping the index and rerunning the script.
Dunno the impact, but icingadb is running now.

Or is there a better way to repair it?

Kind regards,
Chris

6

Hi Chris,

Thanks for your post and please excuse the inconvenience.

Your solution of manually dropping the index and re-running the schema upgrade should be totally fine as no change in the schema upgrade alters data.

However, can you please provide information how you got in this situation? This would help us mitigate those issues in the future.

  • Did you manually ran the schema upgrade twice (maybe interrupting it)?
  • Which SQL server are you using?
  • Which OS (or distribution) are you using and how did you install/upgrade Icinga DB? Are you using our packages, those of your distribution or something else?

Thanks in advance.

7

Hi Alvar,
so i did an “apt-get upgrade” and installed every offered packet. I think there also some packet of an Icinga component available in your mirror.
After i did a reboot, the apache2 won’t start. Because of two mpm workers. I disabled the apache2 modules mpm_prefork and php8.1. I enabled php8.1 again. After this, I am able start Apache2 again.
On the Icingaweb, I saw the error: “It seems that Icinga DB is not running”. So I checked icingadb daemon. It was not running. When I am checkin status, I get this message “unexpected database schema version: v4 (expected v5), please make sure you have applied all database migrations after upgrading Icinga DB”. I did some research and discovered this thread.

Icinga2, version r2.14.2-1, runs on Ubuntu server 22.04.4 LTS. As DB-Server we are using “10.6.16-MariaDB-0ubuntu0.22.04.1”. The complete system was install by a friendly Netways guy with this install-script: https://github.com/NETWAYS/icinga-installer
If you need details, I think you can check ticket [netways #803704].

Do you need more Information?

Kind regards,
Chris

8

Hi Alvar,

i am looking for the right command to drop the index. I found the syntax:


DROP INDEX [IF EXISTS] index_name ON tbl_name

[WAIT n |NOWAIT]

But, where do i get the right index_name? I guess this is the right index name: idx_history_event_time_event_type right?

Which table got the problem? I don’t see any table name in the error message.

Thank you for your help,
Chris

9

I am sorry to hear about the issues with the Apache 2. However, those shouldn’t be (directly) related as neither Icinga DB nor Icinga DB Web has a direct Apache 2 dependency.

For future updates, please consider both the release information and the upgrading section in the documentation.

However, at least this states that no updating automation has performed a schema upgrade, which is good.

Thanks! This helps a lot.

After following the Puppet icinga-installer’s dependencies, I got to https://github.com/Icinga/puppet-icinga/blob/main/data/Debian/common.yaml, which seems to use the official Icinga repositories. Can you verify this, e.g., by apt-cache policy icingadb?

Regarding the failed schema upgrade: Have you manually executed it twice, canceled it or received some other error? Otherwise I am confused how this index was already there.

The index idx_history_event_time_event_type belongs to the history table, as written on line 3 of the schema upgrade.

The following query, which I just tested on a local testing environment, should do the trick for MySQL/MariaDB:

DROP INDEX idx_history_event_time_event_type ON history;
10 
root@karlx020:~# apt-cache policy icingadb
icingadb:
  Installed: 1.2.0-1+ubuntu22.04
  Candidate: 1.2.0-1+ubuntu22.04
  Version table:
 *** 1.2.0-1+ubuntu22.04 500
        500 https://packages.icinga.com/ubuntu icinga-jammy/main amd64 Packages
        100 /var/lib/dpkg/status
     1.1.1-1+ubuntu22.04 500
        500 https://packages.icinga.com/ubuntu icinga-jammy/main amd64 Packages
     1.1.0-1+ubuntu22.04 500
        500 https://packages.icinga.com/ubuntu icinga-jammy/main amd64 Packages
     1.0.0-1.jammy 500
        500 https://packages.icinga.com/ubuntu icinga-jammy/main amd64 Packages
root@karlx020:~#
11

As far as i can remember, i only did apt-get upgrade. Troubleshoot the apache-problem, saw the icingadb-problem, saw this post and tried command:

mysql icingadb < /usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql
12

I went to icingadb:

MariaDB [(none)]> USE icingadb;

Droped the Index:

MariaDB [icingadb]> DROP INDEX idx_history_event_time_event_type ON history;
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

and ran the import again:

root@karlx020:~# mysql -u root icingadb < /usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql
ERROR 1091 (42000) at line 4: Can't DROP INDEX `idx_history_event_time`; check that it exists
root@karlx020:~#
13

Thanks for the information. As written above, I cannot think of a reason how one ends up in this state without running the schema upgrade twice.

As long as your setup is now working, I would leave it this way.
However, if this happens to other people, please feel free to post it here.

For future schema upgrades, we should consider the case of an already existing index, even if it is not straight forward for MySQL, https://dba.stackexchange.com/questions/24531/mysql-create-index-if-not-exists.

14

Oh I droped idx_history_event_time in table history.
the skript wants to drop it too and exit because i killed it before…

should i delete Line4 in the script?

15

In this case, this is kinda fine as you have already executed the schema upgrade - or at least partially.

Please execute the schema upgrade 1.2.0.sql manually line-by-line in your SQL shell. As you have a partially applied schema upgrade, I am not quite sure in which state your setup currently is.

EDIT:

Yes, as I just wrote. As it seems, the community forum is not the ideal place for (almost) real time communication. (If you’re looking for this, there’s the comfy #Icinga IRC channel on the libera network.)

16

I did it. it’s alive!

Looks like it works. No errors after restarting icingadb. No errors on the website.

Thank you for your patience.

1 Like