154

I have a SQLite database that I am using for a website. The problem is that when I try to INSERT INTO it, I get a PDOException

SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

I SSH'd into the server and checked permissions, and the database has the permissions

-rw-rw-r--

I'm not that familiar with *nix permissions, but I'm pretty sure this means

  • Not a directory
  • Owner has read/write permissions (that's me, according to ls -l)
  • Group has read/write permissions
  • Everyone else only has read permissions

I also looked everywhere I knew to using the sqlite3 program, and found nothing relevant.

Because I didn't know with what permissions PDO is trying to open the database, I did

chmod o+w supplies.db

Now, I get another PDOException:

SQLSTATE[HY000]: General error: 14 unable to open database file

But it ONLY occurs when I try to execute an INSERT query after the database is open.

Any ideas on what is going on?

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Austin Hyde
  • 26,347
  • 28
  • 96
  • 129

13 Answers13

377

The problem, as it turns out, is that the PDO SQLite driver requires that if you are going to do a write operation (INSERT,UPDATE,DELETE,DROP, etc), then the folder the database resides in must have write permissions, as well as the actual database file.

I found this information in a comment at the very bottom of the PDO SQLite driver manual page.

Austin Hyde
  • 26,347
  • 28
  • 96
  • 129
  • 10
    Also, SELinux (if installed) must not be enforcing. Took me a day and a half to figure that one out. – Steve V. Dec 19 '10 at 08:31
  • 1
    Hum, sorry, but I thank it was it, but it just solve the issue temporaly, the main issue was my www-data user wasn't in the www-data group. – Dorian Dec 09 '11 at 23:18
  • 7
    As I know, the containing folder must be writable because when writing a journaling file will be created and so the db itself. To have the same user as the webserver, try copy the content of file to another created ad hoc. – lcapra Dec 27 '11 at 19:17
  • 4
    Also the db file and the directory it resides in must be owned by 'www-data' on linux boxes. – anisbet Jan 08 '15 at 21:28
  • 4
    Currently sqlite3 might have 3 files, the `.db`, a `.db-shm` and a `.db-wal` files, and of course the parent directory of the three, which must be all writable for the user running the program. – Marcos Dione Sep 28 '16 at 15:20
  • 1
    For whom it may concern, this applies to the Python `sqlite3` module as well. – ZeroKnight Feb 21 '21 at 00:27
  • Thanks for sharing this answer, Austin. I was debugging this issue in a Mezzio application that uses laminas-db, which in turn uses the PDO_SQLite driver. I was confused as the database file, within a Docker container, had rw set for user, group, and other, yet still the error occurred. After setting the same permissions on the enclosing directory, the app works. Much appreciated. – Matthew Setter Mar 22 '22 at 08:33
20

This can happen when the owner of the SQLite file itself is not the same as the user running the script. Similar errors can occur if the entire directory path (meaning each directory along the way) can't be written to.

Who owns the SQLite file? You?

Who is the script running as? Apache or Nobody?

Charles
  • 50,943
  • 13
  • 104
  • 142
  • 1
    I own the SQLite file, but I don't know who the script runs as. How can I find out? (Keep in mind, this is on a shared host and I have limited permissions) – Austin Hyde Jul 23 '10 at 15:39
  • 2
    Ah, that makes things more fun. If you're on shared hosting, there's a very good chance that the script runs as "nobody" or "apache". Have your script create a file (`file_put_contents('./foo.txt', 'Hello, world');`), that will show you who it's running as. Chances are that you'll need to have the script create the SQLite database. This may be an entertaining exercise if you already have data in your current file... – Charles Jul 23 '10 at 17:07
  • Good idea, but no-go. Whoever PHP is running as doesn't have write privileges, so it can't create the file. Is there anyway PHP can retrieve what user it is currently running as? – Austin Hyde Jul 23 '10 at 18:24
  • The only way seems to be [through the POSIX extension](http://us.php.net/manual/en/function.posix-getuid.php), which is enabled by default on POSIX-y systems. Your hosting provider may have [R'd TFM](http://us.php.net/manual/en/intro.posix.php) and disabled it, though. – Charles Jul 23 '10 at 18:33
  • Well, they R'd TFM, alright. `posix_getuid()` doesn't work either. – Austin Hyde Jul 23 '10 at 19:22
  • Hm. Can you shell out and run `ps aux`? The user Apache is running as (blindly assuming they're running Apache) is almost certainly going to be the user running PHP.... – Charles Jul 23 '10 at 19:54
  • ... that being said, it's a moot point. You aren't root, so you won't be able to change ownership of the SQLite file. Finding out why PHP can't create new files is probably the next step. Try creating a directory with (eww) 0777 permissions and try to see if a PHP script can create a file within. If it can't, you may well be up a creek without a paddle. If it can, try having PHP create a new SQLite file in that directory. – Charles Jul 23 '10 at 19:55
8

For me the issue was SELinux enforcement rather than permissions. The "read only database" error went away once I disabled enforcement, following the suggestion made by Steve V. in a comment on the accepted answer.

echo 0 >/selinux/enforce

Upon running this command, everything worked as intended (CentOS 6.3).

The specific issue I had encountered was during setup of Graphite. I had triple-checked that the apache user owned and could write to both my graphite.db and its parent directory. But until I "fixed" SELinux, all I got was a stack trace to the effect of: DatabaseError: attempt to write a readonly database

Community
  • 1
  • 1
Noah Sussman
  • 4,576
  • 2
  • 28
  • 25
  • 11
    SELinux is a security measure, so should not be disabled without very good reason. It would be better to figure out why SELinux is blocking in the first place and configure it correctly instead of disabling it. – Jens Wegar Feb 18 '14 at 08:05
6

This can be caused by SELinux. If you don't want to disable SELinux completely, you need to set the db directory fcontext to httpd_sys_rw_content_t.

semanage fcontext -a -t httpd_sys_rw_content_t "/var/www/railsapp/db(/.*)?"
restorecon -v /var/www/railsapp/db
Andy Fraley
  • 1,043
  • 9
  • 16
5

In summary, I've fixed the problem by putting the database file (* .db) in a subfolder.

  • The subfolder and the database file within it must be a member of the www-data group.
  • In the www-data group, you must have the right to write to the subfolder and the database file.

####### Additional Notes For Similar Problem #####

I gave write permissions to my sqlite database file to other users and groups but it still didn't work.

File is in my web root directory for my .NET Core WebApi.

It looked like this:

-rw-rw-rw-  1 root root  24576 Jan 28 16:03 librestore.db

Even if I ran the service as root, I kept getting the error :

Error: SQLite Error 8: 'attempt to write a readonly database'.

I also did a chown to www-data on the librestore.db and I still received the same error.

Finally I moved up above my webroot directory and gave others write access to that directory (LibreStore - the root of my WebApi) also and then it worked.

Web root has write access

I'm not sure why I had to give the directory write access if the specific file already had write access, but this is the only thing that worked.

But once I made that change www-data user could access the .db file and inserts succeeded.

raddevus
  • 8,142
  • 7
  • 66
  • 87
Erkan Hürnalı
  • 91
  • 1
  • 2
4

I got this error when I tried to write to a database on an Android system.

Apparently sqlite3 not only needs write permissions to the database file and the containing directory (as @austin-hyde already said in his answer) but also the environment variable TMPDIR has to point to a (possibly writable) directory.

On my Android system I set it to TMPDIR="/data/local/tmp" and now my script runs as expected :)

Edit:

If you can't set environment variables you can use one of the other methods listed here: https://www.sqlite.org/tempfiles.html#temporary_file_storage_locations like PRAGMA temp_store_directory = 'directory-name';

Thilo
  • 234
  • 1
  • 8
2

I got the same error from IIS under windows 7. To fix this error i had to add full control permissions to IUSR account for sqlite database file. You don't need to change permissions if you use sqlite under webmatrix instead of IIS.

l0pan
  • 476
  • 7
  • 11
2

I used:

echo exec('whoami');

to find out who is running the script (say username), and then gave the user permissions to the entire application directory, like:

sudo chown -R :username /var/www/html/myapp

tshepang
  • 12,111
  • 21
  • 91
  • 136
shasi kanth
  • 6,987
  • 24
  • 106
  • 158
0

(For followers looking for an answer to a similar question) I'm building a C# .Net Core 6.0 WPF app. I put the Sqlite.db3 on the c:\ drive for convenience while developing. To write to the database I must open Visual Studio 2019 as Administrator.

Ray Brennan
  • 373
  • 1
  • 6
  • 19
0

@Charles in a comment pointed out the solution to this (or at least, a botch solution). This is merely me spelling it out more clearly. Put file_put_contents('./nameofyourdb.sqlite', null); (or .db, whichever you fancy) in a .php file in the root directory of your app (or wherever you want the db to be created), then load that page which renders the php code. Now you have an sqlite db created by whichever user runs your php code, meaning your php code can write to it. Just don't forget to use sudo when interacting with this db in the console.

A good clean solution to this is to allow the file of your main user account to be written to by (in my case) the http user but this worked for me and its simple.

a.anev
  • 125
  • 1
  • 4
  • 11
0

None of these solutions worked for me and I suppose I had a very rare case that can still happen. Had a power shortage so even with 777 permissions on folder and db file, without SELinux, I would get this error.

Turns out there was a jellyfin.pid file (not sure if it's named after the service or user as they have the same name) locking it after the power shortage. Deleted it, restarted the service and everything worked.

NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58
0

Non of above are my cases. It turns out that the container folder is owned by root and has a sticky bits('drwxrwxrwt') and the sqlite db is owned by other user. And the process tries to insert data into table is run under root user. So either of these approach will work:

  • remove sticky bits
  • change the owner user who run the process the same as the owner of the sqlite db file.
Dat TT
  • 2,850
  • 2
  • 16
  • 18
-1

I got this in my browser when I changed from using http://localhost to http://145.900.50.20 (where 145.900.50.20 is my local IP address) and then changed back to localhost -- it was necessary to stay with the IP address once I had changed to that once

kris
  • 11,868
  • 9
  • 88
  • 110