Keystore table is empty post migrating from Confluence Cloud

I have had a stupendously shitty experience trying to import OnDemand data into local JIRA and Confluences. In this bug report I've documented the problems, what I think OnDemand developers could do to fix them in bold, and how I worked around them.

The problem is that after importing a Confluence Could export into Confluence (5.0.2), you'll find all jiraissues macros broken (note: I had previously migrated JIRA data to its new home):

and a cryptic error in the logs:

java.lang.NullPointerException: currentApplication
	at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:204)
	at com.atlassian.applinks.core.auth.trusted.TrustedApplicationsRequestFactory.<init>(TrustedApplicationsRequestFactory.java:31)
	at com.atlassian.applinks.core.auth.trusted.TrustedAppsAuthenticationProviderPluginModule$1.getRequestFactory(TrustedAppsAuthenticationProviderPluginModule.java:80)
	at com.atlassian.applinks.core.auth.ApplicationLinkRequestFactoryFactoryImpl.getApplicationLinkRequestFactory(ApplicationLinkRequestFactoryFactoryImpl.java:53)

With a bit of thinking one might figure out the error: the applinks are all broken, as is to be expected with the change of host.

Suggested fix 1: improve the jiraissues error message when applinks have problems

So off we go to the applinks page:

Applinks are not editable, because they are marked 'system'.

Suggested fix 2: The Confluence Cloud exporter should remove the 'system' flag from the applink, so mortals can edit them

The 'system' flag can be removed manually in the database:

mysql> select BANDANAKEY,BANDANAVALUE from BANDANA WHERE bandanakey like 'applinks.%.system';
+------------------------------------------------------------------+-----------------------+
| BANDANAKEY                                                       | BANDANAVALUE          |
+------------------------------------------------------------------+-----------------------+
| applinks.admin.30e7e368-6e59-357d-b986-d3374f95a205.system       | <string>true</string> |
| applinks.application.30e7e368-6e59-357d-b986-d3374f95a205.system | <string>true</string> |
+------------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
mysql> delete from BANDANA WHERE bandanakey like 'applinks.%.system';Query OK, 2 rows affected (0.01 sec)

Now I can edit the applink:

And then my problems really began.

For a start, the applink URL isn't editable:

(Questionable design decision here: sure, changing the URL to a different JIRA would break everything, but most times it's the same JIRA, just under a different URL.)

So anyway, let's edit the URL in the database, first by finding the applink ID, finding the applink's properties, then changing the URLs:

mysql> SELECT BANDANAVALUE FROM BANDANA WHERE BANDANAKEY = 'applinks.global.application.ids';
+------------------------------------------------------------------------+
| BANDANAVALUE                                                           |
+------------------------------------------------------------------------+
| <list>
  <string>30e7e368-6e59-357d-b986-d3374f95a205</string>
</list> |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update BANDANA set BANDANAVALUE=replace(BANDANAVALUE, 'somecompany.atlassian.net', 'jira.somecompany.com') where BANDANAKEY like '%30e7e368-6e59-357d-b986-d3374f95a205%.url';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

(and whatever you do, don't change the applink's title-even though it contains an incorrect URL too-the title is embedded in hundreds of Confluence page jiraissues macros. That is a job for bulk-rewriting.)

After a Confluence restart, the applink appears with the correct link.

Switching to the 'Outgoing Authentication' tab, it claims to be in Status: Configured. Just to be sure let's disable and re-enable it. Clicking 'Disable' is easy enough, but then:

Where is the 'Enable' link??

This is a UI bug (affects Firefox 16.0.1, Chrome 26.0.1410.63) - the link is actually there, and can be seen by clicking on the last form element (the timeout field) and pressing tab:

Suggested fix #3: Atlassian should use a sane, modern JS library that sizes popups appropriately

So we click 'Enable', and:

java.lang.NullPointerException
	at com.atlassian.security.auth.trustedapps.filter.TrustedApplicationsFilter$CertificateServerImpl.writeCertificate(TrustedApplicationsFilter.java:201)
	at com.atlassian.security.auth.trustedapps.filter.TrustedApplicationsFilter.doFilter(TrustedApplicationsFilter.java:90)
	at com.atlassian.confluence.util.AbstractBootstrapHotSwappingFilter.doFilter(AbstractBootstrapHotSwappingFilter.java:30)

"But hang on", I hear you say, "why are you trying to fix this obviously borked applink? Just delete it and start again". No such luck:

(and, in case you were wondering, it breaks the same way if you don't change the URLs in the database as I did)

"Okay, what about that 'Upgrade application link' button?" It dies with the JS equivalent of a NullPointerException:

So we're left with an applink we can't fix and can't delete.

Fortunately, Googling the exception brings up this KB article, which points to the cause: the #@#%! Confluence Cloud exporter has erased all the KEYSTORE records, which trustedapps depends on:

mysql> select * from KEYSTORE;
Empty set (0.00 sec)

The KB article points to CONF-11074, which helpfully includes a JSP to recreate the KEYSTORE.

So I run the JSP, which populates my KEYSTORE table with a public/private keypair, and now - hurrah - I can enable Outgoing Authentication. After a restart, the jiraissues macros that rely on the applink work too.

Suggested fix #4: if you have to wipe the KEYSTORE table, replace its values with something else!

Don't just leave them blank when doing so results in a broken, undeletable, unfixable applink. Create a new unique keypair using the code from the CONF-11074 JSP and put the keys in KEYSTORE.

Thank you for reading.