Init fails due to violated check constraints

[nikammerlaan]

I'm initing the database with these commands:

pipx install 'mbslave'
mbslave init

and running into this error:

...
INFO:mbslave.replication:Loading l_series_work to musicbrainz.l_series_work
INFO:mbslave.replication:Loading l_url_work to musicbrainz.l_url_work
INFO:mbslave.replication:Loading l_work_work to musicbrainz.l_work_work
INFO:mbslave.replication:Loading label to musicbrainz.label
Traceback (most recent call last):
  File "/opt/pipx_bin/mbslave", line 8, in <module>
    sys.exit(main())
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 787, in main
    args.func(config, args)
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 297, in mbslave_auto_import_main
    load_tar(url, fileobj, db, config, config.schemas.ignored_schemas, config.tables.ignored_tables)
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 258, in load_tar
    cursor.copy_expert('COPY {} FROM STDIN'.format(fulltable), tar.extractfile(member))
psycopg2.errors.CheckViolation: new row for relation "label" violates check constraint "label_label_code_check"
DETAIL:  Failing row contains (294731, dacec7dc-806e-4f1a-ab41-cc0c46b297e0, beau by Republic, 2022, 10, 9, null, null, null, 202210, 1, 7741, , 0, 2024-05-14 21:06:04.842073+00, f).
CONTEXT:  COPY label, line 39973: "294731	dacec7dc-806e-4f1a-ab41-cc0c46b297e0	beau by Republic	2022	10	9	\N	\N	\N	202210	1	7741		0	202..."

Traceback (most recent call last):
  File "/opt/pipx_bin/mbslave", line 8, in <module>
    sys.exit(main())
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 787, in main
    args.func(config, args)
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 640, in mbslave_init_main
    run_script('mbslave auto-import')
  File "/opt/pipx/venvs/mbslave/lib/python3.10/site-packages/mbslave/replication.py", line 593, in run_script
    subprocess.run(script, check=True, shell=True)
  File "/usr/lib/python3.10/subprocess.py", line 526, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command 'mbslave auto-import' returned non-zero exit status 1.

[nikammerlaan]

I did some digging and this is because version 29 has not been published.

[nikammerlaan]

I fixed it by installing mbslave from git:

pipx install git+https://github.com/acoustid/mbslave.git

[flimzy]

I'm getting the same error, even after instalilng mbslave from git as suggested above. 😢

Update: After uninstalling mbslave before re-installing from git, it works.

[kevinlee819]

I fixed it by installing mbslave from git:

pipx install git+https://github.com/acoustid/mbslave.git

after that. also didn't work

INFO:mbslave.replication:Latest dump is 20250205-001748
INFO:mbslave.replication:Importing data from http://ftp.musicbrainz.org/pub/musicbrainz/data/fullexport/20250205-001748/mbdump.tar.bz2
INFO:mbslave.replication:Loading alternative_release_type to musicbrainz.alternative_release_type
INFO:mbslave.replication:Loading area to musicbrainz.area
INFO:mbslave.replication:Loading area_alias to musicbrainz.area_alias
INFO:mbslave.replication:Loading area_alias_type to musicbrainz.area_alias_type
INFO:mbslave.replication:Loading area_gid_redirect to musicbrainz.area_gid_redirect
INFO:mbslave.replication:Loading area_type to musicbrainz.area_type
INFO:mbslave.replication:Loading artist to musicbrainz.artist
INFO:mbslave.replication:Loading artist_alias to musicbrainz.artist_alias
INFO:mbslave.replication:Loading artist_alias_type to musicbrainz.artist_alias_type
INFO:mbslave.replication:Loading artist_credit to musicbrainz.artist_credit
zINFO:mbslave.replication:Loading artist_credit_gid_redirect to musicbrainz.artist_credit_gid_redirect
INFO:mbslave.replication:Loading artist_credit_name to musicbrainz.artist_credit_name
Traceback (most recent call last):
  File "/home/meizu/miniconda3/bin/mbslave", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 806, in main
    args.func(config, args)
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 304, in mbslave_auto_import_main
    load_tar(url, fileobj, db, config, config.schemas.ignored_schemas, config.tables.ignored_tables)
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 265, in load_tar
    cursor.copy_expert('COPY {} FROM STDIN'.format(fulltable), tar.extractfile(member))
psycopg2.errors.QueryCanceled: COPY from stdin failed: error in .read() call: ReadError unexpected end of data
CONTEXT:  COPY artist_credit_name, line 5080688

Traceback (most recent call last):
  File "/home/meizu/miniconda3/bin/mbslave", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 806, in main
    args.func(config, args)
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 654, in mbslave_init_main
    run_script('mbslave auto-import')
  File "/home/meizu/miniconda3/lib/python3.12/site-packages/mbslave/replication.py", line 607, in run_script
    subprocess.run(script, check=True, shell=True)
  File "/home/meizu/miniconda3/lib/python3.12/subprocess.py", line 571, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command 'mbslave auto-import' returned non-zero exit status 1.

[frisi]

i had similar errors when trying to bootstrap a fresh database the last days and tried multiple things and made a few mistakes. sharing this will hopefully help you.
please try out one after the other and provide some feedback so we know what helped you so we can fix the issues with a pull request.

A) don't split db creation and import

make sure to remove and re-create the database before you run mbslave

i've done something like this to create the database in a first step and import data in a second step.

mbslave init --create-user --create-database --empty

# download package
wget http://ftp.musicbrainz.org/pub/musicbrainz/data/fullexport/20250122-001711/mbdump.tar.bz2

mbslave import mbdump.tar.bz2

⚠ this won't work, as mbslave init works differently:

• it first creates the tables
• second imports data via the postgres \copy command (also more packages than the one mentioned above)
• and after that it installs triggers that will make a second import of data next to impossible (script will get stuck either on copy or commit

B) set keepalive

i've adapted replication.py to set keepalive settings as suggested in https://stackoverflow.com/a/63130830

    def create_psycopg2_kwargs(self, superuser=False, no_db=False):
        kwargs = {
            "keepalives": 1,
            "keepalives_idle": 30,
            "keepalives_interval": 5,
            "keepalives_count": 5,
        }

• you'll need to checkout this repository
• adapt code
• create a virtualenv
• activate it
• install from local repository

git clone ...

python3 -m venv mbslave
cd mbslave
source bin/activate

# install dependencies
sudo apt-get install postgresql-client
sudo apt install libpq-dev

pip install ~/path/to/mbslave

helpful debug commands

i've had hanging copy and commits blocking my database (due to mistake A).
should not happen with a fresh database.

to better know what currently is going on you can connect to your database (eg using psql -h <host> -U <user> <dbname> )
and check the status of the curently running copy command

Whenever COPY is running, the pg_stat_progress_copy view will contain one row for each backend that is currently running a COPY command
(see https://www.postgresql.org/docs/15/progress-reporting.html#COPY-PROGRESS-REPORTING)

select * from pg_stat_progress_copy;

query for any long running commands

    SELECT pid, age(clock_timestamp(), query_start), usename, query 
    FROM pg_stat_activity 
    WHERE state != 'idle' and query NOT ILIKE '%pg_stat_activity%' 
    ORDER BY query_start;

terminate a pid

select pg_cancel_backend(<procpid>);

-- sometimes
select pg_terminate_backend(<procpid>);

[kevinlee819]

i had similar errors when trying to bootstrap a fresh database the last days and tried multiple things and made a few mistakes. sharing this will hopefully help you. please try out one after the other and provide some feedback so we know what helped you so we can fix the issues with a pull request.

A) don't split db creation and import

make sure to remove and re-create the database before you run mbslave

i've done something like this to create the database in a first step and import data in a second step.

mbslave init --create-user --create-database --empty

# download package
wget http://ftp.musicbrainz.org/pub/musicbrainz/data/fullexport/20250122-001711/mbdump.tar.bz2

mbslave import mbdump.tar.bz2

⚠ this won't work, as mbslave init works differently:

• it first creates the tables
• second imports data via the postgres \copy command (also more packages than the one mentioned above)
• and after that it installs triggers that will make a second import of data next to impossible (script will get stuck either on copy or commit

B) set keepalive

i've adapted replication.py to set keepalive settings as suggested in https://stackoverflow.com/a/63130830

    def create_psycopg2_kwargs(self, superuser=False, no_db=False):
        kwargs = {
            "keepalives": 1,
            "keepalives_idle": 30,
            "keepalives_interval": 5,
            "keepalives_count": 5,
        }

• you'll need to checkout this repository
• adapt code
• create a virtualenv
• activate it
• install from local repository

git clone ...

python3 -m venv mbslave
cd mbslave
source bin/activate

# install dependencies
sudo apt-get install postgresql-client
sudo apt install libpq-dev

pip install ~/path/to/mbslave

helpful debug commands

i've had hanging copy and commits blocking my database (due to mistake A). should not happen with a fresh database.

to better know what currently is going on you can connect to your database (eg using psql -h <host> -U <user> <dbname> ) and check the status of the curently running copy command

Whenever COPY is running, the pg_stat_progress_copy view will contain one row for each backend that is currently running a COPY command
(see https://www.postgresql.org/docs/15/progress-reporting.html#COPY-PROGRESS-REPORTING)

select * from pg_stat_progress_copy;

query for any long running commands

    SELECT pid, age(clock_timestamp(), query_start), usename, query 
    FROM pg_stat_activity 
    WHERE state != 'idle' and query NOT ILIKE '%pg_stat_activity%' 
    ORDER BY query_start;

terminate a pid

select pg_cancel_backend(<procpid>);

-- sometimes
select pg_terminate_backend(<procpid>);

You're right, after repeatedly trying to download data directly using mbslave without success, I used the method you mentioned:

mbslave init --create-user --create-database --empty

# download package
wget http://ftp.musicbrainz.org/pub/musicbrainz/data/fullexport/20250122-001711/mbdump.tar.bz2

mbslave import mbdump.tar.bz2

But it gets stuck at "release" table every time

 402740 postgres  20   0  950484 789920 199168 R 100.0   4.9 967:02.00 postgres: 14/main: postgres musicbrainz 127.0.0.1(35860) COMMIT

It seems like the process has been running for a day. I don't know what's going on.

[frisi]

@kevinlee819 please read carefully: the method described in A does not work as it will create a database with triggers
in place that will make importing huge datasets next to impossible (\copy takes a long time, and the deferred triggers will make importer stuck at the commit)

so please make sure you start with a fresh database, and try if updated keepalives settings solve you issues.

[MeaningOfLights]

I think there's a few issues going on here.

I ran into the same problem as OP specifically with the error line numbers: 297, 258, 787

Like OP explained its because of v28. However for me even though I used pip to install the latest it installed version 28! Not 29.1!!

Proof even with the zip versioned file, check last line:

pip install "https://github.com/acoustid/mbslave/archive/refs/tags/v29.1.0.zip"
Collecting https://github.com/acoustid/mbslave/archive/refs/tags/v29.1.0.zip
Downloading https://github.com/acoustid/mbslave/archive/refs/tags/v29.1.0.zip
\ 513.8 kB 4.6 MB/s 0:00:00
Installing build dependencies: started
Installing build dependencies: finished with status 'done'
Getting requirements to build wheel: started
Getting requirements to build wheel: finished with status 'done'
Preparing metadata (pyproject.toml): started
Preparing metadata (pyproject.toml): finished with status 'done'
Requirement already satisfied: prometheus-client<0.21.0,>=0.20.0 in c:\python312\lib\site-packages (from mbslave==28.0.0) (0.20.0)
Requirement already satisfied: psycopg2<3.0.0,>=2.9.2 in c:\python312\lib\site-packages (from mbslave==28.0.0) (2.9.10)
Requirement already satisfied: six<2.0.0,>=1.16.0 in c:\python312\lib\site-packages (from mbslave==28.0.0) (1.16.0)
Building wheels for collected packages: mbslave
Building wheel for mbslave (pyproject.toml): started
Building wheel for mbslave (pyproject.toml): finished with status 'done'
Created wheel for mbslave: filename=mbslave-28.0.0-py3-none-any.whl size=497519 sha256=7e3efae313a05e2d0f7cb6b49bd451e216c38b0ce6942eca74858c8c430daf00
Stored in directory: C:\Users\Jeremy\AppData\Local\Temp\pip-ephem-wheel-cache-uga0n7hn\wheels\1f\e7\f9\6d294c18077440dce0dfa9bb36fc458ee71d05db97ca48b5a1
Successfully built mbslave
Installing collected packages: mbslave
Successfully installed mbslave-28.0.0

28.0.0???

Anyway, I cloned the latest version = "29.1.0" and can see the code matches the new error lines kevinlee819 reported:
806, 304, 265

So given I couldn't use pip to install & had to pack it myself, I did the KeepAlive change and similar to frisi I installed it with Powershell:

1. Open PowerShell in the mbslave source folder. Create & activate a venv (keeps it tidy)

py -3.12 -m venv .venv
.\.venv\Scripts\activate
python -m pip install -U pip setuptools wheel
python -m pip install -U psycopg2-binary

2. Install mbslave from the source you downloaded

python -m pip install .

This creates mbslave.exe in .venv\Scripts.

3. Make sure Postgres client tools are usable (psql/createdb)

$env:PATH = "C:\Program Files\PostgreSQL\15\bin;$env:PATH" # adjust version/path if needed
psql --version

4. Set connection/env vars (same window)

# Admin (superuser)
$env:MBSLAVE_DB_ADMIN_USER="postgres"
$env:MBSLAVE_DB_ADMIN_PASSWORD="REAL_POSTGRES_PASSWORD"    # your real postgres password
$env:MBSLAVE_DB_HOST="127.0.0.1"             # avoid IPv6 (::1)
$env:MBSLAVE_DB_PORT="5432"
# Target DB + role that mbslave will create
$env:MBSLAVE_DB_NAME="musicbrainz"
$env:MBSLAVE_DB_USER="musicbrainz"
$env:MBSLAVE_DB_PASSWORD="MB_PASSWORD"
# Help libpq subprocess auth
$env:PGUSER=$env:MBSLAVE_DB_ADMIN_USER
$env:PGPASSWORD=$env:MBSLAVE_DB_ADMIN_PASSWORD
$env:PGHOST=$env:MBSLAVE_DB_HOST
$env:PGPORT=$env:MBSLAVE_DB_PORT
$env:PGDATABASE="postgres"

5. Initialize DB (creates role, DB, schema)

& ".\.venv\Scripts\mbslave.exe" init --create-user --create-database

Then you get to this next error: #21 - it fails with the medium table.

INFO:mbslave.replication:Loading link_type_attribute_type to musicbrainz.link_type_attribute_type
INFO:mbslave.replication:Loading medium to musicbrainz.medium
Traceback (most recent call last):
File "", line 198, in _run_module_as_main
File "", line 88, in _run_code