Bug report #13836

MySQL Vector geometries are only visible in edit mode

Added by paolo tome over 4 years ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Vectors
Affected QGIS version:3.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:21860

Description

There is a problem with mysql vector; when I connect to the MySQL table (containing a geometry column), the attribute table is loaded correctly, the map is centered properly but the geometries are not displayed;
if I save the vector as a shapefile or other type, the new layer is loaded and displayed correctly. I'm using Windows7 + qgis 2.8 lts

(I tested the same procedure in ubuntu 14.04 + qgis 2.8lts and everything works properly.)

I believe it depends on GDAL/OGR, someone can help me?

I get the same result (geometry not displayed) even if I load the table console py:

from qgis.core import *
uri = "MySQL:xxx,host=localhost,port=3306,user=root,password=xxx|\\
layername=xxx"
vlayer = QgsVectorLayer( uri, "xxx", "ogr" )
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

If I select all records of the attribute table, and I use the tool to move the elements (with the edit mode on) when I click on the map all the elements appear in the map and remain visible until I deactivate the edit mode....

EXAMPLE.sql (2.66 KB) paolo tome, 2016-02-26 03:26 AM

History

#1 Updated by Giovanni Manghi over 4 years ago

  • Priority changed from High to Normal

So, the wrong behavior happens only on QGIS on Windows (with the same server)?

can you suggest a more descriptive text for the subject?

#2 Updated by paolo tome over 4 years ago

The problem occurs only on Windows (I tried it on 3PC with Windows7 + mysql server 5.7.9). The problem is also present in qgis 2.12.
If I use ubuntu 14.04 + qgis 2.8 + mysql version 5.5, the vector layer geometries are drawn correctly.

It does not seem to be a mysql problem, but a problem of rendering: If I select all records of the attribute table and I choose the tool to move the elements (with the edit mode on) when I click on the map all the elements appear correctly!!!

Thanks a lot
Paolo

#3 Updated by paolo tome over 4 years ago

I have tried to change the versions of the mysql server and I found that until 5.7.5 version the problem does not occur.
If I install the version 5.7.7 the problem occurs as described (I was not able to try the version 5.7.6).
Any ideas about the problem?
Thanks
Paolo

#4 Updated by Christian Houle about 4 years ago

from version 5.7.7, all records must have the same srid else the server returns nothing when qgis ask for the visible items.

#5 Updated by paolo tome about 4 years ago

All records have the same srid.
The thing that seems to determine whether geometry is displayed or not displayed is the expression of the coordinates (number of decimals).

Example:
<gml:Point srsName="EPSG:4326"><gml:coordinates>10.2147236,45.5355809</gml:coordinates></gml:Point> --> DISPLAYED
<gml:Point srsName="EPSG:4326"><gml:coordinates>10.214723614633719,45.535580926939041</gml:coordinates></gml:Point> --> NOT DISPLAYED

thanks a lot
Paolo

#6 Updated by Christian Houle about 4 years ago

This is what I did to come to that conclusion:

using mysql-workbench
set mysql system variable general_log to ON
start qgis, load a mysql vector layer that won't display.
turn off general_log
open the log file (specified by general_log_file)
find and copy the select statement with mrbintersects: SELECT ... WHERE MBRIntersects(GeomFromText('POLYGON((visible map rect coordinates))'...
execute that statement on the mysql server (using workbench or mysql cli)

in my case, that gives me:
Error Code: 3033. Binary geometry function mbrintersects given two geometries of different srids: 0 and 4294967294, which should have been identical.
and I found the 'why' on that page: http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html

#7 Updated by Jose Paredes about 4 years ago

Estimado Cristian,

Tu apreciación es correcta, sin embargo, el problema me parece que realmente radica en la forma como Qgis intenta abrir la conexión con Mysql 5.7 pues si bien esta versión de la base de datos es estricta en términos del SRID, QGIS no se ha actualizado al respecto, el ejemplo es apreciable retomando tu aportación :

Christian Houle wrote:

This is what I did to come to that conclusion:

using mysql-workbench
(...)
find and copy the select statement with mrbintersects: SELECT ... WHERE MBRIntersects(GeomFromText('POLYGON((visible map rect coordinates))'...
execute that statement on the mysql server (using workbench or mysql cli)

in my case, that gives me:
Error Code: 3033. Binary geometry function mbrintersects given two geometries of different srids: 0 and 4294967294, which should have been identical.
and I found the 'why' on that page: http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html

La cuestión aquí es que la parte de la sentencia, en particular la función GeomFromText que encontramos no cuenta con una definición específica de SRID, por tanto la comparación devuelve este error al encontrar dos SRID : 0 ( Los registros en MySql ) 4294967294 ( El SRID producto de la definición GeomFromText) .

Podemos probar esto, si tomamos la instrucción como amablemente nos explicas y agregamos el SRID de forma manual... Por ejemplo:

SELECT ... WHERE MBRIntersects(GeomFromText('POLYGON((Coordenadas del rectángulo visible))', SRID)...
En específico: GeomFromText('POLYGON((Coordenadas del rectángulo visible))', 0)

Es decir, si agregamos el SRID que espera la función GeomFromText modificado en MySql 5.7, tendremos el conjunto esperado de datos de vuelta, el problema es que QGIS no agrega este SRID a su petición, y si bien esto funcionaba para las versiones anteriores de MySql, es indispensable agregar el parámetro para esta nueva versión.

Ojalá se pueda solucionar pronto y les envío un saludo desde México.

José Paredes

#8 Updated by Anita Graser about 4 years ago

  • Subject changed from MySQL Vector to MySQL Vector geometries are only visible in edit mode

#9 Updated by paolo tome about 4 years ago

ok, I hope that the problem can be resolved quickly because the only other solution is to use mysql 5.6;
Is there a way to add the SRID to the request by managing the connection through python code?
thanks a lot
Paolo

#10 Updated by Jeffrey Bostoen over 3 years ago

I can confirm it's still a bug in QGis 2.16.0 in combination with MySQL Server 5.7.13 .
It's indeed a visual bug. Feature properties, copying/exporting to GeoJSON works fine though.
But for regular MySQL source editing, it's very annoying. Good thing I came upon a StackOverflow post pointing to this issue.

#11 Updated by paolo tome over 3 years ago

the same rendering error also using geoserver and a WMS from a mysql table

#12 Updated by Giovanni Manghi almost 3 years ago

  • Easy fix? set to No
  • Regression? set to No

#13 Updated by kbs170 kbs170 almost 3 years ago

I can confirm it's still a bug in QGis 2.18.10, Windows 10 and MySQL Server 5.7.16. QGIS can handle the data (search, zoom, cross), but don't show the shapes.
same to: QGIS 2.8.6, Ubuntu 16.10 LTS and MySQL Server 5.7.16 (didn't work)

I have the opportunity to test on:
QGis 2.1.10, Windows 10 and MySQL Server 5.5.54-MariaDB -> that work's!

I find out, that the number 4294967294 plays a role, too: It was created by QGIS as SRID, when I use the mysql-connection to upload an in-QGIS-added shape.

Because GIS-Data make only sense with the newest mysql-Versions, I think the bug is highest priority! Sorry, Giovanni Manghi, what means: "only on QGIS on Windows"??? Are you using Linux?

#14 Updated by Sergey Chernyshov about 2 years ago

I have the same problem with MS SQL Server connection.
I'm using QGIS 2.4 and QGIS 2.16, Windows 7, MSSQLServer 11, MSSQLServer 12.
This bug depends on data coordinate system. No problem with WGS-84 (EPSG:4326) data, but data of any non-WGS georaphical coordinate system are not rendered properly.

#15 Updated by Jeffrey Bostoen almost 2 years ago

Using QGis 3.0.3 now with a connection to a local MySQL Database Server (5.7.22), points are still invisible. Double checked CRS. Panning and zooming and copy-pasting seems to work though, but never shows the features. CRS set to EPSG:3857

It's a really annoying bug; right now I resort to converting the points to shapefiles first just to be able to perform my analysis.

Or another workaround: creating a virtual layer and using select * from <MySQL layer> -> features are shown.

Update: still an issue in QGis 3.2.0

#16 Updated by Michael Charlebois over 1 year ago

Had this same exact issue.
Seems to be resolved by using MariaDB 10.3 instead of Mysql 5.7.11

#17 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Open to Feedback
  • Description updated (diff)
  • Affected QGIS version changed from 2.8.3 to 3.2

Michael Charlebois wrote:

Had this same exact issue.
Seems to be resolved by using MariaDB 10.3 instead of Mysql 5.7.11

So should not we closed this as seems an upstream issue?

#18 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Feedback to Closed
  • Resolution set to up/downstream

#19 Updated by Jose Paredes over 1 year ago

Then Qgis does not allow more to connect to MySql?
Because nobody could correct the query to add the SRID of the current view?

What I interpret of this thread.
Easy fix ?: Sure
Suitable for ARCGIS and other proprietary software: OF COURSE NOT.

#20 Updated by Jeffrey Bostoen over 1 year ago

Jose Paredes wrote:

Then Qgis does not allow more to connect to MySql?
Because nobody could correct the query to add the SRID of the current view?

Although there's a small work around with virtual layers (very grateful I found that way), it's indeed a bit of a hurdle for users to connect to MySQL layers now. If the issue is really upstream, where should we report the issue there, or has that been done?

How much is an upstream issue, since there's a workaround virtual layers?

Additional information: currently working with MySQL 8.0.12 - no change.

#21 Updated by Giovanni Manghi over 1 year ago

Jose Paredes wrote:

Then Qgis does not allow more to connect to MySql?

it could be a regression (it does happens)

Because nobody could correct the query to add the SRID of the current view?

that's not how it works. Probably there are many that could correct it. Asking/raising the issue the proper way is a possibility, supporting the (work necessary for a) fix is another, proposing a patch is another one.

What I interpret of this thread.
Easy fix ?: Sure
Suitable for ARCGIS and other proprietary software: OF COURSE NOT.

#22 Updated by Giovanni Manghi over 1 year ago

Although there's a small work around with virtual layers (very grateful I found that way), it's indeed a bit of a hurdle for users to connect to MySQL layers now. If the issue is really upstream, where should we report the issue there, or has that been done?

This was closed as it was assumed it is an upstream issue. It is not? This comment https://issues.qgis.org/issues/13836#note-16 seems to suggest it is, but I can't check personally as I don't use mysql/mariadb for spatial stuff.

Also available in: Atom PDF