How to fix the SQL script to resolve the Automation For Jira error "Error while deleting/updating rule"
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
Environment
Upgraded environments where:
- A4J was upgraded to a version equal to or newer than 7.3.0 or 9.0.2 (that passed through versions lower than 7.3.0), or Jira SW/JSM was upgraded from any version below 9.11.0/5.11.0
- MySQL or MSSQL DBMS being used
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:
- Automation rules cannot be enabled, disabled or published after an upgrade of Automation for Jira to 9.0.1 or a higher version
- Upgrading to Automation for Jira 7.3 or later
- Automation for Jira not working after upgrading Jira or updating Automation for Jira
When running the script as explained in the article Upgrading to Automation for Jira 7.3 or later (which is also referred 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.
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.