High Problem
Upgrading Jira to a release that requires executing the upgrade task of build 960000 will fail on instances with large number of tickets to be archived, DBCP will close the connection responsible for updating the entries in jiraissue table before the processing finishes , most likely that <pool-remove-abandoned-timeout> is breached
Environment
Jira Datacenter
Steps to Reproduce
- Place Jira in Zero down time mode on an instance with large number of tickets to be archived 14 000 000 tickets as an example
- Run the upgrade finalisation
Expected Results
Jira will finish the update and print X issues from archived projects have been marked as archived in jiraissue table. The update took Y seconds
Actual Results
Connection is closed while the task is running
2024-02-27 20:28:40,571+0000 Caesium-1-2 ERROR ServiceRunner [c.a.jira.database.DatabaseAccessorImpl] Unable to rollback SQL connection.
java.sql.SQLException: Connection org.postgresql.jdbc.PgConnection@2a4753eb is closed.
at org.apache.commons.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:113)
at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:779)
at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:781)
at org.ofbiz.core.entity.jdbc.interceptors.connection.DelegatingConnection.rollback(DelegatingConnection.java:61)
at com.atlassian.jira.ofbiz.sql.ConnectionWrapper.rollback(ConnectionWrapper.java:72)
at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:81)
at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:103)
at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:63)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build960000.doUpgrade(UpgradeTask_Build960000.java:96)
at com.atlassian.jira.upgrade.AbstractUpgradeTask.runUpgrade(AbstractUpgradeTask.java:61)
at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.runOneUpgradeTask(DefaultUpgradeTaskFactoryProcessor.java:109)
The update query fails
Caused by: com.querydsl.core.QueryException: Caught PSQLException for update public.jiraissue set archived = ? where jiraissue.project in (select O_S_PROPERTY_ENTRY.entity_id from public.propertyentry O_S_PROPERTY_ENTRY left join public.propertynumber O_S_PROPERTY_NUMBER on O_S_PROPERTY_ENTRY.id = O_S_PROPERTY_NUMBER.id where O_S_PROPERTY_ENTRY.property_key = ? and O_S_PROPERTY_NUMBER.propertyvalue = ?) and (jiraissue.archived = ? or jiraissue.archived is null)
__
Workaround
Backup then edit dbconfig.xml and raise the value of <pool-remove-abandoned-timeout> to a higher value for example 3000 and <min-evictable-idle-time-millis> to 60000000
Restart Jira and restart the upgrade task.
Notes
Restore the database pool attributes to default once the task finishes.