Re: nagiosxi SQL constraint error after upgrade to 5.3.1

[mhixson2]

Previous (locked) thread: https://support.nagios.com/forum/viewto ... 16&t=40934

Well, this issue returned with a vengeance. Below is the error I'm getting now while trying to deploy changes in Admin > Notification Management to two contact groups.

2016-11-22_14-51-50.png

I updated /usr/local/nagiosxi/tools/fix_postgres_sequences.sh as requested in the previous thread and ran it with the output below. It did not resolve the issue.

Code: Select all

Nagios XI Postgres Database Sequence Information

OLD VALUES
--------------
xi_commands_command_id_seq = 620
xi_events_event_id_seq = 32192
xi_meta_meta_id_seq = 32199
xi_options_option_id_seq = 165
xi_sysstat_sysstat_id_seq = 101
xi_usermeta_usermeta_id_seq = 1129
xi_users_user_id_seq = 52

NEW VALUES
--------------
xi_commands_command_id_seq = 620
xi_events_event_id_seq = 32192
xi_meta_meta_id_seq = 32199
xi_options_option_id_seq = 165
xi_sysstat_sysstat_id_seq = 101
xi_usermeta_usermeta_id_seq = 1129
xi_users_user_id_seq = 52

Upgrade.log below from one of the affected servers.

upgrade.log

I've realized that the three affected servers all have one thing in common - some of their configs were copied over from the first Nagios server I built, which kind of acts like a master, and is not experiencing this issue. They've always been on the same version of Nagios when configs were copied. I emailed you guys previously for help finding a process that would copy users and their dashboards from one Nagios server to another. The process you provided is below. Is this related? What do?

Code: Select all

On source host
	pg_dump -h 127.0.0.1 -U nagiosxi -c -t xi_users -t xi_usermeta > /tmp/pg_userdump.sql
Transfer file to new server	
On destination host
	psql nagiosxi nagiosxi < /tmp/pg_userdump.sql

[tgriep]

It is hard to say if adding the user accounts could be the cause of the error at this time but can you run the following commands as root on the XI server and post the output?

Code: Select all

echo "select max(usermeta_id) from xi_usermeta"|psql nagiosxi nagiosxi
echo "select last_value from xi_usermeta_usermeta_id_seq"|psql nagiosxi nagiosxi

This should print out the sequence numbers for that table that is causing the error.

[mhixson2]

Here's the output:

Code: Select all

$ sudo echo "select max(usermeta_id) from xi_usermeta"|psql nagiosxi nagiosxi
 max  
------
 3073
(1 row)

Code: Select all

$ sudo echo "select last_value from xi_usermeta_usermeta_id_seq"|psql nagiosxi nagiosxi
 last_value 
------------
       1305
(1 row)

[tgriep]

It looks like the postgres sequences are out out order for that table.
If you run the command below, that will update the sequences and hopefully fix the error.
I strongly recommend doing a full backup of the system before running the command in case it doesn't work correctly.

Code: Select all

echo "SELECT setval('xi_usermeta_usermeta_id_seq', (SELECT MAX(usermeta_id) FROM xi_usermeta)+1);"|psql nagiosxi nagiosxi

[mhixson2]

Greatly appreciated. That fixed every server experiencing this issue. I confirmed the numbers given by the first two queries now match on each box.

Thanks for the help and have a great Thanksgiving!