Jira XML restore fails in Azure SQL Database with "The connection has been closed" error
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
Problem
When restoring an XML backups in Jira, the following errors appears after some time and XML backup fails.
1
2
3
4
5
6
7
8
2018-09-13 21:41:58,965 JIRA-Import-Thread-1 WARN admin001 1298x17147x1 pvg4g4 10.100.139.89,127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.OfbizImportHandler] Create failed - retrying. Attempt [1] out of [5]
(...)
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:INSERT INTO dbo.changeitem (ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING) VALUES (?, ?, ?, ?, ?, ?, ?, ?) (SQL Server did not return a response. The connection has been closed.)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:198)
... 11 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.
(...)Diagnosis
Environment
This can happen when Jira is using Azure SQL Database.
Diagnostic Steps
XML restore will kick off successfully and will continue for a while until it fails with this error. This issue is usually repeatable and the backup will fail after a specific period of time.
Cause
Its likely that Azure SQL drops the database connection after a certain time frame. For exact reason, it will be best to contact Azure SQL support.
Workaround
This issue can be avoided by changing the mode of SQL connectivity from proxy mode to redirect mode.
According to the guide of Azure SQL, these modes mean the following.
Redirect mode
A policy of Redirect means that connections after the TCP session is established to the Azure SQL database, the client session is then redirected to the proxy middleware with a change to the destination virtual IP from that of the Azure SQL Database gateway to that of the proxy middleware. Thereafter, all subsequent packets flow directly via the proxy middleware, bypassing the Azure SQL Database gateway.
Proxy Mode
A policy of Proxy means that the TCP session is established via the Azure SQL Database gateway and all subsequent packets flow via the gateway.
More information about this can be found in the following articles.
Was this helpful?