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.

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.

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.

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:

SELECT *
FROM propertyentry pe
INNER JOIN propertystring ps ON pe.id = ps.id
WHERE pe.entity_id IN (10300);

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.

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.

Update corresponding propertystring entries

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.

UPDATE propertystring SET propertyvalue = '<new-id>' WHERE id = <propertystring-id>;