Jira logs show error 'Cannot execute UPDATE in a read-only transaction'.
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
Jira will display the following error in the logs and fail to run properly.
Environment
Jira Data Center connected to Amazon Aurora PostgreSQL-compatible clustered database.
Diagnosis
The following errors can be observed in the atlassian-jira.log file:
2022-02-10 15:43:51,539+0000 Caesium-1-2 ERROR anonymous System [c.a.beehive.db.DatabaseClusterLock] Unable to unlock com.atlassian.beehive.db.DatabaseClusterLock@37b9c552, Number of retries exceeded, rethrowing .Last events on this node at 1644507831539: [lastError 103ms ago, lastLock 49842ms ago, lastUnlock 105882ms ago, lastRenewal 164346ms ago] Lock statistics: [failByError=3, lastAccess=1644507831436, lockSuccess=1038, unlockSuccess=1037, averageHoldTimeMillis=2644]
com.querydsl.core.QueryException: Caught PSQLException for update public.clusterlockstatus
set locked_by_node = ?, update_time = ?
where clusterlockstatus.lock_name = ? and clusterlockstatus.locked_by_node = ?
Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)Cause
This error typically occurs when clustered databases such as Amazon Aurora are in recovery or have recently had a failover.
You can confirm with the following commands:
The PostgreSQL cluster is in recovery:
SELECTpg_is_in_recovery();The parameter
default_transaction_read_onlyis set toon:SHOWdefault_transaction_read_only;
Solution
Ensure that you are using the correct validation query in the dbconfig.xml file as noted in Surviving Connection Closures:
Database type | Validation query | Validation query timeout |
|---|---|---|
MySQL |
|
|
Microsoft SQL Server |
|
|
Oracle |
|
|
PostgreSQL |
|
|
If the validation query is set to an incorrect value, please take a backup of the dbconfig.xml and change the validation query to use the right value as mentioned above. Jira will need to be restarted to have the changes in effect.
Was this helpful?