The UpgradeTask_Build9150000 can't complete successfully

Issue Summary

This is reproducible on Data Center: yes

During the startup process, Jira stops at the upgrade tasks stage. By inspecting the threads, it's visible that the UpgradeTask_Build9150000 is running and waits for the delete query execution.

At this point, the upgrade task will either fail with a database timeout, or will run indefinitely.

Affected databases:

• PostgreSQL (All supported versions)
• MySQL (All supported versions)

The DB query’s complexity executed as a part of this upgrade task is highly affected by the number of entities and duplicates in the propertyentry table. The large number of data in the mentioned table can cause the behavior described above.

Steps to Reproduce

1. Upgrade Jira to 9.15.0 version or later

Expected Results

Jira performs upgrade tasks and is ready to serve in reasonable time

Actual Results

The upgrade task fails with timeout:

Caused by: com.querydsl.core.QueryException: Caught PSQLException for delete from jira.propertynumber
where propertynumber.id not in (select max(O_S_PROPERTY_ENTRY.id)
from (select preload.id, preload.entity_id, preload.entity_name, preload.property_key
from jira.propertyentry preload) as O_S_PROPERTY_ENTRY
group by O_S_PROPERTY_ENTRY.entity_name, O_S_PROPERTY_ENTRY.entity_id, O_S_PROPERTY_ENTRY.property_key)
    at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50

Caused by: java.net.SocketException: Connection timed out
    at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:325) 

or will run indefinitely if the database timeout is not configured.

Unless the upgrade task fails or completes, Jira doesn't respond, the last meaningful logs are:

2024-04-25 11:44:51,679+0000 main INFO      [c.a.jira.upgrade.LoggingUpgradeService] run upgrades has started
2024-04-25 11:44:55,386+0000 Modification Check:thread-1 INFO      [c.a.jira.startup.JiraStartupLogger] 
    
    ___ Modifications ___________________________
    
         Modified Files                                : jira-application.properties
         Removed Files                                 : None
    
2024-04-25 11:45:01,911+0000 pool-25-thread-1 DEBUG      [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] getService bundle [com.atlassian.jira.diagnostics-plugin]

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available