Jira logs show error 'Cannot execute UPDATE in a read-only transaction'.
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
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:
SELECT pg_is_in_recovery();The parameter
default_transaction_read_onlyis set toon:SHOW default_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 |
| 3 |
Microsoft SQL Server |
| N/A |
Oracle |
| N/A |
PostgreSQL |
| N/A |
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.