Bulk change the value of the Incoming mail handler configuration fields
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Solution
Description
Mail Handler configuration data is stored in the tables propertyentry and propertystring. These steps are a guide on how to utilize these two tables for bulk updating mail handler configuration.
The example below would be steps for bulk updating the forward email of mail handlers in a Jira instance.
Get list of Mail Handler IDs
We have a KB on updating all mail handlers to associate with incoming mail server. The query from this KB retrieves information from different tables serviceconfig, mailserver, propertyentry, and propertystring to get the mail handler ID associated with all the mail handlers among other details.
1
2
3
4
5
6
SELECT sc.id AS "Handler ID", sc.servicename AS "Handler Name", ms.id AS "Mail Server ID", ms.servername AS "Mail Server Name", ps.id AS "propertystring ID"
FROM serviceconfig sc
INNER JOIN propertyentry pe ON pe.property_key = 'popserver' AND pe.entity_id = sc.id
INNER JOIN propertystring ps ON ps.id = pe.id
INNER JOIN mailserver ms ON CAST(ps.propertyvalue AS integer) = ms.id
WHERE sc.clazz = 'com.atlassian.jira.service.services.mail.MailFetcherService';Sample result is shown below. We are interested in getting all the Handler IDs, as this is the list of all the incoming mail handlers in the system. We will use this as reference in the next query.
Handler ID | Handler Name | Mail Server ID | Mail Server Name | propertystring ID |
|---|---|---|---|---|
10300 | TEST | 10100 | imap.atlassian.com | 11803 |
Get list of Property String IDs
Building up on this, we turn to developer documentation configuration properties in the database tables for details on the two tables propertyentry and propertystring to come up with the query below to retrieve all mail handler details from the property string and property entry tables.
1
2
3
4
SELECT *
FROM propertyentry pe
INNER JOIN propertystring ps ON pe.id = ps.id
WHERE pe.property_key = 'forwardEmail' AND pe.entity_id IN (<list of Handler IDs we got earlier>);Example using the mail handler ID sample from above:
1
2
3
4
SELECT *
FROM propertyentry pe
INNER JOIN propertystring ps ON pe.id = ps.id
WHERE pe.entity_id IN (10300);id | entity_name | entity_id | property_key | propertytype | id | propertyvalue |
|---|---|---|---|---|---|---|
11800 | ServiceConfig | 10300 | handler | 5 | 11800 | com.atlassian.jira.plugins.mail.handlers.CreateOrCommentHandler |
11801 | ServiceConfig | 10300 | handler.params | 5 | 11801 | project=INAZUMA,issuetype=10002,bulk=ignore,createusers=false,notifyusers=true,ccwatcher=false,ccassignee=true,stripquotes=false |
11802 | ServiceConfig | 10300 | forwardEmail | 5 | 11802 | FORWARDMAIL@ATLASSIAN.COM |
11803 | ServiceConfig | 10300 | popserver | 5 | 11803 | 10100 |
As an example, if we're looking for the forwardEmail property, we can use this more specific query instead by filtering the property_key to the forwardEmail property.
1
2
3
4
SELECT *
FROM propertyentry pe
INNER JOIN propertystring ps ON pe.id = ps.id
WHERE pe.property_key = 'forwardEmail' AND pe.entity_id IN (10300);The property_key forwardEmail contains the forward email/s for bulk update on your instance. Take note of the id, which is the propertystring/propertykey id that will be used for the update statement.
In the example below, the propertystring-id for the forward email is 11802.
id | entity_name | entity_id | property_key | propertytype | id | propertyvalue |
|---|---|---|---|---|---|---|
11802 | ServiceConfig | 10300 | forwardEmail | 5 | 11802 | FORWARDMAIL@ATLASSIAN.COM |
Jira needs a restart for below DB updates to take effect. Plan the changes accordingly.
Update corresponding propertystring entries
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The update statement below is from our KB on updating all mail handlers to associate with incoming mail server. We reuse this update statement to specifically update only specific fields based on the properystring-id from the previous query.
1
UPDATE propertystring SET propertyvalue = '<new-id>' WHERE id = <propertystring-id>;Was this helpful?