Problem

When there is a need to migrate from documentation theme to default theme, there are several details that might make the sysadmin unsure about the impact of the migration, especially if:

• There are too many spaces in the Confluence instance (hundreds or more);
• A large (and possibly unknown) portion of the spaces contains Documentation Theme and/or CSS customization.

In addition to following the Switch to the Default Theme documentation, this KB presents some SQL queries that can be used to count the number of spaces with explicit Documentation Theme configuration, as well as queries to find out which of these spaces contain custom Theme configuration or CSS Stylesheets.

Cause

Future Confluence versions (>= 6.0) will no longer support the The Documentation Theme. Some customers are looking into migrating to the Default Theme prior to Confluence 6.0 release, and there is no direct way of discovering which spaces might be affected by the migration without using SQL queries.

Even if the sysadmin sets the Global Theme to Default, spaces which have explicit Documentation Theme setting enabled are not automatically migrated to Default Theme. Additionally, spaces with Theme/CSS customization may potentially get broken when switching to the default theme;

Workaround

• In order to evaluate how many spaces may be impacted by the theme migration, a sysadmin may run the following SQL query to find out which spaces have explicit Documentation Theme enabled:

  SELECT spaces.spacekey,spaces.spacename
  FROM bandana
  JOIN spaces ON bandana.bandanacontext = spaces.spacekey
  WHERE bandanakey='atlassian.confluence.theme.settings'
    AND bandanavalue LIKE '%<string>com.atlassian.confluence.plugins.doctheme:documentation</string>%';

• If there are just a few explicit documentation theme spaces, we suggest:
  • Switch the global theme to default
  • Manually switch each space to default theme. Contact space admins for this change, and have them check if spaces have customizations:
    

• If there are too many spaces with explicit documentation theme configured, we suggest:

  • Use this query to check which enabled explicit documentation spaces have customizations (either wiki markup or CSS stylesheet). This query also shows the customizations of each space, so there might be spaces that appear twice in the query result:

    SELECT spaces.spacekey,spaces.spacename,bandanakey,bandanavalue FROM bandana 
            JOIN spaces ON bandana.bandanacontext = spaces.spacekey
            WHERE bandana.bandanacontext IN 
    	( SELECT bandanacontext FROM bandana 
                WHERE bandanakey='atlassian.confluence.theme.settings' 
                AND bandanavalue LIKE '%<string>com.atlassian.confluence.plugins.doctheme:documentation</string>%' )
            AND 
            (bandanakey='com.atlassian.confluence.plugins.doctheme' OR bandanakey='atlassian.confluence.css.resource.custom');
    

  • The spaces resulting from this last query will need to be manually migrated by the space admin.

  • This query shows the spaces that present less risk to be migrated to default theme in bulk (with UPDATE SQL query), because they don't have CSS nor Documentation Theme customization:

    SELECT distinct(spaces.spacekey),spaces.spacename FROM spaces 
            WHERE spaces.spacekey IN 
            ( SELECT bandanacontext FROM bandana 
                WHERE bandanakey='atlassian.confluence.theme.settings' 
                AND bandanavalue LIKE '%<string>com.atlassian.confluence.plugins.doctheme:documentation</string>%' )
            AND spaces.spacekey NOT IN
            (SELECT bandanacontext FROM bandana 
                WHERE bandanakey='com.atlassian.confluence.plugins.doctheme' 
                OR bandanakey='atlassian.confluence.css.resource.custom');
    

  • And the following SQL query changes the doc theme back to default theme on any spaces that have doc theme explicitly enabled, but neither CSS nor Wiki Markup customizations:
    

    Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

    UPDATE BANDANA SET bandanavalue ='<map>
      <entry>
        <string>theme.key</string>
        <string></string>
      </entry>
    </map>' 
        WHERE bandanavalue LIKE '%<string>com.atlassian.confluence.plugins.doctheme:documentation</string>%' 
        AND bandanakey = 'atlassian.confluence.theme.settings'
        AND bandanacontext NOT IN
            ( SELECT bandanacontext FROM bandana 
              WHERE bandanakey='com.atlassian.confluence.plugins.doctheme' 
              OR bandanakey='atlassian.confluence.css.resource.custom');
    

Be aware that after the theme migration, some users may still lose some very specific features, as described in Feature differences between Documentation and Default Theme.