Unable to save point features to QGIS on MySQL database

Question

I created a table with point geometry in MySQL. Subsequently added a vector layer from MySQL table. The table could be accessed from QGIS. However on pressing the add button the system threw warning message that no CRS was defined hence WGS84 has been used. I then changed the CRS to EPSG24378 as was desired. Now I select this vector layer and toggle to editing mode to add points. The MySQL table is shown when I click at the desired location. However when I attempt to save then QGIS trows error as

Could not commit changes to layer MySQL:xxxxxxxxxxx
OGR error creating feature-2:
MySQL error message: SRID value is out of range in 'st_geomfromtext'
Description: INSERT INTO tbl_loc(loc_grid, Name) VALUES (ST_Geomfromtext('POINT( 3383449.16856926 973171.230534863)',-2), Garud)

Obviously SRID of -2 is an error. How do I correct this?

QGIS VER 3.4.5
MySQL VER 8.0.3

Answer 1

I have the same error trying to save work in QGIS to a table I created in MySQL. Here is my error message:

Could not commit changes to layer watermain node

Errors: ERROR: 1 feature(s) not added.

Provider errors: OGR error creating feature -14: MySQL error message:SRID value is out of range in 'st_geomfromtext' Description: INSERT INTO node (GEOM , Description) VALUES (ST_GeomFromText('POINT (3153315.74976982 1435822.95030241)',-2) , 'stuff')

I copied part of the error description which includes SQL script: INSERT INTO node (GEOM , Description) VALUES (ST_GeomFromText('POINT (3153315.74976982 1435822.95030241)',-2) , 'stuff')

Then I tried manually running this script in mysql for which it still failed. Later, I started wondering: what does the -2 stands for in the SQL script and why is it negative? It turns out the number should be the SRID or SRS defining the coordinate zone I am using.

The SRID for my coordinate zone is 2231, I changed the -2 value to 2231 in mysql and ran the script and guess what, it worked. I still dont know if there is a way to modify the Qauntum GIS program to make this correction, but if there is I think it would be a great bug to fix.

If you have not set your SRID for the GEOM(point, linestring, etc) column in your MySQL table, but chances are you may want to try 0 first.

Answer 2

I was having the same problem and managed to figure it out using the general_log.csv and observed how MySQL Importer QGIS Plugin wasn't having the same issue. The following query is needed for you to get QGIS working with your table:

CREATE TABLE `target_schema`.`geometry_columns` (
  `F_TABLE_CATALOG` varchar(256) DEFAULT NULL,
  `F_TABLE_SCHEMA` varchar(256) DEFAULT NULL,
  `F_TABLE_NAME` varchar(256) NOT NULL,
  `F_GEOMETRY_COLUMN` varchar(256) NOT NULL,
  `COORD_DIMENSION` int(11) DEFAULT NULL,
  `SRID` int(11) DEFAULT NULL,
  `TYPE` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=your_charset COLLATE=your_collate;

INSERT INTO geometry_columns (F_TABLE_NAME, F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, TYPE) values ('tbl_loc', 'loc_grid', 2, 2231, 'POINT');

QGIS was able to work without setting column SRID but it should be set:

ALTER TABLE `tbl_loc` MODIFY `loc_grid` NOT NULL SRID 2231;

Last thing to do is set the SPATIAL INDEX for your table:

ALTER TABLE `tbl_loc` ADD SPATIAL INDEX (`loc_grid`);

FYI I'm new to posting, I've had this problem for a long time and felt this needed to be answered.