sqlite locked randomly

[Lartza]

Migrating from an sqlite file is inconsistently but often failing on "database is locked".

• pgloader --version

  pgloader version "3.6.2"
  compiled with SBCL 2.0.5
  

• did you test a fresh compile from the source tree?

  pgloader version "3.6.49e5877"
  compiled with SBCL 2.0.5
  

• did you search for other similar issues?

• how can I reproduce the bug?

Running the same exact migration repeatedly fails every so often, sometimes every other run but usually less often. How long you wait between runs does not seem to matter.

The database I am using can be fetched from http://sponsor.ajay.app/database.db (85MB, updates with new data constantly but that should not matter, I am not updating the file between test runs). I did not test if another sqlite database does this.

This is my current load file, but running in a single command line with no load file has the same issue and it's possible it happens more often with that.

load database
     from '/srv/sponsorblock/database.db'
     into pgsql://sponsorblock:<REMOVED>@localhost/sponsorblock

  including only table names like 'usernames','vipusers','sponsortimes'

  with include no drop, truncate, create no tables, create no indexes,
       workers = 4, concurrency = 1

   set work_mem to '32MB', maintenance_work_mem to '512 MB';

• pgloader output you obtain

Example of repeated runs where it eventually just fails

Debug successful run

Debug failing run

[Lartza]

I've now been running pgloader -D /srv/sponsorblock/tmp /srv/sponsorblock/sponsorblock_tmp.load for hours without failure after trying to make the migration redundant and repeat on failure, but I don't know why it would work now suddenly

load database
     from '/srv/sponsorblock/database.db'
     into pgsql://sponsorblock:<REMOVED>@localhost/sponsorblock

 including only table names like 'usernames', 'vipusers', 'sponsortimes'

 with include drop, create tables, create indexes, reset sequences

  set work_mem to '128MB', maintenance_work_mem to '1024 MB', search_path to 'sponsorblock'

  before load do
  $$ create schema if not exists sponsorblock; $$;

(I'm dropping public and renaming the migrated schema in the bash script that calls pgloader, once pgloader succeeds but I can't get it to break for now with these changes)

UPDATE: It did fail with this load file too just now

[dimitri]

Hi @Lartza ; I think I got the gist of the problem thanks to your bug report and some research. I have implemented a retry loop for the "pragma encoding;" query for the case when we have a concurrent access problem with SQLite, manifesting as this BUSY error message.

Please retry with a fresh checkout, building from sources, if you can.

[Lartza]

Tried my best to break it and I'm inclined to say the fix worked. Thank you