Database locked error on create+migrate

[pikeas]

# /lib/project/repo.ex
defmodule Project.Repo do
  use Ecto.Repo,
    otp_app: :project,
    adapter: Ecto.Adapters.SQLite3
end

# /priv/repo/migrations/1234_create_items.exs
defmodule Project.Repo.Migrations.CreateItems do
  use Ecto.Migration

  def change do
    create table(:items) do
    end
  end
end

# config/runtime.exs
config :project, Project.Repo, database: "tmp/project.db"

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
The database for Project.Repo has been dropped
The database for Project.Repo has been created

10:50:36.792 [info] == Running 1234 Project.Repo.Migrations.CreateItems.change/0 forward
10:50:36.795 [info] create table items
10:50:36.795 [info] == Migrated 1234 in 0.0s
✅ 

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
✅ [success again]

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
The database for Project.Repo has been dropped
The database for Project.Repo has been created

❌ 10:50:39.983 [error] Exqlite.Connection (#PID<0.176.0>) failed to connect: ** (Exqlite.Error) database is locked
10:50:40.006 [info] == Running 1234 Project.Repo.Migrations.CreateItems.change/0 forward
10:50:40.008 [info] create table items
10:50:40.009 [info] == Migrated 1234 in 0.0s

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
❌ [error again]

$  mix ecto.drop; mix ecto.create; mix ecto.migrate
✅ [success again]

This is running locally (no virtualization, no remote filesystem) on MacOS. Why does Exqlite sometimes error when drop/create/migrate is run in quick succession? It looks like ecto.create may be holding a write lock despite appearing to exit?

[warmwaffles]

It's a "race" condition I have yet to resolve. This has to do with how the connection pooling works. The process that held the sqlite connection open is shutting down at the same time a new one is spinning up and I have no way to reliably prevent that without dropping DBConnection from this.

Is this a blocker for you or just an annoyance?

[pikeas]

Thanks for the quick response!

Is this a blocker for you or just an annoyance?

Annoyance and makes for a poor new user experience - I spent a lot of time triple-checking my config and narrowing down the root cause.

Since this is a known issue, perhaps the error could be caught and a hint logged to console before re-raising?

[pikeas]

Another idea is to add a single sleep+retry to the migration function, with an opt-in repo config flag.

[warmwaffles]

That error is the process crashing. You would have to build that handler in your application.

Curious, what is your pooling configuration for dev/test?

[pikeas]

Curious, what is your pooling configuration for dev/test?

This is a fresh Elixir app, all packages are up-to-date and all config is 100% defaults. The behavior should be reproducible with the code snippets in the first post.

[warmwaffles]

Set pool_size: 1 in your dev.exs and give it a shot. It should help with the thrashing.

[warmwaffles]

I'm going to leave this issue open until we complete the removal of DBConnection from this library and lift it to ecto_sqlite3 OR do away with it entirely and implement our own pooling strategy since sqlite is not like mysql or postgres with regards to TCP connections.

[pikeas]

Set pool_size: 1 in your dev.exs and give it a shot. It should help with the thrashing.

Good idea. On ~50 runs in quick succession, this reduces the failure rate from ~30% to ~15%.

For context, I ran into this while iterating on a new schema:

• Add field to Thing.ex, add column to 123_create_things.exs migration.
• Drop/create/migrate
• Make another quick change
• Drop/create/migrate again <10 seconds later
• Crash