VACUUM

[asakura]

Every time when I run this command I have got the same result: it ends with timeout. Can I do anything with that? Thanks.

iex(11)> Ecto.Adapters.SQL.query(Stats.Repo, "VACUUM", [], timeout: 10 * 60 * 10000)
[error] GenServer #PID<0.607.0> terminating
** (stop) bad return value: {:error, :timeout, #Reference<0.0.7.137441>}
** (exit) exited in: GenServer.call(#PID<0.607.0>, {:query, "VACUUM", [bind: [], timeout: 6000000]}, 6000000)
    ** (EXIT) bad return value: {:error, :timeout, #Reference<0.0.7.137441>}
         (elixir) lib/gen_server.ex:544: GenServer.call/3
    (sqlite_ecto) lib/sqlite_ecto/query.ex:207: Sqlite.Ecto.Query.do_query/4
         (stdlib) timer.erl:197: :timer.tc/3
           (ecto) lib/ecto/adapters/sql.ex:246: Ecto.Adapters.SQL.query/7
           (ecto) lib/ecto/pool.ex:159: Ecto.Pool.do_run/4
           (ecto) lib/ecto/adapters/sql.ex:231: Ecto.Adapters.SQL.query/6
           (ecto) lib/ecto/adapters/sql.ex:209: Ecto.Adapters.SQL.query/5

[jazzyb]

I have a few questions while I look into it:

1. What versions of Elixir and OTP?
2. What system are you running on, e.g. uname -a?
3. Do you have a repo/gist available I can verify the bug with?
4. ~~How do other adapters handle this code, e.g. PostgreSQL or MySQL?~~
   • Nevermind this one, it looks like VACUUM does different things on different DBs.
5. How long does the query normally take when you manually run VACUUM; on your DB file?
6. How long does it actually take for the timeout to occur? I see you set a timeout of a little over 2 months. I doubt you actually had to wait that long for the error, though ;)

Thanks!

[jazzyb]

I cannot reproduce the error. I get the following return value when I run your query:

{:error,
 %Sqlite.Ecto.Error{message: nil,
  sqlite: {:sqlite_error,
   "cannot VACUUM from within a transaction"}}}

which is expected because all Ecto queries are executed within transactions and VACUUM cannot be executed within a transaction. I suspect once we get the bug sorted out, you still won't be able to run VACUUM from Ecto.

My system:

$ iex --version
Erlang/OTP 18 [erts-7.1] [source] [64-bit] [smp:8:8] [async-threads:10] [hipe] [kernel-poll:false]

Elixir 1.1.0
$ uname -a
FreeBSD elixir.test.local 10.2-RELEASE FreeBSD 10.2-RELEASE #0 r286666: Wed Aug 12 15:26:37 UTC 2015     [email protected]:/usr/obj/usr/src/sys/GENERIC  amd64

[asakura]

Hi @jazzyb

$ iex --version
Erlang/OTP 18 [erts-7.1] [source] [64-bit] [smp:8:8] [async-threads:10] [hipe] [kernel-poll:false]

Elixir 1.1.1

$ uname -a
Linux asakura-desktop 3.19.0-32-generic #37-Ubuntu SMP Wed Oct 21 10:23:06 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 15.04
Release:    15.04
Codename:   vivid

How long does the query normally take when you manually run VACUUM; on your DB file?

$ ls -lh stats.sqlite3 
-rw-r--r-- 1 asakura asakura 754M Ліс 10 15:16 stats.sqlite3

$ time sqlite3 stats.sqlite3 VACUUM

real    1m21.764s
user    0m7.756s
sys 0m7.960s

$ ls -lh stats.sqlite3 
-rw-r--r-- 1 asakura asakura 365M Ліс 10 16:44 stats.sqlite3

How long does it actually take for the timeout to occur? I see you set a timeout of a little over 2 months. I doubt you actually had to wait that long for the error, though ;)

It takes from 20 seconds up to 1 minute.

Information about packages and theirs versions extracted from mix.lock:

  "ecto": {:hex, :ecto, "1.0.4"},
  "esqlite": {:hex, :esqlite, "0.2.1"},
  "poolboy": {:hex, :poolboy, "1.5.1"},
  "sqlite_ecto": {:hex, :sqlite_ecto, "1.0.2"},
  "sqlitex": {:hex, :sqlitex, "0.8.2"},

Thanks!