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

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

robotsnoindex


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:


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.




Last modified on Jun 3, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.