DB error when adding users

[questrad]

Installed Version: 2014R1.0
cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)

Hi Everyone

I am getting the following various errors as I was adding users. After looking over the install log I saw the following owner permissions error. Could this be causing all my errors below when I add/clone users. Please help me ! Also tell me the permissions needed for the db user so they can be corrected.


Install Log Error
Creating postgres database and user...
CREATE ROLE
CREATE DATABASE
Restarting postgres...
Stopping postgresql service: ESC[60G[ESC[0;32m OK ESC[0;39m]
Starting postgresql service: ESC[60G[ESC[0;32m OK ESC[0;39m]
Creating postgres db structure...
SET
SET
SET
SET
SET
ERROR: must be owner of schema public
SET
CREATE SEQUENCE
ALTER TABLE
CREATE SEQUENCE





various errors
1. Get this when I clone various user accounts

Unable to assign account privileges.
Use this functionality to create a new user account that is an exact clone of another account on the system. The cloned account will inherit all preferences, views, and dashboards of the original user.
SQL: INSERT INTO fusion_usermeta (user_id,keyname,keyvalue,autoload) VALUES ('27','userlevel','1','1') SQL Error [nagiosfusion] : ERROR: duplicate key value violates unique constraint "fusion_usermeta_pkey"

2. Get this too when I clone various user accounts
Clone User: *****
Security level is blank.
Unable to get insert id for new user account
Use this functionality to create a new user account that is an exact clone of another account on the system. The cloned account will inherit all preferences, views, and dashboards of the original user.
SQL: INSERT INTO fusion_usermeta (user_id,keyname,keyvalue,autoload) VALUES ('57','userlevel','1','1') SQL Error [nagiosfusion] : ERROR: duplicate key value violates unique constraint "fusion_usermeta_pkey"ERROR: UNABLE TO ADD USERID: ()

3. Get this when I just try to add (not clone)
SQL: INSERT INTO fusion_users (username,email,name,password,backend_ticket) VALUES ('*****','[email protected]','firstname lastname','qwqpasswordqwq') SQL Error [nagiosfusion] : ERROR: duplicate key value violates unique constraint "fusion_users_pkey"ERROR: UNABLE TO ADD USERID: ()

questrad

Posts: 63
Joined: Wed Mar 21, 2012 4:08 pm

Top

[questrad]

Anyone ?

[tmcdonald]

Had you attempted a Fusion install before the one that succeeded? Was postgres already present on the system before installing Fusion?

[questrad]

So, more information.
We were able to add users after we have installed NagiosFusion - that was about 6 months ago - no any issue with adding or cloning.
But starting from now we are unable to add/clone any type of user - admin or simple user.
Nothing was changed from that day, except usual OS update's/patching's.

There ERROR that was mention above from install log is happening in this part:

Code: Select all

(/tmp/nagiosfusion/nagiosfusion/schema.pgsql.sql)

CREATE SEQUENCE fusion_commands_command_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.fusion_commands_command_id_seq OWNER TO nagiosfusion;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: fusion_commands; Type: TABLE; Schema: public; Owner: nagiosfusion; Tablespace:
--

I have try to execute the SQL insert using PgAdmin and get the same error:

Code: Select all

INSERT INTO fusion_users (username,email,name,password,backend_ticket) 
VALUES ('TestUser','[email protected]','Test','f1645abba84cc3f1d0a38e07faa56a71','mt8lavvcjgh5ncfl79m2vqhtt3fj6bbp8jcgibr4pc6emk3sqmrphakec20fac8f')


ERROR:  duplicate key value violates unique constraint "fusion_users_pkey"
********** Error **********

ERROR: duplicate key value violates unique constraint "fusion_users_pkey"
SQL state: 23505

Here is the CREATE script for fusion_users table:

Code: Select all

-- Table: fusion_users

-- DROP TABLE fusion_users;

CREATE TABLE fusion_users
(
  user_id integer NOT NULL DEFAULT nextval('fusion_users_user_id_seq'::regclass),
  username character varying(64) NOT NULL,
  password character varying(64) NOT NULL,
  name character varying(64),
  email character varying(128) NOT NULL,
  backend_ticket character varying(128),
  enabled smallint NOT NULL DEFAULT (1)::smallint,
  CONSTRAINT fusion_users_pkey PRIMARY KEY (user_id),
  CONSTRAINT fusion_users_username_key UNIQUE (username)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE fusion_users
  OWNER TO nagiosfusion;

And CREATE script for constraint "fusion_users_pkey".
The last User_ID is = 112

Code: Select all

-- Sequence: fusion_users_user_id_seq

-- DROP SEQUENCE fusion_users_user_id_seq;

CREATE SEQUENCE fusion_users_user_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 62
  CACHE 1;
ALTER TABLE fusion_users_user_id_seq
  OWNER TO nagiosfusion;

12-08-2014 11-20-21 PM.jpg

I have manually changed current value to latest value of user_id and tried to add user but get next error:

Code: Select all

SQL: INSERT INTO fusion_usermeta (user_id,keyname,keyvalue,autoload) VALUES ('113','userlevel','1','1')	SQL Error [nagiosfusion] : ERROR: duplicate key value violates unique constraint "fusion_usermeta_pkey"ERROR: UNABLE TO ADD USERID: ()

So, looks like current value of all sequences are not matching in some how with the records in the tables that they have reference.
Can you please provide any solution how to sync all sequences key's?


Thanks

[lmiltchev]

So, looks like current value of all sequences are not matching in some how with the records in the tables that they have reference.
Can you please provide any solution how to sync all sequences key's?

Run the following commands:

Code: Select all

cd /usr/local/nagiosfusion/scripts
./fix_postgres_sequences.sh

Let me know if this fixed your issue.

[questrad]

I fixed sequences manually
I hope it will never happened, but if - I have noticed your solution - THANKS