How to fix the SQL script to resolve the Automation For Jira error "Error while deleting/updating rule"

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

When upgrading Automation for Jira (A4J) from any version below 7.3.0 or 9.0.2 (if at some point used version lower than 7.3.0) to those or a newer version, or Jira SW/JSM was upgraded from any version below 9.11.0/5.11.0 to those or a newer version, and the environment is setup with MySQL or MSSQL, an error message "Error while updating/deleting rule. Error Please reload and try again" can be thrown when attempting to update or delete rules.

The solution presented in the articles (also below, in the Diagnosis section) recommends running an SQL script to correct the database. However, for some customers, this might fail due to unexpected indexes presented in the environment, which shall be added in the script to run that correctly or simply dropped.

Diagnosis

The UI throws error messages when editing or deleting rules:

With related messages in logs:

2023-11-27 15:14:22,748-0600 http-nio-8080-exec-7 ERROR user1 914x253122x1 1pcxlou 10.0.0.1 /rest/cb-automation/latest/project/GLOBAL/rule/135 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLServerException for delete from "jiraschema"."AO_589059_RULE_TO_SECRET"
    where "AO_589059_RULE_TO_SECRET"."RULE_ID" = ?
com.querydsl.core.QueryException: Caught SQLServerException for delete from "jiraschema"."AO_589059_RULE_TO_SECRET"
where "AO_589059_RULE_TO_SECRET"."RULE_ID" = ?
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
	at com.querydsl.sql.dml.SQLDeleteClause.execute(SQLDeleteClause.java:222)
	at com.codebarrel.jira.plugin.automation.store.JiraAutomationConfigStore.lambda$delete$14(JiraAutomationConfigStore.java:299)
	at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47)
	at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)
	...
	at com.codebarrel.data.api.jira.JiraDbConnectionManager.execute(JiraDbConnectionManager.java:45)
	at com.codebarrel.jira.plugin.automation.store.JiraAutomationConfigStore.delete(JiraAutomationConfigStore.java:285)
	at com.codebarrel.jira.plugin.automation.store.CachingAutomationConfigStore.delete(CachingAutomationConfigStore.java:115)
	at com.codebarrel.automation.api.service.AutomationConfigServiceImpl.delete(AutomationConfigServiceImpl.java:359)
	at com.codebarrel.automation.api.service.AutomationConfigServiceImpl.delete(AutomationConfigServiceImpl.java:352)
	at com.codebarrel.jira.plugin.automation.rest.RuleResource.deleteRule(RuleResource.java:358)
	...
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'jiraschema.AO_589059_RULE_TO_SECRET'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	...
	... 327 more

2023-11-27 15:14:35,316-0600 http-nio-8080-exec-18 ERROR user1 914x253156x2 1pcxlou 10.0.0.1 /rest/cb-automation/latest/secrets/GLOBAL [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLServerException for select "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_SECRET_PROJ_ASSOC"."PROJECT_ID"
    from "jiraschema"."AO_589059_RULE_SECRET" "AO_589059_RULE_SECRET"
    left join "jiraschema"."AO_589059_SECRET_PROJ_ASSOC" "AO_589059_SECRET_PROJ_ASSOC"
    on "AO_589059_RULE_SECRET"."ID" = "AO_589059_SECRET_PROJ_ASSOC"."RULE_SECRET_ID"
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_SECRET_PROJ_ASSOC"."PROJECT_ID"
from "jiraschema"."AO_589059_RULE_SECRET" "AO_589059_RULE_SECRET"
left join "jiraschema"."AO_589059_SECRET_PROJ_ASSOC" "AO_589059_SECRET_PROJ_ASSOC"
on "AO_589059_RULE_SECRET"."ID" = "AO_589059_SECRET_PROJ_ASSOC"."RULE_SECRET_ID"
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	...
	at com.codebarrel.jira.plugin.automation.store.JiraRuleSecretStore.lambda$getAllSecrets$1(JiraRuleSecretStore.java:47)
	at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47)
	...
	at com.codebarrel.data.api.jira.JiraDbConnectionManager.execute(JiraDbConnectionManager.java:45)
	at com.codebarrel.jira.plugin.automation.store.JiraRuleSecretStore.getAllSecrets(JiraRuleSecretStore.java:41)
	at com.codebarrel.jira.plugin.automation.store.CachingRuleSecretStore.getAllSecrets(CachingRuleSecretStore.java:61)
	at com.codebarrel.automation.api.service.RuleSecretServiceImpl.getAllSecrets(RuleSecretServiceImpl.java:64)
	at com.codebarrel.jira.plugin.automation.rest.SecretsResource.lambda$getSecretsForProject$0(SecretsResource.java:44)
	at com.codebarrel.jira.plugin.automation.rest.SecretsResource.withJiraAdminPermissions(SecretsResource.java:127)
	at com.codebarrel.jira.plugin.automation.rest.SecretsResource.getSecretsForProject(SecretsResource.java:44)
	...
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'jiraschema.AO_589059_RULE_SECRET'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	...
	... 331 more

The referred articles:

When running the script as explained in the article Upgrading to Automation for Jira 7.3 or later (which is also referred to by the other ones), for some environments errors related to indexes can be seen, as the example below (but not limited to them):

Msg 3701, Level 11, State 7, Line 17
Cannot drop the index 'jiraschema.AO_589059_AUDIT_ITEM_COMP_CGE.index_ao_589059_aud1292482400', because it does not exist or you do not have permission.
Msg 5074, Level 16, State 1, Line 18
The index 'AO_589059_AUDIT_ITEM_COMP_CGE_10052021' is dependent on column 'AUDIT_ITEM_ID'.
Msg 5074, Level 16, State 1, Line 18
The index 'AO_589059_AUDIT_ITEM_COMP_CGE_0110052021' is dependent on column 'AUDIT_ITEM_ID'.
Msg 4922, Level 16, State 9, Line 18
ALTER TABLE ALTER COLUMN AUDIT_ITEM_ID failed because one or more objects access this column.

Cause

Solution

We strongly advise proceeding with this solution in a lower environment first.

The script indicated in Upgrading to Automation for Jira 7.3 or late takes into consideration only the indexes that are from a vanilla Jira, if any difference is present in the database, the script execution will fail. To resolve this you shall:

List all indexes present in the environment with the code below (adjust if necessary):

SELECT
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.*
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
WHERE
     ind.is_primary_key = 0
     AND ind.is_unique = 0
     AND ind.is_unique_constraint = 0
     AND t.is_ms_shipped = 0
ORDER BY
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

Stop your Jira and backup your database
With the query result, compare it with the indexes related to ao_589059_* tables, and drop the custom indexes (or you can simply drop the custom indexes if they are already known)
Run the script indicated in Upgrading to Automation for Jira 7.3 or late once more (we expect it to be run without errors)
Restart your Jira
Test if the A4J error is gone
Recreate the custom indexes if necessary (with Jira stopped)

If you have any questions or are uncertain about the indexes or this process, please contact Atlassian support and open a ticket also attaching a screenshot of the A4J error, a Support Zip and the result of the query above.

Updated on June 6, 2024

Was this helpful?

It wasn't accurate

It wasn't clear

It wasn't relevant

Atlassian Support