SQLite Forum

Can -wal -shm files be deleted if -wal file is empty?
Login

Can -wal -shm files be deleted if -wal file is empty?

(1) By 6kEs4Majrd on 2020-07-08 15:35:15 [link] [source]

I see some -wal -shm files left on my disk when there are no sqlite3 processes running. The -wal file is empty. In this case, I can just delete the -wal and -shm files. Thanks.

(2.1) By Dan Kennedy (dan) on 2020-07-08 18:33:17 edited from 2.0 in reply to 1 [link] [source]

You can, if you are quite sure there are no libsqlite3 users at all accessing the databases. If there are processes with the db open and you delete these files, the db might become corrupted later on.

Safer is to use the command-line tool to open, query and then close the database. If there really are no other processes running, this should delete the *-wal and *-shm files. Something like the command:

sqlite3 databasefile.db "SELECT count(*) FROM sqlite_master;"

is enough.

(3) By Simon Slavin (slavin) on 2020-07-09 00:47:40 in reply to 2.1 [link] [source]

Can you quickly run through the possible causes for these files not being deleted ? Assume that the database is working properly, nothing unexpected happening in any apps, just for some reason these two files aren't getting deleted.

Now suppose the computer they're on is restarted, you do the SELECT thing in the command-line tool, and they still aren't being deleted. Is there any way SQLite could cause that ?

(4) By Keith Medcalf (kmedcalf) on 2020-07-09 01:14:02 in reply to 3 [link] [source]

Yes, you can cause that to occur.

I would say there is what we call "insufficient information for meaningful diagnostic".

Let's play 20 questions since apparently no useful information will be provided.

For example, I understand the scenario described is the default behaviour of SQLite3 version -47.34.554.394857 on OggaBoogaOS 42.7 when using the IllconceivedFS 2.7 filesystem over a hyperspace connection.

(5) By Larry Brasfield (LarryBrasfield) on 2020-07-09 01:46:29 in reply to 3 [link] [source]

The possible causes that could be quickly listed are presently listed in "The WAL File". Unless there is reason to believe that 6kEs4Majrd has caused that file, which is usually "deleted automatically when the last connection to the database closes", to linger in some manner not already listed, there is little to be gained (and much time to be wasted) in speculating how it might have happened.

I must confess mild amusement at the assumed condition, "nothing unexpected happening in any apps". If that's my expectation, the WAL file(s) is(are) deleted, just as the docs say will happen, when all of the applications using a SQLite DB in WAL journalling mode close all their open connections. In that same world, those apps always arrange for such eventual closing no matter what else happens. (This because their authors are knowledgeable and diligent.) And the computers running those applications have reliable battery backup and orderly shutdown arranged to happen when line power is absent long enough to nearly deplete the battery. After such computing sessions are over, it's sunny outside but not too hot, and nice, non-farting unicorns gaily prance about, and harps can be heard. Zzzzz.

(6) By anonymous on 2020-07-09 07:41:41 in reply to 3 [link] [source]

My cause for this to happen:

I have git configured to use "sqldiff" for "*.sqlite" files. Whenever a comparison occurs, WAL and SHM files are left behind. OS is Win10.

(7) By Keith Medcalf (kmedcalf) on 2020-07-09 09:37:58 in reply to 6 [link] [source]

And what is git doing when it runs sqldiff -- that is, what command line is it using, and how is it running the child process?

Windows (all versions up the the current version) will ABEND (ABnormally END as in kill it by simply deleting the process) a process which produces output to either stderr or stdout which fills the pipe buffer if the parent is not "reading" from its end of the pipe (technically an I/O error is signalled, but the console mode application has no way to receive the signal and can do nothing about it anyway, so it just crashes).

This means that if the database is in WAL mode and has created WAL and SHM files that when it is simply terminated without the opportunity to properly wipe up after itself, then the journal files will be left behind. They are empty because the database was only read (not updated) so there has been no updated pages written to the WAL journal.

This is most often seen when attempting to convert "Windows Console" applications to run as services because the service manager does provide hooks for stdin/stdout/stderr but does not just redirect them to/from /dev/nul but attaches them to "dead end pipes". Reading stdin returns EOF but you can write to stdout/stderr and when the pipe buffer fills windows signals an I/O error which the program cannot handle causing it to crash.

I suspect that you would need to tell git to spawn the process and produce output to some temporary file in which it expects to find the output and use the --changeset <filename> option to tell sqldiff to write its output to that file.

It is also possible, I suppose, that git is reading the output but something is causing it to terminate the child rather than allowing it to exit normally.

(8.1) By Keith Medcalf (kmedcalf) on 2020-07-09 10:14:08 edited from 8.0 in reply to 6 [link] [source]

And what is git doing when it runs sqldiff -- that is, what command line is it using, and how is it running the child process?

Windows (all versions up the the current version) will ABEND (ABnormally END as in kill it by simply deleting the process) a process which produces output to either stderr or stdout which fills the pipe buffer if the parent is not "reading" from its end of the pipe (technically an I/O error is signalled, but the console mode application has no way to receive the signal and can do nothing about it anyway, so it just crashes).

This means that if the database is in WAL mode and has created WAL and SHM files that when it is simply terminated without the opportunity to properly wipe up after itself, then the journal files will be left behind. They are empty because the database was only read (not updated) so there has been no updated pages written to the WAL journal. (So in this particular case you can just delete them -- however they are an indication that something is not working quite right)

This is most often seen when attempting to convert "Windows Console" applications to run as services because the service manager does provide hooks for stdin/stdout/stderr but does not just redirect them to/from /dev/nul but attaches them to "dead end pipes". Reading stdin returns EOF but you can write to stdout/stderr and when the pipe buffer fills windows signals an I/O error which the program cannot handle causing it to crash.

I suspect that you would need to tell git to spawn the process and produce output to some temporary file in which it expects to find the output and use the --changeset <filename> option to tell sqldiff to write its output to that file.

It is also possible, I suppose, that git is reading the output but something is causing it to terminate the child rather than allowing it to exit normally.

(10) By 6kEs4Majrd on 2020-07-09 13:15:20 in reply to 3 [link] [source]

See my other reply. It is due to syncing problems of the database files. I always only update the db at one location. There may also be improper shutdown of the machine involved (but I am not very sure). So it could be 1) update the db at location A, 2) synch (which will copy -wal and -shm to location B), 3) update the db at location B.

(9) By 6kEs4Majrd on 2020-07-09 13:07:57 in reply to 2.1 [link] [source]

The problem is that I have the -wal -shm files on two different locations which are synced. The -shm files are out of synce, but -wal files are both empty. And I am sure there are no running sqlite3 processes accessing the databases on either locations.

In this case, I am not sure that updating the database files using the command that you mentioned on two locations makes sense. So just deleting the -wal and -shm files should be sufficient?

(11) By Larry Brasfield (LarryBrasfield) on 2020-07-09 14:02:32 in reply to 9 [link] [source]

It makes no sense to be syncing the DB, wal and shm files unless they represent a snapshot in time of the file state for all three. And unless that is arranged, it makes little sense to be syncing the DB file alone when (obviously) that is done while the wal and shm files exist.

Dan's advice, applied to the three files where SQLite has created them, is sensible, safe and simple. It is probably simpler than testing to see whether the wal file is empty and conditionally handling the results. That, together with not syncing the wal and shm files, (which could foul things up when the improperly synced copies are used, if they ever are), would be a complete, safe solution.

The smart solution would be to put an end to whatever sequence of events is routinely causing some application(s) using SQLite to either neglect closing all DB connections or be terminated before doing so.

(12) By Dan Kennedy (dan) on 2020-07-09 14:07:17 in reply to 9 [link] [source]

Sufficient for what?

If you're sure of those things you mention, you can delete the *-wal and *-shm file. But if you're incorrect and delete them, the database may become corrupt.

If you use the shell tool to clean up the *-wal and *-shm files, it will simply delete them if the *-wal file really is empty and unused. But if it is not, then the shell tool will not corrupt the db. So usually it's better to do it that way.

(13) By 6kEs4Majrd on 2020-07-09 14:18:49 in reply to 12 [link] [source]

But wouldn't it cause the main db file to be out-of-synch? At this moment, the main db file are the same in the two locations.

(14) By 6kEs4Majrd on 2020-07-09 14:22:48 in reply to 2.1 [link] [source]

It seems that when I run sqlite3 databasefile.db "SELECT count(*) FROM sqlite_master;", it just delete -shm and -wal file without touching the main db file.

Given -wal is empty, is that command guaranteed to be equivalent to manually deleting -wal and -shm? If not, under what condition they are not equivalent? Thanks.

(15) By Dan Kennedy (dan) on 2020-07-09 14:39:29 in reply to 14 [link] [source]

Not equivalent if another process has the db open.

(16) By 6kEs4Majrd on 2020-07-09 16:24:32 in reply to 15 [link] [source]

As I mentioned before, there is no other processing accesing the db. So they are equivalent?

(22) By Simon Slavin (slavin) on 2020-07-10 17:18:55 in reply to 16 [link] [source]

The power of using the SELECT method is that before it deletes the -wal and -shm files it checks to see whether SQLite thinks they are in use. This is useful in making sure nothing has crashed while using the database. You can delete the files yourself, but if any process accessing the database has crashed, or some process still has the database open, you may lose changes.

(17) By 6kEs4Majrd on 2020-07-09 16:26:30 in reply to 2.1 [link] [source]

Is there a way to suppress the output of sqlite3 databasefile.db "SELECT count(*) FROM sqlite_master;"?

I tried .output /dev/null; SELECT count(*) FROM sqlite_master;. But it is not working. Could you show me the correct way to suppress the output? (I don't want to use shell redirection. I want to use sqlite3's feature to suppress the output.)

(18) By Larry Brasfield (LarryBrasfield) on 2020-07-09 16:30:26 in reply to 17 [link] [source]

"SELECT count(*) FROM sqlite_master LIMIT 0;"

(19.1) By Keith Medcalf (kmedcalf) on 2020-07-09 18:58:58 edited from 19.0 in reply to 18 [link] [source]

Don't forget -batch for the greater certainty. So

sqlite3 database.db -batch "select count(*) from sqlite_master limit 0"

(20) By Larry Brasfield (LarryBrasfield) on 2020-07-09 19:13:20 in reply to 19.1 [link] [source]

That's seriously amusing.

FWIW, I almost said the same thing (without the "greater certainty" bit), but decided this was a case where answering just the question asked was a better bet. Of course, I had no opportunity quite like the one you seized. Touche!

(21) By Keith Medcalf (kmedcalf) on 2020-07-09 20:37:50 in reply to 20 [source]

Hehehe. That's because I have a .sqliterc and without the -batch you get output saying that it was processed. -batch squelches that output although the .sqliterc is still processed.