Why do I get sqlite error, "unable to open database file"?

Question

Using my Django app, I'm able to read from the database just fine. When the application didn't have permission to access the file, it gave me this error:

attempt to write a readonly database

Which made sense. So I edited the permissions on the file, so that the Apache process had write permissions. However, instead of it being able to write, I get this cryptic error:

unable to open database file

If it's useful, here's the entire output:

Request Method: POST
Request URL:    http://home-sv-1/hellodjango1/polls/1/vote/
Exception Type: OperationalError
Exception Value:    
unable to open database file
Exception Location: /usr/lib/pymodules/python2.5/django/db/backends/sqlite3/base.py in execute, line 193
Python Executable:  /usr/bin/python
Python Version: 2.5.2
Python Path:    ['/var/www', '/usr/lib/python2.5', '/usr/lib/python2.5/plat-linux2', '/usr/lib/python2.5/lib-tk', '/usr/lib/python2.5/lib-dynload', '/usr/local/lib/python2.5/site-packages', '/usr/lib/python2.5/site-packages', '/usr/lib/pymodules/python2.5', '/usr/lib/pymodules/python2.5/gtk-2.0']
Server time:    Sun, 23 Aug 2009 07:06:08 -0500

Let me know if a stack trace is necessary.

It think you got this problem when you were deploying. – Mohammed Shareef C Aug 29 '17 at 16:20 — Mohammed Shareef C, Aug 29 '17 at 16:20

score 88 · Accepted Answer · edited Mar 29 '18 at 18:38

88

Aha, just stumbled across an article explaining this. Also Django have info on their NewbieMistakes page.

The solution is to make sure the directory containing the database file also has write access allowed to the process.

In my case, running this command fixed the problem:

sudo chown www-data .

edited Mar 29 '18 at 18:38

Rishabh Agrahari

103
4

answered Aug 23 '09 at 12:14

Nick Bolton

5,016
12
51
62

4

This assumes you're on debian/ubuntu, if you're using CentOS you're going to want to use 'apache' instead of 'www-data' – Luke Chadwick May 23 '13 at 06:44
3

@nbolton Note: `chown www-data. .` should actually be `chown www-data .` otherwise there is some wizardy of chown command that I don't know about... please enlighten me. – Jeff Sheffield Aug 19 '14 at 18:13
4

I believe the extra dot is to set the group to the owner's default group. Otherwise it doesn't change the group. This is just from memory though, I highly recommend you try that out yourself. – Nick Bolton Feb 26 '15 at 11:55
Summarized here in case of future link rot or tl;dr: SQLite3 wants write access to the DB file's directory so that it can create a journal file there when a transaction is opened. – user1454265 Jul 23 '15 at 16:04
Great for Linux users. Bupkiss for Windows. – Jay Blanchard Aug 17 '15 at 16:46
The same error occurs for mysql also. Then what to do? It happened to me. I had solved it. but don't remember how i solved it – Mohammed Shareef C Aug 29 '17 at 16:12
That's exactly what I was missing. The folder should also be owned by the user running that update process. – retromuz Jul 10 '19 at 14:17
and to debug it, use `var_dump(shell_exec('namei -l '.escapeshellarg("/path/to/sqlite.db3")));` - namei will tell you where the permission -or- path issue is, if that's actually your issue. – hanshenrik Nov 28 '20 at 23:18

score 7 · Answer 2 · edited Sep 18 '20 at 17:05

7

From the Django says "Unable to Open Database File" when using SQLite3 section of the Newbie mistakes Django wiki page:

make sure Apache can also write to the parent directory of the database
make sure none of the folders of the database file's full path start with a number
make sure the full path to the db directory exists
make sure your /tmp directory is world-writable
make sure the path to the database specified in settings.py is a full path
make sure there are no special characters in the path
on Windows, make sure the db directory path is written with double backslashes

edited Sep 18 '20 at 17:05

fmalina

103
4

answered Jan 14 '10 at 22:43

ssc

1,129
3
16
30

Copy/paste the relevant parts of the link here. – Christophe De Troyer Apr 21 '16 at 16:10
And it is an upvote! :) – Christophe De Troyer Apr 21 '16 at 19:56
1

The beauty of serverfault.com: I wrote this answer 10 years ago, it's (probably) still valid and useful; commented on (and improved) 4 years ago, spellchecked today :-) – ssc Sep 18 '20 at 17:09

score 7 · Answer 3 · answered Jan 02 '13 at 23:08

7

My solution to this was more like so. I didn't really want to change the ownership of this dir. (mostly because i use the pi user to do things like git)

/var/www/mysite $ ls -la sql*
-rw-rw-r-- 1 pi       pi       25600 Jan  2 22:57 sqlite.db

(or whatever db you are using)

where pi is the user that i created all the files in. (yes this is a raspberry pi)

Instead of changing of permissions to www-data, i found that I only needed to change the permissions like this:

sudo chmod 775 /var/www/mysite
sudo chmod 664 /var/www/mysite/sqlite.db
sudo usermod -a -G pi www-data

This gives group write access to the necessary files and adds the www-data user to the pi group.

Note: if you have logging, you will need to do this for the django logfile as well or apache won't like it much.

answered Jan 02 '13 at 23:08

SpiRail

171
2
6

1

I think you could've just added the pi user to the www-data group too. Also you could've just left the files as they are and used the command setfacl to add a access control list for pi on the files and or directories. – slm Jan 03 '13 at 00:09
setfacl sounds like a potentially good option. It would be good to have this listed as an instructional answer. One issue i face is that I need to delete the .db file often in testing. When it gets recreated, it needs to be chowned again. – SpiRail Jan 04 '13 at 09:30
See my answer for this question, http://serverfault.com/a/462970/2518. Is there something more I should add to that which would help? – slm Feb 01 '13 at 23:28

score 5 · Answer 4 · answered Apr 14 '13 at 12:22

Adding an operational user to the www-data group is working well on my test environment. Additionally I've put the sqlite3.db file into a separate subfolder, to be more secure.

Database file shall be owned by www-data

sudo chown www-data mysite/db_sqlite3/
sudo chown www-data mysite/db_sqlite3/my.db

My operational user hape gets a member of www-data group:

sudo usermod -a -G www-data hape

Allow database file write access to members of group www-data:

sudo chmod u+w+x,g+w+x mysite/db_sqlite3/
sudo chmod u+w+x,g+w+x mysite/db_sqlite3/my.db

As a result, the database can be accessed read+write by apache2-daemon (user www-data), without giving grant to the project root folder, and - on the other hand - the app can be run in dev mode by the operational user hape, e.g.

./manage.py runserver

also.

score 2 · Answer 5 · answered Nov 11 '15 at 20:23

The solution is to make sure the directory containing the database file also has write access allowed to the process.

For Windows 7, 8.1, 10, Server 2012, etc. follow the Bonobo installation directions:

Allow IIS User to modify C:\inetpub\wwwroot\Bonobo.Git.Server\App_Data folder.

To do so:

select Properties of App_Data folder,
go to Security tab,
click edit,
select IIS user (in my case IIS_IUSRS) and add Modify and Write permission,
confirm these settings with Apply button.

score 1 · Answer 6 · edited May 23 '17 at 12:41

Borrowed from SO question: https://stackoverflow.com/questions/4283132/apache-instance-user-permission-issue

Assuming files are owned by apache user to start:

% chown -R apache.apache /var/www/mysite

set ACLs for user/group pi:

% setfacl -d -m u:pi:rwx /var/www/mysite
% setfacl -d -m g:pi:rwx /var/www/mysite

% getfacl /var/www/mysite
# file: /var/www/mysite
# owner: apache
# group: apache
user::rwx
group::r-x
other::r-x
default:user::rwx
default:user:pi:rwx
default:group::r-x
default:group:pi:rwx
default:mask::rwx
default:other::r-x

You can tell there's an ACL with ls -l, the trailing '+' on the permissions bits:

# ls -la /var/www
drwxr-xr-x   3 apache   apache   80 26. Nov 12:43 .
drwxrwxrwt  15 root     root 360 26. Nov 12:40 ..
drwxrwxr-x+  2 apache   apache   40 26. Nov 12:43 mysite

Setting the owner/group to the apache process for the whole django project is a bad idea, no need to give unnecessary privileges. — benjaoming, Feb 01 '13 at 22:10

score 0 · Answer 7 · answered Dec 19 '11 at 09:35

0

The development server needs to be run as the same user who has write perms on the database folder, so if you originally created the database as root, you will need to be root when you run:

python manage.py runserver

answered Dec 19 '11 at 09:35

Kilizo

111
1

While technically correct, running the server as `root` is a *terrible* idea -- It would be better to `chown` the database to the regular unprivileged user that normally runs the server... – voretaq7 Nov 10 '12 at 08:52

score 0 · Answer 8 · answered Jun 17 '14 at 22:49

0

create a subdirectory in the working directory

mkdir db-folder

create sqlite database in the subdiretory

sqlite3 db-folder/db.db

change owner for the subdirectory to www-data in debain or apache in centOS

chown -R www-data db-folder

and grab a cold beer coz your are done.

P/S: to check if the procedure was succesful

ls -l data-folder

you should see such

-rw-r--r-- 2 www-data root 4096 Jun 18 01:38 data-folder

answered Jun 17 '14 at 22:49

brotich

101
1

If you're going to post an answer, please provide the answer that's substantially different to the other answers on same question. – masegaloeh Jun 18 '14 at 01:21

score -2 · Answer 9 · answered Jan 03 '17 at 07:21

-2

just write sudo sqlite3 databaseFilename.sql and it's work

answered Jan 03 '17 at 07:21

Alireza

97
2

That might work for an interactive user session but is not the solution not for a web application... – HBruijn Jan 04 '17 at 11:09

Why do I get sqlite error, "unable to open database file"?

9 Answers9