Disable Write-Ahead Logging in SQLiteDatabaseWrapper::Close

[Sebastian Höffner]

Dear all,

Please find attached (and below) a patch to disable the Write-Ahead
Log when the SQLite database is closed. This solves an issue with
reusing SQLite databases when they get copied around or remounted in a
docker container.

I signed the CLA and sent it out a few minutes ago, so that should go
through soon.

Kind regards
Sebastian

# HG changeset patch
# User Sebastian Höffner <in...@sebastian-hoeffner.de>
# Date 1657550727 -7200
#      Mon Jul 11 16:45:27 2022 +0200
# Node ID c3d7bf3c56e0cc33df60db17ada4ae24b697c0aa
# Parent  942eb3849d8d34bcd20967a97a1025bbdaf18f15
Disable Write-Ahead Logging in SQLiteDatabaseWrapper::Close

This allows network-drive / docker operation with a persisted database on
network mounts.

When running

    podman run -p 4242:4242 -p 8042:8042 --name orthanc \
        -v orthanc_volume:/etc/orthanc/OrthancStorage \
        --rm sjodogne/orthanc-python

twice, the second run will fail with:

    E0711 13:37:41.668864 StatementReference.cpp:88] SQLite: disk I/O error
        (5386)
    E0711 13:37:41.685738 main.cpp:2063] Uncaught exception, stopping now:
        [SQLite: Cannot prepare a cached statement] (code 1012)

Error-code 5386 is SQLITE_IOERR_SHMMAP [1], which is a way the Write-Ahead Log
in SQLite is handled. The underlying issue is that the WAL does not work well
on network devices (see also [2]).

In this particular case, it was possible to run

    sqlite3 path-to-index <<< "PRAGMA journal_mode=DELETE;"

on the host system to allow a proper restart again, however, this does not work
from within the container, rendering the solution inapproriate.
Maybe different file permissions might solve it as well, but instead a good fix
seems to be to disable the WAL on close, so that no specific user intervention
is required.

I found a similar report [3], in which the solution was to switch to PostgreSQL

[1]: https://www.sqlite.org/matrix/rescode.html#ioerr_shmmap
[2]: https://www.sqlite.org/wal.html
[3]: https://stackoverflow.com/questions/71405254/sqlite-cannot-run-a-cached-statement

diff -r 942eb3849d8d -r c3d7bf3c56e0
OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp  Mon
Jul 11 12:17:15 2022 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp  Mon
Jul 11 16:45:27 2022 +0200
@@ -1371,6 +1371,8 @@
   void SQLiteDatabaseWrapper::Close()
   {
     boost::mutex::scoped_lock lock(mutex_);
+    // Disable Write-Ahead Logging to allow sqlite files in, e.g.,
docker containers
+    db_.Execute("PRAGMA journal_mode=DELETE;");
     db_.Close();
   }

[Alain Mazy]

Hi Sebastian,

Thanks for this contribution.  I have integrated it in the mainline: https://hg.orthanc-server.com/orthanc/rev/e6f26be401fa.  Indeed, it seems interesting to close the WAL and SHM files on exit.

However, note that this probably won't solve your issues in case the Orthanc process gets killed and does not exit properly.

Best regards,

Alain.

--
You received this message because you are subscribed to the Google Groups "Orthanc Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/CAJnbjpB2HUOSEu9NyBBER%3DcxEgG2zHnyFFjtCRG6vwQVM3QxOA%40mail.gmail.com.