Cannot create new admin user in database even after following documentation

[Kamil Beer]

Hello,

we are trying to create a new user with administrative privileges in a database, as we had Crowd connected to Jira, but due to a network migration, the contact between Crowd and Jira was interrupted.

In the Jira database (postgresql 7), we have found out that the internal directory is empty.

jira-#   where g.group_name = 'jira-administrators' and d.directory_type='INTERNAL';

 id | user_name | directory_id

----+-----------+--------------

(0 rows)

We have (probably) enabled it through

update cwd_directory set active = 1 where id = 1;

But then we were unable to add the new admin user - we have tried to add him through

insert into cwd_user values (999999,1,'localadmin','localadmin',1,'2012-01-04 19:49:05-08','2012-01-04 19:49:05-08','local','local','admin','admin','local admin','local admin','localadmin@localadmin.com','localadmin@localadmin.com','uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==',NULL,NULL);
insert into app_user values(999999,'localadmin','localadmin');

But Jira told us that there is already an user with ID 999999. However, the internal directory was empty.

jira=# insert into cwd_user values (999999,1,'localadmin','localadmin',1,'2012-01-04 19:49:05-08','2012-01-04 19:49:05-08','local','local','admin','admin','local admin','local admin','localadmin@localadmin.com','localadmin@localadmin.com','uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==',NULL,NULL);

ERROR:  duplicate key value violates unique constraint "pk_cwd_user"

DETAIL:  Key (id)=(999999) already exists.

jira=# insert into app_user values(999999,'localadmin','localadmin');

ERROR:  duplicate key value violates unique constraint "pk_app_user"

DETAIL:  Key (id)=(999999) already exists.

We then have tried to add users through several ways, first

insert into cwd_user(id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, directory_id, credential)

jira-#  values (1212121, 'admin', 'admin', 'T', '2009-11-26 17:42:08', '2009-11-26 17:42:08', 'A. D.', 'a. d.', 'Ministrator', 'ministrator', 'A. D. Ministrator', 'a. d. ministrator', 'admin@example.com', 'admin@example.com', (select id from cwd_directory where directory_type='INTERNAL'), '{PKCS5S2}gf02gc7VU5aYsgWfRG0S4PD1MxPdfLxq7c7PO/DSkq0rSnw/mMv6UpSRUkD2+kyP');

However, all we got was an error

ERROR:  invalid input syntax for type numeric: "T"

LINE 2:  values (1212121, 'admin', 'admin', 'T', '2009-11-26 17:42:0...

                                                                      ^

This happened everytime we tried a command that contained 'T', ex.

jira=# update cwd_directory set active = 'T' where id = (select id from cwd_directory where directory_type='INTERNAL');

ERROR:  invalid input syntax for type numeric: "T"

LINE 1: update cwd_directory set active = 'T' where id = (select id ...

We're at the end of our rope here. How can we...

1) Ensure the Jira internal directory is working

2) And add a user with administrative privileges through which we could work with Jira?

We could really use the assistance, please.

[Sloan N_ B_]

Hi Kamil,

postgres should accept T, maybe try lowercase t 

Alternatively try active =1

or active ='true'

And are you positive cwd_user does not already contains users with the id 999999?

Do a select * on the involved tables to be sure. It never hurts to look "manually" with select * at a table.

Is there any way you can get Crowd up again and connected to your Jira? As long there were no changes to the config the Crowd directory should work again.

[Kamil Beer]

Hey Niklas,

thanks for your reply. It's pretty strange with 'T', as it even says that there is a numeric error. I will certainly try what you mentioned.

As for the 9999 user, we got the error from the db, so that's that.

We can get Crowd to run, but because of the migration, when we sign into the crowd console, we get a 404 apache error. So we can't go that way either... :/