Re: [sqlite] Read-only media
[Richard Hipp] If the last writer to the database file crashed and left a hot journalhttp://www.sqlite.org/atomiccommit.html#section_4_2then the next reader to come along must rollback that journal before it can start reading, and that will require write access. Can similar things happen if you copy the database file (using cp/tar/whatever) while an application wants to write to it? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
On 7 Sep 2012, at 1:19pm, Steinar Midtskogen stei...@latinitas.org wrote: [Richard Hipp] If the last writer to the database file crashed and left a hot journalhttp://www.sqlite.org/atomiccommit.html#section_4_2then the next reader to come along must rollback that journal before it can start reading, and that will require write access. Can similar things happen if you copy the database file (using cp/tar/whatever) while an application wants to write to it? It depends on what you're actually doing when you copy the database file. The hot journal is actually another file, and may be even on a different disk to the database file. So there's a chance that the database file will be marked Part way through a transaction. There should be a journal file somewhere. but the journal file won't be found. Other models have the database app, the database, and the hot journal all in the same folder, and when someone copies the database file they're really copying the whole folder. The real solution to all of this is that if you are writing a program which needs only READONLY access, write your code right from the start to make it open the database in readonly mode. That way, when you are testing your disk image and there's something wrong with the database file it'll be more likely to spit out an understandable error message than to just look like it'll work but fail later. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read-only media
Can sqlite databases be read from a read-only media? I seem to remember seeing something about this on the website, but can't find it. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
'twould appear so...the shell even knows about a read-only database. $ sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table test(a,b); sqlite insert into table values(1,2); Error: near table: syntax error sqlite insert into test values(1,2); sqlite insert into test values(3,4); sqlite select * from test; 1|2 3|4 sqlite .quit $ chmod a-w . $ !sq sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select * from test; 1|2 3|4 sqlite .quit $ chmod -w test.db $ !sq sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select * from test; 1|2 3|4 sqlite insert into test values(5,6); Error: attempt to write a readonly database Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Baruch Burstein [bmburst...@gmail.com] Sent: Thursday, September 06, 2012 8:39 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Read-only media Can sqlite databases be read from a read-only media? I seem to remember seeing something about this on the website, but can't find it. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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] Read-only media
Can sqlite databases be read from a read-only media? I seem to remember seeing something about this on the website, but can't find it. Adding on to Michael's reply, you may need to ensure that you have a writeable location for the temp_store depending on what queries you issue. http://www.sqlite.org/pragma.html#pragma_temp_store http://www.sqlite.org/pragma.html#pragma_temp_store_directory --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
On Thu, Sep 6, 2012 at 9:39 AM, Baruch Burstein bmburst...@gmail.comwrote: Can sqlite databases be read from a read-only media? I seem to remember seeing something about this on the website, but can't find it. Usually. If the last writer to the database file crashed and left a hot journalhttp://www.sqlite.org/atomiccommit.html#section_4_2then the next reader to come along must rollback that journal before it can start reading, and that will require write access. We have encountered cases where companies accidently publish a gazillion copies of a CDROM that contains a hot journal. The database on such CDROMs are unreadable. In WAL mode, readers usually need access to the -shm file. And they certainly need to be able to create the -shm file if it doesn't already exist. So make sure the databases you put onto read-only media (ex: CDROM) are in rollback mode. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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] Read-only media
On 6 Sep 2012, at 3:13pm, Richard Hipp d...@sqlite.org wrote: If the last writer to the database file crashed and left a hot journalhttp://www.sqlite.org/atomiccommit.html#section_4_2then the next reader to come along must rollback that journal before it can start reading, and that will require write access. We have encountered cases where companies accidently publish a gazillion copies of a CDROM that contains a hot journal. The database on such CDROMs are unreadable. Ouch. Yeah, I guess that's going to happen occasionally. Suppose you open a database in read-only mode (using SQLITE_OPEN_READONLY) and SQLite finds a hot journal for it. Is tidying up the hot journal done even in _READONLY, or do you immediately get a result indicating an error, or do you get an error result later ? (I could test this on my Mac, but I don't know whether the results are documented and cross-platform or the result of my particular setup.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
On Thu, Sep 6, 2012 at 10:26 AM, Simon Slavin slav...@bigfraud.org wrote: On 6 Sep 2012, at 3:13pm, Richard Hipp d...@sqlite.org wrote: Suppose you open a database in read-only mode (using SQLITE_OPEN_READONLY) and SQLite finds a hot journal for it. Is tidying up the hot journal done even in _READONLY, or do you immediately get a result indicating an error, or do you get an error result later ? When SQLITE_OPEN_READONLY is used, then the file might as well be on CDROM as far as SQLite is concerned. It won't write to it. Even to rollback a hot journal. -- 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] Read-only media
On 6 Sep 2012, at 3:40pm, Richard Hipp d...@sqlite.org wrote: When SQLITE_OPEN_READONLY is used, then the file might as well be on CDROM as far as SQLite is concerned. It won't write to it. Even to rollback a hot journal. Thanks. That makes sense. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
On Thu, Sep 6, 2012 at 5:26 PM, Simon Slavin slav...@bigfraud.org wrote: On 6 Sep 2012, at 3:13pm, Richard Hipp d...@sqlite.org wrote: If the last writer to the database file crashed and left a hot journalhttp://www.sqlite.org/atomiccommit.html#section_4_2then the next reader to come along must rollback that journal before it can start reading, and that will require write access. We have encountered cases where companies accidently publish a gazillion copies of a CDROM that contains a hot journal. The database on such CDROMs are unreadable. Ouch. Yeah, I guess that's going to happen occasionally. Not that I think this is necessarily a good idea, but maybe if opening a database with a hot journal from read-only media (or with _READONLY), the database pages in the journal can be loaded into the page cache and marked as never_remove_from_cache or some such, effectively giving read-only access to the database (I think this would work, but might not be a good idea. But that company would have saved their reputation if this existed) -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
