Started work on migrating to Ecto3, ran into issues with Sqlite's `busy`

[dmitriid]

Unfortunately, I didn't do a clean fork. The code is here: https://github.com/dmitriid/sqelect

Unfortunately, this is not a direct fork. To understand the changes to Ecto 3 I went ahead and copy-pasta-ed each module callback by callback and function by function until it compiled and started running tests. So if you run a diff, it will show that everything has changed. This is not entirely true :)

See the Readme for the not-so-many things that are changed (if I remembered everything correctly).

I do hope to bring it back to sqlite_ecto2

The problem I've ran into is: when running ecto's Migrator.up, the code seemingly runs all the statement until it tries to insert data into one of the tables. Sqlite returns busy, and then the migration fails with

{"busy",0}
{"busy",1}
{"busy",2}
{"busy",3}
{"busy",4}
{"busy",5}

Sqelect.DbConnection.Protocol (#PID<0.278.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.319.0> exited
** (Sqelect.DbConnection.Error) {{:bad_return_value, :too_many_tries}, {GenServer, :call, [#PID<0.315.0>, {:query_rows, "INSERT INTO \"schema_migrations\" (\"version\",\"inserted_at\") VALUES (?1,?2)", [timeout: :infinity, decode: :manual, types: true, bind: [0, "2020-07-28T13:25:33"]]}, :infinity]}}
    (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.4.5) lib/ecto/repo/schema.ex:661: Ecto.Repo.Schema.apply/4
    (ecto 3.4.5) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:641: Ecto.Migrator.verbose_schema_migration/3
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:293: Ecto.Migrator.async_migrate_maybe_in_transaction/6
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:211: anonymous fn/5 in Ecto.Migrator.up/4
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:493: Ecto.Migrator.lock_for_migrations/4
    integration/sqelect/test_helper.exs:93: (file)

And now I don't have enough knowledge to figure out what's wrong. If anyone is willing to take a look, please please please do :)

[warmwaffles]

@dmitriid I am gonna be taking a look at this and using your code as a base. That busy connection seems like a WAL and some other pragma things that can be toggled to make it work.

[dsignr]

Following, I run a production system based on this library and this would greatly help me maintain it. Thanks a lot to both of you for doing this!

[dmitriid]

@warmwaffles Awesome! There are also discussions on elixir forum:

• "busy" error: https://elixirforum.com/t/help-with-debugging-sqlites-busy-error-with-ecto3/33613

• connection pools https://elixirforum.com/t/request-for-feedback-does-an-elixir-sqlite3-driver-need-a-connection-pool/36689

[dmitriid]

@warmwaffles Also, sorry for the state of my code :) I haven't had time to look further into it.

[warmwaffles]

@dmitriid it's not a problem. I'm just working through what is missing etc... Biggest issue right now is https://github.com/elixir-sqlite/sqlitex doesn't implement the behavior db_connection that postgrex and some others implement as well. I think the first step would be to get that implemented within that adapter and then in theory, we should just be able to copy most of the postgres implementation minus a few parts.

[warmwaffles]

@dmitriid I took some time this week to build a different NIF library that utilizes the Dirty Scheduler as opposed to esqlite's internal command pool.

https://github.com/warmwaffles/exqlite

I could probably use your help building an adapter for ecto3.

[dmitriid]

@warmwaffles

Oh my 😍 I will help however I can (previously I just blindly copy-pasted code from sqlite_ecto2 in hopes that it would work :D )

[warmwaffles]

Yea there is just a lot of inner workings that I don't fully understand.

[dmitriid]

@warmwaffles Well, I started doing the most barebones stuff for now: https://github.com/warmwaffles/exqlite/pull/2 and https://github.com/warmwaffles/exqlite/pull/3

This time I'm going function by function, trying to understand what they are doing, and act accordingly. So far so good 😬

[warmwaffles]

If you are looking for an Ecto3 adapter, it is now located here. https://github.com/elixir-sqlite/ecto_sqlite3