[sqlite] journal files not always removed
Okay let's restart... maybe I misunderstood that Richard was able to reproduce this. I have figured out a simple script that with 3.8.11.1 windows (using binary download sqlite3.exe) causes the problem. running just 'sqlite3.exe' --- script --- .open test.db pragma journal_mode=wal; .open test.db create table test(id int); .quit this always leaves -shm and -wal files laying around. it's when there's multiple opens. tested on linux and test.db-shm and -wal files stay around until the program closes. But they should normally be gone at the end of the create table? [root at tower2 ~]# sqlite3 SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open /tmp/test.db sqlite> pragma journal_mode=WAL; wal sqlite> .open /tmp/test.db sqlite> create table test(id int); sqlite> (ctrl-z) [4]+ Stopped sqlite3 [root at tower2 ~]# ls /tmp test.db test.db-shm test.db-wal they do get deleted after the .quit on linux.
[sqlite] journal files not always removed
On Sat, Aug 15, 2015 at 1:59 PM, Richard Hipp wrote: > On 8/15/15, Ashwin Hirschi wrote: >> >>> But I also see that behavior dates back to 3.7.6 and maybe earlier. It >>> isn't something new. >>> (Testing on Ubuntu). >> >> Also, are you sure you testing things right? I thought URI support was >> only added in 3.7.7... If that's true, your little script should actually >> throw an error for 3.7.6! >> > > My mistake. Checked my shell history and in fact the problem goes > back to 3.7.8, not 3.7.6 as I originally reported. The point is: It > goes back a long time. This is on Ubuntu though. Maybe something > changed on Windows. was there some progress on this? I was noticing that -shm and -wal files are left around, database is not readonly mode; I thought it was because I had a bad (half-functional) virtual file driver for it; but I checked today and it's not triggering using that code; although simple tests with sqlite3 command line tool I wasn't able to make it happen with what I thought was the cause... I will dig into it more later I guess > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files not always removed
> With this new test case, I see that the -shm and -wal files are > retained upon exit in read-only mode. Great. Good to see other people are able to reproduce (some? of) the issue. > But I also see that behavior dates back to 3.7.6 and maybe earlier. It > isn't something new. > (Testing on Ubuntu). How odd...[!] I've tested several 3.7.* and 3.8.* versions before posting and can only see things go wrong for 3.8.11 and after (on my Windows 10 machine). Also, are you sure you testing things right? I thought URI support was only added in 3.7.7... If that's true, your little script should actually throw an error for 3.7.6! Ashwin.
[sqlite] journal files not always removed
> Also, make sure that folder is not a shared resource or inside a shared > resource that is visible from any other network node or machine. > > This piece, describing the same symptom for Internet Explorer temporary > files, may help: > https://support.microsoft.com/en-us/kb/814782 The folder is a regular one, alas. > What happens when you make the DB, set WAL mode, do a Query, then wait 2 > minutes doing nothing, then close the DB. Do the temp files still loiter? I've tried your 2 minute pause suggestion, just to be sure. But the lingering journal files remain. Also, I've added the folder to Windows Defender's exclusion list. There makes no difference either. But, as I wrote in my previous post: older versions of SQLite *don't* show this behaviour (on the same machine)! Ashwin.
[sqlite] journal files not always removed
>> I've recently found SQLite doesn't always remove its journal files >> anymore. >> >> The issue seems to occur when opening WAL databases in read-only mode >> (i.e. using SQLITE_OPEN_READONLY). >> >> A scenario to reproduce this (using version 3.8.11.1) looks like: >> >> 1. open a WAL database in read-only mode >> 2. prepare & finalize a query >> 3. close the database >> >> The "-shm" and "-wal" journal files are created during the prepare() in >> step 2. But, after step 3, they're both still present. > > I am not able to reproduce the observed behavior on either Ubuntu, > Win7, or Win8. Please provide additional clues. Here's a verbatim replay on my Windows 10 machine using the sqlite(3) commandline-tool (downloaded-but-renamed from sqlite.org): = c:\prj\demo>dir /b c:\prj\demo>sqlite test.db SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. sqlite> create table person(name text, email text); sqlite> pragma journal_mode=wal; wal sqlite> .exit c:\prj\demo>dir /b test.db c:\prj\demo>sqlite file:test.db?mode=ro SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. sqlite> .tables person sqlite> .exit c:\prj\demo>dir /b test.db test.db-shm test.db-wal = The used folder is a regular, non-shared folder. Running the same scenario using the official commandline tool for the older version 3.8.10.2 works exactly as expected (i.e. without any journal files remaining). That last bit has me believing this is not related to the virus scanner (Windows Defender, btw.), but something must have changed in the SQLite code... Ashwin.
[sqlite] journal files not always removed
Also, make sure that folder is not a shared resource or inside a shared resource that is visible from any other network node or machine. This piece, describing the same symptom for Internet Explorer temporary files, may help: https://support.microsoft.com/en-us/kb/814782 What happens when you make the DB, set WAL mode, do a Query, then wait 2 minutes doing nothing, then close the DB. Do the temp files still loiter? On 2015-08-15 07:28 PM, Ashwin Hirschi wrote: > > Hello, > > I've recently found SQLite doesn't always remove its journal files > anymore. > > The issue seems to occur when opening WAL databases in read-only mode > (i.e. using SQLITE_OPEN_READONLY). > > A scenario to reproduce this (using version 3.8.11.1) looks like: > > 1. open a WAL database in read-only mode > 2. prepare & finalize a query > 3. close the database > > The "-shm" and "-wal" journal files are created during the prepare() > in step 2. But, after step 3, they're both still present. > > If the database is opened in read-write mode, everything is fine. > Lastly, I'm having the issue on a Windows 10 machine and am not > currently able to test anywhere else... > > So, can anyone else reproduce the above scenario? > > Ashwin. > > P.S. I've also checked some older SQLite versions. It looks like this > unfortunate "non-cleanup" was introduced in 3.8.11. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files not always removed
Checked this on WIndows 7 & 8, works as expected. This means either Windows 10 has some glitch or the access level to the folder is not correct, files may not be deleted by that process or some antivirus is checking the file while SQLite is trying to delete it, or you are not closing the DB connection fully (though if it works with previous versions of SQLite, this last option should not be the problem). On 2015-08-15 07:28 PM, Ashwin Hirschi wrote: > > Hello, > > I've recently found SQLite doesn't always remove its journal files > anymore. > > The issue seems to occur when opening WAL databases in read-only mode > (i.e. using SQLITE_OPEN_READONLY). > > A scenario to reproduce this (using version 3.8.11.1) looks like: > > 1. open a WAL database in read-only mode > 2. prepare & finalize a query > 3. close the database > > The "-shm" and "-wal" journal files are created during the prepare() > in step 2. But, after step 3, they're both still present. > > If the database is opened in read-write mode, everything is fine. > Lastly, I'm having the issue on a Windows 10 machine and am not > currently able to test anywhere else... > > So, can anyone else reproduce the above scenario? > > Ashwin. > > P.S. I've also checked some older SQLite versions. It looks like this > unfortunate "non-cleanup" was introduced in 3.8.11. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files not always removed
Hello, I've recently found SQLite doesn't always remove its journal files anymore. The issue seems to occur when opening WAL databases in read-only mode (i.e. using SQLITE_OPEN_READONLY). A scenario to reproduce this (using version 3.8.11.1) looks like: 1. open a WAL database in read-only mode 2. prepare & finalize a query 3. close the database The "-shm" and "-wal" journal files are created during the prepare() in step 2. But, after step 3, they're both still present. If the database is opened in read-write mode, everything is fine. Lastly, I'm having the issue on a Windows 10 machine and am not currently able to test anywhere else... So, can anyone else reproduce the above scenario? Ashwin. P.S. I've also checked some older SQLite versions. It looks like this unfortunate "non-cleanup" was introduced in 3.8.11.
[sqlite] journal files not always removed
On 8/15/15, Ashwin Hirschi wrote: > >> But I also see that behavior dates back to 3.7.6 and maybe earlier. It >> isn't something new. >> (Testing on Ubuntu). > > Also, are you sure you testing things right? I thought URI support was > only added in 3.7.7... If that's true, your little script should actually > throw an error for 3.7.6! > My mistake. Checked my shell history and in fact the problem goes back to 3.7.8, not 3.7.6 as I originally reported. The point is: It goes back a long time. This is on Ubuntu though. Maybe something changed on Windows. -- D. Richard Hipp drh at sqlite.org
[sqlite] journal files not always removed
On 8/15/15, Keith Medcalf wrote: > > Quite Fascinating! Here is the transcript (it is running the Windows > SQLITE.EXE which I have in a different location in the path, so slightly > modified the script): > Ugh. There is a bug in my script. The final "ls -l" should have a "*" at the end Revised script: rm -rf t1.db* ./sqlite3 t1.db <<\EOF PRAGMA journal_mode=WAL; CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(4,5,6); EOF ls -l t1.db* echo '***' ./sqlite3 'file:t1.db?mode=ro' <<\EOF SELECT * FROM t1; PRAGMA journal_mode; EOF ls -l t1.db* echo '***' ./sqlite3 'file:t1.db?mode=rw' <<\EOF SELECT * FROM t1; PRAGMA journal_mode; EOF ls -l t1.db* With this new test case, I see that the -shm and -wal files are retained upon exit in read-only mode. But I also see that behavior dates back to 3.7.6 and maybe earlier. It isn't something new. (Testing on Ubuntu). -- D. Richard Hipp drh at sqlite.org
[sqlite] journal files not always removed
On 8/15/15, Keith Medcalf wrote: > > This is something changed in SQLITE itself. These tests are on Windows 10 > using the current MinGW compiler with the same configuration and windows > headers. > Do you also have MSYS installed? If so can you run the following shell script and see if it demonstrates the problem on Win10? It does not on my Win7 and Win8 boxes. rm -rf t1.db* ./sqlite3 t1.db <<\EOF PRAGMA journal_mode=WAL; CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3),(4,5,6); EOF ls -l t1.db* echo '***' ./sqlite3 'file:t1.db?mode=ro' <<\EOF SELECT * FROM t1; PRAGMA journal_mode; .exit 1 EOF ls -l t1.db -- D. Richard Hipp drh at sqlite.org
[sqlite] journal files not always removed
On 8/15/15, Ashwin Hirschi wrote: > > Hello, > > I've recently found SQLite doesn't always remove its journal files anymore. > > The issue seems to occur when opening WAL databases in read-only mode > (i.e. using SQLITE_OPEN_READONLY). > > A scenario to reproduce this (using version 3.8.11.1) looks like: > > 1. open a WAL database in read-only mode > 2. prepare & finalize a query > 3. close the database > > The "-shm" and "-wal" journal files are created during the prepare() in > step 2. But, after step 3, they're both still present. I am not able to reproduce the observed behavior on either Ubuntu, Win7, or Win8. Please provide additional clues. > > If the database is opened in read-write mode, everything is fine. Lastly, > I'm having the issue on a Windows 10 machine and am not currently able to > test anywhere else... > > So, can anyone else reproduce the above scenario? > > Ashwin. > > P.S. I've also checked some older SQLite versions. It looks like this > unfortunate "non-cleanup" was introduced in 3.8.11. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] journal files not always removed
Quite Fascinating! Here is the transcript (it is running the Windows SQLITE.EXE which I have in a different location in the path, so slightly modified the script): KMedcalf at WYNPCLLT3 ~ $ rm -rf t1.db* KMedcalf at WYNPCLLT3 ~ $ sqlite t1.db <<\EOF > PRAGMA journal_mode=WAL; > CREATE TABLE t1(a,b,c); > INSERT INTO t1 VALUES(1,2,3),(4,5,6); > EOF wal KMedcalf at WYNPCLLT3 ~ $ ls -l t1.db* -rw-r--r-- 1 KMedcalf Administrators 8192 Aug 15 13:12 t1.db KMedcalf at WYNPCLLT3 ~ $ echo '***' *** KMedcalf at WYNPCLLT3 ~ $ sqlite 'file:t1.db?mode=ro' <<\EOF > SELECT * FROM t1; > PRAGMA journal_mode; > .exit 1 > EOF 1|2|3 4|5|6 wal KMedcalf at WYNPCLLT3 ~ $ ls -l t1.db -rw-r--r-- 1 KMedcalf Administrators 8192 Aug 15 13:12 t1.db KMedcalf at WYNPCLLT3 ~ $ Doing similar under CMD.EXE as the shell however results in the following (same executable run): 2015-08-15 13:15:15 [D:\Temp] >sqlite file:test.db?mode=ro SQLite version 3.8.12 2015-08-15 16:32:50 Enter ".help" for usage hints. sqlite> select * from test; 1 2 3 sqlite> pragma journal_mode; wal sqlite> .exit 2015-08-15 13:15:54 [D:\Temp] >dir Volume in drive D is DATA Volume Serial Number is 343E-C7BF Directory of D:\Temp 2015-08-15 13:15 . 2015-08-15 13:15 .. 2015-08-15 12:13 old 2015-08-15 12:34 1,836,032 sqlite3.exe 2015-08-15 12:29 8,192 test.db 2015-08-15 13:1532,768 test.db-shm 2015-08-15 13:15 0 test.db-wal 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 7 File(s) 1,877,404 bytes 3 Dir(s) 157,980,798,976 bytes free > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp > Sent: Saturday, 15 August, 2015 12:41 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] journal files not always removed > > On 8/15/15, Keith Medcalf wrote: > > > > This is something changed in SQLITE itself. These tests are on Windows > 10 > > using the current MinGW compiler with the same configuration and windows > > headers. > > > > Do you also have MSYS installed? If so can you run the following > shell script and see if it demonstrates the problem on Win10? It does > not on my Win7 and Win8 boxes. > > rm -rf t1.db* > ./sqlite3 t1.db <<\EOF > PRAGMA journal_mode=WAL; > CREATE TABLE t1(a,b,c); > INSERT INTO t1 VALUES(1,2,3),(4,5,6); > EOF > ls -l t1.db* > echo '***' > ./sqlite3 'file:t1.db?mode=ro' <<\EOF > SELECT * FROM t1; > PRAGMA journal_mode; > .exit 1 > EOF > ls -l t1.db > > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files not always removed
This is something changed in SQLITE itself. These tests are on Windows 10 using the current MinGW compiler with the same configuration and windows headers. For the current head of trunk: 2015-08-15 12:17:05 [D:\Temp] >type test.sql pragma journal_mode=wal; pragma journal_mode; create table test(test); insert into test values (1),(2),(3); select * from test; 2015-08-15 12:17:08 [D:\Temp] >type test.py import apsw cn = apsw.Connection('test.db', apsw.SQLITE_OPEN_READONLY) print cn.cursor().execute('select * from test').fetchall() cn.close() 2015-08-15 12:17:16 [D:\Temp] >type test2.py import apsw cn = apsw.Connection('test.db') print cn.cursor().execute('select * from test').fetchall() cn.close() 2015-08-15 12:17:20 [D:\Temp] >dir 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 3 File(s)412 bytes 2015-08-15 12:17:25 [D:\Temp] >sqlite test.db < test.sql wal wal 1 2 3 2015-08-15 12:17:37 [D:\Temp] >dir 2015-08-15 12:17 8,192 test.db 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 2015-08-15 12:17:39 [D:\Temp] >test.py [(1,), (2,), (3,)] 2015-08-15 12:17:43 [D:\Temp] >dir 2015-08-15 12:17 8,192 test.db 2015-08-15 12:1732,768 test.db-shm 2015-08-15 12:17 0 test.db-wal 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 2015-08-15 12:17:45 [D:\Temp] >test2.py [(1,), (2,), (3,)] 2015-08-15 12:17:49 [D:\Temp] >dir 2015-08-15 12:17 8,192 test.db 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py For version 3.8.10: 2015-08-15 12:29:12 [D:\Temp] >sqlite test.db < test.sql wal wal 1 2 3 2015-08-15 12:29:23 [D:\Temp] >dir 2015-08-15 12:29 8,192 test.db 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 2015-08-15 12:29:24 [D:\Temp] >test.py [(1,), (2,), (3,)] 2015-08-15 12:29:36 [D:\Temp] >dir 2015-08-15 12:29 8,192 test.db 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py 2015-08-15 12:29:37 [D:\Temp] >test2.py [(1,), (2,), (3,)] 2015-08-15 12:29:42 [D:\Temp] >dir 2015-08-15 12:29 8,192 test.db 2015-08-15 12:11 151 test.py 2015-08-15 12:12 137 test.sql 2015-08-15 12:16 124 test2.py > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of R.Smith > Sent: Saturday, 15 August, 2015 11:59 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] journal files not always removed > > Also, make sure that folder is not a shared resource or inside a shared > resource that is visible from any other network node or machine. > > This piece, describing the same symptom for Internet Explorer temporary > files, may help: > https://support.microsoft.com/en-us/kb/814782 > > What happens when you make the DB, set WAL mode, do a Query, then wait 2 > minutes doing nothing, then close the DB. Do the temp files still loiter? > > > On 2015-08-15 07:28 PM, Ashwin Hirschi wrote: > > > > Hello, > > > > I've recently found SQLite doesn't always remove its journal files > > anymore. > > > > The issue seems to occur when opening WAL databases in read-only mode > > (i.e. using SQLITE_OPEN_READONLY). > > > > A scenario to reproduce this (using version 3.8.11.1) looks like: > > > > 1. open a WAL database in read-only mode > > 2. prepare & finalize a query > > 3. close the database > > > > The "-shm" and "-wal" journal files are created during the prepare() > > in step 2. But, after step 3, they're both still present. > > > > If the database is opened in read-write mode, everything is fine. > > Lastly, I'm having the issue on a Windows 10 machine and am not > > currently able to test anywhere else... > > > > So, can anyone else reproduce the above scenario? > > > > Ashwin. > > > > P.S. I've also checked some older SQLite versions. It looks like this > > unfortunate "non-cleanup" was introduced in 3.8.11. > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] journal files
Actually, I believe the entire 28 byte header is zeroed, not just the first 4 bytes. See the zeroJournalHdr() function in pager.c for details. -Shane On Tue, Feb 2, 2010 at 7:09 AM, Pavel Ivanov wrote: > What do you want to see in journal files? You can execute 'PRAGMA > journal_mode = persist' and all information in journal file except > first 4 bytes will be left on disk for you. Is it enough? > > Pavel > > On Tue, Feb 2, 2010 at 7:00 AM, rishabh wrote: > > > > hey, > > > > I am coding for an application wherein i need to check the journal files > as > > in i dont want it to get deleted after the commit. how to go about it? > where > > in the Sqlite3.c code can i edit it. > > > > also, is it possible to customize the sqlite code for the journal file a > bit > > as per my needs? > > > > thanx > > -- > > View this message in context: > http://old.nabble.com/journal-files-tp27419280p27419280.html > > Sent from the SQLite mailing list archive at Nabble.com. > > > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] journal files
What do you want to see in journal files? You can execute 'PRAGMA journal_mode = persist' and all information in journal file except first 4 bytes will be left on disk for you. Is it enough? Pavel On Tue, Feb 2, 2010 at 7:00 AM, rishabh wrote: > > hey, > > I am coding for an application wherein i need to check the journal files as > in i dont want it to get deleted after the commit. how to go about it? where > in the Sqlite3.c code can i edit it. > > also, is it possible to customize the sqlite code for the journal file a bit > as per my needs? > > thanx > -- > View this message in context: > http://old.nabble.com/journal-files-tp27419280p27419280.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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
[sqlite] journal files
hey, I am coding for an application wherein i need to check the journal files as in i dont want it to get deleted after the commit. how to go about it? where in the Sqlite3.c code can i edit it. also, is it possible to customize the sqlite code for the journal file a bit as per my needs? thanx -- View this message in context: http://old.nabble.com/journal-files-tp27419280p27419280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
Okay I updated to version 3.6.6.2 and now I am only seeing "PRAGMA main.journal_mode = OFF;" not work with my custom defined OS ( when I switch to windows it works ) I should note that I changed sqlite3.c to sqlite3.cpp and got the amalgamation to compile in C++ I also defined SQLITE_OS_OTHER and wrote my own functions for sqlite3_io_methods and sqlite3_vfs Is there anything else I would need to do that's OS specific to get the "PRAGMA main.journal_mode = OFF;" to work if I define my own OS like I have? >I originally thought "maybe we introduced a bug and broke >journal_mode". But I tried it myself and everything works correctly. >And the regression tests for journal_mode=OFF are running. So I do >not have any idea what you are doing wrong. Everything is working >great here. > > >D. Richard Hipp >[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
Cheers adding the extra pragma has stopped the temporary file activity :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 6:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal files On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote: > I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory > database ":memory:" on version 3.6.1. And I too am seeing lots of > temporary file activity, which is really killing our performance as > our > storage medium is so slow. Try using PRAGMA temp_store=MEMORY; In addition to PRAGMA journal_mode=MEMORY; > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Stephen > Abbamonte > Sent: Monday, December 01, 2008 3:50 PM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] Journal files > > I just tried this line also and the journal files are still being > created > here is the code I am running: > > >int32_t ret = sqlite3_open(filename, m_DatabaseRef); > if( ret == SQLITE_OK ) >{ > sqlite3_stmt* sqlStmt = NULL; > ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA > main.journal_mode = OFF;", -1, &sqlStmt, NULL); > if( ret == SQLITE_OK ) > { > sqlite3_step(sqlStmt); > const unsigned char* colValue = > sqlite3_column_text(sqlStmt, 0); <- returns "off" > sqlite3_finalize(sqlStmt); > } > } > > Anything wrong with the way I am doing this? > Thanks for the help. > > >> Did you set >> >> PRAGMA journal_mode = OFF; >> >> ? The way I read the documentation (on a second or third close >> reading, I think), this only sets the default value for new databases >> to be attached, but doesn't affect your main connection and any >> databases that have already been attached. So what I do is >> >> PRAGMA main.journal_mode = OFF; >> >> IIRC, I needed this line to actually turn off the journals (tested >> with 3.4.x and 3.5.x versions of SQLite only, though). >> >> Cheers, >> Stefan > > > > ___ > 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 D. Richard Hipp [EMAIL PROTECTED] ___ 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] Journal files
On Dec 1, 2008, at 6:49 PM, Stephen Abbamonte wrote: > I just tried this line also and the journal files are still being > created > here is the code I am running: > > >int32_t ret = sqlite3_open(filename, m_DatabaseRef); > if( ret == SQLITE_OK ) >{ > sqlite3_stmt* sqlStmt = NULL; > ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA > main.journal_mode = OFF;", -1, &sqlStmt, NULL); > if( ret == SQLITE_OK ) > { > sqlite3_step(sqlStmt); > const unsigned char* colValue = > sqlite3_column_text(sqlStmt, 0); <- returns "off" > sqlite3_finalize(sqlStmt); > } > } > > Anything wrong with the way I am doing this? > Thanks for the help. > I originally thought "maybe we introduced a bug and broke journal_mode". But I tried it myself and everything works correctly. And the regression tests for journal_mode=OFF are running. So I do not have any idea what you are doing wrong. Everything is working great here. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote: > I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory > database ":memory:" on version 3.6.1. And I too am seeing lots of > temporary file activity, which is really killing our performance as > our > storage medium is so slow. Try using PRAGMA temp_store=MEMORY; In addition to PRAGMA journal_mode=MEMORY; > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Stephen > Abbamonte > Sent: Monday, December 01, 2008 3:50 PM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] Journal files > > I just tried this line also and the journal files are still being > created > here is the code I am running: > > >int32_t ret = sqlite3_open(filename, m_DatabaseRef); > if( ret == SQLITE_OK ) >{ > sqlite3_stmt* sqlStmt = NULL; > ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA > main.journal_mode = OFF;", -1, &sqlStmt, NULL); > if( ret == SQLITE_OK ) > { > sqlite3_step(sqlStmt); > const unsigned char* colValue = > sqlite3_column_text(sqlStmt, 0); <- returns "off" > sqlite3_finalize(sqlStmt); > } > } > > Anything wrong with the way I am doing this? > Thanks for the help. > > >> Did you set >> >> PRAGMA journal_mode = OFF; >> >> ? The way I read the documentation (on a second or third close >> reading, I think), this only sets the default value for new databases >> to be attached, but doesn't affect your main connection and any >> databases that have already been attached. So what I do is >> >> PRAGMA main.journal_mode = OFF; >> >> IIRC, I needed this line to actually turn off the journals (tested >> with 3.4.x and 3.5.x versions of SQLite only, though). >> >> Cheers, >> Stefan > > > > ___ > 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 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory database ":memory:" on version 3.6.1. And I too am seeing lots of temporary file activity, which is really killing our performance as our storage medium is so slow. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Abbamonte Sent: Monday, December 01, 2008 3:50 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Journal files I just tried this line also and the journal files are still being created here is the code I am running: int32_t ret = sqlite3_open(filename, m_DatabaseRef); if( ret == SQLITE_OK ) { sqlite3_stmt* sqlStmt = NULL; ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA main.journal_mode = OFF;", -1, &sqlStmt, NULL); if( ret == SQLITE_OK ) { sqlite3_step(sqlStmt); const unsigned char* colValue = sqlite3_column_text(sqlStmt, 0); <- returns "off" sqlite3_finalize(sqlStmt); } } Anything wrong with the way I am doing this? Thanks for the help. >Did you set > > PRAGMA journal_mode = OFF; > >? The way I read the documentation (on a second or third close >reading, I think), this only sets the default value for new databases >to be attached, but doesn't affect your main connection and any >databases that have already been attached. So what I do is > > PRAGMA main.journal_mode = OFF; > >IIRC, I needed this line to actually turn off the journals (tested >with 3.4.x and 3.5.x versions of SQLite only, though). > >Cheers, >Stefan ___ 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] Journal files
I just tried this line also and the journal files are still being created here is the code I am running: int32_t ret = sqlite3_open(filename, m_DatabaseRef); if( ret == SQLITE_OK ) { sqlite3_stmt* sqlStmt = NULL; ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA main.journal_mode = OFF;", -1, &sqlStmt, NULL); if( ret == SQLITE_OK ) { sqlite3_step(sqlStmt); const unsigned char* colValue = sqlite3_column_text(sqlStmt, 0); <- returns "off" sqlite3_finalize(sqlStmt); } } Anything wrong with the way I am doing this? Thanks for the help. >Did you set > > PRAGMA journal_mode = OFF; > >? The way I read the documentation (on a second or third close >reading, I think), this only sets the default value for new databases >to be attached, but doesn't affect your main connection and any >databases that have already been attached. So what I do is > > PRAGMA main.journal_mode = OFF; > >IIRC, I needed this line to actually turn off the journals (tested >with 3.4.x and 3.5.x versions of SQLite only, though). > >Cheers, >Stefan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
> I tried that on sqlite3 version 3.6.6 and the return value was "OFF" > but the > journals are still being created. Any reason why this wouldn't work? Did you set PRAGMA journal_mode = OFF; ? The way I read the documentation (on a second or third close reading, I think), this only sets the default value for new databases to be attached, but doesn't affect your main connection and any databases that have already been attached. So what I do is PRAGMA main.journal_mode = OFF; IIRC, I needed this line to actually turn off the journals (tested with 3.4.x and 3.5.x versions of SQLite only, though). Cheers, Stefan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
I tried that on sqlite3 version 3.6.6 and the return value was "OFF" but the journals are still being created. Any reason why this wouldn't work? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 9:56 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal files On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: >>I am looking for a way to completely turn off the >> creation >> of journal files. Any help is much appreciated. > > http://www.sqlite.org/pragma.html And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ 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] Journal files
Does that control the creation of all temporary files created at runtime? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 7:56 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal files On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: >>I am looking for a way to completely turn off the >> creation >> of journal files. Any help is much appreciated. > > http://www.sqlite.org/pragma.html And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ 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] Journal files
On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: >>I am looking for a way to completely turn off the >> creation >> of journal files. Any help is much appreciated. > > http://www.sqlite.org/pragma.html And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
> I am looking for a way to completely turn off the creation > of journal files. Any help is much appreciated. http://www.sqlite.org/pragma.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Journal files
Hello all, I am looking for a way to completely turn off the creation of journal files. Any help is much appreciated. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] journal files
Dave Dyer <[EMAIL PROTECTED]> wrote: > How does sqlite distinguish between a journal file left over > from a crash, and a journal file that some other process is > still using? It checks for existence of locks on the main database file. For more details, see http://sqlite.org/atomiccommit.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files
How does sqlite distinguish between a journal file left over from a crash, and a journal file that some other process is still using? .. and if the answer is "try to open it yourself", then what about the race condition where process A has finished, and is about to delete the journal, but hasn't succeeded yet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal Files
"Mark Riehl" <[EMAIL PROTECTED]> wrote: > I'm working with version 3.5.2 under Linux. I've got a database that > is being shared between two processes and I'm running into issues with > the journal file that doesn't go away. When that happens, one process > appears to have the lock on the database and the other process is > essentially locked out. > > A few questions for you: > > 1. Is there a way to look at the contents of the journal file so that > I know which insert statements are currently queued up? This way, I > can try and work backwards from there and see what the issue is. If you are storing text data, I guess you could use "strings" too see bits and pieces of it. But it won't be in any recognizable order. > > 2. Is there a way to determine the current database state (unlocked, > shared, pending, reserved, exclusive) ? > http://www.sqlite.org/c3ref/c_fcntl_lockstate.html For additional background information one what SQLite does with locking and journal files, please see http://www.sqlite.org/atomiccommit.html http://www.sqlite.org/lockingv3.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Journal Files
I'm working with version 3.5.2 under Linux. I've got a database that is being shared between two processes and I'm running into issues with the journal file that doesn't go away. When that happens, one process appears to have the lock on the database and the other process is essentially locked out. A few questions for you: 1. Is there a way to look at the contents of the journal file so that I know which insert statements are currently queued up? This way, I can try and work backwards from there and see what the issue is. 2. Is there a way to determine the current database state (unlocked, shared, pending, reserved, exclusive) ? Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal files not deleted.
Guilty of extending this silly thread... On 4/12/07, Noah Hart <[EMAIL PROTECTED]> wrote: Not to quibble, but to quote from wipro's website "Only Indian company to be ranked among the top 10 global outsourcing providers in IAOP's 2006 Global Outsourcing 100 listing" you are indeed quibbling by pointing out this absolutely useless piece of trivia. For that matter, nowhere on Wipro's website do they say that they are NOT the largest SW IT companies, and I took Ravi's "NO" to not deny that they are not an outsourcing shop but that they are not _just_ an outsourcing shop. In any case, none of this has anything to do with what the poor bloke was asking in the first place. All he wanted to know was about the journal files that were not getting deleted. Here is the original messge -- The journal files created during the transactions are not getting deleted. These files are created per transaction or only one file per connection? I am seeing hundreds of journal files with a '-' suffix causing an error "Database disk full" (DB file is stored in flash card). Note : I am closing the file after every transaction. Something like this. Sqlite3_open(); Sqlite3_exec(); Sqlite3_close(); Sheesh... answer an email or, if not, at least make a funny observation, not one that makes others defensive. Else, just delete it and go your way. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 10:26 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] Journal files not deleted. NO, This is one of the largest IT (SW development) companies. Does this matter in anyways? Regards, Ravi K -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 8:41 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Journal files not deleted. [EMAIL PROTECTED] wrote: > www.wipro.com Would this be Wipro, the outsourcing company? Martin CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal files not deleted.
The original question was something about journal files not being deleted Nobody else has reported seeing this behavior. If you want help, you will have to give us additional information. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Journal files not deleted.
Not to quibble, but to quote from wipro's website "Only Indian company to be ranked among the top 10 global outsourcing providers in IAOP's 2006 Global Outsourcing 100 listing" NH -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 10:26 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] Journal files not deleted. NO, This is one of the largest IT (SW development) companies. Does this matter in anyways? Regards, Ravi K -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 8:41 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Journal files not deleted. [EMAIL PROTECTED] wrote: > www.wipro.com Would this be Wipro, the outsourcing company? Martin CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Journal files not deleted.
NO, This is one of the largest IT (SW development) companies. Does this matter in anyways? Regards, Ravi K -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 8:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Journal files not deleted. [EMAIL PROTECTED] wrote: > www.wipro.com Would this be Wipro, the outsourcing company? Martin - To unsubscribe, send email to [EMAIL PROTECTED] - The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal files not deleted.
[EMAIL PROTECTED] wrote: www.wipro.com Would this be Wipro, the outsourcing company? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Journal files not deleted.
Hi All, The journal files created during the transactions are not getting deleted. These files are created per transaction or only one file per connection? I am seeing hundreds of journal files with a '-' suffix causing an error "Database disk full" (DB file is stored in flash card). Note : I am closing the file after every transaction. Something like this. Sqlite3_open(); Sqlite3_exec(); Sqlite3_close(); Please help me in this regard. I am struck Thanks in advance Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: [sqlite] journal files and database corruption
On Jan 20, 2006, at 8:25 AM, Will Leshner wrote: Thanks for this information. It sounds like a reasonable precaution might be not to make "too many" changes to a database in one transaction. But I guess knowing how many changes is too many would be a pretty hard thing to figure out. Sorry to reply my own email, but I wonder if it might be useful to have some mechanism by which SQLite could report back when it has filled its cache and is about to spill it to disk? I'm not sure what a user would do with such a callback. It's probably unlikely that a commit could occur right at that spot because SQLite itself will be in the middle of doing something else. But maybe the callback could allow the user to abort the writing of the cache, and if that happened, it could be treated as some kind of fatal error that would basically abort the entire transaction.
Re: [sqlite] journal files and database corruption
On Jan 20, 2006, at 8:07 AM, [EMAIL PROTECTED] wrote: Deleting a hot journal after a power loss or OS crash is more likely to result in severe database corruption, but database corruption is possible from deleting a hot journal after an ordinary program crash. Thanks for this information. It sounds like a reasonable precaution might be not to make "too many" changes to a database in one transaction. But I guess knowing how many changes is too many would be a pretty hard thing to figure out. I'm mainly thinking about what happens if a customer doesn't realize what the journal file is and throws it away. Probably the best way to avoid that problem is to be sure to tell the customer not to do that :)
Re: [sqlite] journal files and database corruption
Will Leshner <[EMAIL PROTECTED]> wrote: > On Jan 20, 2006, at 7:41 AM, [EMAIL PROTECTED] wrote: > > > The database might be completely unusable. It depends on > > which writes completed and which had not at the time of the > > crash. > > Am I right in thinking that that would be more likely to happen in, > say, a hardware failure, and less likely to happen if the crash > occurs because of a bug in my code? In other words, if every SQLite > call returns successfully, is it unlikely that the database is in an > unusable state? > Deleting a hot journal after a power loss or OS crash is more likely to result in severe database corruption, but database corruption is possible from deleting a hot journal after an ordinary program crash. Here is one scenario for generating reasonably severe database corruption by crashing a program: (1) Start a transaction (2) Make *many* changes to the database - so many that the internal cache overflows and has to spill to disk. (3) Crash the program. (4) Delete the journal file Another way to get database corruption from a program crash is to crash the program during a COMMIT and then delete the journal. This might happen, for example, if one thread is doing a COMMIT and a different thread happens to segfault at the same time. (Yet another reason to not used threads!) -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] journal files and database corruption
On Jan 20, 2006, at 7:41 AM, [EMAIL PROTECTED] wrote: The database might be completely unusable. It depends on which writes completed and which had not at the time of the crash. Am I right in thinking that that would be more likely to happen in, say, a hardware failure, and less likely to happen if the crash occurs because of a bug in my code? In other words, if every SQLite call returns successfully, is it unlikely that the database is in an unusable state? Thanks.
Re: [sqlite] journal files and database corruption
Will Leshner <[EMAIL PROTECTED]> wrote: > In the article about locking and concurrency on the SQLite website, > where it talks about how to corrupt a SQLite database, it says that a > SQLite database can be corrupted if a hot journal file is missing > when SQLite reconnects to the database. What kind of corruption are > we talking about, exactly? Is it that the data in the database is > inconsistent, in the sense that only some of the data has been > written with no way to roll any of it back? Or is this a more serious > kind of corruption in which the database file becomes completely > unusable? I guess I'd always thought the latter, but after reading > through the article more carefully, I am now thinking it might be the > former. If it is the former, it might be useful to make that explicit > in the article. While having an inconsistent database is certainly a > BadThing, having a completely unusable database (one that SQLite > can't even open anymore) is far worse, in my opinion. > The database might be completely unusable. It depends on which writes completed and which had not at the time of the crash. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] journal files and database corruption
In the article about locking and concurrency on the SQLite website, where it talks about how to corrupt a SQLite database, it says that a SQLite database can be corrupted if a hot journal file is missing when SQLite reconnects to the database. What kind of corruption are we talking about, exactly? Is it that the data in the database is inconsistent, in the sense that only some of the data has been written with no way to roll any of it back? Or is this a more serious kind of corruption in which the database file becomes completely unusable? I guess I'd always thought the latter, but after reading through the article more carefully, I am now thinking it might be the former. If it is the former, it might be useful to make that explicit in the article. While having an inconsistent database is certainly a BadThing, having a completely unusable database (one that SQLite can't even open anymore) is far worse, in my opinion. Thanks.