search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.43k stars 2.98k forks source link

Connection to Azure SQL Server fails #50259

Open styrke10 opened 2 years ago

styrke10 commented 2 years ago

What is the bug or the crash?

After having established a connection to a table in Azure SQL Server everything works normally. However after a varying amount of idle time (typically 30-60 minutes), the connection fails, and data can not be read or written to the table. Closing and reopening the project reestablishes the connection, and everything works normally again, until the next longer idle time.

We have tried similar scenarios (lengthy connections to Azure SQL tables) from Azure Data Studio and MS Excel, which have not suffered from the same errors.

When monitoring TCP traffic in TCPView, one can see, when the connection closes down (which also happens for Azure Data Studio and Excel), but the latter two reestablishes the connection, when it is needed - QGIS doesn't!

The bug has been reproduced on several clients/pcs.

Steps to reproduce the issue

  1. Load a layer from a Azure SQL Server.
  2. Check that you can read features with the Identify tool.
  3. Let sit idle for 30-60 minutes.
  4. Use the Identify tool again and the status bar says 'No features found at this position'.

Versions

Most heavily investigated in 3.26 (see below), but also seen in 3.22 and 3.18.

QGIS version 3.26.3-Buenos Aires QGIS code revision 65e4edfdada Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.5.1 PROJ version 9.1.0 EPSG Registry database version v10.074 (2022-08-01) GEOS version 3.10.3-CAPI-1.16.1 SQLite version 3.38.1 PDAL version 2.4.3 PostgreSQL client version unknown SpatiaLite version 5.0.1 QWT version 6.1.6 QScintilla2 version 2.13.1 OS version Windows 10 Version 2009

Active Python plugins changeDataSource 3.1 contour 2.0.10 Datafordeler 1.2 Dataforsyningen 0.3 geosearch_dk 1.2.0 LAStools 1.4 qgiscloud 3.4.2 scriptrunner3 3.0.5 db_manager 0.1.20 MetaSearch 0.3.6 processing 2.12.99 sagaprovider 2.12.99

Supported QGIS version

New profile

Additional context

No response

styrke10 commented 2 years ago

One more observation: After the connection to Azure SQL Server has failed (ie. when I can't save my edits anymore or identify objects), if I delete or add objects through Azure Data Studio, and then refresh my QGIS canvas these edits are shown, but they can't be edited, selected or identified in QGIS!

styrke10 commented 2 years ago

Screenshot from the QGIS Debugging Panel: QGIS_MSSQL_error Note that queries with ST_Filter apparently work flawlessly, while similar queries but with ST_Intersects fail!!

styrke10 commented 1 year ago

I was wondering whether it is possible to give some feedback on this issue?

Can you give me any hints on the probability of this being looked into in the near future? I have some users considering whether to move to another data store and/or whether this issue is related only to their Azure setup or a general phenomenon?

Thanks in advance!

ataidecarlos commented 4 months ago

Not sure if this is still relevant, since the issue is so old. Connections made from outside of Azure to an Azure SQL Database, are by default using the Proxy Connection Policy, which kills connections that are idle for 30 or more minutes. If QGIS is not keeping the connection alive (not just sending keep-alives, but actually sending queries), this is likely the issue you're seeing.

styrke10 commented 4 months ago

Thanks for your comment, Carlos! It is not immediately relevant any longer, since the customer has moved to PostGIS (luckily). However your observations seem very plausible, and I will keep this in mind for future reference!