duplicate key value violates unique constraint, key already exists

[mtdeguzis]

Expected Behavior

Adding / changing host defintions on Director > Assign Where clause updates fine, members tab is updated for Director > Host > Host Groups > NAME

Group creation worked fine.

Current Behavior

Adding / changing host defintions on Director > Assign Where clause shows:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "icinga_hostgroup_host_resolved_pkey" DETAIL: Key (hostgroup_id, host_id)=(13, 83) already exists., query was: INSERT INTO icinga_hostgroup_host_resolved (hostgroup_id, host_id) VALUES (?, ?) (Pdo.php:225)

DB:

director=# select * from icinga_hostgroup_host_resolved where host_id in (13,83);
 hostgroup_id | host_id
--------------+---------
           13 |      83
           29 |      13
(2 rows)

Director shows a pending change and can apply it successfully, but group members are not updated.

Steps to Reproduce (for bugs)

1. create group
2. Add clause

   host.name = host.domain.com

3. Click Store

Your Environment

• Director version (System - About): master
• Icinga Web 2 version and modules (System - About): 2.5.3
• Icinga 2 version (icinga2 --version): r2.9.0-1

  
  Application information:
  Installation root: /usr
  Sysconf directory: /etc
  Run directory: /run
  Local state directory: /var
  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

System information: Platform: Red Hat Enterprise Linux Server Platform version: 7.5 (Maipo) Kernel: Linux Kernel version: 3.10.0-693.17.1.el7.x86_64 Architecture: x86_64

Build information: Compiler: GNU 4.8.5 Build host: unknown

* Operating System and version:

System information: Platform: Red Hat Enterprise Linux Server Platform version: 7.5 (Maipo) Kernel: Linux Kernel version: 3.10.0-693.17.1.el7.x86_64 Architecture: x86_64

* Webserver, PHP versions:

rh-php71-php-fpm-7.1.8-1.el7.x86_64 [root@udaicinga sqlline]# /opt/rh/rh-php71/root/usr/sbin/php-fpm --version PHP 7.1.8 (fpm-fcgi) (built: Aug 8 2017 09:02:40) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies

[mtdeguzis]

Ran into this again when trying to clone a command:

Storing icinga_command_argument[] failed: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "icinga_command_argument_pkey" DETAIL: Key (id)=(3965) already exists., query was: INSERT INTO icinga_command_argument (command_id, argument_name, argument_value, argument_format, key_string, description, skip_key, set_if, sort_order, repeat_key, set_if_format, required) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {array ( 'id' => NULL, 'command_id' => 311, 'argument_name' => '-A', 'argument_value' => NULL, 'argument_format' => NULL, 'key_string' => NULL, 'description' => 'Explicitly select all paths. This is equivalent to -R .*', 'skip_key' => NULL, 'set_if' => '$disk_all$', 'sort_order' => '1', 'repeat_key' => NULL, 'set_if_format' => NULL, 'required' => NULL, )} (DbObject.php:832)

This happened after a database restore via pg dump backup. Anything I can do to solve this?

[mtdeguzis]

This is the solution I came across (sequence was out of sync):

Storing icinga_command_argument[] failed: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "icinga_command_argument_pkey" DETAIL: Key (id)=(3965) already exists., query was: INSERT INTO icinga_command_argument (command_id, argument_name, argument_value, argument_format, key_string, description, skip_key, set_if, sort_order, repeat_key, set_if_format, required) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {array ( 'id' => NULL, 'command_id' => 311, 'argument_name' => '-A', 'argument_value' => NULL, 'argument_format' => NULL, 'key_string' => NULL, 'description' => 'Explicitly select all paths. This is equivalent to -R .*', 'skip_key' => NULL, 'set_if' => '$disk_all$', 'sort_order' => '1', 'repeat_key' => NULL, 'set_if_format' => NULL, 'required' => NULL, )} (DbObject.php:832)

This likely was a side effect of a prior situation wherein we needed to restore the pgdump data of the director database. In doing so, the sequences (not indexes) for this table became out of sync.

ERROR: duplicate key violates unique constraint That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands

# result
director=# SELECT MAX(id) FROM icinga_command_argument;
 max
------
 3984
(1 row)

director=# SELECT nextval('icinga_command_argument_id_seq');
 nextval
---------
    3973
(1 row)

Resolution:

# get primary key
director=# SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'icinga_command_argument'::regclass
AND    i.indisprimary;
 attname | data_type
---------+-----------
 id      | integer
(1 row)

# Get sequence
director=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' and relname like '%icinga_command%';
            relname
--------------------------------
 icinga_command_argument_id_seq
 icinga_command_id_seq
 icinga_commands_command_id_seq
(3 rows)

Here we are dealing with:

• Table: icinga_command_argument
• PKEY: id
• Sequence table: icinga_command_argument_id_seq

Since our max id in icinga_command_argument is higher then the sequence nextval, adjust:

SELECT setval('icinga_command_argument_id_seq', (SELECT MAX(id) FROM icinga_command_argument:)+1);