• Uncategorized

About linux : WAL-enabled-SQLite-database-blocking-user-on-read

Question Detail

Here is the background:

I am currently running an ETL process on a linux server (CentOS 8) which also hosts applications which read from a local SQLite database.
At certain times when the ETL is running and writing to the SQLite database, applications are also reading from the database.

In order to avoid database locking when the SQLite database is in use by the applications, I have enabled WAL on the SQLite database so that the ETL may write to the database while applications are in use.

However there is now the following issue whereby the ETL process is unable to query the database after the connection has been established. I have logged the following information when this occurs:

The ‘shinysuite’ user runs the ETL process.
The ‘shiny’ user runs the applications.
According to the admin, these users belong to the same group.

Output from /etc/groups

First, I do not understand why the ‘shiny’ user owns the -wal file even though it only reads.
Second, I do not understand why the ETL process (‘shinysuite’) would be unable to read from the -wal file even it did not own the file.

What could be the problem here?

Question Answer

First, I do not understand why the ‘shiny’ user owns the -wal file even though it only reads.

When reading from a WAL-mode sqlite3 database, the helper -wal and -shm files are created if they don’t already exist.

They’re owned by the shiny user and belong to the shiny group, but shinysuite is not a member of that group so it doesn’t have permission to use the files. If your application being run by shiny does so in the shinysuite group instead of shiny (If it’s a binary executable, using chgrp(1) to change the group of the file and then making it set-gid with chmod g+s shinyapp is one way, or maybe just add shinysuite to the shiny group.) it should work.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.