Connection inadvertently tries to change in/out of WAL journal_mode

[rhcarvalho]

I ran into an unexpected situation today:

• A Phoenix app uses a SQLite database in WAL mode (using Ecto).
• I'm writing some one-off code using the primitives from Exqlite:

  {:ok, conn} = Exqlite.Connection.connect(database: path, mode: :readonly)

• The above fails with SQLITE_BUSY.

After some debugging, it turns out it is not failing to connect because "the database is locked", but because it is trying, behind the scenes, to change the journal mode right after opening the database:

Details

exqlite/lib/exqlite/connection.ex

Lines 545 to 551 in e7828d5

	defp do_connect(database, options) do
	with {:ok, directory} <- resolve_directory(database),
	:ok <- mkdir_p(directory),
	{:ok, db} <- Sqlite3.open(database, options),
	:ok <- set_key(db, options),
	:ok <- set_custom_pragmas(db, options),
	:ok <- set_journal_mode(db, options),

exqlite/lib/exqlite/connection.ex

Lines 466 to 468 in e7828d5

	defp set_journal_mode(db, options) do
	maybe_set_pragma(db, "journal_mode", Pragma.journal_mode(options))
	end

exqlite/lib/exqlite/connection.ex

Lines 404 to 416 in e7828d5

	defp maybe_set_pragma(db, pragma_name, value) do
	case get_pragma(db, pragma_name) do
	{:ok, current} ->
	if current == value do
	:ok
	else
	set_pragma(db, pragma_name, value)
	end
	_ ->
	set_pragma(db, pragma_name, value)
	end
	end

exqlite/lib/exqlite/pragma.ex

Lines 14 to 24 in e7828d5

	def journal_mode(options) do
	case Keyword.get(options, :journal_mode, :delete) do
	:delete -> "delete"
	:memory -> "memory"
	:off -> "off"
	:persist -> "persist"
	:truncate -> "truncate"
	:wal -> "wal"
	_ -> raise ArgumentError, "invalid :journal_mode"
	end
	end

The problem is that SQLite's WAL mode is persistent.
If I update my code snippet to force WAL mode it works:

{:ok, conn} = Exqlite.Connection.connect(database: path, mode: :readonly, journal_mode: :wal)

But it has the undesired side effect that it will change the database to use WAL mode (I guess the readonly mode should prevent that...).
Not saying anything about the journal_mode has the undesired side effect of moving the database out of WAL mode (because it calls PRAGMA journal_mode='delete' behind the scenes).

I think the correct behavior is to not try to set a journal_mode unless the user has explicitly chosen one (that's the behavior of sqlite3 CLI and libraries in other languages I've used).

[rhcarvalho]

I provided a possible fix in #341, which I'm now successfully using in my app.

[LostKobrakai]

I'm curious, does this also affect the default journal mode (when creating a fresh database)?

[rhcarvalho]

Considering the default in exqlite is/was the same as SQLite's default behavior (delete mode), then no, a new database would start as delete mode and the PRAGMA would reapply delete mode.

In ecto_sqlite though, the default is WAL. So creating a file with ecto then opening with exqlite used to cause the unintended change of mode (wal to delete).

Now that you ask, I don't remember if the ecto integration might have the same problem of switching existing databases unintentionally.

[LostKobrakai]

Ah makes sense. I was worried this would affect the default to wal mode for the ecto integration.