Database defined UNIQUE constraint causes conflicts #2933
Comments
|
Hello @leorudczenko, thank you for such a detailed report! This is likely happening because of how |
|
Hey @tomasMizera, thanks for the quick reply 🙂 I mean a conflict in terms of creating a copy of the geopackage in a separate file called I have tried using {
"geodiff": [
{
"changes": [
{
"column": 0,
"old": 1
},
{
"column": 1,
"new": "R1AAAREPAAABAQAAAALYHbPRNf/A+IJ2heqZWkE=",
"old": "R1AAAREPAAABAQAAAIL3i1d8J/TAmPsdPsKUW0E="
},
{
"column": 2,
"new": "2023-12-01T13:30:38.097",
"old": "2023-12-01T13:28:46.017"
},
{
"column": 3,
"new": "This point was made in MerginMaps Input",
"old": "This point was made in QGIS"
},
{
"column": 4,
"new": "PXL_20231017_154620713.jpg",
"old": "20231121_093845.jpg"
}
],
"table": "Survey_points",
"type": "update"
}
]
} |
|
I will forward this to our support team and we will get back to you. |
|
Hi @leorudczenko, can you check if the problem persists if using QGIS 3.34? There was a bug in previous releases that caused issues when merging features with a 'unique' constraint, which has been fixed in the 3.34 update. |
|
Hey @raherin, I've just followed the same s 8000 teps to recreate this bug in QGIS 3.34, and I'm getting the same problem as I did in QGIS 3.32 unfortunately. |
|
I've tested this in the latest version of the plugin (2024.1) on QGIS 3.34.2 and v2.5.0 of the mobile app on Android and the conflict file is not created. I made an example project that is publicly available: support/unique-test The unique constraint is set in the photo field. |
|
Hey @raherin, thanks for taking the time to look at this. However, I've just checked the project you've provided for your test and the The way we did this on the example project was using these steps:
We found that you have to sync the project in-between deleting and re-creating the GeoPackage file because mergin does not recognise database constraint changes with Alternatively, you could just create a new mergin project from scratch and ensure the I have tried to recreate this bug again using the versions you specified, |
|
Hi @leorudczenko, I could still not replicate the conflict generation by following your extended instructions, unless I intentionally set a duplicate value to both the point created in QGIS and the one created in Mergin Maps for the unique field, which is the properly expected behavior. Please also bear in mind that changing the database schema after the project has been created is something that is currently not supported and can lead to conflicts. |
|
Hey @uclaros, yes I am 100% sure that I am not storing duplicate values on a unique field. I have been able to recreate this bug consistently across 40+ Mergin Maps projects of varying structures. Thank you for the information about database schema changes. I have created a new minimum project to replicate this issue without modifying database schemas, but the issue still persists as it has always been. But, here is the SQL script I am using to generate my fresh GeoPackage file without any schema modifications in-between: PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE gpkg_spatial_ref_sys (srs_name TEXT NOT NULL,srs_id INTEGER NOT NULL PRIMARY KEY,organization TEXT NOT NULL,organization_coordsys_id INTEGER NOT NULL,definition TEXT NOT NULL,description TEXT);
INSERT INTO gpkg_spatial_ref_sys VALUES('Undefined Cartesian SRS',-1,'NONE',-1,'undefined','undefined Cartesian coordinate reference system');
INSERT INTO gpkg_spatial_ref_sys VALUES('Undefined geographic SRS',0,'NONE',0,'undefined','undefined geographic coordinate reference system');
INSERT INTO gpkg_spatial_ref_sys VALUES('WGS 84 geodetic',4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AXIS["Latitude",NORTH],AXIS["Longitude",EAST],AUTHORITY["EPSG","4326"]]','longitude/latitude coordinates in decimal degrees on the WGS 84 spheroid');
CREATE TABLE gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,data_type TEXT NOT NULL,identifier TEXT UNIQUE,description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),min_x DOUBLE, min_y DOUBLE,max_x DOUBLE, max_y DOUBLE,srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id));
INSERT INTO gpkg_contents VALUES('locality_point','features','locality_point','','2024-02-29T15:43:12.515Z',NULL,NULL,NULL,NULL,4326);
CREATE TABLE gpkg_ogr_contents(table_name TEXT NOT NULL PRIMARY KEY,feature_count INTEGER DEFAULT NULL);
INSERT INTO gpkg_ogr_contents VALUES('locality_point',NULL);
CREATE TABLE gpkg_geometry_columns (table_name TEXT NOT NULL,column_name TEXT NOT NULL,geometry_type_name TEXT NOT NULL,srs_id INTEGER NOT NULL,z TINYINT NOT NULL,m TINYINT NOT NULL,CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),CONSTRAINT uk_gc_table_name UNIQUE (table_name),CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
INSERT INTO gpkg_geometry_columns VALUES('locality_point','geometry','POINT',4326,0,0);
CREATE TABLE gpkg_tile_matrix_set (table_name TEXT NOT NULL PRIMARY KEY,srs_id INTEGER NOT NULL,min_x DOUBLE NOT NULL,min_y DOUBLE NOT NULL,max_x DOUBLE NOT NULL,max_y DOUBLE NOT NULL,CONSTRAINT fk_gtms_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),CONSTRAINT fk_gtms_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
CREATE TABLE gpkg_tile_matrix (table_name TEXT NOT NULL,zoom_level INTEGER NOT NULL,matrix_width INTEGER NOT NULL,matrix_height INTEGER NOT NULL,tile_width INTEGER NOT NULL,tile_height INTEGER NOT NULL,pixel_x_size DOUBLE NOT NULL,pixel_y_size DOUBLE NOT NULL,CONSTRAINT pk_ttm PRIMARY KEY (table_name, zoom_level),CONSTRAINT fk_tmm_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name));
CREATE TABLE IF NOT EXISTS "locality_point" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geometry" POINT, "name" TEXT UNIQUE);
CREATE TABLE gpkg_extensions (table_name TEXT,column_name TEXT,extension_name TEXT NOT NULL,definition TEXT NOT NULL,scope TEXT NOT NULL,CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name));
INSERT INTO gpkg_extensions VALUES('locality_point','geometry','gpkg_rtree_index','http://www.geopackage.org/spec120/#extension_rtree','write-only');
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','rtree_locality_point_geometry','rtree_locality_point_geometry',0,'CREATE VIRTUAL TABLE "rtree_locality_point_geometry" USING rtree(id, minx, maxx, miny, maxy)');
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_rowid"(rowid INTEGER PRIMARY KEY,nodeno);
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_node"(nodeno INTEGER PRIMARY KEY,data);
INSERT INTO rtree_locality_point_geometry_node VALUES(1,X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_parent"(nodeno INTEGER PRIMARY KEY,parentnode);
DELETE FROM sqlite_sequence;
CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: zoom_level cannot be less than 0') WHERE (NEW.zoom_level < 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_update' BEFORE UPDATE of zoom_level ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: zoom_level cannot be less than 0') WHERE (NEW.zoom_level < 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: matrix_width cannot be less than 1') WHERE (NEW.matrix_width < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_update' BEFORE UPDATE OF matrix_width ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: matrix_width cannot be less than 1') WHERE (NEW.matrix_width < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: matrix_height cannot be less than 1') WHERE (NEW.matrix_height < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_update' BEFORE UPDATE OF matrix_height ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: matrix_height cannot be less than 1') WHERE (NEW.matrix_height < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: pixel_x_size must be greater than 0') WHERE NOT (NEW.pixel_x_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_update' BEFORE UPDATE OF pixel_x_size ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: pixel_x_size must be greater than 0') WHERE NOT (NEW.pixel_x_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: pixel_y_size must be greater than 0') WHERE NOT (NEW.pixel_y_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_update' BEFORE UPDATE OF pixel_y_size ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: pixel_y_size must be greater than 0') WHERE NOT (NEW.pixel_y_size > 0); END;
CREATE TRIGGER "rtree_locality_point_geometry_insert" AFTER INSERT ON "locality_point" WHEN (new."geometry" NOT NULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update1" AFTER UPDATE OF "geometry" ON "locality_point" WHEN OLD."fid" = NEW."fid" AND (NEW."geometry" NOTNULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update2" AFTER UPDATE OF "geometry" ON "locality_point" WHEN OLD."fid" = NEW."fid" AND (NEW."geometry" ISNULL OR ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; END;
CREATE TRIGGER "rtree_locality_point_geometry_update3" AFTER UPDATE ON "locality_point" WHEN OLD."fid" != NEW."fid" AND (NEW."geometry" NOTNULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update4" AFTER UPDATE ON "locality_point" WHEN OLD."fid" != NEW."fid" AND (NEW."geometry" ISNULL OR ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id IN (OLD."fid", NEW."fid"); END;
CREATE TRIGGER "rtree_locality_point_geometry_delete" AFTER DELETE ON "locality_point" WHEN old."geometry" NOT NULL BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; END;
CREATE TRIGGER "trigger_insert_feature_count_locality_point" AFTER INSERT ON "locality_point" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('locality_point'); END;
CREATE TRIGGER "trigger_delete_feature_count_locality_point" AFTER DELETE ON "locality_point" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('locality_point'); END;
PRAGMA writable_schema=OFF;
COMMIT;And here is the list of steps I follow exactly to recreate the bug:
When comparing the actual GeoPackage file with the conflict GeoPackage file using {
"geodiff": [
{
"changes": [
{
"column": 0,
"old": 1
},
{
"column": 1,
"new": "R1AAAeYQAAABAQAAAGYVNy2RfPK/E2w8EO98SkA=",
"old": "R1AAAeYQAAABAQAAAD9yXJ/8+/G/HvOdojl9SkA="
},
{
"column": 2,
"new": "leo_mergin",
"old": "leo_qgis"
}
],
"table": "locality_point",
"type": "update"
}
]
}Therefore, this could be an issue with Here is a link to a public MerginMaps project where all of these files and examples came from: https://app.merginmaps.com/projects/SIGMALite/conflict-bug-min-2/tree |
|
Hi @leorudczenko
See: https://app.merginmaps.com/projects/saber/uq-test/history?version_id=v2
See: https://app.merginmaps.com/projects/saber/uq-test/history?version_id=v3 As you can see the process did not create an edit conflict. |
|
Hey @saberraz, thanks for taking the time to look over this. Unfortunately, I've checked the project links you sent and it looks like the I have tried to recreate this myself again using the instructions which I explained in more detail here. I can confirm that I am still able to reproduce this bug with the exact same steps. Here is a link to the public Mergin Maps project: https://app.merginmaps.com/projects/SIGMALite/conflict-bug-min-20240502/tree These are the software versions I was using to reproduce it this time:
This is the output when using {
"geodiff": [
{
"changes": [
{
"column": 0,
"old": 1
},
{
"column": 1,
"new": "R1AAAeYQAAABAQAAALUd/JjDfPK/hpM3rO98SkA=",
"old": "R1AAAeYQAAABAQAAAGwT2eol9/G/teN/L3OASkA="
},
{
"column": 2,
"new": "leorud_mergin",
"old": "leorud_qgis"
}
],
"table": "locality_point",
"type": "update"
}
]
}As you can see, Both the |
|
hi @leorudczenko many thanks for the clarification. Before digging deep into this, would it be possible to see if having the constraint within the form addresses your requirements. Generally, it is better to keep those type of setting within QGIS layer/project settings and use the GeoPackage simply as a storage (like shapefiles :)). |
|
It is important for us to use the constraints within the GeoPackage as this represents our "one-source-of-truth" for our data model. For example, we use UNIQUE constraints on our UUID columns, which underpin our foreign key (FK) relationships. There are FK relationships between a measurement, the locality where it was measured and the project that it belongs to. FKs also constrain the values that we can use in drop downs. QGIS auto-detects these relationships, which is really helpful for setting up forms. We also use UNIQUE constraints elsewhere e.g. for sample IDs. |
|
Hey @saberraz, we have done some more experimenting and found the problem lies in |
|
We should keep this issue open, however, as we have found that the problem only exists when using the mobile app. When using QGIS, if the server version has had points added while you were adding points locally, then you sync, all works well. When using the mobile version, if the server version has points added while you are adding points locally, then you will get a conflict. Perhaps this is a clue to the cause of the problem e.g. if the mobile app is using geodiff's |
|
I'm hopeful that we will get funding to work on this. When we do so, we should have a test case that more closely represents our data model. We have foreign key constraints as well as unique constraints. |
Uh oh!
There was an error while loading. Please reload this page.
We believe we have found a bug involving MerginMaps mobile Input.
Summary
Our database/GeoPackage structure requires the use of the
UNIQUEconstraint on certain fields, specifically for primary/foreign keys. Adding a record to a table in Mergin Maps mobile Input that has a UNIQUE constraint on a field will cause a subsequent sync to fail with a conflict if the data on the server have been changed by QGIS in the meantime.To Reproduce
We found that this can be replicated in a minimal way using the following steps:
New basic QGIS projectSurvey_points.gpkgby adding aUNIQUEconstraint to any fieldSurveylayer in QGISSurveylayer in MerginMaps InputExample Project
We have created a public MerginMaps project with these same steps applied. In this instance, the
UNIQUEconstraint has been applied to thephotofield in theSurvey_pointstable. You can view the project here.It is worth noting that this issue only occurs when syncing QGIS first, and then syncing MerginMaps Input. If you sync MerginMaps Input first, and then you sync QGIS, the conflict issue does not occur. We have also tried to replicate it between 2 QGIS instances, and 2 MerginMaps Input instances, but neither of those scenarios encountered the conflict issue either.
Software Versions
We have successfully replicated this issue on multiple devices that are using the most up-to-date versions of MerginMaps Input and the MerginMaps QGIS plugin.
3.32.3for Windows2023.4.12.4.1for AndroidThe text was updated successfully, but these errors were encountered: