Ecto: disconnected: ** (DBConnection.ConnectionError) client #PID<0.368.0> timed out because it checked out the connection for longer than 15000ms

[madshargreave]

Environment

Elixir: 1.2.1
PostgreSQL 9.4
Ecto: 2.0.0-rc.6
OS: OSX

I have some long running queries that aggregate data into a separate table for certain specific analytics

However I am getting hit by

disconnected: ** (DBConnection.ConnectionError) client #PID<0.368.0> timed out because it checked out the connection for longer than 15000ms

Is there a way to lift this restriction?

[josevalim]

Yes, increase the timeout value. It can be done in your repo configuration
or when calling the repository. There are two configurations: :timeout (likely the one you want) and
:pool_timeout.

[madshargreave]

Is it also possible when executing raw SQL through the Ecto.Adapters.SQL.query function?

[josevalim]

@madshargreave yes, in the fourth argument. :)

[thiagoinformal]

Hey, I'm having problem with Elix syntax, is that right?

Ecto.Migrator.run( (...) , :up, all: true, log: :debug, timeout: 60000)

I'm having timout in that migration. =/

[josevalim]

I don't think the timeout option is supported in the migrator, we default everything to infinity in there. Can you please show which error message are you getting alongside the stacktrace?

[thiagoqd]

@josevalim I see

That's the error message:

00:38:52.558 [error] Postgrex.Protocol (#PID<0.254.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.70.0> timed out because it checked out the connection for longer than 15000ms

00:38:52.620 [debug] == Running PB.Repo.Migrations.AddCreatedbyProject.change/0 forward

00:38:52.621 [debug] alter table projects
** (DBConnection.ConnectionError) tcp send: closed
    (ecto) lib/ecto/adapters/postgres/connection.ex:97: Ecto.Adapters.Postgres.Connection.execute/4
    (ecto) lib/ecto/adapters/sql.ex:235: Ecto.Adapters.SQL.sql_call/6
    (ecto) lib/ecto/adapters/sql.ex:185: Ecto.Adapters.SQL.query!/5
    (ecto) lib/ecto/adapters/postgres.ex:71: Ecto.Adapters.Postgres.execute_ddl/3
    (ecto) lib/ecto/migration/runner.ex:101: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/migration/runner.ex:99: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:181: :timer.tc/2

Before the error, the log shows it running a bunch of queries normally.

[thiagoqd]

and the system is based on Ecto 2.0.5 (and phoenix_ecto ~> 3.0), its was set to infinity in wich version? 2.1?

[josevalim]

Oh, so this is another error. You have two options:

1. Use Mix.Task.run "ecto.migrate" or whatever Phoenix generates as it will make sure to not use the sandbox pool
2. Increase the :ownership_timeout in your repository configuration rather than the timeout as this value is coming from the sandbox pool

[thiagoqd]

@josevalim

I've tryed alter the repository configuration in the following files whith the ownership_timeout, but i get the same error (timed out because it checked out the connection for longer than 15000ms).

I've done it right? I'm new at the Elixir syntaxe:

dev.exs

config :pb, PB.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "xxx",
  password: "xxx",
  database: "pb_dev",
  pool_size: 10, 
  ownership_timeout: 60_000

test.exs

config :pb, PB.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: System.get_env("DATABASE_POSTGRESQL_USERNAME") || "xxx",
  password: System.get_env("DATABASE_POSTGRESQL_PASSWORD") || "xxx",
  database: "pb_test",
  ownership_timeout: 60_000,
  pool: Ecto.Adapters.SQL.Sandbox

prod.exs

config :pb, PB.Repo,
  adapter: Ecto.Adapters.Postgres,
  url: System.get_env("DATABASE_URL"),
  ownership_timeout: 60_000,
  pool_size: System.get_env("DB_POOL") || 20 

and finally config.exs

config :pb, PB.Repo,
  ownership_timeout: 60_000

[thiagoqd]

guess, I solve this.

Adding the 'timeout' and 'pool_timeout' in those files get it done .

  timeout: 60_000,
  pool_timeout: 60_000

There was a commit [] that was taking 18ms, rather than 15ms. I'm testing everything now.
Thanks @josevalim

[luispeerez]

ownership_timeout: worked for me :+1:

[Olshansk]

While increasing the timeout will work, I think this is an extremely important thread to read if you're encountering this issue.