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

[Even Rouault]

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

-- 
http://www.spatialys.com
My software is free, but my time generally not.