[QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible

[Richard Duivenvoorde]

Thanks Even for checking Even!
I replayed your test, and with me it was fine too

My gut feeling is that it is something in the ogr - gdal bridge.
OR there is a (?wkt) issue because of the move from an older mysql to a newer version?
Is is actually exact the same issue as
https://github.com/qgis/QGIS/issues/34170 :

Ogr shows all, attributes shown ok, it is just QGIS that is not showing the points.

I'll try to debug the current db, and I've asked the people to create a test db (which hopefully then has the same issue :-) )

Regards & Thanks,

Richard

On 1/23/24 13:40, Even Rouault wrote:
> Richard,
> 
> I've failed to reproduce. Here's my testing procedure:
> 
> docker run --name gdal-mysql1 -e MYSQL_ROOT_PASSWORD=passwd -e "MYSQL_ROOT_HOST=%" -p 33060:3306 -d mysql:8.0.34 mysqld --default-authentication-plugin=mysql_native_password
> docker exec gdal-mysql1 sh -c "echo 'CREATE DATABASE test; SELECT Version()' | mysql -uroot -ppasswd"
> printf 'id,WKT\n1,"POINT(168937.186906043 175180.302889316)"\n' > test.csv
> ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point -a_srs EPSG:31370
> 
> then opening it with QGIS displays the point on the canvas and the attribute table. I can also add other points.
> 
> Even
> 
> Le 16/01/2024 à 20:07, Richard Duivenvoorde via QGIS-Developer a écrit :
>> No hints :-) ?
>>
>> I now have a live connection to the db, and can reproduce the issue here in master on Linux too.
>>
>> It's really strange: have a local db, I can even copy features from the attribute table to my local table, and THEN they show up
>>
>> ogrinfo shows exactly the same information of the db ( using -so).
>>
>> coordinates are fine too:
>>
>> select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1
>>
>> coord |ST_AsWKT(coord)                         |
>> -----------------------------------------+----------------------------------------+
>> POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)|
>>
>> could it be in char encoding, or in a strange attribute value? We already tried to create a simple view with only coord and ID, but that did not show something either.
>>
>> As I cannot share the connection info, I'm happy to share my screen or so to show this.
>>
>> SELECT version();
>> version()|
>> ---------+
>> 8.0.34-26|
>>
>> but is an upgraded db, not sure where it came from
>>
>> Regards,
>>
>> Richard Duivenvoorde
>>
>>
>> On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote:
>>> Hi Devs,
>>>
>>> I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore.
>>>
>>> Note that 'all worked' when they used QGIS 3.10 (and an older mysql db)
>>> After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher.
>>>
>>> A small export loaded in my local db was OK in QGIS.
>>>
>>> So in a online meeting, sharing their screen I tried:
>>>
>>> - opening the db with ogrinfo: all data is visible (POINT(.....)) showing correct coords and attributes
>>> - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 :
>>>      - attribute table shows all records
>>>      - mapcanvas empty!
>>>      - able to 'zoom to' records (Belgium), but NO points visible/selectable
>>> - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy
>>> - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE!
>>> - loading the data in dbeaver: all data is shown in the spatial tab
>>>
>>> There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing.
>>>
>>> Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be?
>>>
>>> Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious...
>>>
>>> Regards,
>>>
>>> Richard Duivenvoorde
>>> _______________________________________________
>>> QGIS-Developer mailing list
>>> QGIS-Developer at lists.osgeo.org
>>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>> _______________________________________________
>> QGIS-Developer mailing list
>> QGIS-Developer at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>