[sqlite] WAL: no longer able to use read-only databases?
In testing the latest SQLite snapshot with WAL enabled, it seems that there's no way to use a database in a read-only location. For example, let's say I've created a database as root, then closed it (cleanly): $ ls -l /flash/alarms.db* -rw-r--r--1 root root 36864 Jan 1 00:14 /flash/alarms.db If I try as another user to use that database, I get an error: $ sqlite3 /flash/alarms.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA journal_mode; Error: unable to open database file sqlite> However, if I create a symlink to that database in a location that I have write access to, then everything works fine: $ ln -s /flash/alarms.db ./alarms.db $ ls -l alarms.db* lrwxrwxrwx1 adminadmin 16 Jan 1 00:15 alarms.db -> /flash/alarms.db $ sqlite3 alarms.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA journal_mode; wal sqlite> .quit $ ls -l alarms.db* lrwxrwxrwx1 adminadmin 16 Jan 1 00:15 alarms.db -> /flash/alarms.db -rw-r--r--1 adminadmin32768 Jan 1 00:15 alarms.db-shm -rw-r--r--1 adminadmin0 Jan 1 00:15 alarms.db-wal So clearly this is a side-effect of WAL creation, which happens in the same directory as the database file. This doesn't seem like it should fundamentally be any different than normal journaling mode, in that opening a database in read-only mode makes the creation of a journal / WAL unnecessary. But I'm not familiar with the WAL internals, so maybe there's more to it. FYI, this works fine with normal journaling mode (we bumped in existing code after changing the journal_mode). Any additional flags or ways of doing this that I'm missing? Or is it a bug? Thanks! -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
On Fri, Jul 9, 2010 at 3:21 PM, Matthew L. Creech wrote: > In testing the latest SQLite snapshot with WAL enabled, it seems that > there's no way to use a database in a read-only location. > Documentation on the developers' current view of this issue can be found here: http://www.sqlite.org/draft/wal.html#readonly If you have any comments and complaints, please send them to this mailing list. Thanks. -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
> > > In testing the latest SQLite snapshot with WAL enabled, it seems that > > there's no way to use a database in a read-only location. > > > > Documentation on the developers' current view of this issue can be found > here: > > > In my opinion it's ok, WAL already has special conditions on which it would operate and the current documentation describes them all thoroughly. I wish only the "advantages" sections of WAL also grew a little, maybe giving more details about speed improvement encouraging using this mode more frequently Thanks Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
So if I'm reading the documentation correctly: The wal-index is in shared memory, and so technically it does not have to have a name in the host computer filesystem. Custom VFS implementations are free to implement shared memory in any way they see fit, but the default unix and windows drivers that come built-in with SQLite implement shared memory using mmapped files named using the suffix "-shm" and located in the same directory as the database file. ... Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption. It seems like the only thing preventing WAL from working with read-only databases is this lack of a global namespace for shared memory. This exists in many Linux systems as "/dev/shm", or even "/tmp" would work fine for a lot of users. I totally understand that you can't make this the default, because it could potentially lead to strange behavior with chroot()s and the like. But for those of us with controlled environments who know that all applications using SQLite share the same view of the filesystem, it would be great if we could #define an option which turns this on. For my application, chroot()ed apps are a complete non-issue, whereas lack of read-only DB access is a dealbreaker for WAL (which would really be a shame, the performance benefit is substantial!) I know I could always write my own VFS to do this, but that seems like overkill. :) More importantly, it requires maintenance - I'd probably create my VFS by copying os_unix.c, but then I wouldn't automatically be getting any fixes/updates that you guys make to that file going forward. Best would be if SQLite had a #define like SQLITE_CUSTOM_WAL_LOCATION, which defaults to undefined (and hence you use the same directory as the DB file), but which users could define to "/dev/shm", "/tmp", or some other location to place all the shm files there, globally. The changes in http://www.sqlite.org/src/fdiff?v1=ae173c9f6afaa58b2833a1c95c6cd32021755c42&v2=a76d1952ac7984574701c48b665220b871c5c9a5 are pretty straightforward, so I could probably take a stab at this if you want. What do you guys think? -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
On 15 Jul 2010, at 4:52pm, Matthew L. Creech wrote: > This exists in many Linux systems as "/dev/shm", or even "/tmp" would > work fine for a lot of users. I don't think so. Just like the older SQLite journal system, it's important that the WAL files survive through a crash. SQLite finds the WAL file the next time the database is opened, and uses the contents to restore the database to a sane and useful state. Most forms of Unix wipe the /tmp directory during boot, so the WAL file would not survive. And /dev/shm is sometimes real RAM storage so naturally that will be empty after a boot too. These options work fine for read-only databases but read-only databases don't actually need a WAL file at all. Rather than spend time writing code to move the WAL file to a different place, it makes more sense to spend that time writing code so that a WAL file is not made at all for a read-only database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
> I don't think so. Just like the older SQLite journal system, it's important > that the WAL files survive through a crash. I believe WAL file is not a problem here (despite some confusing macro name that Matthew proposed). The problem is SHM file which don't have to survive - SQLite rebuilds it in case if it's missing. Pavel On Thu, Jul 15, 2010 at 12:01 PM, Simon Slavin wrote: > > On 15 Jul 2010, at 4:52pm, Matthew L. Creech wrote: > >> This exists in many Linux systems as "/dev/shm", or even "/tmp" would >> work fine for a lot of users. > > I don't think so. Just like the older SQLite journal system, it's important > that the WAL files survive through a crash. SQLite finds the WAL file the > next time the database is opened, and uses the contents to restore the > database to a sane and useful state. Most forms of Unix wipe the /tmp > directory during boot, so the WAL file would not survive. And /dev/shm is > sometimes real RAM storage so naturally that will be empty after a boot too. > > These options work fine for read-only databases but read-only databases don't > actually need a WAL file at all. Rather than spend time writing code to move > the WAL file to a different place, it makes more sense to spend that time > writing code so that a WAL file is not made at all for a read-only database. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL: no longer able to use read-only databases?
On Thu, Jul 15, 2010 at 12:23 PM, Pavel Ivanov wrote: >> I don't think so. Just like the older SQLite journal system, it's important >> that the WAL files survive through a crash. > > I believe WAL file is not a problem here (despite some confusing macro > name that Matthew proposed). The problem is SHM file which don't have > to survive - SQLite rebuilds it in case if it's missing. > Right, sorry for the confusing terminology. The "-shm" file is what I was referring to, since that's the part that needs to be writable even for a read-only app, if I understand correctly. I believe that it's okay to put that in a ramdisk, issues with chroot() aside. -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
