[sqlite] journal files not always removed

2015-09-10 Thread J Decker
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

2015-09-01 Thread J Decker
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

2015-08-15 Thread Ashwin Hirschi

> 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

2015-08-15 Thread Ashwin Hirschi

> 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

2015-08-15 Thread Ashwin Hirschi

>> 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

2015-08-15 Thread R.Smith
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

2015-08-15 Thread R.Smith
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

2015-08-15 Thread Ashwin Hirschi

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

2015-08-15 Thread Richard Hipp
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

2015-08-15 Thread Richard Hipp
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

2015-08-15 Thread Richard Hipp
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

2015-08-15 Thread Richard Hipp
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

2015-08-15 Thread Keith Medcalf

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

2015-08-15 Thread Keith Medcalf

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