-
Bug
-
Resolution: Obsolete
-
Medium
-
None
-
3.5, 3.5.1, 4.0.5, 4.2.4, 4.3.7
Summary
After upgrading to Confluence 3.5 or higher, where the user property migration (e.g. com.atlassian.confluence.user.migration.AtlassianUserDataMigrator#migrateUserProperties or com.atlassian.user.util.migration.OSUCrowdEntityMigrator#migratePropertySet) won't happen/called during the upgrade which is expected for the following scnearios:
- Upgrading to Confluence with JIRA User Management
- Upgrading to Confluence without dropping atlassian-user.xml
- LDAP AtlassianUser to Delegated EmbeddedCrowd
.....user settings (confluence.user.*) in OS_PROPERTYENTRY table will be lost such as:
- Profile picture
- Default setting of editor (wikimarkup/RTE)
- User site home page
- Login details
- etc.
Workaround
Go here.
Possible Cause
The upgrade process does not migrate the respective entities in OS_PROPERTYENTRY table. They are no more taken into account due to different structure - previously the entity_name is OSUser_user (and entity_id set to JIRA's userbase.id) and now is CWD_username (entity_id set to 0).
- Mismapped OS_PROPERTYENTRY entries of JIRA users
+---------------+-----------+---------------------------------------------+----------+-------------+------------+-------------------------------------+----------+----------+---------+---------------------+ | entity_name | entity_id | entity_key | key_type | boolean_val | double_val | string_val | text_val | long_val | int_val | date_val | +---------------+-----------+---------------------------------------------+----------+-------------+------------+-------------------------------------+----------+----------+---------+---------------------+ | OSUser_user | 10000 | confluence.user.last.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-04-12 03:45:37 | | OSUser_user | 10000 | confluence.user.previous.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-04-12 03:42:12 | | OSUser_user | 10000 | confluence.user.profile.picture | 5 | | 0 | /images/icons/profilepics/dude1.gif | | 0 | 0 | NULL | | OSUser_user | 10010 | confluence.user.last.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-04-12 03:42:22 | | OSUser_user | 10010 | confluence.user.previous.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-03-18 21:26:59 | | OSUser_user | 10010 | confluence.user.profile.picture | 5 | | 0 | stylesheets.jpg | | 0 | 0 | NULL | | OSUser_user | 10011 | confluence.user.last.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-03-18 21:27:54 | | OSUser_user | 10011 | confluence.user.previous.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-03-17 21:36:41 | | OSUser_user | 10011 | confluence.user.profile.picture | 5 | | 0 | jbb-95295-pp-ochenklogo.jpg | | 0 | 0 | NULL | +---------------+-----------+---------------------------------------------+----------+-------------+------------+-------------------------------------+----------+----------+---------+---------------------+
- New structure:
+---------------+-----------+---------------------------------------------+----------+-------------+------------+-----------------------------------------+----------+----------+---------+---------------------+ | entity_name | entity_id | entity_key | key_type | boolean_val | double_val | string_val | text_val | long_val | int_val | date_val | +---------------+-----------+---------------------------------------------+----------+-------------+------------+-----------------------------------------+----------+----------+---------+---------------------+ | CWD_adminconf | 0 | confluence.user.keyboard.shortcuts.disabled | 1 | | 0 | NULL | | 0 | 0 | NULL | | CWD_adminconf | 0 | confluence.user.last.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-04-12 04:03:42 | | CWD_adminconf | 0 | confluence.user.previous.login.date | 7 | | 0 | NULL | | 0 | 0 | 2011-04-12 04:00:38 | | CWD_adminconf | 0 | confluence.user.profile.picture | 5 | | 0 | /images/icons/profilepics/Avatar-15.png | | 0 | 0 | NULL | | CWD_adminconf | 0 | confluence.user.site.homepage | 5 | | 0 | ds | | 0 | 0 | NULL | | CWD_adminconf | 0 | confluence.user.time.zone | 5 | | 0 | Asia/Kuala_Lumpur | | 0 | 0 | NULL | | CWD_adminconf | 0 | confluence.user.whats.new.dont.show.version | 5 | | 0 | 3.5 | | 0 | 0 | NULL | +---------------+-----------+---------------------------------------------+----------+-------------+------------+-----------------------------------------+----------+----------+---------+---------------------+
Finding
- The lost profile pictures will still be available in the selection of profile pictures, but not applied (only for the uploaded profile pictures).
- It seems that OS_PROPERTYENTRY.ENTITY_NAME is a combination of CWD_ and CWD_USER.LOWER_USER_NAME. However, it seems that Confluence expects it differently, CWD_ and CWD_USER.USER_NAME. So if the user has a different case username in CWD_USER.USER_NAME:
CWD_USER.USER_NAME CWD_USER.LOWER_USER_NAME OS_PROPERTYENTRY.ENTITY_NAME TestUser testuser CWD_testuser The above seems the default, but it won't work. You'll need to workaround it by changing OS_PROPERTYENTRY.ENTITY_NAME to CWD_TestUser
- Confluence will throw this error in the log after upgrade:
2011-04-12 03:49:03,442 ERROR [Indexer: 2] [atlassian.bonnie.search.BaseDocumentBuilder] getDocument Error extracting search fields from Attachment: jbb-95295-pp-ochenklogo.jpg v.1 (983042) jbb using BackwardsCompatibleExtractor wrapping com.atlassian.confluence.search.lucene.extractor.ConfluenceAttachmentMetadataExtractor@15a7085 (confluence.extractors.core:attachmentMetadataExtractor): value cannot be null java.lang.NullPointerException: value cannot be null at org.apache.lucene.document.Field.<init>(Field.java:330) at org.apache.lucene.document.Field.<init>(Field.java:305) at org.apache.lucene.document.Field.<init>(Field.java:284) at com.atlassian.confluence.search.lucene.extractor.ConfluenceAttachmentMetadataExtractor.addFields(ConfluenceAttachmentMetadataExtractor.java:66) at com.atlassian.confluence.plugin.descriptor.ExtractorModuleDescriptor$BackwardsCompatibleExtractor.addFields(ExtractorModuleDescriptor.java:45) at com.atlassian.bonnie.search.BaseDocumentBuilder.getDocument(BaseDocumentBuilder.java:104) at com.atlassian.confluence.search.lucene.ConfluenceDocumentBuilder.getDocument(ConfluenceDocumentBuilder.java:102) at com.atlassian.confluence.search.lucene.tasks.AddDocumentIndexTask.perform(AddDocumentIndexTask.java:43) at com.atlassian.bonnie.index.TempIndexWriter.perform(TempIndexWriter.java:73) at com.atlassian.confluence.search.lucene.TempIndexWriterStrategy.perform(TempIndexWriterStrategy.java:43) at com.atlassian.confluence.search.lucene.tasks.TempIndexBackedIndexTaskPerformer.perform(TempIndexBackedIndexTaskPerformer.java:21) at com.atlassian.confluence.search.lucene.DefaultObjectQueueWorker.indexCollection(DefaultObjectQueueWorker.java:78) at com.atlassian.confluence.search.lucene.DefaultObjectQueueWorker$1.doInTransactionWithoutResult(DefaultObjectQueueWorker.java:62) at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:127) at com.atlassian.confluence.search.lucene.DefaultObjectQueueWorker.run(DefaultObjectQueueWorker.java:51) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662)
- incorporates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- is related to
-
-
- Closed
-
- mentioned in
-
![[Atlassian Documentation] Page](/images/icons/generic_link_16.png "[Atlassian Documentation] Page")
Page
No Confluence page found with the given URL.
-
Page
No Confluence page found with the given URL.
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
(15 mentioned in)
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[CONFSERVER-22298] OS_PROPERTYENTRY User Property Settings Lost after Upgrade to Confluence 3.5 or higher
Our customer developed these sql queries for Oracle in order to fix this as we can see here:
UPDATE OS_PROPERTYENTRY SET entity_name = REPLACE (OS_PROPERTYENTRY.entity_name, 'com.atlassian.user.impl.ldap.DefaultLDAPUser_','CWD_') WHERE OS_PROPERTYENTRY.entity_key = 'confluence.user.profile.picture' and OS_PROPERTYENTRY.entity_name like 'com.atlassian.user.impl.ldap.DefaultLDAPUser_%'; UPDATE OS_PROPERTYENTRY SET OS_PROPERTYENTRY.entity_id = 0 WHERE OS_PROPERTYENTRY.entity_key = 'confluence.user.profile.picture' and OS_PROPERTYENTRY.entity_id != 0 and OS_PROPERTYENTRY.entity_name like 'CWD_%';
Bernardo Acevedo [Atlassian]
added a comment - - edited Our customer developed these sql queries for Oracle in order to fix this as we can see here:
UPDATE OS_PROPERTYENTRY
SET entity_name = REPLACE (OS_PROPERTYENTRY.entity_name, 'com.atlassian.user.impl.ldap.DefaultLDAPUser_' , 'CWD_' )
WHERE OS_PROPERTYENTRY.entity_key = 'confluence.user.profile.picture'
and OS_PROPERTYENTRY.entity_name like 'com.atlassian.user.impl.ldap.DefaultLDAPUser_%' ;
UPDATE OS_PROPERTYENTRY
SET OS_PROPERTYENTRY.entity_id = 0
WHERE OS_PROPERTYENTRY.entity_key = 'confluence.user.profile.picture'
and OS_PROPERTYENTRY.entity_id != 0
and OS_PROPERTYENTRY.entity_name like 'CWD_%' ;
This is help for our pg customers for step 3 above:
1. Copy userbase from Jira
a. Dump the userbase table to an sql file
i. pg_dump jira -t userbase -f user_table.sql
b. To add the userbase table to the confluence DB:
i. psql confluence user_table.sql
2. //copy OS_PROPERTYENTRY to a temp table
a.
CREATE TABLE tempospe AS (SELECT * FROM OS_PROPERTYENTRY);
3. //delete any existing confluence.user.* that belongs to JIRA user to prevent any duplicate failures in running the last query
a. This deletes any entries that are duplicates (admin from the confluence db which is ok because the admin user will be re-imported in the next step.
b.
DELETE FROM OS_PROPERTYENTRY WHERE entity_name IN (SELECT entity_name FROM tempospe conf, userbase jira WHERE conf.entity_name=concat('CWD_', jira.username)) AND entity_key like 'confluence.user%';
4. Update the correct mappings in OS_propertyentry table
a.
Update OS_propertyentry set entity_name=concat('CWD_',ub.username),entity_id=0 from userbase ub where ub.id=entity_id and (entity_key like 'confluence.user%' or entity_key like 'confluence.prefs%');
Further findings:
- This can also affect a user where they used to have LDAP in prior 3.5. Then they upgrade without dropping atlassian-user.xml. And reintegrate the LDAP through the UI after the upgrade. The LDAP user's OS_PROPERTYENTRY user settings would be lost as well since they are not migrated (during upgrade).
- (CSP-61714) It seems that OS_PROPERTYENTRY.ENTITY_NAME is a combination of CWD_ and CWD_USER.LOWER_USER_NAME. However, it seems that Confluence expects it differently, CWD_ and CWD_USER.USER_NAME. So if the user has a different case username in CWD_USER.USER_NAME:
CWD_USER.USER_NAME CWD_USER.LOWER_USER_NAME OS_PROPERTYENTRY.ENTITY_NAME TestUser testuser CWD_testuser The above seems the default, but it won't work. You'll need to workaround it by changing OS_PROPERTYENTRY.ENTITY_NAME to CWD_TestUser
Workaround
Applies for Upgrading to Confluence with JIRA User Management only
Update OS_PROPERTYENTRY to reflect the new structure. The following queries has been tested on MySQL, so you might need to translate it to your own for different database. Please backup your database first beforehand.
Replace ConfluenceDB with Confluence database name and JIRADB with JIRA database name.
//copy JIRA userbase to Confluence DB CREATE TABLE jirauser AS (SELECT * FROM JIRADB.userbase); //copy OS_PROPERTYENTRY to a temp table CREATE TABLE tempospe AS (SELECT * FROM OS_PROPERTYENTRY); //delete any existing confluence.user.* that belongs to JIRA user to prevent any duplicate failures in running the last query DELETE FROM OS_PROPERTYENTRY WHERE entity_name IN (SELECT entity_name FROM tempospe conf, jirauser jira WHERE conf.entity_name=concat('CWD_', jira.username)) AND entity_key like 'confluence.user%'; //update the correct mapping UPDATE OS_PROPERTYENTRY conf, jirauser jira SET conf.entity_name=concat('CWD_', jira.username), conf.entity_id=0 WHERE conf.entity_name = 'OSUser_user' AND conf.entity_id=jira.id AND conf.entity_key LIKE 'confluence.user%';
Other scenarios
Dumping some queries that might be useful here. Note that the following are only for non-JIRA related.
Make sure to backup Confluence database before running any of the following SQL queries
MySQL
-- Query#1 Delete any existing CWD_ profile pictures to avoid any duplicates in #2, #3, #4 DELETE FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.user.profile.picture' AND entity_name LIKE 'CWD_%'; -- Query#2 Resetting the profile pictures for Confluence local users that were in users table UPDATE OS_PROPERTYENTRY ospe, users loc SET ospe.entity_name=concat('CWD_', loc.name), ospe.entity_id = 0 WHERE ospe.entity_name =concat('LOC_', loc.name) AND ospe.entity_id = loc.id AND ospe.entity_key = 'confluence.user.profile.picture'; -- Query#3 Resetting the profile pictures for Confluence (LDAP) users that were in external_entities table UPDATE OS_PROPERTYENTRY ospe, external_entities ext SET ospe.entity_name=concat('CWD_', ext.name), ospe.entity_id = 0 WHERE ospe.entity_name =concat('EXT_', ext.name) AND ospe.entity_id = ext.id AND ospe.entity_key = 'confluence.user.profile.picture'; -- Query#4 Resetting the profile pictures for Confluence users that were in os_user table UPDATE OS_PROPERTYENTRY ospe, os_user os SET ospe.entity_name=concat('CWD_', os.username), ospe.entity_id = 0 WHERE ospe.entity_id = os.id AND ospe.entity_key = 'confluence.user.profile.picture' AND ospe.entity_name='OSUser_user';
PostgreSQL
-- Query#1 Delete any existing CWD_ profile pictures to avoid any duplicates in #2, #3, #4 DELETE FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.user.profile.picture' AND entity_name LIKE 'CWD_%'; -- Query#2 Resetting the profile pictures for Confluence local users that were in users table UPDATE OS_PROPERTYENTRY ospe SET entity_name= 'CWD_'||loc.name, entity_id = 0 FROM users loc WHERE ospe.entity_name = 'LOC_'||loc.name AND ospe.entity_id = loc.id AND ospe.entity_key = 'confluence.user.profile.picture'; -- Query#3 Resetting the profile pictures for Confluence (LDAP) users that were in external_entities table UPDATE OS_PROPERTYENTRY ospe SET entity_name= 'CWD_'||ext.name, entity_id = 0 FROM external_entities ext WHERE ospe.entity_name = 'EXT_'||ext.name AND ospe.entity_id = ext.id AND ospe.entity_key = 'confluence.user.profile.picture'; -- Query#4 Resetting the profile pictures for Confluence users that were in os_user table UPDATE OS_PROPERTYENTRY ospe SET entity_name= 'CWD_'||os.username, entity_id = 0 FROM os_user os WHERE ospe.entity_id = os.id AND ospe.entity_key = 'confluence.user.profile.picture' AND ospe.entity_name='OSUser_user';
Cheat sheet
| Scenario | Users are stored at which table? | OS_PROPERTYENTRY.entity_name format | <username> is picked from... | OS_PROPERTYENTRY.entity_id is picked from... |
|---|---|---|---|---|
| Scenario #1 | users | LOC_<username> | users.name | users.id |
| Scenario #2 | os_user or JIRADB.userbase | OSUser_user | os_user.username or JIRADB.userbase.username | os_user.id or JIRADB.userbase.id |
| Scenario #3 | external_entities | 'EXT_<username>' or 'com.atlassian.user.impl.ldap.DefaultLDAPUser_<username>' | external_entities.name | external_entities.id |
| Scenario #4 | cwd_user | CWD_<username> | cwd_user.user_name | nowhere, all set to '0' |
Hi Bernardo Acevedo,
I did confluence upgrade from 3.4.9 to 4.3 with the CROWD integration.I executed the queries given by you for ORACLE but still i'm facing the problem.Can you please help in this regard.
Thanks in advance,
Krishna