SQLite Error 14: 'unable to open database file' using Azure AppServices Linux and WEBSITES_ENABLE_APP_SERVICE_STORAGE=true

[WernerMairl]

Hi

I have a asp net core app using ef core and Sqlite!
Everything in the latest released dotnet core 3.1 versions.

Hosting is up and running as Azure Web App Linux (docker) container
(base container: mcr.microsoft.com/dotnet/core/aspnet:3.1)

The app is configured to use a sqlite file on '/home/data/task365.sqlite'

using 'WEBSITES_ENABLE_APP_SERVICE_STORAGE=false" everything woks fine:

• directory "data" is created if does not exists
• migrations are executed
• ef-context is working

if we change the setting to

"WEBSITES_ENABLE_APP_SERVICE_STORAGE=true"

then we are not able to start the app anymore!
this setting basically means that a volume is mapped to "/home".......

Exception message/stack trace:
An error occurred using the connection to database 'main' on server '/home/data/task365.sqlite'.
2020-01-09T06:27:17.714552777Z Application startup exception: Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'.
2020-01-09T06:27:17.714586977Z at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)

After a lot of investigations i must assume that the problem is caused by using a mounted path.
I see the same symptoms if i'm using the volume mapping feature in Azure Web Apps for Linux instead of 'WEBSITES_ENABLE_APP_SERVICE_STORAGE'.

What i have verified:
my process has write access to the mounted folder - File.Copy or File.Move to the mounted folder is working!

There are some known restrictions for (linux) mounted volumes in general or Azure App Service Mounts ?

I have found (via google) the following item that means
"Unfortunately, Sqlite is not supported on App Service on Linux."
but without any background information....

https://social.msdn.microsoft.com/Forums/vstudio/en-US/9e650466-2e97-454e-9096-dfb3243bb1d6/linux-and-sqlite?forum=windowsazurewebsitespreview

regards
Werner

[ajcvickers]

@WernerMairl The reason SQLite is not supported on App Service for Linux is because it uses a file system that prevents locking. As far as I am aware there are no workarounds for this. It is marginally better documented here: https://docs.microsoft.com/en-us/azure/devops/pipelines/ecosystems/python-webapp?view=azure-devops#considerations-for-django

[WernerMairl]

Hi,

You can't use a SQLite database, because App Service locks the db.sqlite3 file, preventing both reads and writes.

Thanks for this clarifications - THIS information should better visible somewhere, it costs me a few days to find out this.....

a) I know all the risks and considerations about using sqlite on file shares caused by the bad implementations of locking (Windows AND Linux)

b). my idea was to avoid multiple concurrent processes using my sqlitefile but i need a solution that persists after a container restart, and a docker volume seems to be a easy solution.

Someone knows how and where this lock is exactly implemented ?
In my investigations it seems to be active

1. for the volume/mount coming from the app-service-plan
2. for volumes/mounts using App Service Configuration Mappings and Storage FileShares.

The lock is really implemented in the "file system" btw mount ?
How they identify sqlite file, by extension, by header ?

On the other side: using File.Copy and File.Move are working.....
strange story....

Feedback: i have another solution now - closing the issue is OK!
But the most frustrating detail here is the fact that we get only a "unexpected" exception from the filesystem, and no other informations (google and stackoverflow are not helping here).....

[Fuco1]

@WernerMairl

Feedback: i have another solution now - closing the issue is OK!

Could you share what was the solution?

[WernerMairl]

hi
it was a workaround focused on my concrete environment:

1. sqlite file up and running on a a path that is not persisted over restarts -but sqlite enabled
2. high frequent copy database commands and copy-file to the long living storage
3. on each restart: copy last version of sqlite file from the long living storage to the short living but sqlite-enabled storage

.. a dirty workaround... that has survived only a few months...

@Fuco1