The Jira application throws a 500 error in the UI when any Automation For Jira page is accessed

関連コンテンツ

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。

*Fisheye および Crucible は除く

    

要約

The Jira application throws a 500 error when any Automation For Jira (A4J) page is accessed, along with the message "Sorry, we had some technical problems during your last operation":


This error is observed in the pages listed below:

  • the page ⚙ > System > Automation For Jira > Automation rules (from Jira admin menu)
  • the page Project Settings > Automation (from any Jira project)

The purpose of this article is to list all the known root causes for this error, how to identify them, and how to fix the error.

(info) In this page, Automation For Jira will be referred to as A4J.

環境

  • 8.0.0 以降の任意のバージョンの Jira Server/Data Center
  • Automation For Jira (A4J) Server/Data Center on any version from 7.3.0

診断

Diagnostic Steps for Root Cause 1

  1. Check the Automation For Jira version via the page ⚙ > Manage Apps > Manage Apps.
    • If the version is 9.0.3 or above:
      • This root cause is not relevant. In this case, move on to the section Diagnosis Steps for Root Cause 2
    • If the version is lower than 9.0.3:
      • Move on to the next step
  2. Re-start the Jira application
    1. If re-starting the Jira application resolved the issue
      1. This root cause is relevant, and you can move on to the Solution for Root Cause 1 step for a more permanent solution
    2. If re-starting the application did not resolve the issue
      1. Move on to the section Diagnosis Steps for Root Cause 2

Diagnostic Steps for Root Cause 2

  1. Check the type of database used by the Jira application
    1. If the database type is neither MySQL nor MSSQL
      1. This root cause is not relevant. In such case, move on to the section Diagnosis Steps for Root Cause 3
    2. If the database type is either MySQL or MSSQL
      1. Move on to the next step
  2. Look into the Jira logs, and check if you can find errors similar to the ones below:

    1. Example of error 1

      2022-06-02 16:58:01,559+0000 http-nio-127.0.0.1-8082-exec-23 ERROR admin 1018x25070x1 1govjlw 123.45.678.901,10.11.12.13,127.0.0.1 /rest/cb-automation/latest/project/13800/rule/3470 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught MySQLSyntaxErrorException for select distinct `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID`, `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID`
    from `AO_589059_AUTOMATION_QUEUE` `AO_589059_AUTOMATION_QUEUE`
    where `AO_589059_AUTOMATION_QUEUE`.`RULE_ID` = ? and `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID` is not null and `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID` is not null
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for select distinct `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID`, `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID`
from `AO_589059_AUTOMATION_QUEUE` `AO_589059_AUTOMATION_QUEUE`
where `AO_589059_AUTOMATION_QUEUE`.`RULE_ID` = ? and `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID` is not null and `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID` is not null
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
	...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AO_589059_AUTOMATION_QUEUE.EXECUTION_UUID' in 'field list'
	at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	...

    2. Example of error 2

      java.sql.SQLException: Referencing column 'CONDITION_PARENT_ID' and referenced column 'ID' in foreign key constraint 'fk_ao_589059_rule_cfg_component_condition_parent_id' are incompatible.
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:54)
	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.lambda$create$0(AbstractActiveObjectsFactory.java:73)

    3. Example of error 3

      2022-06-02 17:00:24,465+0000 Caesium-1-2 ERROR anonymous     [c.c.j.p.automation.schedule.AutomationRuleSchedulerJob] Error executing rule scheduler service job
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for insert into `AO_589059_AUTOMATION_QUEUE` (`CLAIM_COUNT`, `CLIENT_KEY`, `CREATED`, `PAYLOAD`, `PRIORITY`, `RULE_ID`, `EXECUTION_UUID`)
values (?, ?, ?, ?, ?, ?, ?)
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
	...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'EXECUTION_UUID' in 'field list'
	at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	...

    4. Example of error 4

      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'jira.AO_589059_RULE_SECRET'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)

    5. Example of error 5

      Caused by: java.sql.SQLSyntaxErrorException: Table 'jiradb.ao_589059_rule_secret' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)



  3. If you see any of the error listed above, and you are using either MySQL or MSSQL:
    1. This root cause might be relevant.
    2. Move on to the section Solution for Root Cause 2
  4. If you did not find any of these error, or if you tried the resolution steps for this root cause but they did not help:
    1. Move on to the section Diagnosis Steps for Root Cause 3

Diagnostic Steps for Root Cause 3

  1. Check the Jira application logs and look for the error below:

    2024-02-22 05:03:19,820-0600 https-jsse-nio-8443-exec-15 ERROR xxxx 303x42889x1 xxxx 13.12.13.42 /secure/AutomationProjectAdminAction!iframe.jspa [c.c.j.p.a.web.admin.AutomationProjectAdminAction] Unexpected error loading project automation UI:
java.lang.RuntimeException: com.fasterxml.jackson.databind.JsonMappingException: Null key for a Map not allowed in JSON (use a converting NullKeySerializer?) (through reference chain: com.codebarrel.automation.api.context.AutomationManagerContext["configProperties"])
        at com.codebarrel.api.JsonSupport.toJsonString(JsonSupport.java:83)
        at com.codebarrel.jira.plugin.automation.web.admin.AdminContextProvider.createRenderProps(AdminContextProvider.java:233)
        at com.codebarrel.jira.plugin.automation.web.admin.AutomationGlobalAdminAction.getIframeViewData(AutomationGlobalAdminAction.java:58)
......
Caused by: com.fasterxml.jackson.databind.JsonMappingException: Null key for a Map not allowed in JSON (use a converting NullKeySerializer?) (through reference chain: com.codebarrel.automation.api.context.AutomationManagerContext["configProperties"])
        at com.fasterxml.jackson.databind.JsonMappingException.from(JsonMappingException.java:283)
        at com.fasterxml.jackson.databind.SerializerProvider.mappingException(SerializerProvider.java:1358)
        at com.fasterxml.jackson.databind.SerializerProvider.reportMappingProblem(SerializerProvider.java:1254)
        at com.fasterxml.jackson.databind.ser.impl.FailingSerializer.serialize(FailingSerializer.java:32)
.......
        at com.fasterxml.jackson.databind.ObjectMapper._writeValueAndClose(ObjectMapper.java:4568)
        at com.fasterxml.jackson.databind.ObjectMapper.writeValueAsString(ObjectMapper.java:3821)
        at com.codebarrel.api.JsonSupport.toJsonString(JsonSupport.java:81)

2024-02-22 05:03:19,824-0600 https-jsse-nio-8443-exec-15 ERROR xxxx 303x42889x1 xxxx 13.12.13.42 /secure/AutomationProjectAdminAction!iframe.jspa [o.a.c.c.C.[Catalina].[localhost].[/]] Unhandled exception occurred whilst decorating page
com.google.template.soy.tofu.SoyTofuException: In 'print' tag, expression "$localeString" evaluates to undefined.
        at JIRA.Automation.Project.Admin.Page.iframe(automation-admin-page.soy:53)
  2. If you see the error mentioned above, then:
    1. This root cause might be relevant
    2. Move on to the section Solution for Root Cause 3
  3. If you did not find this error
    1. Move on to the section Diagnosis Steps for Root Cause 4

Diagnostic Steps for Root Cause 4

(warning) Only go through these diagnostic steps, if you confirmed that Root Cause 1, Root Cause 2 and Root Cause 3 were not relevant.

  1. Check the Jira application and look for any of the error listed below

    1. Example of error 1

      2023-10-18 14:06:32,358-0700 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [c.a.activeobjects.osgi.TenantAwareActiveObjects] bundle [com.atlassian.jira.plugin.automation.for-jira] failed to create ActiveObjects
com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select `AO_589059_RULE_CFG_COMPONENT`.`COMPONENT`, `AO_589059_RULE_CFG_COMPONENT`.`ID`, `AO_589059_RULE_CFG_COMPONENT`.`PARENT_CFG_COMPONENT_ID`, `AO_589059_RULE_CFG_COMPONENT`.`RULE_CONFIG_ID`, `AO_589059_RULE_CFG_COMPONENT`.`SCHEMA_VERSION`, `AO_589059_RULE_CFG_COMPONENT`.`SEQUENCE`, `AO_589059_RULE_CFG_COMPONENT`.`TYPE`, `AO_589059_RULE_CFG_COMPONENT`.`VALUE`, `AO_589059_RULE_CFG_COMPONENT`.`CONDITION_PARENT_ID`
from `AO_589059_RULE_CFG_COMPONENT` `AO_589059_RULE_CFG_COMPONENT`
where `AO_589059_RULE_CFG_COMPONENT`.`TYPE` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)

...

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'AO_589059_RULE_CFG_COMPONENT.CONDITION_PARENT_ID' in 'field list'

    2. Example of error 2

      2023-10-18 14:06:32,286-0700 active-objects-init-JiraTenantImpl{id='system'}-0 ERROR anonymous     [c.c.j.p.automation.upgradetasks.AOTableMigrater] Error migrating AO tables - could be because the plugin system wasn't fully initialised yet.  Ignoring and continuing to run for now.  Error was: 
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:MySQL
	- version:5.7.41-log
	- minor version:7
	- major version:5
Driver:
	- name:MySQL Connector/J
	- version:mysql-connector-j-8.0.33 (Revision: 7d6b0800528b6b25c68b52dc10d6c1c8429c100c)
java.sql.SQLException: Cannot change column 'PARENT_CFG_COMPONENT_ID': used in a foreign key constraint 'fk_ao_589059_rule_cfg_component_parent_cfg_component_id'

...

Caused by: java.sql.SQLException: Cannot change column 'PARENT_CFG_COMPONENT_ID': used in a foreign key constraint 'fk_ao_589059_rule_cfg_component_parent_cfg_component_id'

    3. Example of error 3

      2023-11-27 14:07:51,699+0200 Caesium-1-2 ERROR ServiceRunner     [c.c.j.p.automation.schedule.AutomationRulePollerJob] Error executing rule poller service job
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_589059_RULE_CONFIG"."ID", "AO_589059_RULE_CONFIG"."ACTOR_KEY", "AO_589059_RULE_CONFIG"."AUTHOR_KEY", "AO_589059_RULE_CONFIG"."CAN_OTHER_RULE_TRIGGER", "AO_589059_RULE_CONFIG"."CLIENT_KEY", "AO_589059_RULE_CONFIG"."CREATED", "AO_589059_RULE_CONFIG"."DESCRIPTION", "AO_589059_RULE_CONFIG"."ID", "AO_589059_RULE_CONFIG"."NAME", "AO_589059_RULE_CONFIG"."PROJECT_ID", "AO_589059_RULE_CONFIG"."PROJECT_TYPE_KEY", "AO_589059_RULE_CONFIG"."STATE", "AO_589059_RULE_CONFIG"."UPDATED", "AO_589059_RULE_CONFIG"."NOTIFY_ON_ERROR", "AO_589059_RULE_CFG_COMPONENT"."COMPONENT", "AO_589059_RULE_CFG_COMPONENT"."ID", "AO_589059_RULE_CFG_COMPONENT"."PARENT_CFG_COMPONENT_ID", "AO_589059_RULE_CFG_COMPONENT"."RULE_CONFIG_ID", "AO_589059_RULE_CFG_COMPONENT"."SCHEMA_VERSION", "AO_589059_RULE_CFG_COMPONENT"."SEQUENCE", "AO_589059_RULE_CFG_COMPONENT"."TYPE", "AO_589059_RULE_CFG_COMPONENT"."VALUE", "AO_589059_RULE_CFG_COMPONENT"."CONDITION_PARENT_ID"
from "jiraschema"."AO_589059_RULE_CONFIG" "AO_589059_RULE_CONFIG"
left join "jiraschema"."AO_589059_RULE_CFG_COMPONENT" "AO_589059_RULE_CFG_COMPONENT"
on "AO_589059_RULE_CONFIG"."ID" = "AO_589059_RULE_CFG_COMPONENT"."RULE_CONFIG_ID"
where "AO_589059_RULE_CONFIG"."ID" in (select "AO_589059_RULE_TAG"."RULE_CONFIG_ID"
from "jiraschema"."AO_589059_RULE_TAG" "AO_589059_RULE_TAG"
where "AO_589059_RULE_TAG"."TAG_TYPE" = ?)
order by "AO_589059_RULE_CONFIG"."NAME" asc, "AO_589059_RULE_CFG_COMPONENT"."SEQUENCE" asc
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
	at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410)

...

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'CONDITION_PARENT_ID'.

    4. Example of error 4

      2023-10-19 04:42:22,371-0700 Caesium-1-4 ERROR ServiceRunner     [c.c.j.p.automation.schedule.RuleInsightsUpdateJob] Error executing rule insights update job
com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select `AO_589059_RULE_STAT_ROLLUP_HR`.`CREATED`
from `AO_589059_RULE_STAT_ROLLUP_HR` `AO_589059_RULE_STAT_ROLLUP_HR`
order by `AO_589059_RULE_STAT_ROLLUP_HR`.`CREATED` desc
limit ?
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)


...

Caused by: java.sql.SQLSyntaxErrorException: Table 'jiradb.AO_589059_RULE_STAT_ROLLUP_HR' doesn't exist
  2. Check if all of the 3 DB tables listed below are missing from the Jira Database

    1. AO_589059_RULE_STAT_ROLLUP_DAY

    2. AO_589059_RULE_STAT_ROLLUP_HR

    3. AO_589059_RULE_STAT_ROLLUP_MIN

  3. This root cause might be relevant if:
    1. You already ruled out Root Cause 1, Root Cause 2 and Root Cause 3
    2. You were able to find one of the errors listed above
    3. All of the 3 DB tables which name starts with AO_589059_RULE_STAT_ are missing from the Jira Database
  4. In such case, move on to the section Solution for Root Cause 4

原因

Root Cause 1 - Known bug

We are facing the known bug which is tracked in the ticket https://jira.atlassian.com/browse/JIRAAUTOSERVER-196, and which was fixed in the A4J version 9.0.3.

Because of this bug, if the Jira Service Management (JSM) application is installed on top of a Jira application after A4J is installed, then the 500 will be thrown in any A4J page. A re-start of the Jira application will be required to fix this error, unless A4J is upgraded to the fixed version 9.0.3.

Root Cause 2 - A4J Upgrade Task issue

As explained in the documentation Upgrading to Automation for Jira 7.3 or later, some SQL scripts need to be manually run in the Jira DB by a DB Admin, if the 2 conditions below are met:

  • Jira は MySQL または MS SQL データベースのどちらかに接続されています
  • A4J を、7.3.0 より低いバージョンから 7.3.0 以上にアップグレードしようとしている

If A4J was at some point in time installed on a version lower than 7.3.0, and if the script was never executed, then some A4J upgrade tasks will fail to complete and there will be some incorrect data formatting in the database tables used by A4J.

These inconsistencies might have various impacts on the A4J application:

Root Cause 3 - Data corruption in one of the property tables in the Jira Database

A4J relies on the Jira property tables (listed below) to load its pages:

  • propertyentry
  • propertytext
  • propertystring

If any of these tables contain invalid data, A4J pages will fail to load and throw a 500 error.

You can find more information about this root cause in the KB article After an upgrade of the Jira application to 9.12.x, the Automation For Jira admin page throws 500 error.

Root Cause 4 - Database structure issue with the A4J tables in the Jira Database

We have seen a few situations where the A4J Database tables had a lot of inconsistency/corruption. The most common inconsistencies we noticed were the ones below:

  • at least one of the 3 DB tables listed below was missing

    • AO_589059_RULE_STAT_ROLLUP_DAY

    • AO_589059_RULE_STAT_ROLLUP_HR

    • AO_589059_RULE_STAT_ROLLUP_MIN

  • the column CONDITION_PARENT_ID was missing from the table AO_589059_RULE_CFG_COMPONENT

In such case, following the steps from Upgrading to Automation for Jira 7.3 or later did not fix these errors. The only way to go back to a situation where all the A4J DB tables are healthy was to drop all the tables, and let the A4J application re-create them from scratch. Unfortunately, by following such approach, all the data from Automation For Jira (such as automation rules) will be lost. More information about the resolution steps and their impact in the section Solution for Root Cause 3.

The actual root cause for all these discrepancies is still unclear at the time this article was written. We suspect that it might happen in the case where A4J was installed at some point on a very old version (or trial version) and then un-installed. This very old version might have a distinct structure than recent versions, and it might prevent the upgrade/install tasks from creating properly all the right A4J tables and columns.

ソリューション

根本原因の解決策 1

There are 2 possible solutions:

  • Long Term Solution
    • Upgrade Automation For Jira to 9.0.3 or any higher version (which contains the fix for the bug), via the ⚙ > Manage Apps > Manage Apps page
  • Short Term Solution
    • Re-start the Jira application (or re-start all the Jira nodes in case of a cluster of nodes)

根本原因の解決策 2

The solution consists in following the steps from the documentation Upgrading to Automation for Jira 7.3 or later.

根本原因の解決策 3

The solution consists in following the steps from the KB article After an upgrade of the Jira application to 9.12.x, the Automation For Jira admin page throws 500 error.

根本原因の解決策 4


Before you follow these steps, please be aware that this solution will entirely delete all the data related to Automation For Jira (all the automation rules), therefore only follow these steps if you never used Automation For Jira before, or never configured Automation rules.

This solution consists in dropping all the Database tables related to Automation For Jira (A4J). This solution is only valid if you never used A4J before, and agree to delete all data related to A4J.

(info) For now, we only have a SQL Script available for MySQL, MSSQL and PostgreSQL. If you are using a different type of Database and are facing this issue, please reach out to Atlassian Support for assistance.

(warning) Please make sure to:

  • Test and validate this solution in a test/dev/clone Jira application
  • Backup the Jira Database before testing this solution on the Production Jira application, since dropping the A4J DB tables is an irreversible action (unless you took a back up of the Database)

ステップは次のとおりです。

  • Stop the Jira application (or all the Jira nodes, in case of a cluster of nodes)
  • Log into the Jira DB
  • Download the SQL script that corresponds to the type of database used by the Jira application
  • Run the SQL script in the Jira DB
  • Verify that the script completed without any error

  • Verify that all the A4J DB tables are now gone from the Jira DB by running the SQL query below and making sure that it does not return any result

    SELECT table_name 
FROM information_schema.tables 
WHERE table_name LIKE 'AO_589059_%';
  • Start the Jira application (or start all the Jira nodes 1 by 1, in case of a cluster of nodes), and wait until the Jira application is accessible via the UI

  • Verify that all the A4J DB tables are back into the Jira DB by running the SQL query below and making sure that it returns some result

    SELECT table_name 
FROM information_schema.tables 
WHERE table_name LIKE 'AO_589059_%';
  • Open any A4J page (such as the examples below), and make sure that you can access them without any error:
    • the page ⚙ > System > Automation For Jira > Automation rules (from Jira admin menu)
    • the page Project Settings > Automation (from any Jira project)


最終更新日 2024 年 7 月 24 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

Powered by Confluence and Scroll Viewport.