[Triage] Could not create schema migrations table.

user-566 commented 1 month ago

Describe the bug Hello, I'm a Docker newbie trying to install Pinchflat on a Windows server running Docker Desktop (apologies in advance). Here's what my compose.yaml file looks like:

volumes:
  SMB_Config:
    driver_opts:
      type: cifs
      o: "username=invite,password=12345,vers=3.0"
      device: '//NAS/Media/pinchflat/config'
  SMB_Downloads:
    driver_opts:
      type: cifs
      o: "username=invite,password=12345,vers=3.0"
      device: '//NAS/Media/pinchflat/downloads'

version: '3'
services:
  Pinchflat:
    image: ghcr.io/kieraneglin/pinchflat:latest
    container_name: Pinchflat
    restart: unless-stopped
    environment:
      - TZ=America/New_York
      - JOURNAL_MODE=delete
    ports:
      - '8945:8945'
    volumes:
      - SMB_Config:/config
      - SMB_Downloads:/downloads

As you can see, since the /config and /download folders are stored on a network share, I set the JOURNAL_MODE environment variable to delete as instructed in the readme.

And here is the output I get when I run docker compose up:

PS N:\Programs\Pinchflat> docker compose up
time="2024-08-23T19:55:47-04:00" level=warning msg="N:\\Programs\\Pinchflat\\compose.yaml: the attribute `version` is obsolete, it will be ignor
ed, please remove it to avoid potential confusion"
[+] Running 4/4
 ✔ Network pinchflat_default         Created                                                                                                            0.3s 
 ✔ Volume "pinchflat_SMB_Config"     Created                                                                                                            0.1s 
 ✔ Volume "pinchflat_SMB_Downloads"  Created                                                                                                            0.3s 
 ✔ Container Pinchflat               Created                                                                                                           21.3s 
Attaching to Pinchflat
Pinchflat  | 2024-08-23 19:56:32.176 [info] | Checking permissions for /config
Pinchflat  | 2024-08-23 19:56:32.243 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:32.243 [info] | Checking permissions for /downloads
Pinchflat  | 2024-08-23 19:56:32.297 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:32.297 [info] | Checking permissions for /tmp/pinchflat/data
Pinchflat  | 2024-08-23 19:56:32.298 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:32.298 [info] | Checking permissions for /config/extras
Pinchflat  | 2024-08-23 19:56:32.372 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:32.372 [info] | Checking permissions for /config/metadata
Pinchflat  | 2024-08-23 19:56:32.453 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:32.453 [info] | Checking permissions for /etc/elixir_tzdata_data
Pinchflat  | 2024-08-23 19:56:32.454 [info] | Permissions OK
Pinchflat  | 2024-08-23 19:56:33.237 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:35.287 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:37.322 [error] | Could not create schema migrations table. This error usually happens due to the following:
Pinchflat  | 
Pinchflat  |   * The database does not exist
Pinchflat  |   * The "schema_migrations" table, which Ecto uses for managing
Pinchflat  |     migrations, was defined by another library
Pinchflat  |   * There is a deadlock while migrating (such as using concurrent
Pinchflat  |     indexes with a migration_lock)
Pinchflat  | 
Pinchflat  | To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.
Pinchflat  | 
Pinchflat  | To address the second, you can run "mix ecto.drop" followed by
Pinchflat  | "mix ecto.create", both for the desired MIX_ENV. Alternatively you may
Pinchflat  | configure Ecto to use another table and/or repository for managing
Pinchflat  | migrations:
Pinchflat  | 
Pinchflat  |     config :pinchflat, Pinchflat.Repo,
Pinchflat  |       migration_source: "some_other_table_for_schema_migrations",
Pinchflat  |       migration_repo: AnotherRepoForSchemaMigrations
Pinchflat  | 
Pinchflat  | The full error report is shown below.
Pinchflat  | 
Pinchflat  | ** (Exqlite.Error) Database busy
Pinchflat  | CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" INTEGER PRIMARY KEY, "inserted_at" TEXT)
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
Pinchflat  |     (elixir 1.17.0) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:755: Ecto.Migrator.verbose_schema_migration/3
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:563: Ecto.Migrator.lock_for_migrations/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:432: Ecto.Migrator.run/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
Pinchflat  |     nofile:1: (file)
Pinchflat  | 2024-08-23 19:56:39.920 [info] | No tzdata release files found in custom data dir. Copying release file from tzdata priv dir.
Pinchflat  | 2024-08-23 19:56:40.107 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:40.107 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:40.107 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:40.107 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:40.108 [debug] | Exqlite: loading extension `/app/lib/pinchflat-2024.8.14/priv/repo/extensions/sqlean-linux-x86/sqlean`        
Pinchflat  | 2024-08-23 19:56:42.134 [debug] | QUERY ERROR source="oban_jobs" db=0.0ms queue=2041.7ms idle=0.0ms
Pinchflat  | UPDATE "oban_jobs" AS o0 SET "state" = ? WHERE (o0."state" = 'executing') ["retryable"]
Pinchflat  | 2024-08-23 19:56:42.139 [notice] | Application pinchflat exited: Pinchflat.Application.start(:normal, []) returned an error: shutdown: failed to
 start child: Pinchflat.Boot.PreJobStartupTasks
Pinchflat  |     ** (EXIT) an exception was raised:
Pinchflat  |         ** (Exqlite.Error) no such table: oban_jobs
Pinchflat  | UPDATE "oban_jobs" AS o0 SET "state" = ? WHERE (o0."state" = 'executing')
Pinchflat  |             (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
Pinchflat  |             (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
Pinchflat  |             (pinchflat 2024.8.14) lib/pinchflat/boot/pre_job_startup_tasks.ex:58: Pinchflat.Boot.PreJobStartupTasks.reset_executing_jobs/0      
Pinchflat  |             (pinchflat 2024.8.14) lib/pinchflat/boot/pre_job_startup_tasks.ex:35: Pinchflat.Boot.PreJobStartupTasks.init/1
Pinchflat  |             (stdlib 5.2.3) gen_server.erl:980: :gen_server.init_it/2
Pinchflat  |             (stdlib 5.2.3) gen_server.erl:935: :gen_server.init_it/6
Pinchflat  |             (stdlib 5.2.3) proc_lib.erl:241: :proc_lib.init_p_do_apply/3
Pinchflat  | Runtime terminating during boot (terminating)
Pinchflat  | 
Pinchflat  | Crash dump is being written to: erl_crash.dump...done
Pinchflat  | [os_mon] cpu supervisor port (cpu_sup): Erlang has closed
Pinchflat  | [os_mon] memory supervisor port (memsup): Erlang has closed
Pinchflat exited with code 0

The user invite has full access to the pinchflat folder and all sub-folders:

If I remove the JOURNAL_MODE environment variable (or set it to the default wal value) from compose.yaml, the output is slightly different:

PS N:\Programs\Pinchflat> docker compose up
time="2024-08-23T20:04:24-04:00" level=warning msg="N:\\Programs\\Pinchflat\\compose.yaml: the attribute `version` is obsolete, it will be ignor
ed, please remove it to avoid potential confusion"
[+] Running 4/4
 ✔ Network pinchflat_default         Created                                                                                                            0.4s 
 ✔ Volume "pinchflat_SMB_Downloads"  Created                                                                                                            0.1s 
 ✔ Volume "pinchflat_SMB_Config"     Created                                                                                                            0.1s 
 ✔ Container Pinchflat               Created                                                                                                           21.5s 
Attaching to Pinchflat
Pinchflat  | 2024-08-23 20:05:08.823 [info] | Checking permissions for /config
Pinchflat  | 2024-08-23 20:05:08.867 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:08.867 [info] | Checking permissions for /downloads
Pinchflat  | 2024-08-23 20:05:08.913 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:08.913 [info] | Checking permissions for /tmp/pinchflat/data
Pinchflat  | 2024-08-23 20:05:08.914 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:08.914 [info] | Checking permissions for /config/extras
Pinchflat  | 2024-08-23 20:05:08.954 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:08.954 [info] | Checking permissions for /config/metadata
Pinchflat  | 2024-08-23 20:05:09.010 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:09.010 [info] | Checking permissions for /etc/elixir_tzdata_data
Pinchflat  | 2024-08-23 20:05:09.011 [info] | Permissions OK
Pinchflat  | 2024-08-23 20:05:11.759 [error] | Exqlite.Connection (#PID<0.132.0>) failed to connect: ** (Exqlite.Error) database is locked
Pinchflat  | 2024-08-23 20:05:12.717 [error] | Could not create schema migrations table. This error usually happens due to the following:
Pinchflat  | 
Pinchflat  |   * The database does not exist
Pinchflat  |   * The "schema_migrations" table, which Ecto uses for managing
Pinchflat  |     migrations, was defined by another library
Pinchflat  |   * There is a deadlock while migrating (such as using concurrent
Pinchflat  |     indexes with a migration_lock)
Pinchflat  | 
Pinchflat  | To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.
Pinchflat  | 
Pinchflat  | To address the second, you can run "mix ecto.drop" followed by
Pinchflat  | "mix ecto.create", both for the desired MIX_ENV. Alternatively you may
Pinchflat  | configure Ecto to use another table and/or repository for managing
Pinchflat  | migrations:
Pinchflat  | 
Pinchflat  |     config :pinchflat, Pinchflat.Repo,
Pinchflat  |       migration_source: "some_other_table_for_schema_migrations",
Pinchflat  |       migration_repo: AnotherRepoForSchemaMigrations
Pinchflat  | 
Pinchflat  | The full error report is shown below.
Pinchflat  | 
Pinchflat  | ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2980ms. This means requests are coming in an
d your connection pool cannot serve them fast enough. You can address this by:
Pinchflat  | 
Pinchflat  |   1. Ensuring your database is available and that you can connect to it
Pinchflat  |   2. Tracking down slow queries and making sure they are running fast enough
Pinchflat  |   3. Increasing the pool_size (although this increases resource consumption)
Pinchflat  |   4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
Pinchflat  | 
Pinchflat  | See DBConnection.start_link/2 for more information
Pinchflat  | 
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
Pinchflat  |     (elixir 1.17.0) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:755: Ecto.Migrator.verbose_schema_migration/3
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:563: Ecto.Migrator.lock_for_migrations/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:432: Ecto.Migrator.run/4
Pinchflat  |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
Pinchflat  |     nofile:1: (file)
Pinchflat  | 2024-08-23 20:05:15.261 [info] | No tzdata release files found in custom data dir. Copying release file from tzdata priv dir.
Pinchflat  | 2024-08-23 20:05:17.459 [error] | Exqlite.Connection (#PID<0.2251.0>) failed to connect: ** (Exqlite.Error) database is locked
Pinchflat  | 2024-08-23 20:05:17.520 [error] | Failure while translating Erlang's logger event
Pinchflat  | ** (MatchError) no match of right hand side value: %{label: {:gen_statem, :terminate}, name: #PID<0.2254.0>, reason: {:error, %RuntimeError{mess
age: "connect raised MatchError exception. The exception details are hidden, as they may contain sensitive data such as database credentials. You may set :sh
ow_sensitive_data_on_connection_error to true when starting your connection if you wish to see all of the details"}, [{Exqlite.Connection, :get_pragma, 2, [f
ile: ~c"lib/exqlite/connection.ex", line: 383]}, {Exqlite.Connection, :maybe_set_pragma, 3, [file: ~c"lib/exqlite/connection.ex", line: 392]}, {Exqlite.Conne
ction, :do_connect, 2, [file: ~c"lib/exqlite/connection.ex", line: 531]}, {DBConnection.Connection, :handle_event, 4, [file: ~c"lib/db_connection/connection.
ex", line: 92]}, {:gen_statem, :loop_state_callback, 11, [file: ~c"gen_statem.erl", line: 1397]}, {:proc_lib, :init_p_do_apply, 3, [file: ~c"proc_lib.erl", l
ine: 241]}]}, log: [], state: Exqlite.Connection, queue: [internal: {:connect, :init}], modules: [DBConnection.Connection], client_info: :undefined, callback
_mode: :handle_event_function, postponed: [], timeouts: {0, []}, state_enter: false}
Pinchflat  |     (logger 1.17.0) lib/logger/translator.ex:306: Logger.Translator.report_gen_statem_terminate/2
Pinchflat  |     (logger 1.17.0) lib/logger/utils.ex:47: Logger.Utils.translate/5
Pinchflat  |     (logger 1.17.0) lib/logger/utils.ex:25: Logger.Utils.translator/2
Pinchflat  |     (kernel 9.2.4) logger_backend.erl:101: :logger_backend.do_apply_filters/4
Pinchflat  |     (kernel 9.2.4) logger_backend.erl:86: :logger_backend.apply_filters/4
Pinchflat  |     (kernel 9.2.4) logger_backend.erl:32: :logger_backend.log_allowed/3
Pinchflat  |     (stdlib 5.2.3) gen_statem.erl:2591: :gen_statem.terminate/7
Pinchflat  |     (stdlib 5.2.3) proc_lib.erl:241: :proc_lib.init_p_do_apply/3
Pinchflat  | 
Pinchflat  | 2024-08-23 20:05:18.432 [debug] | Tzdata polling for update.
Pinchflat  | 2024-08-23 20:05:18.441 [debug] | QUERY ERROR source="oban_jobs" queue=2998.0ms
Pinchflat  | UPDATE "oban_jobs" AS o0 SET "state" = ? WHERE (o0."state" = 'executing') ["retryable"]
Pinchflat  | 2024-08-23 20:05:18.446 [notice] | Application pinchflat exited: Pinchflat.Application.start(:normal, []) returned an error: shutdown: failed to
 start child: Pinchflat.Boot.PreJobStartupTasks
Pinchflat  |     ** (EXIT) an exception was raised:
Pinchflat  |         ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2998ms. This means requests are comi
ng in and your connection pool cannot serve them fast enough. You can address this by:
Pinchflat  | 
Pinchflat  |   1. Ensuring your database is available and that you can connect to it
Pinchflat  |   2. Tracking down slow queries and making sure they are running fast enough
Pinchflat  |   3. Increasing the pool_size (although this increases resource consumption)
Pinchflat  |   4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
Pinchflat  | 
Pinchflat  | See DBConnection.start_link/2 for more information
Pinchflat  | 
Pinchflat  |             (ecto_sqlite3 0.15.1) lib/ecto/adapters/sqlite3/connection.ex:47: Ecto.Adapters.SQLite3.Connection.prepare_execute/5
Pinchflat  |             (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:960: Ecto.Adapters.SQL.execute!/5
Pinchflat  |             (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
Pinchflat  |             (pinchflat 2024.8.14) lib/pinchflat/boot/pre_job_startup_tasks.ex:58: Pinchflat.Boot.PreJobStartupTasks.reset_executing_jobs/0      
Pinchflat  |             (pinchflat 2024.8.14) lib/pinchflat/boot/pre_job_startup_tasks.ex:35: Pinchflat.Boot.PreJobStartupTasks.init/1
Pinchflat  |             (stdlib 5.2.3) gen_server.erl:980: :gen_server.init_it/2
Pinchflat  |             (stdlib 5.2.3) gen_server.erl:935: :gen_server.init_it/6
Pinchflat  |             (stdlib 5.2.3) proc_lib.erl:241: :proc_lib.init_p_do_apply/3
Pinchflat  | Runtime terminating during boot (terminating)
Pinchflat  | 
Pinchflat  | Crash dump is being written to: erl_crash.dump...done
Pinchflat  | [os_mon] cpu supervisor port (cpu_sup): Erlang has closed
Pinchflat  | [os_mon] memory supervisor port (memsup): Erlang has closed
Pinchflat exited with code 0

Any help would be greatly appreciated, as I don't really know what to try at the moment.

To Reproduce Installing Pinchflat with Docker Desktop running on a Windows server using the compose.yaml file provided.

Expected behavior Container installs and runs correctly.

Screenshots Can provide if needed

Diagnostic info N/A

Additional context N/A

user-566 commented 1 month ago

Issue was solved by moving the config folder to system running Docker. I still believe it should be possible to save the configuration on a network drive (especially since the readme seems to indicate so), but prehaps a feature request would be better suited for this.

jaycedk commented 4 weeks ago

It should be possible to have the DB on a network share. But I believe SQlite is used as a DB in this project. And SQlite is not suited for network shares. It can work, if you do not care about possible corruptions in the DB, resulting in you need to start over.

https://sqlite.org/useovernet.html https://runebook.dev/en/articles/sqlite/useovernet

I would suggest @kieraneglin to put a warning in big fat red about using config on a network share.

kieraneglin commented 4 weeks ago

Hey all, thanks for chiming in! Sorry for the delay - I've been trying to do more outdoors stuff and less development on the weekends (:

As mentioned, The sqlite team doesn't recommend storing your database on network storage. The readme already says this in multiple locations, including the blurb on write-ahead log's environment variable so I don't see any need to change that.

Even though it's not recommended, it is still technically possible if you change the environment variable setting for the write-ahead log as you have done. That's actually not the issue here, the original issue is entirely permissions and roles-based. Normally in those cases I try to help out where I can, but I know genuinely nothing about Windows Server so unfortunately I have no advice. it sounds like ultimately you found a solution which is great!

kieraneglin / pinchflat

[Triage] Could not create schema migrations table. #366