How can I bulk migrate groups and users with SQL

[Christian Schlaefcke]

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

[Christian Schlaefcke · Accepted solution]

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.

[Caspar Krieger]

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.)

[Christian Schlaefcke]

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

[Christian Schlaefcke]

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);

[Caspar Krieger]

(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.

[Christian Schlaefcke]

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

[Christian Schlaefcke]

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

[Christian Schlaefcke]

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);