Error while changing priority of the Import Source Modifier

[AK3830]

Oops, an error occurred!
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "import_row_modifier_prio"
DETAIL: Key (source_id, priority)=(1, 2) already exists., query was: UPDATE import_row_modifier m SET priority = CASE WHEN id = 1 THEN 2 ELSE 1 END WHERE id IN (1, 2) AND (m.source_id = '1') (Pdo.php:225)
#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE import_r...', Array)
#3 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/Extension/ZfSortablePriority.php(163): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE import_r...')
#4 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/Extension/ZfSortablePriority.php(49): Icinga\Module\Director\Web\Table\PropertymodifierTable->moveRow(1, 'down')
#5 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/Extension/ZfSortablePriority.php(227): Icinga\Module\Director\Web\Table\PropertymodifierTable->reallyHandleSortPriorityActions()
#6 /usr/share/icingaweb2/modules/director/library/Director/Web/Table/PropertymodifierTable.php(43): Icinga\Module\Director\Web\Table\PropertymodifierTable->renderWithSortableForm()
#7 /usr/share/icingaweb2/modules/ipl/vendor/ipl/html/src/HtmlDocument.php(226): Icinga\Module\Director\Web\Table\PropertymodifierTable->render()
#8 /usr/share/icingaweb2/modules/ipl/vendor/ipl/html/src/BaseHtmlElement.php(255): ipl\Html\HtmlDocument->renderUnwrapped()
#9 /usr/share/icingaweb2/modules/ipl/vendor/ipl/html/src/BaseHtmlElement.php(339): ipl\Html\BaseHtmlElement->renderContent()
#10 /usr/share/icingaweb2/modules/ipl/vendor/ipl/html/src/HtmlDocument.php(350): ipl\Html\BaseHtmlElement->renderUnwrapped()
#11 /usr/share/icingaweb2/modules/ipl/vendor/ipl/html/src/HtmlDocument.php(252): ipl\Html\HtmlDocument->render()
#12 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Zf1/SimpleViewRenderer.php(65): ipl\Html\HtmlDocument->__toString()
#13 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Zf1/SimpleViewRenderer.php(104): gipfl\IcingaWeb2\Zf1\SimpleViewRenderer->render()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): gipfl\IcingaWeb2\Zf1\SimpleViewRenderer->postDispatch()
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#16 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('modifierAction')
#17 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#19 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#20 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#21 {main}

Your Environment

• Director version (System - About): 1.8.0 (based on Postgres SQL)
• Icinga Web 2 version and modules (System - About): 2.8.2
• Icinga 2 version (icinga2 --version): 2.12.3
• Operating System and version: SLES 12 SP5
• Webserver, PHP versions: PHP Version 7.0.7

[orbison]

I have encountered the same problem in our Icinga Director. Let me provide more details.

Expected behavior

Clicking the up or down arrows in the list of import source modifiers results in the modifier entry to move up or down the list.

Actual behavior

There are nine modifiers defined for an import source. Clicking the up or down arrow results in the following exception: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "import_row_modifier_prio" DETAIL: Key (source_id, priority)=(1, 9) already exists., query was: UPDATE import_row_modifier m SET priority = CASE WHEN id = 15 THEN 8 ELSE 9 END WHERE id IN (15, 14) AND (m.source_id = '1') (Pdo.php:225)

The problem occurs in all import sources which have modifiers defined.

General Information

Icinga 2 installed from Debian repository. Contents of /etc/apt/sources.list.d/stretch-icinga.list:

deb [signed-by=/usr/share/keyrings/icinga-archive-keyring.gpg] http://packages.icinga.com/debian icinga-stretch main
deb-src [signed-by=/usr/share/keyrings/icinga-archive-keyring.gpg] http://packages.icinga.com/debian icinga-stretch main

Version information:

$ icinga2 --version
icinga2 - The Icinga 2 network monitoring daemon (version: r2.13.0-1)

Copyright (c) 2012-2021 Icinga GmbH (https://icinga.com/)
License GPLv2+: GNU GPL version 2 or later <https://gnu.org/licenses/gpl2.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

System information:
  Platform: Debian GNU/Linux
  Platform version: 10 (buster)
  Kernel: Linux
  Kernel version: 4.9.0-15-amd64
  Architecture: x86_64

Build information:
  Compiler: GNU 6.3.0
  Build host: runner-hh8q3bz2-project-298-concurrent-0
  OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019

Application information:

General paths:
  Config directory: /etc/icinga2
  Data directory: /var/lib/icinga2
  Log directory: /var/log/icinga2
  Cache directory: /var/cache/icinga2
  Spool directory: /var/spool/icinga2
  Run directory: /run/icinga2

Old paths (deprecated):
  Installation root: /usr
  Sysconf directory: /etc
  Run directory (base): /run
  Local state directory: /var

Internal paths:
  Package data directory: /usr/share/icinga2
  State path: /var/lib/icinga2/icinga2.state
  Modified attributes path: /var/lib/icinga2/modified-attributes.conf
  Objects path: /var/cache/icinga2/icinga2.debug
  Vars path: /var/cache/icinga2/icinga2.vars
  PID path: /run/icinga2/icinga2.pid

$ icinga2 feature list
Disabled features: compatlog debuglog elasticsearch gelf icingadb influxdb influxdb2 opentsdb perfdata statusdata syslog
Enabled features: api checker command graphite ido-pgsql livestatus mainlog notification

$ icinga2 daemon -C
[2021-08-10 11:12:19 +0200] information/cli: Icinga application loader (version: r2.13.0-1)
[2021-08-10 11:12:19 +0200] information/cli: Loading configuration file(s).
[2021-08-10 11:12:19 +0200] information/ConfigItem: Committing config item(s).
[2021-08-10 11:12:19 +0200] information/ApiListener: My API identity: ICGSRV.<redacted>.cz
[2021-08-10 11:12:19 +0200] warning/ApplyRule: Apply rule 'satellite-host' (in /etc/icinga2/conf.d/satellite.conf: 29:1-29:41) for type 'Dependency' does not match anywhere!
[2021-08-10 11:12:19 +0200] warning/ApplyRule: Apply rule 'SNMPv3-based checks' (in /var/lib/icinga2/api/packages/director/e38b56cc-cbaa-4851-8310-547230b452ba/zones.d/director-global/dependency_apply.conf: 15:1-15:49) for type 'Dependency' does not match anywhere!
[2021-08-10 11:12:19 +0200] warning/ApplyRule: Apply rule '' (in /var/lib/icinga2/api/packages/director/e38b56cc-cbaa-4851-8310-547230b452ba/zones.d/director-global/service_apply.conf: 36:1-36:51) for type 'Service' does not match anywhere!
[2021-08-10 11:12:19 +0200] warning/ApplyRule: Apply rule 'SNMPv3 Agent' (in /var/lib/icinga2/api/packages/director/e38b56cc-cbaa-4851-8310-547230b452ba/zones.d/director-global/service_apply.conf: 49:1-49:28) for type 'Service' does not match anywhere!
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 13 Notifications.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 650 Dependencies.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 GraphiteWriter.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 IcingaApplication.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 2 NotificationCommands.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 21 HostGroups.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 87 Hosts.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 Downtime.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 FileLogger.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 CheckerComponent.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 ApiListener.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 77 Zones.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 ExternalCommandListener.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 75 Endpoints.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 IdoPgsqlConnection.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 2 ApiUsers.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 282 CheckCommands.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 LivestatusListener.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 NotificationComponent.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 UserGroup.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 User.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 3 TimePeriods.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 10 ServiceGroups.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 1 ScheduledDowntime.
[2021-08-10 11:12:19 +0200] information/ConfigItem: Instantiated 745 Services.
[2021-08-10 11:12:19 +0200] information/ScriptGlobal: Dumping variables to file '/var/cache/icinga2/icinga2.vars'
[2021-08-10 11:12:19 +0200] information/cli: Finished validating the configuration file(s).

Icinga Web 2 version and modules:

The list of modifiers is as follows:

An attempt to move the last modifier up results in the following exception report:

Please note that we — as well as the original reporter — are using PostgreSQL as a backend database.

PostgreSQL has logged the following lines:

2021-08-10 11:24:29.874 CEST [25030] director@director ERROR:  duplicate key value violates unique constraint "import_row_modifier_prio"
2021-08-10 11:24:29.874 CEST [25030] director@director DETAIL:  Key (source_id, priority)=(1, 9) already exists.
2021-08-10 11:24:29.874 CEST [25030] director@director STATEMENT:  UPDATE import_row_modifier m SET priority = CASE WHEN id = 15 THEN 8 ELSE 9 END WHERE id IN (15, 14) AND (m.source_id = '1')

Please check out the definitions of our import sources:
Director-Basket_Import_Souce_Modifier_Priority-Changing_Bug_Report_2021-08-10_25c4669.txt

[orbison]

Attached please find contents of import_row_modifier and import_row_modifier_setting tables.

import_row_modifier_tables.txt