How can I bulk migrate groups and users with SQL

Hi,

I need to do a massive bulk change of the groups in Crowd mainly used by JIRA and found doing it with SQL might be a good idea. It seem that I only need to update the tables cwd_group and cwd_membership to create a set of new groups (nested) and assign users to them.

When running my SQL statements in Crowd anything looks fine but unfortunately JIRA refuses to synchrionize the new groups from Crowd.

I added new groups via the web interface of crowd as well. After a directory sync the group will appear in JIRA.

I also compared the database using a dump to find out what happens in the database when adding a new group. I can only find a change in the table cwd_group. The groups I created using SQL only differ very slightly in the created and update columns.

Created via Web-Gui

diff crowd_270_20140527.sql crowd_270_20140527_2.sql 
1320a1321
> 5308418	Area_Test_Test	area_test_test	T	T	2014-05-27 13:45:45.043	2014-05-27 13:45:45.043	Area_Test_Test	GROUP	2162689

Created with SQL:

diff crowd_270_20140527_2.sql crowd_270_20140527_3.sql 
1322
> 5308420	Area_Test_Test_Test	area_test_test_test	T	T	2014-05-27 15:47:41.883489	2014-05-27 15:47:41.883489	Area_Test_Test_Test	GROUP	2162689

I have no clue why my groups are not showing up in JIRA. Any hint in the right direction is very much appreciated!

Best Regards,

Christian

6 answers

1 accepted

This widget could not be displayed.

When editing the groups in Crowd UI and re-synchronization in JIRA the groups will appear!

As I think it would be nice to do bulk changes in Crowd with SQL I will forward this issue to the crowd support.

The JIRA->Crowd synchronization process relies on JIRA being able to tell when changes have been made to Crowd's database, because it does an incremental sync of only the changes. If you make the changes to the database directly, then Crowd won't know about them.

On the other hand, the changes should've been picked up on the first sync of JIRA to Crowd after you restarted Crowd. Possibly you restarted Crowd after JIRA and then didn't wait long enough for JIRA to try and sync with Crowd.. but I'm not sure.

In any case, explicitly supporting (/encouraging) messing with Crowd's database restricts how we can evolve Crowd for performance or clustering/high availability, so it's unlikely we will make changes to support that. (I'm a Crowd developer.)

This widget could not be displayed.

Here is one of my SQL create statements:

INSERT INTO cwd_group (id, group_name, lower_group_name, active, is_local, created_date, updated_date, description, group_type, directory_id) VALUES (5308418, 'Area_Test_Test_Test', 'area_test_test_test', 'T', 'T', now(), now(), 'Area_Test_Test_Test', 'GROUP', 3506177);

This widget could not be displayed.

Re-indexing, synchronization and even restarting/rebooting both JIRA and Crowd did not help to solve the problem.

This widget could not be displayed.

(The "answers" that you've added should be comments on your question; you should be able to convert them to comments still)

Does adding a group via the Crowd UI make it show up in JIRA?

If so, you can add the groups via the Crowd REST API . We recommend not editing the database directly; that kind of thing can mess with the synchronization process.

If groups don't show up when you add them via Crowd's UI, you should raise a support request at https://support.atlassian.comand our friendly support team will help you troubleshoot why the changes aren't getting synchronised correctly.

Good advice - I changed my answers to comments. And yes when adding groups via Crowd UI they show up in JIRA.

This widget could not be displayed.

Here is one of my SQL create statements:

INSERT INTO cwd_group (id, group_name, lower_group_name, active, is_local, created_date, updated_date, description, group_type, directory_id) VALUES (5308418, 'Area_Test_Test_Test', 'area_test_test_test', 'T', 'T', now(), now(), 'Area_Test_Test_Test', 'GROUP', 3506177);

This widget could not be displayed.

Re-indexing, synchronization and even restarting/rebooting both JIRA and Crowd did not help to solve the problem.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

573 views 25 15
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you