[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
