Hello everyone!
 
In an effort to keep bug reports up to date, we have tested the behavior mentioned here and were not able to replicate this issue on Confluence 6.15.7. As such, I will resolve this issue as "Obsolete", fixed in 6.15.7
 
If you believe you're encountering a regression or you're seeing a similar issue, please reach out to our [support team|https://support.atlassian.com/] with details on how you can replicate this and they can help narrow down the issue. They can also help re-open any closed tickets when appropriate.
 
Cheers,
Monique | Atlassian Support

Wow. This was a really big pain in the neck. Neither of the above 2 combos worked form me with Confluence 3.4.6.

Here was the magic sauce for Conf 3.4.6 on mysql. Note this will probably ONLY work on my sql because of the specific mysql keyword IGNORE I used in the last statement which skips over the insertion of lines with duplicate keys. There are probably equivalents in other databases.

If anyone else gives answers they're most useful if you give the exact versions for what you're dealing with. I'm guessing this doesn't happen to everyone because it's kind of a big deal when it does.

CREATE 
    TABLE test2
    ( 
        entity_name varchar(125) NOT NULL, 
        entity_id bigint(20) NOT NULL, 
        entity_key varchar(200) NOT NULL, 
        key_type int(11), 
        boolean_val bit(1), 
        double_val double, 
        string_val varchar(255), 
        text_val mediumtext, 
        long_val bigint(20), 
        int_val int(11), 
        date_val datetime, 
        PRIMARY KEY USING BTREE (entity_name, entity_id, entity_key), 
        INDEX ospe_entityid_idx USING BTREE (entity_id) 
    );
    
    
    INSERT INTO test2 (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
SELECT DISTINCT concat('EXT_', e.name), e.id, 'confluence.user.profile.picture', 5, false, 0.0, null, null, 0, 0, null
FROM external_entities e, os_propertyentry o where o.entity_name = concat('LOC_',e.name);


UPDATE test2 t
SET t.string_val = (SELECT o.string_val  
FROM os_propertyentry o, external_entities e
WHERE o.entity_name = concat('LOC_', e.name) AND t.entity_name = concat('EXT_',e.name) AND o.entity_key='confluence.user.profile.picture');

INSERT IGNORE INTO OS_PROPERTYENTRY (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
SELECT t.entity_name, t.entity_id, t.entity_key, t.key_type, t.boolean_val, t.double_val, t.string_val, t.text_val, t.long_val, t.int_val, t.date_val FROM test2 t
WHERE t.string_val IS NOT NULL;

If the affected users' ENTITY_TYPE in EXTERNAL_ENTITIES table is com.atlassian.user.impl.ldap.DefaultLDAPUser (not EXT), you will need to modify Roy's workaround to cater those users. The modified SQL commands should be something like this:

1. First:

   CREATE 
       TABLE test
       ( 
           entity_name varchar(125) NOT NULL, 
           entity_id bigint(20) NOT NULL, 
           entity_key varchar(200) NOT NULL, 
           key_type int(11), 
           boolean_val bit(1), 
           double_val double, 
           string_val varchar(255), 
           text_val mediumtext, 
           long_val bigint(20), 
           int_val int(11), 
           date_val datetime, 
           PRIMARY KEY USING BTREE (entity_name, entity_id, entity_key), 
           INDEX ospe_entityid_idx USING BTREE (entity_id) 
       )
   

2. Second:

   INSERT INTO test (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
   SELECT DISTINCT concat('com.atlassian.user.impl.ldap.DefaultLDAPUser_', e.name), e.id, 'confluence.user.profile.picture', 5, false, 0.0, null, null, 0, 0, null
   FROM external_entities e, os_propertyentry o where o.entity_name = concat('LOC_',e.name);
   

3. Third:

   UPDATE test t
   SET t.string_val = (SELECT o.string_val  
   FROM os_propertyentry o, external_entities e
   WHERE o.entity_name = concat('LOC_', e.name) AND t.entity_name = concat('com.atlassian.user.impl.ldap.DefaultLDAPUser_',e.name) AND o.entity_key='confluence.user.profile.picture');
   

4. Fourth:

   INSERT INTO OS_PROPERTYENTRY (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
   SELECT t.entity_name, t.entity_id, t.entity_key, t.key_type, t.boolean_val, t.double_val, t.string_val, t.text_val, t.long_val, t.int_val, t.date_val FROM test t
   WHERE t.string_val IS NOT NULL;
   

Hi Alberto,

Sorry for not noticing your reply. Do you still need help to fix your profile setting?

Cheers,

Roy

Hi,
we are working with confluence 3.1 and crowd. After join confluence to crowd we encurred in this issue.
We tried the procedure suggested by Roy but the issue is still present also restarting confluence and reindexing it.

Some help?

Thanks.
AGB Lugano

You can update the OS_PROPERTYENTRY table with the required value to make your users' profile pictures to show up.

Please get your DBA to do this for you. First make a database backup, then please translate the MYSQL code below to your own:

1. Create a new table called 'BLAH', for this you can copy the DDL for OS_PROPERTYENTRY table. For example, the equivalent in MySQL is:

   CREATE 
       TABLE BLAH
       ( 
           entity_name varchar(125) NOT NULL, 
           entity_id bigint(20) NOT NULL, 
           entity_key varchar(200) NOT NULL, 
           key_type int(11), 
           boolean_val bit(1), 
           double_val double, 
           string_val varchar(255), 
           text_val mediumtext, 
           long_val bigint(20), 
           int_val int(11), 
           date_val datetime, 
           PRIMARY KEY USING BTREE (entity_name, entity_id, entity_key), 
           INDEX ospe_entityid_idx USING BTREE (entity_id) 
       ) 
   

2. Populate table BLAH with the list of user names that require their profile pictures to be updated:
   1. First populate all columns except for the string_val column. This is for the profile picture file, which we will update later.

      INSERT INTO blah (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
      SELECT DISTINCT concat('EXT_', e.name), e.id, 'confluence.user.profile.picture', 5, false, 0.0, null, null, 0, 0, null
      FROM external_entities e, os_propertyentry o where o.entity_name = concat('EXT_',e.name);
      

      Concatenation in Oracle is different from the MySQL code above.
      Your users have to have logged in previously for them to show up in the external_entities table. Only then their external user id can be created and we can add the profile picture for them with this query.
      If your users have not logged in to Confluence, their profile picture won't be copied by this query and they will have to update their profile setting themselves.

   2. Populate the string_val column in table blah:

      UPDATE BLAH b
      SET b.string_val = (SELECT o.string_val  
      FROM os_propertyentry o, external_entities e
      WHERE o.entity_name = concat('LOC_', e.name) AND b.entity_name = concat('EXT_',e.name) AND o.entity_key='confluence.user.profile.picture');
      

3. The next step is to copy from table BLAH into OS_PROPERTYENTRY table where 'EXT_<user name>' does not have a profile picture, ie. 'confluence.user.profile.picture' entry does not exist:

   INSERT INTO OS_PROPERTYENTRY (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) 
   SELECT b.entity_name, b.entity_id, b.entity_key, b.key_type, b.boolean_val, b.double_val, b.string_val, b.text_val, b.long_val, b.int_val, b.date_val FROM BLAH b
   WHERE b.string_val IS NOT NULL;
   

The last SQL command does not check for duplicate entry - it assumes that the 'EXT_<username>' does not have profile pictures yet. If you have crowd users that have created a profile picture you can just delete their rows. The insert statement will recreate the row.
If your users do not have a profile picture, the last SQL command will not update your OS_PROPERTYENTRY provided that the WHERE b.string_val IS NOT NULL flag is declared.

Cheers,
Roy

I'm having the same issue with Confluence 3.0.2 and Crowd 2.0.2: After delegating the user management to Crowd, the profile pictures are no longer assigned and every user needs to reassign them manually.

FYI, I reported this issue to Atlassian support and I am running Confluence 2.8.2 with Crowd 3.0.1.