mbslave sync fails with missing dbmirror2.pending_data relation

[sfussenegger]

I had this error after upgrading to the latest version:

psycopg2.errors.UndefinedTable: relation "dbmirror2.pending_data" does not exist
cursor.execute('TRUNCATE dbmirror2.pending_data')

I've tried creating a fresh copy and go from there, but it still wouldn't work. Now the whole dbmirror2 schema was missing:

psycopg2.errors.InvalidSchemaName: schema "dbmirror2" does not exist
cursor.execute('TRUNCATE dbmirror2.pending_data')

Seems like the changes introduced by pull-request #5 are causing this error.

Should mbslave/sql/dbmirror2/ReplicationSetup.sql be executed after init to fix this?

I tried that to but ran right into the next problem:

  File "/usr/local/lib/python3.12/site-packages/mbslave/replication.py", line 432, in process
    cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: relation "artist_release_group_pending_update" does not exist
LINE 1: INSERT INTO artist_release_group_pending_update VALUES (NEW....
                    ^
QUERY:  INSERT INTO artist_release_group_pending_update VALUES (NEW.id)
CONTEXT:  PL/pgSQL function musicbrainz.a_ins_release_group_mirror() line 3 at SQL statement

This one was a bit surprising as musicbrainz.artist_release_group_pending_update existed. So I'm a bit lost now. May that be a permission issue?

Thanks!

[lalinsky]

Yep, it seems that we need to include ReplicationSetup in the init command and also in the 28 upgrade instructions.

[lalinsky]

And the second error most likely corresponds to some search_path diffferences between the musicbrainz-server and this repo. I'm upgrading the DB myself now, so I'll try to resolve it.

[sfussenegger]

Thanks for looking into this so quickly!

I've also arrived at the conclusion that it's a search_path issue. I don't normally use Postgres so it took a while and I'm not really confident either. Anyway, I think it might have to do with CreateMirrorOnlyTriggers.sql being executed right after eaa/CreateViews.sql which has SET search_path = 'event_art_archive'; while the former doesn't set search_path at all.

[lalinsky]

The easiest temporary workaround is to redefine the function with explicit schema name. It will take a little more to fully fix it. search_path in functions and triggers always confuses me.

[sfussenegger]

What about just resetting search_path before running each sql_scripts[]?

    for schema, sql_script in sql_scripts:
        if schema in config.schemas.ignored_schemas:
            continue
        set_search_path(schema) // SET search_path
        run_sql_script(sql_script)