Unable to set up Email Channel: Email is currently used by another Service Management project

Summary

Problem

Users are not able to configure Email Request in Jira Service Desk. The following error message appears:

Diagnosis

Diagnostic Steps

• Check if there is an existing project that is currently using that email address

• If there is none, you can then check further if you are affected by this problem.

Cause

The email address is somehow not deleted from the database properly when the corresponding project is removed.

Solution

Resolution

Remove the "residue" of the problematic email address from the database.

1. Run the following query to find the deleted project that contains problematic email address. If it returns any result, you can use below resolution.

   1 select * from "AO_54307E_VIEWPORT" where "PROJECT_ID" not in (select id from project);

2. Check all email addresses in the database.

   1 select * from "AO_2C4E5C_MAILCONNECTION";

3. Backup the database before proceeding with below steps.

4. Bring down Jira and run below queries.

   1 delete from "AO_2C4E5C_MAILHANDLER" where "MAIL_CHANNEL_ID" in (select "ID" from "AO_2C4E5C_MAILCHANNEL" where "PROJECT_ID" not in (select id from project));
   1 delete from "AO_2C4E5C_MAILCHANNEL" where "PROJECT_ID" not in (select id from project);
   1 delete from "AO_2C4E5C_MAILCONNECTION" where "ID" not in (select "MAIL_CONNECTION_ID" from "AO_2C4E5C_MAILCHANNEL");
   1 delete from "AO_54307E_EMAILCHANNELSETTING" where "SERVICE_DESK_ID" in (select "ID" from "AO_54307E_SERVICEDESK" where "PROJECT_ID" not in (select id from project));

5. Bring up Jira.