At the office we use Atlassian Confluence as our internal Wiki system. I do like it, but it has some idiosyncrasies. Similar to JIRA there is no internal way to change user names. Here is the SQL needed to update Confuence.
This is pretty clean SQL because you really only need to update the first two lines.
SET @oldusername = "OLD_USER_NAME";
SET @newusername = "NEW_USER_NAME";
SET @tildedoldusername = CONCAT('~', @oldusername);
SET @tildednewusername = CONCAT('~', @newusername);
SET @locoldusername = CONCAT('LOC_', @oldusername);
SET @locnewusername = CONCAT('LOC_', @newusername);
-- Attachments
update ATTACHMENTS set creator = @newusername where creator = @oldusername;
update ATTACHMENTS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- Bandana
update BANDANA set bandanacontext = @newusername where bandanacontext = @oldusername;
-- Content
update CONTENT set creator = @newusername where creator = @oldusername;
update CONTENT set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT set username = @newusername where username = @oldusername;
update CONTENT set draftspacekey = @tildednewusername where draftspacekey = @tildeoldusername;
-- content_label
update CONTENT_LABEL set owner = @newusername where owner = @oldusername;
-- update CONTENT_LABEL set spacekey = @tildednewusername where owner = @tildedoldusername;
-- content_perl
update CONTENT_PERM set creator = @newusername where creator = @oldusername;
update CONTENT_PERM set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT_PERM set username = @newusername where username = @oldusername;
-- contentlock
update CONTENTLOCK set creator = @newusername where creator = @oldusername;
update CONTENTLOCK set lastmodifier = @newusername where lastmodifier = @oldusername;
-- decorator
update DECORATOR set SPACEKEY = @tildednewusername where SPACEKEY = @tildedoldusername;
-- extrnlnks
update EXTRNLNKS set creator = @newusername where creator = @oldusername;
update EXTRNLNKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- label
update LABEL set owner = @newusername where owner = @oldusername;
-- links
update LINKS set creator = @newusername where creator = @oldusername;
update LINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
update LINKS set destspacekey = @tildednewusername where destspacekey = @tildedoldusername;
update LINKS set destpagetitle = @tildednewusername where destpagetitle = @tildedoldusername;
-- notifications
update NOTIFICATIONS set creator = @newusername where creator = @oldusername;
update NOTIFICATIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update NOTIFICATIONS set username = @newusername where username = @oldusername;
-- os_propertyEntry
update OS_PROPERTYENTRY set entity_name = @locnewusername where entity_name = @locoldusername;
update OS_PROPERTYENTRY set string_val = @tildednewusername where entity_name = @tildedoldusername;
-- pagetemplates
update PAGETEMPLATES set creator = @newusername where creator = @oldusername;
update PAGETEMPLATES set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacegrouppermissions
update SPACEGROUPPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spacegroups
update SPACEGROUPS set creator = @newusername where creator = @oldusername;
update SPACEGROUPS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacepermissions
update SPACEPERMISSIONS set creator = @newusername where creator = @oldusername;
update SPACEPERMISSIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACEPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spaces
update SPACES set creator = @newusername where creator = @oldusername;
update SPACES set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACES set spacekey = @tildednewusername where lastmodifier = @tildedoldusername;
-- trackbacklinks
update TRACKBACKLINKS set creator = @newusername where creator = @oldusername;
update TRACKBACKLINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- os_user and users
update os_user set username = @newusername where username = @oldusername;
update users set name = @newusername where name = @oldusername;
I used this code on about 50 users recently and did not have any problems.
Here are the steps I did:
- I took the template above and made copies for each user that needed to be changed. Each was edited as needed.
- I shutdown Confluence
- I used the mysql command line utility to connect to my confluence database
- I loaded each SQL file by hand, which will update the database
- I relocated the cache which is in the data directory and called index.
- I restarted confluence and rebuilt the cache
Everything went pretty well. After I did the user migration, I then integrated LDAP to our AD and every user now has one less password to remember.
Hope this helps someone.