Vector layer points are not being imported/shown from MySQL datasource

[Sakoes]

What is the bug or the crash?

Issue

QGis 3.28.13 does not visualise/import points from a vector layer with a MySQL datasource anymore.

In QGis 3.28.13, I import a table from a MySQL datasource as a vector layer. However, the points are not being visualized. I suspect QGis is not importing the data correctly.

I classified this as a bug because this worked fine in QGis 3.4. I had to upgrade because my SQL provider does not accept the TLS version used by QGis 3.4 anymore.

Context

In the images below following can be observed: The coord (coordinate) column from the MySQL database, containing the points that need to be plotted, is missing from the QGis table structure.
I suspect that is the reason why QGis is not plotting/visualizing any points.
It looks like QGis is incorrectly converting the coord column into a geometry column.
Compare images below:

MySQL table structure

The coord column is a "point" datatype:

QGis 3.28.13 table structure

The coord column is missing, a geometry is added

MySQL record example

The coord column contains coordinates:

QGis 3.28.13 example record:

The geometry column only contains the word "POINT"

Questions

• Is this a bug in QGis? This worked fine in QGis 3.4
• Should I reconfigure my MySQL table to use another datatype than "point" in order for QGis to correctly import the coordinates.

Steps to reproduce the issue

Steps

1. layer > add layer > vector layer
2. In the vector layer panel:

source type: database
encoding automatic

Database Type: mysql
Connections: myconnection

Test Connection > Succesful

add 

3. select items to add: tableWithCoordinates (shown in image above) > add layers
4. Check attributes of imported layer -> coord column is missing, geometry column is added as described above
5. I get following warning but I think it is unrelated:

this layer appears to have no projection specification. by default, this..

Choose: BD72 / Belgian Lambert 72

Versions

3.4 -> worked fine, but uses a deprecated TLS version
3.28.13 -> issue described above

QGIS version
3.28.13-Firenze
QGIS code revision
3b4db4f
Qt version
5.15.3
Python version
3.9.5
GDAL/OGR version
3.8.0
PROJ version
9.3.0
EPSG Registry database version
v10.094 (2023-08-08)
GEOS version
3.12.1-CAPI-1.18.1
SQLite version
3.41.1
PDAL version
2.6.0
PostgreSQL client version
unknown
SpatiaLite version
5.1.0
QWT version
6.1.6
QScintilla2 version
2.13.4
OS version
Windows 10 Version 2009

Active Python plugins
advison_module
0.1
geopunt4Qgis
2.3.0.5
db_manager
0.1.20
sagaprovider
2.12.99

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[agiudiceandrea]

@Sakoes, thanks for reporting. In the issue report you mentioned QGIS 3.04 (maybe QGIS 3.4?), QGIS 3.24 which is no longer supported and QGIS 3.28. Could you please clarify with which version exactly the issue didn't occur and with which currently supported version the issue occurs? Please also provide the full version info of the currently supported versions with which the issue occurs ("In the QGIS Help menu -> About, click in the table, Ctrl+A and then Ctrl+C. Finally paste here. Do not make a screenshot.") as requested in the issue report form.

[Sakoes]

@agiudiceandrea yes my apologies. I switched up some versions in my head.
The correct versions are:

• 3.4 & 3.10: Point are correctly visualized, but the TLS version is to low to connect to my MySQL db
• 3.28.12 & 3.28.13: Correct TLS version, but points are not being plotted/visualized as described in the original post

I'll update it in the original post

[agiudiceandrea]

Please also provide the full version info of the currently supported versions with which the issue occurs ("In the QGIS Help menu -> About, click in the table, Ctrl+A and then Ctrl+C. Finally paste here. Do not make a screenshot.")

[Sakoes]

QGIS version
3.28.13-Firenze
QGIS code revision
3b4db4f
Qt version
5.15.3
Python version
3.9.5
GDAL/OGR version
3.8.0
PROJ version
9.3.0
EPSG Registry database version
v10.094 (2023-08-08)
GEOS version
3.12.1-CAPI-1.18.1
SQLite version
3.41.1
PDAL version
2.6.0
PostgreSQL client version
unknown
SpatiaLite version
5.1.0
QWT version
6.1.6
QScintilla2 version
2.13.4
OS version
Windows 10 Version 2009

Active Python plugins
advison_module
0.1
geopunt4Qgis
2.3.0.5
db_manager
0.1.20
sagaprovider
2.12.99

[rduivenvoorde]

See also this thread: https://lists.osgeo.org/pipermail/qgis-developer/2024-January/066352.html

In short: if mysql does not have a crs defined on the geom column in the table, QGIS will never be able to show the geoms (though it IS retrieving the data and able to show attributes).
If you (even afterwards) fix the column/crs registration all is fine.

Longer anwer:

It looks like mysql has 2 places to store crs information: on the column (geom column registration during creation of the the table) and in the geometry.

With the help of Even R it is easy to reproduce in a docker container:

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

# use ONE of the following lines:

# after THIS one the columns has a crs (and QGIS will show the point):
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
# after THIS one the column has no crs (and QGIS will NOT show the point):
ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point

Some queries to show the differences (with help of https://ant.burnett.com.au/adding-spatial-column-to-existing-mysql-table/ )"

SELECT SRS_ID FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME = 'test' ;

SRS_ID|
------+
      |      

select SHAPE, ST_AsWKT(SHAPE), ST_SRID(SHAPE) from test;

SHAPE                                    |ST_AsWKT(SHAPE)                         |ST_SRID(SHAPE)|
-----------------------------------------+----------------------------------------+--------------+
POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)|             0|

Fixing the srid of the geom:

UPDATE test SET SHAPE = ST_GeomFromText(ST_AsText(SHAPE), 31370);
-- UPdated Rows | 1

select SHAPE, ST_AsWKT(SHAPE), ST_SRID(SHAPE) from test;

SHAPE                                    |ST_AsWKT(SHAPE)                         |ST_SRID(SHAPE)|
-----------------------------------------+----------------------------------------+--------------+
POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)|         31370|

SELECT SRS_ID FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME = 'test' 
SRS_ID|
------+
      |

Still makes QGIS not show the geom as the QgsCoordinateReferenceSystem is invalid:

iface.activeLayer().dataProvider().crs()
<QgsCoordinateReferenceSystem: invalid>

BUT fixing the columns crs (in the INFORMATION_SCHEMA, see the link above):

-- first drop spatial index (called 'SHAPE')
ALTER TABLE test DROP INDEX SHAPE;
-- change the column defintion of SHAPE (see the link for the funny syntax needed) via:
ALTER TABLE test CHANGE COLUMN SHAPE SHAPE GEOMETRY /*!80003 SRID 31370 */ NOT NULL DEFAULT (ST_SRID(POINT(0,0),31370));
--  create a new spatial index:
ALTER TABLE test ADD SPATIAL INDEX(SHAPE);

NOW also the INFORMATION_SCHEMA holds the crs:

SELECT SRS_ID FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME = 'test' ;

SRS_ID|
------+
 31370|

And QGIS is showing the point and reporting a valid crs:

iface.activeLayer().dataProvider().crs()
<QgsCoordinateReferenceSystem: EPSG:31370>

Only question I have is why QGIS, even when telling that the layer the right crs, is still not able to show the features.

[rouault]

Likely resolution in the OGR MySQL driver queued in OSGeo/gdal#9152