QGIS » QGIS Application

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

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

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

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

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

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

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.

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

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?

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.

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

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

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.

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.

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.

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.