GEODIFF: Error: Unable to prepare SQL statement

[jreinier]

After lots of troubleshooting, I've gotten an error I can't seem to work around.

== starting mergin-db-sync daemon == version 1.1.2 ==
Logging in to Mergin...
Processing Mergin Maps project 'Cleveland-Metroparks/nr_test'
Connecting to the database...
Modified and base schemas already exist
Downloading version v1 of Mergin Maps project Cleveland-Metroparks/nr_test to /tmp/dbsync/nr_test
Error: diff failed!
GEODIFF: Error: Unable to prepare SQL statement in prepare() call (SQLITE3 error [1]: near ")": syntax error)
Error: Failed to create a copy of modified source for driver postgres

Traceback (most recent call last):
File "dbsync_daemon.py", line 63, in
main()
File "dbsync_daemon.py", line 33, in main
dbsync.dbsync_init(mc, from_gpkg=True)
File "/mergin-db-sync/dbsync.py", line 666, in dbsync_init
init(conn, mc, from_gpkg)
File "/mergin-db-sync/dbsync.py", line 551, in init
summary_modified = _compare_datasets("sqlite", "", gpkg_full_path, conn_cfg.driver,
File "/mergin-db-sync/dbsync.py", line 148, in _compare_datasets
_geodiff_create_changeset_dr(src_driver, src_conn_info, src, dst_driver, dst_conn_info, dst, tmp_changeset, ignored_tables)
File "/mergin-db-sync/dbsync.py", line 138, in _geodiff_create_changeset_dr
_run_geodiff([config.geodiff_exe, "diff", "--driver-1", src_driver, src_conn_info, "--driver-2", dst_driver, dst_conn_info, "--skip-tables", _tables_list_to_string(ignored_tables), src, dst, changeset])
File "/mergin-db-sync/dbsync.py", line 75, in _run_geodiff
raise DbSyncError("geodiff failed!\n" + str(cmd))
dbsync.DbSyncError: geodiff failed!

[wonder-sk]

@jreinier can you please post how do you run db-sync, what is the configuration? (just make sure to remove any passwords)

Also, if you delete the "modified" and "base" schemas in your postgresql database and restart db-sync, does it work?

[jreinier]

Here is what I run:

docker run --name mergin_db_sync -it -e MERGIN__USERNAME=user -e MERGIN__PASSWORD="password" -e CONNECTIONS="[{driver='postgres', conn_info='host=host dbname=dbname user=db_user password=db_password', modified='wetland', base='sync_base', mergin_project='Cleveland-Metroparks/nr_test', sync_file='cm_wetlands.gpkg',
skip_tables=['cm_wetland_class_oram','cm_wetland_class_oram_merge','cm_wetland_class_oram_updated','cm_wetland_class_oram_vibi','cm_wetland_class_oram_vibi_merge','cm_wetland_class_oram_vibi_merge_expanded','cm_wetland_classification_to_fulcrum_format','cm_wetland_class_oram_vibi_updated','oram_data_form_merge','oram_metric_values','oram_scores','oram_v2','oram_v2_changesets','oram_v2_current','oram_v2_photos','usace_delineation_data','usace_delineation_data_herbaceous_stratum_species','usace_delineation_data_shrub_stratum_species','usace_delineation_data_site_photos','usace_delineation_data_soil_layers','usace_delineation_data_soil_layers_photos','usace_delineation_data_soil_layers_redox_features','usace_delineation_data_tree_stratum_species','usace_delineation_data_vine_stratum_species','wetland_classification','wetland_classification_changesets','wetland_classification_photos','wetland_classification_pre_fulcrum','wetland_classification_pre_fulcrum_with_notes','wetland_grts_large_polys','wetland_grts_large_polys_EPSG_4326','wetland_grts_small_polys','wetland_grts_xlarge_polys','wetland_grts_xlarge_polys_EPSG_4326','wetland_oram_data_pre_fulcrum','wetland_vibi.geom','wetland_vibi.vibi_plot_geom']}]" lutraconsulting/mergin-db-sync:latest python3 dbsync_daemon.py --init-from-gpkg

If I test without using any existing schemas and run what is below, things seem to work. However, I very much want to use an existing table in an existing schema as above.

docker run --name mergin_db_sync -it -e MERGIN__USERNAME=user -e MERGIN__PASSWORD="password" -e CONNECTIONS="[{driver='postgres', conn_info='host=host dbname=db_name user=db_user password=db_password', modified='sync_modified', base='sync_base', mergin_project='Cleveland-Metroparks/nr_test', sync_file='cm_wetlands.gpkg'}]" lutraconsulting/mergin-db-sync:latest python3 dbsync_daemon.py --init-from-gpkg

[jreinier]

And here is the table schema for the existing table I want to use. I was curious if there was maybe something about the default value for polygon_number since it's not a straightforward sequence.

CREATE TABLE wetland.cm_wetlands (
fid int4 NOT NULL DEFAULT nextval('wetland.wetland_fid_seq'::regclass),
polygon_number text NOT NULL DEFAULT to_char(nextval('wetland.wetland_polygon_number_seq'::regclass), 'FM9999'::text),
reservation text NOT NULL,
geom public.geometry(multipolygon, 3734) NULL,
area_acres numeric NULL,
poly_type varchar(20) NULL DEFAULT 'wetland'::character varying,
fulcrum_id text NULL
);
CREATE INDEX cm_wetlands_gix ON wetland.cm_wetlands USING gist (geom);
CREATE UNIQUE INDEX wetland_poly_res_unique ON wetland.cm_wetlands USING btree (reservation, polygon_number);

UPDATE: The sequence issue I mention above is not the problem. I tried a test run without that polygon_number field and I got the same error that I opened the issue about.