Summary

After a new installation of Jira Service Management, creating a Service Management type project failed. Error cacheLoader returned null for key 'SD_REQTYPE' can be seen on the screen as well as in the log

Environment

JSM version 3.2.0 and above

Diagnosis

• In the UI or in the application log, you'll see exception that goes like this

  java.lang.IllegalArgumentException: cacheLoader returned null for key 'SD_REQTYPE'
  	at com.atlassian.jira.cache.request.RequestCacheImpl.getNotNull(RequestCacheImpl.java:147)
  	at com.atlassian.jira.cache.request.RequestCacheImpl.get(RequestCacheImpl.java:58)
  	at com.atlassian.jira.avatar.AvatarManagerImpl.getDefaultAvatarId(AvatarManagerImpl.java:505)
  	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  ...

• Check for an error like the following in atlassian-jira.log.* or atlassian-servicedesk.log.*

  2021-02-05 10:49:21,541+0100 JIRA-Bootstrap ERROR      [c.a.s.b.lifecycle.server.ServerPluginLifeCycle] '1.2.6.1' version of Service Desk cannot be upgraded to '4.12.3-REL-0010'. Upgrade to '3.0.0' <= <version> < '3.2.0' first.
  com.atlassian.servicedesk.bootstrap.lifecycle.server.UnsatisfiedVersionRequirementException: '1.2.6.1' version of Service Desk cannot be upgraded to '4.12.3-REL-0010'. Upgrade to '3.0.0' <= <version> < '3.2.0' first.

  or

  2021-02-05 10:49:21,541+0100 JIRA-Bootstrap ERROR      [c.a.s.b.lifecycle.server.ServerPluginLifeCycle] '2.1.1' version of Service Desk cannot be upgraded to '4.12.0-REL-0008'. Upgrade to '3.0.0' <= <version> < '3.2.0' first.
  com.atlassian.servicedesk.bootstrap.lifecycle.server.UnsatisfiedVersionRequirementException: '2.1.1' version of Service Desk cannot be upgraded to '4.12.0-REL-0008'. Upgrade to '3.0.0' <= <version> < '3.2.0' first.

Cause/Explanation

It could be a new installation of Jira Service Management that doesn't seem like an upgrade. This happens if a version of Service Management (formerly known as Service Desk) below 3.0.0 was installed before and removed, however, left a remnant in the database.

• This is caused by the same issue mentioned in https://confluence.atlassian.com/jirakb/unable-to-render-element-error-in-navigation-bar-after-upgrading-jira-service-management-845943102.html.

Solution

For "new" installation of Jira Service Management that encounters this issue, and  if it's totally alright to purge all previous data of Service Management, we can avoid the error by removing some data directly from the database.

1. Uninstall Service Management from Administration > Applications > Versions & licenses with the option "Also remove my Jira Service Desk license and configuration" remain selected.
2. Stop Jira.

3. Check <Jira_home>/plugins/installed-plugins folder. You might still see some Service Management plugins in the folder, remove them.

   atlassian-chaperone- atlassian-client-resource- atlassian-frontend-runtime-plugin- atlassian-pocketknife-api-commons-plugin- atlassian-timed-promise-plugin- jira-assets-plugin- jira-email-processor-plugin jira-servicedesk- jira-servicedesk-application- jira-servicedesk-public-rest-api-plugin- jira-workinghours-plugin- jwt-plugin- psmq-plugin- querydsl-4.1.4-provider-plugin- servicedesk-approvals-plugin- servicedesk-automation-modules-plugin- servicedesk-automation-plugin- servicedesk-automation-then-webhook-plugin- servicedesk-canned-responses-plugin- servicedesk-core-ui-plugin- servicedesk-frontend-plugin- servicedesk-internal-base-plugin- servicedesk-knowledge-base-plugin- servicedesk-lingo-plugin- servicedesk-notifications-plugin- servicedesk-project-ui-plugin- servicedesk-reports-plugin- servicedesk-search-plugin- servicedesk-variable-substitution-plugin-

4. Delete these hidden folders to clear the plugin cache.
   1. <Jira_home>/plugins/.bundled-plugins
   2. <Jira_home>/plugins/.osgi-plugins
      

5. Run these queries in the database.

   DROP TABLE IF EXISTS AO_2C4E5C_MAILGLOBALHANDLER; DROP TABLE IF EXISTS AO_2C4E5C_MAILHANDLER; DROP TABLE IF EXISTS AO_2C4E5C_MAILCHANNEL; DROP TABLE IF EXISTS AO_2C4E5C_MAILCONNECTION; DROP TABLE IF EXISTS AO_2C4E5C_MAILITEMAUDIT; DROP TABLE IF EXISTS AO_2C4E5C_MAILITEMCHUNK; DROP TABLE IF EXISTS AO_2C4E5C_MAILITEM; DROP TABLE IF EXISTS AO_2C4E5C_MAILRUNAUDIT; DROP TABLE IF EXISTS AO_2C4E5C_MAILBLOCKLISTS; DROP TABLE IF EXISTS AO_2C4E5C_MAILWHITELISTS; DROP TABLE IF EXISTS AO_54307E_AGENTSIGNATURES; DROP TABLE IF EXISTS AO_54307E_ASYNCUPGRADERECORD; DROP TABLE IF EXISTS AO_54307E_CAPABILITY; DROP TABLE IF EXISTS AO_54307E_CONFLUENCEKBENABLED; DROP TABLE IF EXISTS AO_54307E_CONFLUENCEKBLABELS; DROP TABLE IF EXISTS AO_54307E_CONFLUENCEKB; DROP TABLE IF EXISTS AO_54307E_CSATENTRIES; DROP TABLE IF EXISTS AO_54307E_CUSTOMGLOBALTHEME; DROP TABLE IF EXISTS AO_54307E_EMAILCHANNELSETTING; DROP TABLE IF EXISTS AO_54307E_EMAILSETTINGS; DROP TABLE IF EXISTS AO_54307E_GOAL; DROP TABLE IF EXISTS AO_54307E_GROUPTOREQUESTTYPE; DROP TABLE IF EXISTS AO_54307E_IMAGES; DROP TABLE IF EXISTS AO_54307E_METRICCONDITION; DROP TABLE IF EXISTS AO_54307E_PARTICIPANTSETTINGS; DROP TABLE IF EXISTS AO_54307E_QUEUECOLUMN; DROP TABLE IF EXISTS AO_54307E_SERIES; DROP TABLE IF EXISTS AO_54307E_STATUSMAPPING; DROP TABLE IF EXISTS AO_54307E_THRESHOLD; DROP TABLE IF EXISTS AO_54307E_TIMEMETRIC; DROP TABLE IF EXISTS AO_54307E_VIEWPORTFIELDVALUE; DROP TABLE IF EXISTS AO_54307E_VIEWPORTFIELD; DROP TABLE IF EXISTS AO_54307E_VIEWPORTFORM; DROP TABLE IF EXISTS AO_7A2604_HOLIDAY; DROP TABLE IF EXISTS AO_7A2604_WORKINGTIME; DROP TABLE IF EXISTS AO_7A2604_CALENDAR; DROP TABLE IF EXISTS AO_9B2E3B_EXEC_RULE_MSG_ITEM; DROP TABLE IF EXISTS AO_9B2E3B_IF_COND_CONF_DATA; DROP TABLE IF EXISTS AO_9B2E3B_IF_COND_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_IF_CONDITION_CONFIG; DROP TABLE IF EXISTS AO_9B2E3B_IF_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_PROJECT_USER_CONTEXT; DROP TABLE IF EXISTS AO_9B2E3B_RSETREV_PROJ_CONTEXT; DROP TABLE IF EXISTS AO_9B2E3B_RSETREV_USER_CONTEXT; DROP TABLE IF EXISTS AO_9B2E3B_THEN_ACT_CONF_DATA; DROP TABLE IF EXISTS AO_9B2E3B_THEN_ACT_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_THEN_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_THEN_ACTION_CONFIG; DROP TABLE IF EXISTS AO_9B2E3B_WHEN_HAND_CONF_DATA; DROP TABLE IF EXISTS AO_9B2E3B_WHEN_HANDLER_CONFIG; DROP TABLE IF EXISTS AO_F1B27B_HISTORY_RECORD; DROP TABLE IF EXISTS AO_F1B27B_KEY_COMPONENT; DROP TABLE IF EXISTS AO_F1B27B_KEY_COMP_HISTORY; DROP TABLE IF EXISTS AO_F1B27B_PROMISE; DROP TABLE IF EXISTS AO_F1B27B_PROMISE_HISTORY_CASCADE; DROP TABLE IF EXISTS AO_54307E_GROUP; DROP TABLE IF EXISTS AO_54307E_QUEUE; DROP TABLE IF EXISTS AO_54307E_REPORT; DROP TABLE IF EXISTS AO_9B2E3B_IF_THEN; DROP TABLE IF EXISTS AO_9B2E3B_IF_THEN_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_RULE; DROP TABLE IF EXISTS AO_9B2E3B_RULESET_REVISION; DROP TABLE IF EXISTS AO_9B2E3B_RULE_EXECUTION; DROP TABLE IF EXISTS AO_9B2E3B_RULESET; DROP TABLE IF EXISTS AO_54307E_VIEWPORT; DROP TABLE IF EXISTS AO_54307E_CUSTOMTHEME; DROP TABLE IF EXISTS AO_54307E_OUT_EMAIL_SETTINGS; DROP TABLE IF EXISTS AO_54307E_SUBSCRIPTION; DROP TABLE IF EXISTS AO_54307E_SYNCUPGRADERECORD; DROP TABLE IF EXISTS AO_54307E_SERVICEDESK; DROP TABLE IF EXISTS AO_54307E_ORGANIZATION_MEMBER; DROP TABLE IF EXISTS AO_54307E_ORGANIZATION_MEMBERS; DROP TABLE IF EXISTS AO_54307E_ORGANIZATION_PROJECT; DROP TABLE IF EXISTS AO_54307E_ORGANIZATION; DROP TABLE IF EXISTS AO_54307E_SLAAUDITLOGDATA; DROP TABLE IF EXISTS AO_54307E_SLAAUDITLOG; DROP TABLE IF EXISTS AO_F1B27B_PROMISE_HISTORY;

6. The goal is to remove all tables in the range so make sure there are no more table with name starts with

   AO_2C4E5C_* AO_54307E_* AO_7A2604_* AO_9B2E3B_* AO_F1B27B_*

7. Run these queries in the database.

   DELETE FROM propertytext where id in (select id from propertyentry where entity_name like 'com.atlassian.servicedesk%' and propertytype='6');
   DELETE FROM propertyentry where entity_name like 'com.atlassian.servicedesk%' and propertytype='6';
   DELETE FROM pluginstate where pluginkey like 'com.atlassian%';

8. Start Jira.
9. Before you install Service Management again, check the database to see if any table with the prefix mentioned in step 6 appears. If there's none, continue with installing Service Management. 
    If there's any Service Management related table found, do not install Service Management yet but contact Atlassian Support for further assistance. Share the following:
   1. 1. Support zip
      2. list of <Jira_home>/plugins/installed-plugins
      3. list of database tables