Jira XML restore fails in Azure SQL Database with "The connection has been closed" error
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
Problem
When restoring an XML backups in Jira, the following errors appears after some time and XML backup fails.
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.
- Azure SQL Database Connectivity Architecture
- Use Virtual Network service endpoints and rules for Azure SQL Database and SQL Data Warehouse
- Connect to Azure SQL Database V12 via Redirection