Changing Usernames

Not sure how up to date the SQL above is for this problem. Below is the SQL I had to use to fix a naming problem for users as we moved from one LDAP service to another (their 6+2 login names were slightly different in some cases).

begin
for i in (select * from usermigration)
loop

delete from external_entities
where name = i.newusername;

update external_entities
set name = i.newusername
where name = i.oldusername;

update attachments
set creator = i.newusername
where creator = i.oldusername;

update attachments
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update content
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update content
set creator = i.newusername
where creator = i.oldusername;

update content
set username = i.newusername
where username = i.oldusername;

update content_label
set owner = i.newusername
where owner = i.oldusername;

update draft
set owner = i.newusername
where owner = i.oldusername;

update extrnlnks
set creator = i.newusername
where creator = i.oldusername;

update extrnlnks
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update links
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update links
set creator = i.newusername
where creator = i.oldusername;

update notifications
set username = i.newusername
where username = i.oldusername;

update notifications
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update notifications
set creator = i.newusername
where creator = i.oldusername;

update pagetemplates
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update pagetemplates
set creator = i.newusername
where creator = i.oldusername;

update spaces
set creator = i.newusername
where creator = i.oldusername;

update spaces
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update spacepermissions
set permusername = i.newusername
where permusername = i.oldusername;

update spacepermissions
set creator = i.newusername
where creator = i.oldusername;

update spacepermissions
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update contentlock
set creator = i.newusername
where creator = i.oldusername;

update contentlock
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

--update emailtemplates
--set creator = i.newusername
--where creator = i.oldusername;

--update emailtemplates
--set lastmodifier = i.newusername
--where lastmodifier = i.oldusername;

--update emailtemplates
--set username = i.newusername
--where username = i.oldusername;

update os_user
set username = i.newusername
where username = i.oldusername;

update trackbacklinks
set creator = i.newusername
where creator = i.oldusername;

update trackbacklinks
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update OS_PROPERTYENTRY
set entity_name = replace(entity_name,i.oldusername,i.newusername)
where entity_id = (select entity_id from external_entities where entity_name = i.newusername);

end loop;
end;

The Article is outdated.

As Donald wrote, there are some new Tables which are not mentioned. Here is my SQL-Code running in Version 2.2.8, with MySQL 4.1:

update attachments x inner join usermigration u on
x.creator = u.oldusername
set x.CREATOR = u.newusername;

update attachments x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update content x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update content x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update content x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update content_label x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update draft x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update extrnlnks x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update extrnlnks x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update label x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update links x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update links x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update notifications x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update notifications x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update notifications x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update pagetemplates x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update pagetemplates x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update spaces x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update spaces x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update spacepermissions x inner join usermigration u on
x.permusername = u.oldusername
set x.permusername = u.newusername;

update spacepermissions x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update spacepermissions x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update contentlock x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update contentlock x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.lastmodifier = u.oldusername
--set x.lastmodifier = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.creator = u.oldusername
--set x.creator = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.username = u.oldusername
--set x.username = u.newusername;

update os_user x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update trackbacklinks x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update trackbacklinks x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

Hint:

• as there can be duplicates in the new username-list, run all SQL-Code except of os_user-update.
• run only os_user, if there is an error, change the problematic newusername
• After running the scripts make a full reindexing, to have affect on the recentlyupdated section for example

I found that I had to modify the confluencedb.script file as well as the confluence-home/database/confluencedb.log file (infact, after making changes in both, it looked like the confluencedb.log file got reset after restarting Confluence, so perhaps the only place requiring the change is confluencedb.script).

I rewrote for SQL Server

A mid way point should be to get this added to as a restful service accessible by the SOAP client.

1. http://jira.atlassian.com/browse/CONF-4063 is tracking this issue.
2. I suspect this is a project that I am going to have to tackle. Are there guidelines somewhere that advise how this SQL should become code that runs on the server and invoked from the remote client?

To have only the sql-solution mentioned is good, as the xml-workaround was to faulty, but can you also say a workaround to update bodycontent (like linking to a user profile) which would be broken if you change the username?

That isnt a showstopper, just to have that in mind. I currently use a sql change to make the necessary field-updates and for the rest i check for profile links in the xml-entities file and change these values directly in the memo-field in the bodycontent. Maybe that could be the work of a xml-rpc tool or the like to change this more automatic. For a sql-replace the memo-field of the bodycontent is to huge, thats the reason why you cant do it like the field updates above.

None of these instructions work for Oracle.

Further, making admins perform database SQL commands to make up for this functionality is asking for trouble (especially given the many dialects of SQL).

After changing username, the profile pictures are disabled. The user can see and select the uploaded pictures in Preferences, but they are disabled. How can I correct this in the database? (the pictures are attached to content of type userinfo, but I don't know how this is connected to the user)

Here is an updated version of the update statements in the original article (items 6, 7 and 8).
I dumped the Confluence database schema for version 2.5.5 and scanned through it for possible username and spacekey fields and added the missing statements. I am pretty sure I didn't leave anything out.

UPDATE attachments      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE attachments      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content          SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE content          SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content          SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE content_label    SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE content_perm     SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE content_perm     SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content_perm     SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE contentlock      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE contentlock      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE draft            SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE extrnlnks        SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE extrnlnks        SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE label            SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE links            SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE links            SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE notifications    SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE notifications    SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE notifications    SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE os_user          SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE pagetemplates    SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE pagetemplates    SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacegroups      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spacegroups      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacepermissions SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spacepermissions SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacepermissions SET permusername = newusername FROM usermigration u WHERE permusername = u.oldusername;
UPDATE spaces           SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spaces           SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE trackbacklinks   SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE trackbacklinks   SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE user             SET name         = newusername from usermigration u WHERE name         = u.oldusername;

UPDATE draft         SET spacekey = '~newusername' WHERE spacekey = '~oldusername';
UPDATE content_label SET spacekey = '~newusername' WHERE spacekey = '~oldusername';
UPDATE decorator     SET spacekey = '~newusername' WHERE spacekey = '~oldusername';

I left out OS_PROPERTYENTRY as I don't understand it's connection to external_entities (and didn't need to on my server).

The formatting should make it easier to change for other SQL dialects using global replace.

We seem to have lost profile pictures and favorite for renamed users? I will submit a support request, but, perhaps someone has some experiences they can share.

I found a few more columns that were not included; using these as well, I could get profile pictures to change and display normally. I also could add favorites successfully

UPDATE os_propertyEntry SET entity_name = LOC_newusername where entity_name=LOC_oldusername

UPDATE os_propertyEntry SET string_val=_{'newusername' where string_val=}'oldusername'

screwed up the format; that last one is

'~newusername' and '~oldusername'

Can anybody confirm this piece of code that I now came up with?
It should work for a Confluence 2.8.2 installation and MySQL 5.0.51 as the database.

Building off of all of this, and tested only on Postgres 8.3 with Confluence 2.9:

The /tmp/username_migration.csv file looks like:

The script has to be run as the Postgres superuser due to the copy command, and it allows you to update email address as well.

The premise behind our need for this tool is that we're integrating with our AD, and while IT said they would be going to first.last, they in fact stayed with firstinitiallastname, and so few of our Confluence users match the AD names, and this provides an easy bulk update mechanism.

However, it was written with the idea of ongoing username changes, which is why it stores a username migration index, so that you can keep the history of whose name changed when and where (hm, guess I could add a datestamp) but keep adding new users to run the tool against, say, because of marriage, etc.

The 4th column in the CSV allows you to specify a migration number if you so choose, otherwise it will auto-create it as 1 higher than the last run migration.

Questions, comments, direct here, I'll just watch the page. Atlassian, if you wanted to incorporate this into C4.x, I'd have NO problem letting you maintain this

John

Are there any updated bits of code for doing this on Oracle?

We've got two copies of each user that we need combining into one so that active directory works properly

Could anyone that actually works at atlassian comment on exactly what tables and feilds that contain either the short (~user) or the long (user@domain.com) username on jira 3.13.2 and confluence 2.9.2? I need to have both converted for testing purpuse in a week and im afraid i might miss some small fields or alternavly accidentaly replace a binary string/string containing the name(especially the short name).

Regards

Jonas

Updated SQL queries, shared by one of the customers Bryan Dobson, for MySQL 5.0 and above:

The information in "Section 6" of how to change it was wrong. I have made
the correct queries that work on mysql 5.0 and above which I'll share:

update ATTACHMENTS a, usermigration u set a.creator=u.newusername where
a.creator=u.oldusername;

update ATTACHMENTS a, usermigration u set a.lastmodifier=u.newusername where
a.lastmodifier=u.oldusername;

update CONTENT c, usermigration u set c.LASTMODIFIER=u.newusername where
c.LASTMODIFIER=u.oldusername;

update CONTENT c, usermigration u set c.CREATOR=u.newusername where
c.CREATOR=u.oldusername;

update CONTENT c, usermigration u set c.USERNAME=u.newusername where
c.USERNAME=u.oldusername;

update EXTRNLNKS e, usermigration u set e.CREATOR=u.newusername where
e.CREATOR=u.oldusername;

update EXTRNLNKS e, usermigration u set e.LASTMODIFIER=u.newusername where
e.LASTMODIFIER=u.oldusername;

update LABEL l, usermigration u set l.OWNER=u.newusername where
l.OWNER=u.oldusername;

update CONTENT_LABEL cl, usermigration u set cl.OWNER=u.newusername where
cl.OWNER=u.oldusername;

update LINKS li, usermigration u set li.LASTMODIFIER=u.newusername where
li.LASTMODIFIER=u.oldusername;

update LINKS li, usermigration u set li.CREATOR=u.newusername where
li.CREATOR=u.oldusername;

update NOTIFICATIONS n, usermigration u set n.LASTMODIFIER=u.newusername
where n.LASTMODIFIER=u.oldusername;

update NOTIFICATIONS n, usermigration u set n.CREATOR=u.newusername where
n.CREATOR=u.oldusername;

update PAGETEMPLATES p, usermigration u set p.LASTMODIFIER=u.newusername
where p.LASTMODIFIER=u.oldusername;

update PAGETEMPLATES p, usermigration u set p.CREATOR=u.newusername where
p.CREATOR=u.oldusername;

update SPACES s, usermigration u set s.CREATOR=u.newusername where
s.CREATOR=u.oldusername;

update SPACES s, usermigration u set s.LASTMODIFIER=u.newusername where
s.LASTMODIFIER=u.oldusername;

update SPACEPERMISSIONS sp, usermigration u set
sp.PERMUSERNAME=u.newusername where sp.PERMUSERNAME=u.oldusername;

update SPACEPERMISSIONS sp, usermigration u set
sp.LASTMODIFIER=u.newusername where sp.LASTMODIFIER=u.oldusername;

update CONTENTLOCK co, usermigration u set co.CREATOR=u.newusername where
co.CREATOR=u.oldusername;

update CONTENTLOCK co, usermigration u set co.LASTMODIFIER=u.newusername
where co.LASTMODIFIER=u.oldusername;

update os_user os, usermigration u set os.username=u.newusername where
os.username=u.oldusername;

update TRACKBACKLINKS t, usermigration u set t.CREATOR=u.newusername where
t.CREATOR=u.oldusername;

update TRACKBACKLINKS t, usermigration u set t.LASTMODIFIER=u.newusername
where t.LASTMODIFIER=u.oldusername;

update users us, usermigration u set us.name=u.newusername where
us.name=u.oldusername;

update SPACES set SPACEKEY ='~newusername' where SPACEKEY = '~oldusername';

update CONTENT_LABEL set SPACEKEY ='~newusername' where SPACEKEY =
'~oldusername';

my headache is getting bigger everytime i read these articles... Atlassian please do something about it.

For Example somehow the draft-table has dropped in the official document as well as in the updated sql from Gurleen. I am just going through step by step (at least the table in my databases if full of data), its my first replacement since i upgraded to 2.10... upgrading is a blessing (or something other ironic )

edit: list of missing replacements from my point of view:
edit #2: ok after i digged through the list of John W. i think there were a bit more
edit #3: added draft.spacekey
edit #4: added content.draftspacekey and spacegrouppermissions.permusername (not sure with this one)

This is a complete SQL Script for MySQL 5.1 and Confluence 2.10 (used concat for tilde-operations)
^{complete means it run without errors, should be equally to John W.'s List and the renamed user could access/edit his stuff. Did not check whether atlassian included new fields/tables as of 2.9}

Confluence 3.0.1 + Mysql 5

Here is an updated version of John W. Knights awesome Postgres Script. I also added one procedure for manual addition of users (to bypass the administrator restriction of the copy function).

Tested with Confluence 3.0.1 and Postgres 8.3

Hi,

i just applied the queries to update my database, but still no users can logon. 

What I did:

1. create db usermigration with old uname and new uname

2. ran the sql queries listed above for MySQL

3. restarted confluence

But still, I am not able to logon with the new user names.

What could be wrong?

Regards,

Pieter

I modified my LDAP setting from <usernameAttribute>sAMAccountName</usernameAttribute> to <usernameAttribute>cn</usernameAttribute>. With it I got an about 30% performance increase to open a page. I think the AD isn´t indexed for sAMAccountName.
BUT: sAMAccoutnName is always lowercase, the cn is a mix of upper and lower characters. At the end I had users setup twice in the system with different writing (e.g. "ConfluenceAdmin" and "confluenceadmin"). Resulting problems, e.g.:

• lost of private settings, picture
• lost of draft pages
• double user selection at "Set restriction"

The above statements help a lot to cleanup the system, it took me some hours. Here my summary:

• Execute a full backup of your database and system first.
• Test the update on system copy (system, confluence-home, database) first.
• A separate backup of each table can help to rollback if you fail to modify a part.
• You can do it on a live system, but I can´t recommend this. Most changes are effective only after a cache cleanup or re-index.
• I executed delete statements manually in my SQL browser to be sure to select the right entries.

Check if there are double user entries in the external_entities and content table:

Create user mapping table

MySQL statement, other DB can differ:

fill table with the old and new usernames incl. the id´s from table external_entities:

Update with the ID´s from the table CONTENT:

Check if still other users in the content table (this can happen when username in cn is lower case or the selects are wrong):

Add this user to the table if necessary to update:

check result:

Update simple tables:

Personal Settings

This settings are defined, I don´t care (do not updated to the new user name):
'confluence.prefs.email.mimetype'
'confluence.prefs.email.show.diff'
'confluence.prefs.email.show.full.content'
'confluence.prefs.notify.for.my.own.actions'
'confluence.user.dashboard.spaces.selected.tab'
'confluence.user.last.login.date'
'confluence.user.previous.login.date'
'confluence.user.profile.picture'
'confluence.user.runtime.edit-wysiwyg'
'confluence.user.runtime.edit.show-labels'
'confluence.user.runtime.edit.show-restrictios'
'confluence.user.runtime.recent-changes.size'
'confluence.user.runtime.show-children'
'confluence.user.runtime.show-comments'
'confluence.user.site.homepage'
'confluence.user.time.zone'
'confluence.user.wysiwyg.contextmenu.on'
But for later clarifcation, I do NOT remove it from the table.

Update the string settings:
Get all user with duplicate string settings:

Get all entries:

Check if there still duplicate entries in:

Merge all remaining entries into new user/userid:

Update all department settings (TO BE CHECKED!)

Update the department/phone/location/... settings. I can fail (error message due to duplicate entry – key), then you need to update manually:

About Me

Get all user with about me entry:

Changed BODYCONTENT.CONTENTID to content id of new user name:

Get old entries of lower username:

They need to be delete manually:

Pictures:
Update attachments:

Remove image details of this users with duplicated entry of old username:

Remove duplicate entries of old user from ATTACHMENTS (above select statement).

Assign pictures to user:

Move the attachments of the jpeg/picture files from the old-content-id releated location to the new content-id location:
Get a mapping list:

Export list to Excel, insert 2 columns before oldcontid and 2 colomns before newcontid
Insert this formulas in

This describe the old/new diretory structure for the picture location. Copy down the formulars to the other cells in columns.
Open the the server <confluence-home>/attachments/ver003/nonspaced folder in a browser/explorer.
For each entry, navigate to:
/B2/C2/D2/K2 --> there must be a file (e.g. "1") for the picture ("1" mean the version 1)
Check if a folder
/G2/H2/I2/K2 exist: eather change the name of B2 to G2, C2 to H2, D2 to I2 or merge K2 into /G2/H2/I2.
More details on the hierachy in the attachment folder see Hierarchical File System Attachment Storage.

(May someone find a better solution).

Update other username:

Update Old Users

Get all users:

Check result (there can be some users in, e.g. NON-LDAP users):

• Update all users which show up ONLY by lower case with correct case:

Remove old users from groups

Change old user/group mapping to newUser/group (may need to remove old user from groups before)

Check result:

Remove users not used the Wiki:

Update old user not used the wiki since change:

Remove old user entries

Draft pages:

The draft pages should be available again to the user. After system up, request to user to check the access. Otherwise send content to user:

Install Fix for label problem

When use use Confluence 3.1, you need ot fix the label problem

Remove Index

Move <confluence-home>/index to ./index_OLD (can be deleted afterwards)

Restart Confluence

• Remove/Backup old logfiles
• Verify system was down (ps -efa | grep confluence)
• restart via startup.sh
• set cache settings to target values
• restart index + did-you-mean index

Access to spaces / personal space

Additional help on page Fix Case Mismatches in Permissions

Rebuild Anchestor table:

<base url>/admin/permissions/pagepermsadmin.action

Other recommendations welcome.

Isn't the purpose of relational databases to prevent the same data everywhere? shouldnt the user name and details be stored in one table and then that is referenced by the other tables?

Change the user name in one spot and everything everywhere is updated?

Comment from Support Customer:

Once I understood the principle, I looked through the other tables and found another pair of update statements I believe should be included:

update content_perm
set creator = newusername from usermigration u
where creator = u.oldusername

update content_perm
set username = newusername from usermigration u
where username = u.oldusername

We did a domain migration and username change on Confluence 3.5.13 running on a MSSQL 2005

We had to modify the queries a little bit

7 B i

7 B ii

ignored

7 B iii