Upgrading Confluence (using Jira for auth) causes profile pictures to vanish

[joshua kugler]

So, we upgraded our Confluence 3.3/Jira 4.2.4 pair to Confluence 4.3/Jira 5.1. The upgrade went well, and we've worked out all minor issues that have come up. Confluence uses Jira for auth, and that works (i.e. a user can log in to Confluence using their Jira credentials).

I read this: https://confluence.atlassian.com/display/DOC/Confluence+3.5+Upgrade+Notes#Confluence3.5UpgradeNotes-NewProfilePictures which says the user should keep their old profile pictures, if they had one selected.

But we've hit this issue: the user in Jira still has a profile picture displaying. However, the user in confluence no longer has a profile picture selected. If, however, they go to /users/editmyprofilepicture.action the first option for them is the profile picture they had prior to the upgrade. So, on one hand, great it's still there, but on the other hand, I don't want to make all my users go through the process to reclaim something that never should have vanished in the first place. I read over the upgrade notes again, and I don't see anything about profile pictures (except for the pasted link above).

What am I missing?

[Giuliano C_]

Joshua, before you try to reproduce the following commands, please, do a backup of your documents.

You're experiencing a seriusly bug reported.

This one workaround I can provide to you:

Maybe there are some missing attachments in the attachments folder. I'd advise to run the index rebuild and if it doesn't work, a rebuild from scratch, but first I would like to have some more information.

Could you try run these queries on your database?

select * from attachments a inner join content c on a.pageid = c.contentid
where ATTACHMENT_COMMENT = 'Uploaded Profile Picture' and USERNAME = '<USER_NAME>'

Where <USER_NAME> is the some user with the profile picture problem

and

select * from attachments where ATTACHMENT_COMMENT = 'Uploaded Profile Picture'

Export the results to a .csv file and attach to this issue.

Also, there's a lot of workaround about it through this page: https://jira.atlassian.com/browse/CONF-22298

Cheers

[joshua kugler]

I ran the index rebuild from scratch. No change.

The output of the queries are attached in a pipe delimited file. I also included a show tables output, as I had to modify your query due to MySQL being case-sensitive and most of the table names being upper case. (show_tables.txt) (uploaded_profile_pictures.txt) (user_picture.txt)

I will try some of the work-arounds listed in the issue report next.

[joshua kugler]

I tried the work-around here:
https://jira.atlassian.com/browse/CONF-22298?focusedCommentId=237904&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-237904

The " Applies for Upgrading to Confluence with JIRA User Management only" part.

This was run on Jira 5.1 and Confluence 3.5

CREATE TABLE jirauser AS (SELECT * FROM jiradb.userbase);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE tempospe AS (SELECT * FROM OS_PROPERTYENTRY);
Query OK, 2826 rows affected (0.20 sec)
Records: 2826  Duplicates: 0  Warnings: 0

mysql> 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%';
Query OK, 0 rows affected (0.01 sec)

mysql> 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%';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

I'm open to more ideas. :)