"Base schema exists but missing which project it belongs to"

[Konan1236]

I'm trying to set up the connection, I've been able to troubleshoot myself so far but now I get above error message. What does this error message entail? It's unclear to me what the 'project' is, it's referring to.

[Konan1236]

So the problem is within the DB_SCHEMA_MODIFIED and DB_SCHEMA_BASE I'm assuming. What should I do if I do not want to create new schema's but want to just update an existing table within my public schema based on a GeoPackage that's already active within Input/Mergin?

[lucalanteri]

Hi. I have the same problem, I can't configure an existing schema to start with. I saw that the issue was labeled as bug. If it helps I could do some test.

[wonder-sk]

Assuming that you have your database tables in a schema, let's call it "my_data", then this is the value you should use for DB_SCHEMA_MODIFIED. The value for DB_SCHEMA_BASE should be a new schema name that db-sync will create and maintain by itself (to keep the sync working correctly). You could call it e.g. "my_data_db_sync_internal":

• DB_SCHEMA_MODIFIED=my_data
• DB_SCHEMA_BASE=my_data_db_sync_internal

Does that make sense? (sorry the naming is somehow confusing)

[lucalanteri]

Yes, the names seem corrects. May be the problem is in the initializing phase ? If I add by hand this comment to the base schema: {"name": "admin/db-sync", "version": "v0"} this part of the script work but I have another error:

DbSyncError(f"The base schema exists but the output GPKG exists is missing: {gpkg_full_path}"

[wonder-sk]

Your base schema should not exist at the time you initialize db sync - when you delete it, things should work... (and after the init you should not modify anything in the "base" schema as it would break the sync mechanism).

[lucalanteri]

I've created the schema by hand because I've this previous error

File "/mergin-db-sync/dbsync.py", line 651, in dbsync_init _geodiff_make_copy(config.db_driver, config.db_conn_info, config.db_schema_modified, File "/mergin-db-sync/dbsync.py", line 199, in _geodiff_make_copy _run_geodiff([config.geodiff_exe, "copy", "--driver-1", src_driver, src_conn_info, "--driver-2", dst_driver, dst_conn_info, src, dst]) File "/mergin-db-sync/dbsync.py", line 153, in _run_geodiff raise DbSyncError("geodiff failed!\n" + str(cmd)) dbsync.DbSyncError: geodiff failed! ['/geodiff/build/geodiff', 'copy', '--driver-1', 'postgres', '***** '--driver-2', 'postgres', '*****' 'hab', 'base']

File "/mergin-db-sync/dbsync.py", line 257, in _set_db_project_comment cur.execute(query.as_string(conn), (json.dumps(comment), )) psycopg2.errors.InvalidSchemaName: schema "base" does not exist

[lucalanteri]

Problem seem partially to due to starting data. If I change my real table with a very simple test table the base schema was created successfully, but this error remains [0] .

I also hav this comment for the base schema {"name": "admin/ha", "version": "v0", "error": "Initialization of db-sync failed due to a bug in geodiff"}

[0] File "dbsync_daemon.py", line 65, in main() File "dbsync_daemon.py", line 37, in main dbsync.dbsync_init(mc, from_gpkg=False) File "/mergin-db-sync/dbsync.py", line 522, in dbsync_init changes_gpkg_base = _compare_datasets("sqlite", "", gpkg_full_path, config.db_driver, File "/mergin-db-sync/dbsync.py", line 211, in _compare_datasets _geodiff_create_changeset_dr(src_driver, src_conn_info, src, dst_driver, dst_conn_info, dst, tmp_changeset) File "/mergin-db-sync/dbsync.py", line 203, 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, src, dst, changeset]) File "/mergin-db-sync/dbsync.py", line 153, in _run_geodiff raise DbSyncError("geodiff failed!\n" + str(cmd)) dbsync.DbSyncError: geodiff failed! ['/geodiff/build/geodiff', 'diff', '--driver-1', 'sqlite', '', '--driver-2', 'postgres', '****', '/tmp/dbsync/ha.gpkg', 'base', '/tmp/clTQBxZY']

[lucalanteri]

Ok. The last problem was resolved by add some data to the test table. So the main problem remain my real data. I discovered some limits:

1. table must have at least 1 row
2. numeric data (es: numeric(10,2) ) is not allowed

Do you think it's better to open separate tickets ?

BTW thanks for the gorgeous work of mergin and mergin-db-sync !

[wonder-sk]

@lucalanteri That's strange that the copy failed when you had zero rows :thinking: ...would it be possible to provide a dump of your schema in Postgres in the state when it causes the error during db-sync init? I'd like to see if we can find the cause (and fix it). If your dump is sensitive, please feel free to send it by email: martin.dobias at lutraconsulting.co.uk

If I can replicate the bugs, I would raise tickets as needed (in db-sync repo or in geodiff repo).

[lucalanteri]

Hi Martin, after some other tests it seems that changing data type from numeric(10,2) to real resolve also the problem of void table. Thanks and sorry for the noise

[wonder-sk]

The numeric data type should be supported since db-sync 1.1.0. Let me close this ticket, and please feel free to open a new one if things still don't work as expected.