Timeout while running migrations

[waj]

Environment

• Elixir version (elixir -v):

# elixir -v
Erlang/OTP 19 [erts-8.3.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]

Elixir 1.4.4

• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.):
  mysql 5.7
• Ecto version (mix deps):

* ecto 2.1.4 (Hex package) (mix)
  locked at 2.1.4 (ecto) d1ba9328
  ok

• Database adapter and version (mix deps):

* mariaex 0.8.2 (Hex package) (mix)
  locked at 0.8.2 (mariaex) a9ee64a0
  ok

• Operating system:

Current behavior

While running a long running migration (ie: adding a column to a huge table) the execution often times out. For example:

defmodule App.Repo.Migrations.Foo do
  use Ecto.Migration

  def up do
    App.Repo.query!("SELECT sleep(20)")
  end
end

# mix ecto.migrate

17:37:18.558 [info]  == Running App.Repo.Migrations.Foo.up/0 forward

17:37:33.687 [debug] QUERY ERROR db=15013.1ms
SELECT sleep(20) []

17:37:33.687 [error] Mariaex.Protocol (#PID<0.187.0>) disconnected: ** (Mariaex.Error) [tcp] `recv` failed with: :timeout
** (Mariaex.Error) [tcp] `recv` failed with: :timeout
    (ecto) lib/ecto/adapters/sql.ex:195: Ecto.Adapters.SQL.query!/5
    (stdlib) timer.erl:197: :timer.tc/3
    (ecto) lib/ecto/migration/runner.ex:26: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:127: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:72: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3```
    ....

Seems like this issue was fixed in the past (#279), but the adapters code has changed dramatically since then and the :infinite timeout is not being passed to the connector anymore.

Expected behavior

During migrations, infinite timeout should be allowed for any query.

[josevalim]

We set the timeout for infinity to any migration command. If you are running queries, then you should set the timeout individually. Also please note we typically do not recommend running queries during migration. If you need to insert data or run some particular command, it is preferred to do that after migration executes in a separate task.

[waj]

@josevalim I disagree with the recommendation about not running queries. Sometimes queries are needed to fix existing data after some schema changes. But maybe this is better to discuss in the mailing list.

In fact mine was just a minimal example to trigger the error without knowing there is a difference with DDL queries. But in my case I found the error while running migrations in production server where a column was being added (using alter table(...) do ... end to a table with millions of rows.

Wouldn't be much easier to override the timeout parameter of the repository config during migrations? That way both DDL and custom queries could run without timing out.

[fishcakez]

This issue is tracked at xerions/mariaex#162