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.52k stars 2.99k forks source link

MySQL-hosted layer doesn't display in QGIS Server (bad SQL command) #50610

Closed LoonSongSoftware closed 1 year ago

LoonSongSoftware commented 2 years ago

What is the bug or the crash?

When a layer is stored in a MySQL database, the QGIS Server code issues different SQL queries to display the layer than the ones issued by QGIS Desktop. The QGIS Desktop SQL queries--which include the SRS_ID--work, while the QGIS Server one does not include the proper SRS_ID. I can't think of a good reason the queries are different, and can't find any configuration settings to fix the issue.

Steps to reproduce the issue

Store a layer in a MySQL database

  1. Download the QGIS Training Dataset
  2. Copy the naturalearth.sqlite data to a MySQL database, creating a table called 'test4' c:\Program Files\QGIS 3.26.2\bin\ogr2ogr.exe" MySQL:tobh_gis,host=do.ustiger.net,user=tobh,"password=3SF4w%H&PS$cKM8#H" "C:\Users\scott.USTIGER\Documents\git\QGIS-Training-Data\exercise_data\qgis-server-tutorial-data\naturalearth.sqlite" -nln test4 -update -t_srs EPSG:4326 -overwrite -lco engine=InnoDB

Create a project using the layer

  1. In QGIS Desktop, create a new project with two layers: a. The airports data loaded from the MySQL table created above b. An OpenStreetMap layer (just to help orient things)

You should see something like this: image

  1. Save the layer (attached as bad_sql_query.qgs): bad_sql_query.qgs.txt

Host the project on QGIS server

  1. Copy the project file (created above) to the QGIS server (in the right location)
  2. Test to see if the information is being served properly:

See if the OpenStreetMap layer works

http://gis.ustiger.net/cgi-bin/qgis_mapserv.fcgi?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&MAP=/home/qgis/projects/bad_sql_query.qgs&WIDTH=400&HEIGHT=200&CRS=EPSG:4326&LAYERS=OpenStreetMap&BBOX=30,-93,45,-47 image

See if the airports layer works

http://gis.ustiger.net/cgi-bin/qgis_mapserv.fcgi?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&MAP=/home/qgis/projects/bad_sql_query.qgs&WIDTH=400&HEIGHT=200&CRS=EPSG:4326&LAYERS=airports&BBOX=30,-93,45,-47 image

Hmmm...not so good.

Diagnostics

  1. If you log the MySQL queries and/or errors, you will find that MySQL (v. 8.x) logs an error code when the 'airports' layer is to be shown, something like: ERROR 1: MySQL error message:SRID value is out of range in 'st_geomfromtext' Description: SELECT `id`, `geom` `geom`, `scalerank`, `featurecla`, `type`, `name`, `abbrev`, `location`, `gps_code`, `iata_code`, `wikipedia`, `natlscale` FROM `test4` WHERE MBRIntersects(ST_GeomFromText('POLYGON((-93.121019108280251 30, -46.8789808917197419 30, -46.8789808917197419 45, -93.121019108280251 45, -93.121019108280251 30))', -2), `geom`) I think the issue is with the '-2' at the end, ST_GeomFromText's second argument is supposed to be the SRS_ID.

The equivalent call when QGIS Desktop is displaying the layer is: SELECT `id`, `geom` `geom`, `scalerank`, `featurecla`, `type`, `name`, `abbrev`, `location`, `gps_code`, `iata_code`, `wikipedia`, `natlscale` FROM `test4` WHERE MBRIntersects(ST_GeomFromText('POLYGON((-93.121019108280251 30, -46.8789808917197419 30, -46.8789808917197419 45, -93.121019108280251 45, -93.121019108280251 30))', **4326, 'axis-order=long-lat'**), `geom`)

I suspect, for some reason, the code in Server that generates the MySQL query has gotten out of step with the corresponding code in Desktop. But I don't know why they'd be different and, browsing through the repository code, I don't see where the differences lie. Hopefully someone more familiar with the code can identify the issue. The fix may be as 'simple' as aligning the database query code between Desktop and Server.

Versions

The QGIS Desktop info is pasted below. QGIS Server is running on Ubuntu and is version 3.26.3.

QGIS version 3.26.2-Buenos Aires QGIS code revision feec3d3b12f Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.5.1 PROJ version 9.0.1 EPSG Registry database version v10.064 (2022-05-19) GEOS version 3.10.3-CAPI-1.16.1 SQLite version 3.38.1 PDAL version 2.4.2 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 db_manager 0.1.20 grassprovider 2.12.99 MetaSearch 0.3.6 processing 2.12.99 sagaprovider 2.12.99

Supported QGIS version

New profile

Additional context

No response

LoonSongSoftware commented 2 years ago

Here's a file showing the MySQL queries generated by Desktop vs. Server when retrieving information about a layer. Note the difference at the very end of the long SELECT statement. (This one is a query to retrieve information from the natural-earth.sqlite 'places' table.)

QGIS Desktop vs Server.docx

A quick grep search for 'axis-order' doesn't identify any results in the QGIS repository, so I can't tell where Desktop is creating the SQL query that it executes. A similar search for 'ST_GeomFrom_Text' identifies several uses of the term, but they all look like two-argument (instead of three) calls.

It looks like the 'axis-order' may be a MySQL 8.0 extension of the standard call. So maybe there's a separate MySQL plugin that's modifying the SQL query?

Without tracing execution, I can't really tell where the "good" query is generated in Desktop and, therefore, where Server might be going awry. Hopefully someone more familiar with the QGIS code can short-circuit this process.

LoonSongSoftware commented 1 year ago

I built the server from scratch (in Linux) with the latest GitHub repository files and the server works properly. So, I assume that once the Debian packages catch up with GitHub 'master,' the problem will go away.

I'll close this issue, but open a new one with tag "Build/Install" (after searching the issues list for duplicates) to understand why the latest release (3.28 as of today) isn't available in the repository.