Confluence Data Center Page gadgets don't migrate after Jira 9.15 upgrade

Summary

After upgrading to Jira to 9.15 or above, existing Confluence Page gadgets are not migrated.

• Confluence 9 removed gadgets as announced in Gadgets moved to a Marketplace app from Confluence 9.2 onwards

• In Jira 9.15 we introduced a new Confluence page gadget: Jira Software 9.15.x release notes

Solution

Migrate gadgets manually

Run the following scripts to generate the needed SQL queries to migrate old Confluence Page gadgets to the newer Confluence Page Viewer gadgets.

1. Shut all nodes down before making these changes

2. Generate CSV file containing id numbers of Confluence page gadgets:

   • COPY (select id from portletconfiguration where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%') TO 'portlet.csv' DELIMITER ',' CSV; $ cat portlet.csv 10201 10202 10203

3. Determine applink ID for Confluence in Jira’s database:

   • SELECT pe.id AS property_id, pe.property_key AS property_key, ps.propertyvalue AS property_value FROM propertyentry pe JOIN propertystring ps ON pe.id = ps.id WHERE pe.property_key LIKE 'applinks%' ORDER BY pe.id;

4. Determine max ID of gadgetuserpreference table:

   • select max (id) from gadgetuserpreference ;

5. Generate SQL inserts:

   1. Create a .sh file using the below code:

   2. #!/bin/bash # Usage: ./generate_sql.sh start_id filename.csv # Check if the correct number of arguments is provided if [ "$#" -ne 3 ]; then echo "Usage: $0 start_id applink_id filename.csv" exit 1 fi # Assign command-line arguments to variables START_ID=$1 APPLINK_ID=$2 CSV_FILE=$3 # Check if the CSV file exists if [ ! -f "$CSV_FILE" ]; then echo "File not found: $CSV_FILE" exit 1 fi # Initialize ID counter ID=$START_ID # Read the CSV file while IFS=, read -r variables; do # Increment the ID counter ID=$((ID + 1)) echo "insert into gadgetuserpreference ( id, portletconfiguration, userprefkey, userprefvalue ) values ( $ID, ${variables[@]}, 'appLinkName' , 'Confluence' );" ID=$((ID + 1)) echo "insert into gadgetuserpreference ( id, portletconfiguration, userprefkey, userprefvalue ) values ( $ID, ${variables[@]}, 'appLinkId' , '$APPLINK_ID' );" ID=$((ID + 1)) echo "insert into gadgetuserpreference ( id, portletconfiguration, userprefkey, userprefvalue ) values ( $ID, ${variables[@]}, 'pageSpaceTitle' , '' );" ID=$((ID + 1)) echo "insert into gadgetuserpreference ( id, portletconfiguration, userprefkey, userprefvalue ) values ( $ID, ${variables[@]}, 'pageSpace' , '' );" ID=$((ID + 1)) echo "insert into gadgetuserpreference ( id, portletconfiguration, userprefkey, userprefvalue ) values ( $ID, ${variables[@]}, 'searchAndTitle' , '' );" done < "$CSV_FILE"

   3. Run the file using the ID from gadgetuserpreference query and CSV file above, for example:

      • ./generate_sql.sh 10374 20e5d165-daca-35e4-8da9-e97750c224b4 portlet.csv > insert-confluence-page-viewer.sql

6. Execute the insert SQL statements ( insert-confluence-page-viewer.sql ) then run the following conversion and cleanup SQL:

   • # Change isEditable to showEdit update gadgetuserpreference set userprefkey = 'showEdit' where userprefkey = 'isEditable' and portletconfiguration in ( select id from portletconfiguration where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%' ); # Modify showLink values update gadgetuserpreference set userprefvalue = 'on' where userprefkey = 'showLink' and userprefvalue = 'true' and portletconfiguration in ( select id from portletconfiguration where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%' ); update gadgetuserpreference set userprefvalue = 'off' where userprefkey = 'showLink' and userprefvalue = 'false' and portletconfiguration in ( select id from portletconfiguration where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%' ); # Update showLink attribute update gadgetuserpreference set userprefkey = 'showView' where userprefkey = 'showLink' and portletconfiguration in ( select id from portletconfiguration where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%' ); # Convert Confluence 8.5 page gadget to Jira 10.3 Page Viewer update portletconfiguration set gadget_xml = 'rest/gadgets/1.0/g/com.atlassian.jira.gadgets:confluence-pages-gadget/gadgets/confluence-pages-gadget.xml' where gadget_xml like '%com.atlassian.confluence.plugins.gadgets:confluence-page-gadget%' # update max sequence for gadgetuserpref update sequence_value_item set seq_id = (select max(id) +10 from gadgetuserpreference ) where seq_name = 'GadgetUserPreference';