search

eikek / docspell

Assist in organizing your piles of documents, resulting from scanners, e-mails and other sources with miminal effort.
https://docspell.org
GNU Affero General Public License v3.0
1.62k stars 124 forks source link

Docspell Instance Breaks when Restarted #521

Closed typkrft closed 3 years ago

typkrft commented 3 years ago

Everytime, I restart my containers to change the .conf or for any reason my working instance of docspell breaks. I am able to occasionally resolve it by connecting to the Postgres container and dropping the custom_field custom_field_value tables but this doesn't feel right. Occasionally this doesn't work and I have to destroy the containers and restore from a Duplicati backup.

This happens 100% of the time whether it's restarted from the command line or portainer. I have tried to stop them gracefully and then start them to no avail.

Any help would be very appreciated.

Rest-Server:

***>  ______                          _ _
***>  |  _  \                        | | |
***>  | | | |___   ___ ___ _ __   ___| | |
***>  | | | / _ \ / __/ __| '_ \ / _ \ | |
***>  | |/ / (_) | (__\__ \ |_) |  __/ | |
***>  |___/ \___/ \___|___/ .__/ \___|_|_|
***>                      | |
***>                      |_| v0.17.1 (#14428c0e)
***>  << REST Server >>
***>  Id:       rest1
***>  Base-Url: http://192.168.1.129:9023
***>  Database: jdbc:postgresql://docspell-db:5432/docspellDB
***>  Fts:      http://docspell-solr:8983/solr/docspell
***>  Config:   /opt/docspell.conf
***>   
[ioapp-compute-0] INFO  d.s.m.FlywayMigrate - Running db migrations... 
[ioapp-compute-0] INFO  d.s.m.FlywayMigrate - Using migration locations: List(classpath:db/migration/postgresql) 
[ioapp-compute-0] INFO  o.f.c.i.l.VersionPrinter - Flyway Community Edition 7.3.1 by Redgate 
[ioapp-compute-0] INFO  o.f.c.i.d.b.DatabaseType - Database: jdbc:postgresql://docspell-db:5432/docspellDB (PostgreSQL 11.7) 
[ioapp-compute-0] INFO  o.f.c.i.s.JdbcTableSchemaHistory - Repair of failed migration in Schema History table "public"."flyway_schema_history" not necessary. No failed migration detected. 
[ioapp-compute-0] INFO  o.f.c.i.c.DbRepair - Successfully repaired schema history table "public"."flyway_schema_history" (execution time 00:00.071s). 
[ioapp-compute-0] INFO  o.f.c.i.l.VersionPrinter - Flyway Community Edition 7.3.1 by Redgate 
[ioapp-compute-0] INFO  o.f.c.i.c.DbValidate - Successfully validated 31 migrations (execution time 00:00.045s) 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Current version of schema "public": 1.12.0 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.13.0 - custom fields" 
[ioapp-compute-0] ERROR o.f.c.i.c.DbMigrate - Migration of schema "public" to version "1.13.0 - custom fields" failed! Changes successfully rolled back. 
org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1.13.0__custom_fields.sql failed
-------------------------------------------
SQL State  : 42P07
Error Code : 0
Message    : ERROR: relation "custom_field" already exists
Location   : db/migration/postgresql/V1.13.0__custom_fields.sql (/file:/opt/docspell-restserver/lib/com.github.eikek.docspell-store-0.17.1.jar!/db/migration/postgresql/V1.13.0__custom_fields.sql)
Line       : 1
Statement  : CREATE TABLE "custom_field" (
  "id" varchar(254) not null primary key,
  "name" varchar(254) not null,
  "label" varchar(254),
  "cid" varchar(254) not null,
  "ftype" varchar(100) not null,
  "created" timestamp not null,
  foreign key ("cid") references "collective"("cid"),
  unique ("cid", "name")
)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:432)
    at org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:56)
    at org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:335)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:332)
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:297)
    at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:56)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:201)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:198)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:73)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:140)
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:198)
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:159)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:215)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:164)
    at org.flywaydb.core.Flyway.execute(Flyway.java:538)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:164)
    at docspell.store.migrate.FlywayMigrate$.$anonfun$run$1(FlywayMigrate.scala:36)
    at map @ docspell.store.impl.StoreImpl.migrate(StoreImpl.scala:25)
    at liftF @ doobie.hikari.HikariTransactor$.newHikariTransactor(HikariTransactor.scala:58)
    at map @ fs2.internal.CompileScope.interruptibleEval(CompileScope.scala:413)
    at flatMap @ fs2.internal.FreeC$.go$1(Algebra.scala:503)
    at flatMap @ fs2.internal.FreeC$.$anonfun$compile$7(Algebra.scala:463)
    at flatMap @ fs2.internal.FreeC$.go$1(Algebra.scala:460)
    at flatMap @ fs2.internal.FreeC$.$anonfun$compile$7(Algebra.scala:463)
    at flatMap @ fs2.internal.FreeC$.go$1(Algebra.scala:460)
    at flatMap @ fs2.internal.FreeC$.interruptGuard$1(Algebra.scala:436)
    at flatMap @ fs2.internal.FreeC$.interruptGuard$1(Algebra.scala:436)
    at flatMap @ fs2.internal.CompileScope.$anonfun$acquireResource$4(CompileScope.scala:185)
    at flatten @ fs2.internal.ScopedResource$$anon$1.acquired(ScopedResource.scala:139)
    at flatMap @ fs2.internal.CompileScope.$anonfun$acquireResource$1(CompileScope.scala:183)
    at flatMap @ fs2.internal.CompileScope.acquireResource(CompileScope.scala:180)
    at flatMap @ fs2.internal.FreeC$.$anonfun$compile$12(Algebra.scala:511)
    at flatMap @ fs2.internal.FreeC$.interruptGuard$1(Algebra.scala:436)
Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration V1.13.0__custom_fields.sql failed
-------------------------------------------
SQL State  : 42P07
Error Code : 0
Message    : ERROR: relation "custom_field" already exists
Location   : db/migration/postgresql/V1.13.0__custom_fields.sql (/file:/opt/docspell-restserver/lib/com.github.eikek.docspell-store-0.17.1.jar!/db/migration/postgresql/V1.13.0__custom_fields.sql)
Line       : 1
Statement  : CREATE TABLE "custom_field" (
  "id" varchar(254) not null primary key,
  "name" varchar(254) not null,
  "label" varchar(254),
  "cid" varchar(254) not null,
  "ftype" varchar(100) not null,
  "created" timestamp not null,
  foreign key ("cid") references "collective"("cid"),
  unique ("cid", "name")
)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:273)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:218)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:78)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:67)
    at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:28)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:66)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:428)
    at org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:56)
    at org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:335)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:332)
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:297)
    at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:56)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:201)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:198)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:73)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:140)
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:198)
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:159)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:215)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:164)
    at org.flywaydb.core.Flyway.execute(Flyway.java:538)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:164)
    at docspell.store.migrate.FlywayMigrate$.$anonfun$run$1(FlywayMigrate.scala:36)
    at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:104)
    at cats.effect.internals.IORunLoop$.restartCancelable(IORunLoop.scala:51)
    at cats.effect.internals.IOBracket$BracketStart.run(IOBracket.scala:100)
    at cats.effect.internals.Trampoline.cats$effect$internals$Trampoline$$immediateLoop(Trampoline.scala:67)
    at cats.effect.internals.Trampoline.startLoop(Trampoline.scala:35)
    at cats.effect.internals.TrampolineEC$JVMTrampoline.super$startLoop(TrampolineEC.scala:90)
    at cats.effect.internals.TrampolineEC$JVMTrampoline.$anonfun$startLoop$1(TrampolineEC.scala:90)
    at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)
    at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:94)
    at cats.effect.internals.TrampolineEC$JVMTrampoline.startLoop(TrampolineEC.scala:90)
    at cats.effect.internals.Trampoline.execute(Trampoline.scala:43)
    at cats.effect.internals.TrampolineEC.execute(TrampolineEC.scala:42)
    at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:80)
    at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:58)
    at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:183)
    at cats.effect.internals.IORunLoop$.restart(IORunLoop.scala:41)
    at cats.effect.internals.IOBracket$.$anonfun$apply$1(IOBracket.scala:48)
    at cats.effect.internals.IOBracket$.$anonfun$apply$1$adapted(IOBracket.scala:34)
    at cats.effect.internals.IOAsync$.$anonfun$apply$1(IOAsync.scala:37)
    at cats.effect.internals.IOAsync$.$anonfun$apply$1$adapted(IOAsync.scala:37)
    at cats.effect.internals.IORunLoop$RestartCallback.start(IORunLoop.scala:447)
    at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:156)
    at cats.effect.internals.IORunLoop$RestartCallback.signal(IORunLoop.scala:463)
    at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:484)
    at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:422)
    at cats.effect.internals.IOShift$Tick.run(IOShift.scala:36)
    at cats.effect.internals.PoolUtils$$anon$2$$anon$3.run(PoolUtils.scala:52)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "custom_field" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:241)
    at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:111)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:206)
    ... 54 more

DB Logs:

2020-12-26 11:09:53.061 EST [84] ERROR:  relation "custom_field" already exists
2020-12-26 11:09:53.061 EST [84] STATEMENT:  CREATE TABLE "custom_field" (
      "id" varchar(254) not null primary key,
      "name" varchar(254) not null,
      "label" varchar(254),
      "cid" varchar(254) not null,
      "ftype" varchar(100) not null,
      "created" timestamp not null,
      foreign key ("cid") references "collective"("cid"),
      unique ("cid", "name")
    )

Docker Compose

  docspell-restserver:
    image: eikek0/docspell:restserver-LATEST
    container_name: docspell-restserver
    restart: unless-stopped
    ports:
      - "9023:7880"
    volumes:
      - ./docspell/config/docspell.conf:/opt/docspell.conf
    depends_on:
      - docspell-solr
    environment:
      - PUID=$PUID
      - PGID=$PGID
      - TZ=$TZ
      - DOCSPELL_HEADER_VALUE=SECRET
      - DOCSPELL_DB_TYPE=postgresql
      - DOCSPELL_DB_HOST=docspell-db
      - DOCSPELL_DB_PORT=5432
      - DOCSPELL_DB_NAME=docspellDB
      - DOCSPELL_DB_USER=docspellAdmin
      - DOCSPELL_DB_PASS=$DOCSPELL_DB_PASS

  docspell-joex:
    image: eikek0/docspell:joex-LATEST
    container_name: docspell-joex
    restart: unless-stopped
    ports:
      - "9027:7878"
    volumes:
      - ./docspell/config/docspell.conf:/opt/docspell.conf
    depends_on:
      - docspell-solr
    environment:
      - PUID=$PUID
      - PGID=$PGID
      - TZ=$TZ
      - DOCSPELL_HEADER_VALUE=SECRET
      - DOCSPELL_DB_TYPE=postgresql
      - DOCSPELL_DB_HOST=docspell-db
      - DOCSPELL_DB_PORT=5432
      - DOCSPELL_DB_NAME=docspellDB
      - DOCSPELL_DB_USER=docspellAdmin
      - DOCSPELL_DB_PASS=$DOCSPELL_DB_PASS

  docspell-consumedir:
    image: eikek0/docspell:consumedir-LATEST
    container_name: docspell-consumedir
    restart: unless-stopped
    environment:
      - PUID=$PUID
      - PGID=$PGID
      - TZ=$TZ
      - DOCSPELL_HEADER_VALUE=SECRET
      - DOCSPELL_DB_TYPE=postgresql
      - DOCSPELL_DB_HOST=docspell-db
      - DOCSPELL_DB_PORT=5432
      - DOCSPELL_DB_NAME=docspellDB
      - DOCSPELL_DB_USER=docspellAdmin
      - DOCSPELL_DB_PASS=$DOCSPELL_DB_PASS
    volumes:
      - /volume1/Files/Document Upload:/opt/docs
    depends_on:
      - docspell-restserver

  docspell-db:
    image: postgres:11.7
    container_name: docspell-db
    ports:
      - 11432:5432
    restart: unless-stopped
    volumes:
      - ./docspell/db:/var/lib/postgresql/data/
    environment:
      - PUID=$PUID
      - PGID=$PGID
      - TZ=$TZ
      - POSTGRES_USER=$DOCSPELL_DB_USER
      - POSTGRES_PASSWORD=$DOCSPELL_DB_PASS
      - POSTGRES_DB=$DOCSPELL_DB_NAME

  docspell-solr:
    image: solr:8
    container_name: docspell-solr
    restart: unless-stopped
    volumes:
      - docspell-solr_data:/var/solr
    command:
      - solr-precreate
      - docspell
    healthcheck:
      test: ["CMD", "curl", "f", "http://localhost:8983/solr/docspell/admin/ping"]
      interval: 1m
      timeout: 10s
      retries: 2
      start_period: 30s
eikek commented 3 years ago

hi @typkrft , im currently away from the computer, I'll look at it as soon as I'm back! I'm sorry for this, this shouldn't happen of course, there seems to be some inconsistency regarding the database migration. docspell thinks that the changeset version 1.13.0 needs to be applied. the table flyway_migration lists all changesets. maybe there is a row missing for this version? this from the top of my head, going to look more into it

typkrft commented 3 years ago

No worries, whenever you have the time. I'll manage until then. If you need anything at all that I can provide don't hesitate to reach out. Thank you for your work on this and happy holidays!

eikek commented 3 years ago

Happy holidays for you too :)

Could you send me your output of this db select:

select installed_rank,version,description,type,script,checksum,success from flyway_schema_history;

It contains all applied db migrations with their checksum.

Did you ever had a SNAPSHOT version running in the past?

typkrft commented 3 years ago 
installed_rank | version |     description     |  type  |             script              |  checksum   | success
----------------+---------+---------------------+--------+---------------------------------+-------------+---------
              1 | 1.0.0   | initial database    | SQL    | V1.0.0__initial_database.sql    |   317337288 | t
              2 | 1.1.0   | useremail           | SQL    | V1.1.0__useremail.sql           |   655930044 | t
              3 | 1.2.0   | origin source       | SQL    | V1.2.0__origin_source.sql       |   811358974 | t
              4 | 1.3.0   | periodic job        | SQL    | V1.3.0__periodic_job.sql        | -1440002901 | t
              5 | 1.4.0   | attachment archive  | SQL    | V1.4.0__attachment_archive.sql  |   857738097 | t
              6 | 1.5.0   | userimap            | SQL    | V1.5.0__userimap.sql            |   405891450 | t
              7 | 1.6.0   | integration enabled | SQL    | V1.6.0__integration_enabled.sql | -1400448292 | t
              8 | 1.7.0   | fts-migration       | SQL    | V1.7.0__fts-migration.sql       |   945262447 | t
              9 | 1.8.0   | folders             | SQL    | V1.8.0__folders.sql             |  1074998524 | t
             10 | 1.9.0   | updated column      | SQL    | V1.9.0__updated_column.sql      |  -922383879 | t
             11 | 1.9.1   | classifier          | SQL    | V1.9.1__classifier.sql          |  -920209386 | t
             12 | 1.9.3   | joblog counter      | SQL    | V1.9.3__joblog_counter.sql      |  -807943034 | t
             13 | 1.9.4   | unique equipments   | SQL    | V1.9.4__unique_equipments.sql   |   770293754 | t
             14 | 1.10.0  | attachment preview  | SQL    | V1.10.0__attachment_preview.sql |   175251272 | t
             15 | 1.11.0  | pdf pages           | SQL    | V1.11.0__pdf_pages.sql          |  -235351002 | t
             16 | 1.12.0  | upload data         | SQL    | V1.12.0__upload_data.sql        | -1136859296 | t
             17 | 1.13.0  | custom fields       | SQL    | V1.13.0__custom_fields.sql      |  1090781225 | t
             18 | 1.13.0  | custom fields       | DELETE | V1.13.0__custom_fields.sql      |  1090781225 | t
             19 | 1.13.0  | custom fields       | SQL    | V1.13.0__custom_fields.sql      |  1090781225 | t
             20 | 1.13.0  | custom fields       | DELETE | V1.13.0__custom_fields.sql      |  1090781225 | t
             21 | 1.13.0  | custom fields       | SQL    | V1.13.0__custom_fields.sql      |  1090781225 | t
             22 | 1.13.0  | custom fields       | DELETE | V1.13.0__custom_fields.sql      |  1090781225 | t
             23 | 1.13.0  | custom fields       | SQL    | V1.13.0__custom_fields.sql      |  1090781225 | t
             24 | 1.14.0  | person org          | SQL    | V1.14.0__person_org.sql         | -1121419990 | t
             25 | 1.15.0  | rememberme          | SQL    | V1.15.0__rememberme.sql         |  -456374843 | t
             26 | 1.13.0  | custom fields       | DELETE | V1.13.0__custom_fields.sql      |  1090781225 | t
             27 | 1.14.0  | person org          | DELETE | V1.14.0__person_org.sql         | -1121419990 | t
             28 | 1.15.0  | rememberme          | DELETE | V1.15.0__rememberme.sql         |  -456374843 | t
(28 rows)

I apologize I am not sure what you mean by SNAPSHOT version?

eikek commented 3 years ago

Ah, this doesn't look right. I can give you later instructions how to revert back to db version 1.12.0 and then it should be possible to move forward. Each version should only occur once. No need to apologize: i mean with SNAPSHOT the corresponding tags on dockerhub, like restserver-SNAPSHOT, instead of LATEST.

typkrft commented 3 years ago

That sounds great. No rush. I don't believe I've used anything other than latest. I do kind of have a feeling that a wild WatchTower container that I didn't properly configure initially may have caused some chaos, but I can't be certain.

eikek commented 3 years ago

Well, no worries. To fix it, shut down all docspell instances and execute this sql snippet:

-- revert 1.15.0
drop table if exists rememberme;

-- revert 1.14.0
update person set oid = null;
alter table person drop column oid;

-- revert 1.13.0
drop table if exists custom_field_value;
drop table if exists custom_field;

-- revert back schema history
delete from flyway_schema_history
where version in ('1.13.0', '1.14.0', '1.15.0');

This drops some tables and clears a field in the person table (the reference to an organization). I guess you'd need to re-assign those or use a backup etc. The snippet reverts back the migrations for 1.15,1.14 and 1.13. Then only rows up until 1.12.0 should be in your flyway_schema_history table. Then start docspell and look at the output. It should be something like this:

restserver [ioapp-compute-0] INFO  o.f.c.i.l.VersionPrinter - Flyway Community Edition 7.3.2 by Redgate
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbValidate - Successfully validated 23 migrations (execution time 00:00.012s)
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Current version of schema "public": 1.12.0
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.13.0 - custom fields"
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.14.0 - person org"
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.15.0 - rememberme"
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Successfully applied 3 migrations to schema "public" (execution time 00:00.220s)

it should then also be possible to stop/restart it at will. I've never had problems with this, but if they persist, please report back. Then there is something wrong:) When you then restart docspell it should realize that all migrations have been run already:

restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Current version of schema "public": 1.15.0
restserver [ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Schema "public" is up to date. No migration necessary.

hope this helps…

typkrft commented 3 years ago

Thanks! It did work to get it back up and running, but unfortunately when i restart them or stop and start them I get what appears to be the same issue.

The script run line by line:

docspellDB=# drop table if exists rememberme;
DROP TABLE
docspellDB=# update person set oid = null;
UPDATE 0
docspellDB=# alter table person drop column oid;
ALTER TABLE
docspellDB=# drop table if exists custom_field_value;
DROP TABLE
docspellDB=# drop table if exists custom_field;
DROP TABLE
docspellDB=# delete from flyway_schema_history
docspellDB-# where version in ('1.13.0', '1.14.0', '1.15.0');
DELETE 6

Restserver upon startup:

***>  ______                          _ _
***>  |  _  \                        | | |
***>  | | | |___   ___ ___ _ __   ___| | |
***>  | | | / _ \ / __/ __| '_ \ / _ \ | |
***>  | |/ / (_) | (__\__ \ |_) |  __/ | |
***>  |___/ \___/ \___|___/ .__/ \___|_|_|
***>                      | |
***>                      |_| v0.17.1 (#14428c0e)
***>  << REST Server >>
***>  Id:       rest1
***>  Base-Url: http://192.168.1.129:9023
***>  Database: jdbc:postgresql://docspell-db:5432/docspellDB
***>  Fts:      http://docspell-solr:8983/solr/docspell
***>  Config:   /opt/docspell.conf
***>   
[ioapp-compute-0] INFO  d.s.m.FlywayMigrate - Running db migrations... 
[ioapp-compute-0] INFO  d.s.m.FlywayMigrate - Using migration locations: List(classpath:db/migration/postgresql) 
[ioapp-compute-0] INFO  o.f.c.i.l.VersionPrinter - Flyway Community Edition 7.3.1 by Redgate 
[ioapp-compute-0] INFO  o.f.c.i.d.b.DatabaseType - Database: jdbc:postgresql://docspell-db:5432/docspellDB (PostgreSQL 11.7) 
[ioapp-compute-0] INFO  o.f.c.i.s.JdbcTableSchemaHistory - Repair of failed migration in Schema History table "public"."flyway_schema_history" not necessary. No failed migration detected. 
[ioapp-compute-0] INFO  o.f.c.i.c.DbRepair - Successfully repaired schema history table "public"."flyway_schema_history" (execution time 00:00.418s). 
[ioapp-compute-0] INFO  o.f.c.i.l.VersionPrinter - Flyway Community Edition 7.3.1 by Redgate 
[ioapp-compute-0] INFO  o.f.c.i.c.DbValidate - Successfully validated 19 migrations (execution time 00:00.214s) 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Current version of schema "public": 1.12.0 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.13.0 - custom fields" 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.14.0 - person org" 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Migrating schema "public" to version "1.15.0 - rememberme" 
[ioapp-compute-0] INFO  o.f.c.i.c.DbMigrate - Successfully applied 3 migrations to schema "public" (execution time 00:00.870s) 
[ioapp-compute-0] INFO  d.b.ops.ONode - Registering node rest1 
[docspell-dbconnect-0] INFO  c.z.h.HikariDataSource - HikariPool-1 - Starting... 
[docspell-dbconnect-0] INFO  c.z.h.HikariDataSource - HikariPool-1 - Start completed.

Everything works. Then Upon restarting all containers again I get the same logs as above.

eikek commented 3 years ago

Oh really strange! I can only guess that then the changes did not persist in the db container, is that possible? Can you try to only restart the database container after docspell created the schema (to the current version, 1.15.0) and look into the flyway_schema_history table?

typkrft commented 3 years ago

Maybe I should nuke the container and volume folder? If so is there an easy way to save the documents in there. I think I have them all elsewhere, but just in case. If not it's okay.

 installed_rank | version |     description     | type |             script              |  checksum   | installed_by  |        installed_on        | execution_time | success
----------------+---------+---------------------+------+---------------------------------+-------------+---------------+----------------------------+----------------+---------
              1 | 1.0.0   | initial database    | SQL  | V1.0.0__initial_database.sql    |   317337288 | docspellAdmin | 2020-11-15 22:01:48.609672 |           2060 | t
              2 | 1.1.0   | useremail           | SQL  | V1.1.0__useremail.sql           |   655930044 | docspellAdmin | 2020-11-15 22:01:50.714169 |            169 | t
              3 | 1.2.0   | origin source       | SQL  | V1.2.0__origin_source.sql       |   811358974 | docspellAdmin | 2020-11-15 22:01:50.90735  |            108 | t
              4 | 1.3.0   | periodic job        | SQL  | V1.3.0__periodic_job.sql        | -1440002901 | docspellAdmin | 2020-11-15 22:01:51.057195 |            138 | t
              5 | 1.4.0   | attachment archive  | SQL  | V1.4.0__attachment_archive.sql  |   857738097 | docspellAdmin | 2020-11-15 22:01:51.220713 |            116 | t
              6 | 1.5.0   | userimap            | SQL  | V1.5.0__userimap.sql            |   405891450 | docspellAdmin | 2020-11-15 22:01:51.575662 |            133 | t
              7 | 1.6.0   | integration enabled | SQL  | V1.6.0__integration_enabled.sql | -1400448292 | docspellAdmin | 2020-11-15 22:01:51.734002 |              2 | t
              8 | 1.7.0   | fts-migration       | SQL  | V1.7.0__fts-migration.sql       |   945262447 | docspellAdmin | 2020-11-15 22:01:51.843625 |            106 | t
              9 | 1.8.0   | folders             | SQL  | V1.8.0__folders.sql             |  1074998524 | docspellAdmin | 2020-11-15 22:01:51.971165 |            287 | t
             10 | 1.9.0   | updated column      | SQL  | V1.9.0__updated_column.sql      |  -922383879 | docspellAdmin | 2020-11-15 22:01:52.364216 |            428 | t
             11 | 1.9.1   | classifier          | SQL  | V1.9.1__classifier.sql          |  -920209386 | docspellAdmin | 2020-11-15 22:01:52.847494 |             98 | t
             12 | 1.9.3   | joblog counter      | SQL  | V1.9.3__joblog_counter.sql      |  -807943034 | docspellAdmin | 2020-11-15 22:01:53.021493 |            126 | t
             13 | 1.9.4   | unique equipments   | SQL  | V1.9.4__unique_equipments.sql   |   770293754 | docspellAdmin | 2020-11-15 22:01:53.204784 |             67 | t
             14 | 1.10.0  | attachment preview  | SQL  | V1.10.0__attachment_preview.sql |   175251272 | docspellAdmin | 2020-11-15 22:01:53.357833 |            166 | t
             15 | 1.11.0  | pdf pages           | SQL  | V1.11.0__pdf_pages.sql          |  -235351002 | docspellAdmin | 2020-11-15 22:01:53.577011 |              1 | t
             16 | 1.12.0  | upload data         | SQL  | V1.12.0__upload_data.sql        | -1136859296 | docspellAdmin | 2020-11-15 22:01:53.642615 |            232 | t
(16 rows)
eikek commented 3 years ago

That is really strange. For some reason the changes were not persisted. Right now it seems unlikely to me that the migrations are not applied correctly, since these are done via a quite popular and widely used library…. I'm not versed with docker, I'm sorry I'm not of big help here. I would try to find out (first without any docspell container) if changes to the database get persisted across restarts of the database container. Then add one docspell container and restart only this one and so on…. It also is strange, that the db script that reverted some things, seems to be applied persistently….

Regarding saving the documents, you could create a sql dump using pg_dump and restore it later using pg_restore. You can also download all files using the (new) export script, which is harder to get back into docspell (the files can be re-processed, but for restoring metadata there is currently nothing to help). I would do both, I think the sql dump would work, at least it can recreate the current state of the db. And with the export script you at least have all your files (as files) in one place.

typkrft commented 3 years ago

Sorry for the late follow up. The export script worked great. I removed the containers and the folders it's volumes were mounted to. I also removed the PUID and PGID environmental variables in the compose file. I recreated the containers and after two restarts I ran into the same problem. I then did the same thing but also removed the images and pulled the latest and it seems to work as it's expected too after several restarts.

One weird issue to note the script reported ~1500 documents, but I only have roughly a third of those. Maybe this is due to the broken database/instance I had.

I do get this message when I restart the containers now, which I don't recall before. I'm not sure if it's meaningful in anyway, but I thought I'd include it. 

2021-01-03 04:28:10.412 UTC [49] DETAIL:  Key (version, fts_engine)=(1, solr) already exists.
2021-01-03 04:28:10.412 UTC [49] STATEMENT:  INSERT INTO  fts_migration  ( id , version , fts_engine , description , created ) VALUES ( $1,$2,$3,$4,$5 ) 
2021-01-03 04:28:10.477 UTC [49] ERROR:  duplicate key value violates unique constraint "fts_migration_version_engine_idx"
2021-01-03 04:28:10.477 UTC [49] DETAIL:  Key (version, fts_engine)=(3, solr) already exists.
2021-01-03 04:28:10.477 UTC [49] STATEMENT:  INSERT INTO  fts_migration  ( id , version , fts_engine , description , created ) VALUES ( $1,$2,$3,$4,$5 ) 
2021-01-03 04:28:10.527 UTC [49] ERROR:  duplicate key value violates unique constraint "fts_migration_version_engine_idx"
2021-01-03 04:28:10.527 UTC [49] DETAIL:  Key (version, fts_engine)=(4, solr) already exists.
2021-01-03 04:28:10.527 UTC [49] STATEMENT:  INSERT INTO  fts_migration  ( id , version , fts_engine , description , created ) VALUES ( $1,$2,$3,$4,$5 ) 
2021-01-03 04:28:10.556 UTC [49] ERROR:  duplicate key value violates unique constraint "fts_migration_version_engine_idx"
2021-01-03 04:28:10.556 UTC [49] DETAIL:  Key (version, fts_engine)=(5, solr) already exists.
2021-01-03 04:28:10.556 UTC [49] STATEMENT:  INSERT INTO  fts_migration  ( id , version , fts_engine , description , created ) VALUES ( $1,$2,$3,$4,$5 ) 
2021-01-03 04:28:10.568 UTC [49] ERROR:  duplicate key value violates unique constraint "fts_migration_version_engine_idx"
2021-01-03 04:28:10.568 UTC [49] DETAIL:  Key (version, fts_engine)=(6, solr) already exists.
2021-01-03 04:28:10.568 UTC [49] STATEMENT:  INSERT INTO  fts_migration  ( id , version , fts_engine , description , created ) VALUES ( $1,$2,$3,$4,$5 )

Thank so much for your help throughout and your amazing work on this project. I'll go ahead and close this for the time being. I've made a donation through librepay. Happy New year!

A Side Note just in case any macOS users stumble across the export script. You'll need to install sha256sum through brew in order to use the script.

eikek commented 3 years ago

Sorry for the late follow up. The export script worked great. I removed the containers and the folders it's volumes were mounted to. I also removed the PUID and PGID environmental variables in the compose file. I recreated the containers and after two restarts I ran into the same problem. I then did the same thing but also removed the images and pulled the latest and it seems to work as it's expected too after several restarts.

Thanks a lot for the explanation! The behaviour is really strange to me, I only found this comment randomly that might deal with a similar problem, but you already had the /data part appended in your compose file…. Never worry about being late, there's time for these things when there is time :-)

One weird issue to note the script reported ~1500 documents, but I only have roughly a third of those. Maybe this is due to the broken database/instance I had.

It could be due to a broken db state. The export script takes the same data out that is visible in the webui. Were only about a third of these items exported or about ~1500? There might also be a counting bug in the export script….

I do get this message when I restart the containers now, which I don't recall before. I'm not sure if it's meaningful in anyway, but I thought I'd include it.

These messages are from postgresql right? It means that the solr schema has already been applied and so nothing needs to be done in this regard. You can ignore these. The joex log output should have lines saying that "Migration xy already applied".

Thank so much for your help throughout and your amazing work on this project. I'll go ahead and close this for the time being. I've made a donation through librepay. Happy New year!

Wow, thank you so much! You are very welcome and thank you for your interest in this project! Happy New year!