Quantcast

-shm and -wal files left after trivially simple select statement from command line

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

-shm and -wal files left after trivially simple select statement from command line

Rob Willett
Hi,

We've encountered what we think is an odd situation and we can't find
any explanation for why this is. We're also not sure if its a problem or
not.

A brief summary is that we are doing a major database upgrade and are
doing (lots) of testing. The box is a Linux box running Ubuntu 14.04 LTS
Server. This is a standard box we have used for a few years, we *might*
move to 16 LTS shortly.

As part of the testing we noticed that -shm and -wal files were being
left after we used sqlite3 on the command line. This puzzled us as we
didn't see any errors in our test scripts.

We then narrowed this problem down to doing a simple SELECT statement on
a table.

e.g. This is our starting state. Note that nothing is touching these
database as they are isolated in a directory, so there is no other
process playing with them.

root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66892
-rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
-rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38
accountmanagement.sqlite.orig

root@demonotifications:/jambuster/notifications/upgrade_test# cp
accountmanagement.sqlite.orig accountmanagement.sqlite

root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3
accountmanagement.sqlite
-- Loading resources from /root/.sqliterc

SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from device;
UUID                                                 isValid     Version
     WhatAmI     Application
---------------------------------------------------  ----------  
----------  ----------  ------------------
<<REDACTED>                                          1           1.1    
     ios_app     JambusterForLondon
...... Hundreds more lines of device data which looks correct
<<REDACTED>                                          1           1.1    
     ios_app     JambusterForLondon

sqlite> .exit
root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66924
-rw-r--r-- 1 root root    32768 May 15 10:48
accountmanagement.sqlite-shm
-rw-r--r-- 1 root root        0 May 15 10:48
accountmanagement.sqlite-wal
-rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
-rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38
accountmanagement.sqlite.orig
root@demonotifications:/jambuster/notifications/upgrade_test


As can be seen above, we have accountmanagement.sqlite-shm and
accountmanagement.sqlite-wal files left which is odd. All of this from a
select statement.

If we do an pragma integrity check we get

   integrity_check
   ---------------
   ok

All the reading we have done seems to indicate that having -shm and -wal
files indicates an error, if this was our code, we'd be looking deep
into that but a simple select statement shouldn't cause this sort or
error, can it?

The select statement has a number of foreign key delete cascades but
thats it, and as we are not deleting anything this shouldn't be an issue
anyway.

Do we even have a problem? Going back into the database and closing it
again, just with the command line utility sqlite3 doesn't clear the
files.

Any help or suggestions welcomed.

Thanks

Rob
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: -shm and -wal files left after trivially simple select statement from command line

J Decker
that was an old issue... current sqlite version do not have this issue.
You MUST close the connection though, for the files to get deleted.

http://sqlite.1065341.n5.nabble.com/journal-files-not-always-removed-td83700.html#a83705

On Mon, May 15, 2017 at 3:01 AM, Rob Willett <[hidden email]>
wrote:

> Hi,
>
> We've encountered what we think is an odd situation and we can't find any
> explanation for why this is. We're also not sure if its a problem or not.
>
> A brief summary is that we are doing a major database upgrade and are
> doing (lots) of testing. The box is a Linux box running Ubuntu 14.04 LTS
> Server. This is a standard box we have used for a few years, we *might*
> move to 16 LTS shortly.
>
> As part of the testing we noticed that -shm and -wal files were being left
> after we used sqlite3 on the command line. This puzzled us as we didn't see
> any errors in our test scripts.
>
> We then narrowed this problem down to doing a simple SELECT statement on a
> table.
>
> e.g. This is our starting state. Note that nothing is touching these
> database as they are isolated in a directory, so there is no other process
> playing with them.
>
> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
> total 66892
> -rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
> -rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
> -rw-r--r-- 1 root root 34217984 May 13 20:38 accountmanagement.sqlite.orig
>
> root@demonotifications:/jambuster/notifications/upgrade_test# cp
> accountmanagement.sqlite.orig accountmanagement.sqlite
>
> root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3
> accountmanagement.sqlite
> -- Loading resources from /root/.sqliterc
>
> SQLite version 3.8.2 2013-12-06 14:53:30
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select * from device;
> UUID                                                 isValid     Version
>    WhatAmI     Application
> ---------------------------------------------------  ----------
> ----------  ----------  ------------------
> <<REDACTED>                                          1           1.1
>    ios_app     JambusterForLondon
> ...... Hundreds more lines of device data which looks correct
> <<REDACTED>                                          1           1.1
>    ios_app     JambusterForLondon
>
> sqlite> .exit
> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
> total 66924
> -rw-r--r-- 1 root root    32768 May 15 10:48 accountmanagement.sqlite-shm
> -rw-r--r-- 1 root root        0 May 15 10:48 accountmanagement.sqlite-wal
> -rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
> -rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
> -rw-r--r-- 1 root root 34217984 May 13 20:38 accountmanagement.sqlite.orig
> root@demonotifications:/jambuster/notifications/upgrade_test
>
>
> As can be seen above, we have accountmanagement.sqlite-shm and
> accountmanagement.sqlite-wal files left which is odd. All of this from a
> select statement.
>
> If we do an pragma integrity check we get
>
>   integrity_check
>   ---------------
>   ok
>
> All the reading we have done seems to indicate that having -shm and -wal
> files indicates an error, if this was our code, we'd be looking deep into
> that but a simple select statement shouldn't cause this sort or error, can
> it?
>
> The select statement has a number of foreign key delete cascades but thats
> it, and as we are not deleting anything this shouldn't be an issue anyway.
>
> Do we even have a problem? Going back into the database and closing it
> again, just with the command line utility sqlite3 doesn't clear the files.
>
> Any help or suggestions welcomed.
>
> Thanks
>
> Rob
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: -shm and -wal files left after trivially simple select statement from command line

Rob Willett
Thanks for this. This seems to be the old version of SQLite that comes
with Ubuntu. We'll upgrade.

Just to be clear we do close the connection as all we do is open the
database using sqlite3 from the command line, do a select and close
sqlite3 from the command line.

We do not do anything that modifies the database.

We'll have to upgrade the version of SQLite, test and then upgrade our
database.

Thanks

Rob

On 15 May 2017, at 12:09, J Decker wrote:

> that was an old issue... current sqlite version do not have this
> issue.
> You MUST close the connection though, for the files to get deleted.
>
> http://sqlite.1065341.n5.nabble.com/journal-files-not-always-removed-td83700.html#a83705
>
> On Mon, May 15, 2017 at 3:01 AM, Rob Willett
> <[hidden email]>
> wrote:
>
>> Hi,
>>
>> We've encountered what we think is an odd situation and we can't find
>> any
>> explanation for why this is. We're also not sure if its a problem or
>> not.
>>
>> A brief summary is that we are doing a major database upgrade and are
>> doing (lots) of testing. The box is a Linux box running Ubuntu 14.04
>> LTS
>> Server. This is a standard box we have used for a few years, we
>> *might*
>> move to 16 LTS shortly.
>>
>> As part of the testing we noticed that -shm and -wal files were being
>> left
>> after we used sqlite3 on the command line. This puzzled us as we
>> didn't see
>> any errors in our test scripts.
>>
>> We then narrowed this problem down to doing a simple SELECT statement
>> on a
>> table.
>>
>> e.g. This is our starting state. Note that nothing is touching these
>> database as they are isolated in a directory, so there is no other
>> process
>> playing with them.
>>
>> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
>> total 66892
>> -rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
>> -rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
>> -rw-r--r-- 1 root root 34217984 May 13 20:38
>> accountmanagement.sqlite.orig
>>
>> root@demonotifications:/jambuster/notifications/upgrade_test# cp
>> accountmanagement.sqlite.orig accountmanagement.sqlite
>>
>> root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3
>> accountmanagement.sqlite
>> -- Loading resources from /root/.sqliterc
>>
>> SQLite version 3.8.2 2013-12-06 14:53:30
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> select * from device;
>> UUID                                                 isValid    
>> Version
>>    WhatAmI     Application
>> ---------------------------------------------------  ----------
>> ----------  ----------  ------------------
>> <<REDACTED>                                          1           1.1
>>    ios_app     JambusterForLondon
>> ...... Hundreds more lines of device data which looks correct
>> <<REDACTED>                                          1           1.1
>>    ios_app     JambusterForLondon
>>
>> sqlite> .exit
>> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
>> total 66924
>> -rw-r--r-- 1 root root    32768 May 15 10:48
>> accountmanagement.sqlite-shm
>> -rw-r--r-- 1 root root        0 May 15 10:48
>> accountmanagement.sqlite-wal
>> -rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
>> -rw-r--r-- 1 root root    58567 May 15 10:31 upgradedb.sql
>> -rw-r--r-- 1 root root 34217984 May 13 20:38
>> accountmanagement.sqlite.orig
>> root@demonotifications:/jambuster/notifications/upgrade_test
>>
>>
>> As can be seen above, we have accountmanagement.sqlite-shm and
>> accountmanagement.sqlite-wal files left which is odd. All of this
>> from a
>> select statement.
>>
>> If we do an pragma integrity check we get
>>
>>   integrity_check
>>   ---------------
>>   ok
>>
>> All the reading we have done seems to indicate that having -shm and
>> -wal
>> files indicates an error, if this was our code, we'd be looking deep
>> into
>> that but a simple select statement shouldn't cause this sort or
>> error, can
>> it?
>>
>> The select statement has a number of foreign key delete cascades but
>> thats
>> it, and as we are not deleting anything this shouldn't be an issue
>> anyway.
>>
>> Do we even have a problem? Going back into the database and closing
>> it
>> again, just with the command line utility sqlite3 doesn't clear the
>> files.
>>
>> Any help or suggestions welcomed.
>>
>> Thanks
>>
>> Rob
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: -shm and -wal files left after trivially simple select statement from command line

Richard Hipp-3
In reply to this post by Rob Willett
On 5/15/17, Rob Willett <[hidden email]> wrote:
>
> As part of the testing we noticed that -shm and -wal files were being
> left after we used sqlite3 on the command line. This puzzled us as we
> didn't see any errors in our test scripts.

How is the command being ended.  Are you inserting a ".quit" command?
Are you somehow causing the shell to terminate prematurely, perhaps by
piping the output into a filter (such as "head") that closes the input
connection early?  What version of the SQLite shell is running?  What
is the query?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: -shm and -wal files left after trivially simple select statement from command line

Richard Hipp-3
On 5/15/17, Richard Hipp <[hidden email]> wrote:

> On 5/15/17, Rob Willett <[hidden email]> wrote:
>>
>> As part of the testing we noticed that -shm and -wal files were being
>> left after we used sqlite3 on the command line. This puzzled us as we
>> didn't see any errors in our test scripts.
>
> How is the command being ended.  Are you inserting a ".quit" command?
> Are you somehow causing the shell to terminate prematurely, perhaps by
> piping the output into a filter (such as "head") that closes the input
> connection early?  What version of the SQLite shell is running?  What
> is the query?

Never mind - it appears that JDecker already knew the problem and the
solution....

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: -shm and -wal files left after trivially simple select statement from command line

Rob Willett
Richard, J Decker,

Just to close this down.

The command was

select * from device;

We used .quit and Ctrl-D to exit the shell

The command sequence was as simple as

sqlite3 DATABASE.sqlite

We knew about things like less and head and so we avoided them.

The version of Sqlite seems to be the problem, the standard one with
Ubuntu 14.04 LTS is 3.8.2 which we know is old, but until now seems to
have been fine.

Upgrading our production servers to Ubuntu 16 LTS is a massive
undertaking. We have nine of them with test and preprod so upgrading and
testing everything again is many, many weeks work :(

We've upgraded Sqlite CLI to the latest 3.18.0 and are trying this out
in a nice safe environment.

Thanks

Rob

On 15 May 2017, at 12:38, Richard Hipp wrote:

> On 5/15/17, Richard Hipp <[hidden email]> wrote:
>> On 5/15/17, Rob Willett <[hidden email]> wrote:
>>>
>>> As part of the testing we noticed that -shm and -wal files were
>>> being
>>> left after we used sqlite3 on the command line. This puzzled us as
>>> we
>>> didn't see any errors in our test scripts.
>>
>> How is the command being ended.  Are you inserting a ".quit" command?
>> Are you somehow causing the shell to terminate prematurely, perhaps
>> by
>> piping the output into a filter (such as "head") that closes the
>> input
>> connection early?  What version of the SQLite shell is running?  What
>> is the query?
>
> Never mind - it appears that JDecker already knew the problem and the
> solution....
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...