A process crash during a query wipes an in-memory database

[Gazler]

When using an in memory database, if the a process that performs a query crashes, then the database is destroyed.

This could potentially be avoided by isolating the process that performs the query from the calling process (e.g. using Task.Supervisor). If this behaviour is intentional, perhaps it should be documented in the "limitations" section so users are aware of it.

Here is a simple example to replicate this. You'll see that the second query fails as the database has been destroyed.

Mix.install([
  {:ecto_sql, "~> 3.10"},
  {:ecto_sqlite3, ">= 0.12.0"},
  {:exqlite, ">= 0.0.0"}
])

Application.put_env(:foo, Repo, database: ":memory:", pool_size: 1)

defmodule Repo do
  use Ecto.Repo,
    adapter: Ecto.Adapters.SQLite3,
    otp_app: :foo
end

defmodule Migration0 do
  use Ecto.Migration

  def change do
    create table("posts") do
      add(:title, :string)
      timestamps(type: :utc_datetime_usec)
    end
  end
end

defmodule Post do
  use Ecto.Schema

  schema "posts" do
    field(:title, :string)
    timestamps(type: :utc_datetime_usec)
  end
end

defmodule Main do
  import Ecto.Query, warn: false

  def start() do
    children = [
      Repo
    ]

    _ = Repo.__adapter__().storage_down(Repo.config())
    :ok = Repo.__adapter__().storage_up(Repo.config())

    {:ok, _} = Supervisor.start_link(children, strategy: :one_for_one)

    Ecto.Migrator.run(Repo, [{0, Migration0}], :up, all: true, log_migrations_sql: :debug)
    Repo.insert!(%Post{title: "Hello, World!"})

  end

  def query do
    from(Post) |> Repo.all()
  end

  def blowup do
    query = """
      WITH RECURSIVE r(i) AS (
      VALUES(0)
      UNION ALL
      SELECT i FROM r
      LIMIT 1000000
      )
      SELECT i FROM r WHERE i = 1;
      """
    spawn_link(fn ->
      :timer.sleep(10)
      raise "bad"
    end)

    Repo.query(query)
  end
end

Main.start()

Main.query() |> IO.inspect()

# We can trigger an error during a query
Process.flag(:trap_exit, true)
pid = spawn(&Main.blowup/0)
Process.monitor(pid)
receive do
  {:DOWN, _ref, :process, ^pid, _} -> :noop
end

Main.query() |> IO.inspect()

[warmwaffles]

This is unfortunately an intentional issue. DBConnection does connection pooling. When the connection is closed, the database is also closed by virtue of sqlite_close.

There has been discussions of dropping DBConnection all together and just keeping one process and facilitating who can and cannot write https://github.com/elixir-sqlite/exqlite/issues/192. I have not had time to really dive deeper into this yet and would like to play with it.

perhaps it should be documented in the "limitations" section so users are aware of it.

Definitely. A PR would be useful for this. I've also listed some background info in #133 about why this has a separate adapter implementation than what is offered by ecto_sql.