question

DieuMinhLy-6595 avatar image
1 Vote"
DieuMinhLy-6595 asked RalphStadie-4852 commented

How to update or insert sqlite database in Web Apps?

Hi there,
I currently have an Azure Web App using PHP and SQLite.
In my localhost, I am able to update and insert into the SQLite database.
However, when I deploy the Web App to the Azure App Service, I can only select data from the SQLite .db file.
I cannot do update or insert because the database is locked.
Please let me know how to resolve this issue.
My Azure Web App is on Linux.
Thank you.

azure-webapps
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ryanchill avatar image
0 Votes"
ryanchill answered SebastianMaLikKeung-2283 commented

Hi @DieuMinhLy-6595,

This isn't a viable option. The underlying storage used by the app service is CIFS (Linux) and SMB (Windows) based. SQLite as a provider doesn't play well with these protocol types and you don't have sufficient access to change underlying properties of the storage. So, there are two options.

  1. The easiest, switch to a cloud offering like MySQL or Postgres. Tutorial: Build a PHP and MySQL app in Azure App Service walks you through how to setup a MySQL instance on Azure and connect your PHP application to it.

  2. You can create an Azure File Share and create a storage mount from your app to the share. See https://docs.microsoft.com/en-us/azure/app-service/configure-connect-to-azure-storage?pivots=container-linux.

Regards,
Ryan





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ryan

Followed option 2 you mentioned.
Place sqlite db file to mountPath. Unfortunately, application can read and still cannot write - "database is locked"
When db file is placed back in application directly, no such read/write error.

$ az webapp config storage-account list --resource-group pd-ntu --name pdntu-apiserver
This command is in preview and under development. Reference and support levels: https://aka.ms/CLI_refstatus
[
{
"name": "-id",
"slotSetting": false,
"value": {
"accessKey": "
",
"accountName": "",
"mountPath": "/az/share/data",
"shareName": "
-share",
"state": "Ok",
"type": "AzureFiles"
}
}
]

Regards
Seb

0 Votes 0 ·
SebastianMaLikKeung-2283 avatar image
0 Votes"
SebastianMaLikKeung-2283 answered GordonChen-3428 commented

Hi all,
When db file is placed back in application directory, even when codes write directly to the db, data is not persisted albeit no error thrown.
Installed sqlite3 in the same docker image, inserted data using sqlite3. Stop and start Azure App Service. The data is not persisted, not durable.

Regards
Seb

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Why don't you use other filesystems, such as ext4, for your linux server if the CIFS caused the problem for writing Sqlite database? Thanks.

0 Votes 0 ·
GordonChen-3428 avatar image
1 Vote"
GordonChen-3428 answered RalphStadie-4852 commented

I think it is a bug. The web hosting space should be read and written. The purpose which user used Sqlite database is to simplify deployment. Because Sqlite don't need a server to run, it is a embed database, don't need to setup. Most of web hosting companies provide the user the rights for both reading and writing in web hosting space. Why you can't give the user the rights? Your solution trouble users, users have to use other database or looking for other storage. I found this bug only happen in "Linux" and "Code" mode, we can use Sqlite Database in "Windows" and "Code" mode in Your App Service(But it needs VSCode to deploy to Azure App Service). Hope you can improve App service, fix the bug in "Linux" and "Code" mode for the App Service. Thanks.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't think it's a bug but just the way that Azure web services are designed. I can understand deploying a Sqlite database as read-only storage that can be retrieved by the web app, however for it to allow writes would mean that you could, theoretically, create an infinite sized database either intentionally or unintentionally (i.e. a bug). By steering you to use an external data provider (eg. SQL Server, MySQL, Postgres, etc.), the onus is on that provider to monitor and enforce any storage limits you may run into in case you do something crazy. Just my 2 cents worth.

0 Votes 0 ·