wal

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

wal

ingo
From the docs,

"The WAL journaling mode uses a write-ahead log instead of a rollback
journal to implement transactions. The WAL journaling mode is
persistent; after being set it stays in effect across multiple database
connections and after closing and reopening the database."

When using 'single shot' access to the database, with no other
connections, I see a wal file being created and deleted. Just for my
understanding, would it be of advantage to have a second persistent
connection just for keeping the wal alive?

(I have no real world scenario for this, I just saw this happen and
wondered while setting up SQLTools on Sublime for SQLite. It
doesn't/can't create a persistent connection)

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

Re: wal

Andy Bennett
Hi,

THe most persists after it has been set.
The file comes and goes as needed. I seem to remember there are some
caveats in the manual about what directory permissions are required and
what happens when you can write the journal file and the database file
but not the directory that contains them.


> "The WAL journaling mode uses a write-ahead log instead of a rollback
> journal to implement transactions. The WAL journaling mode is
> persistent; after being set it stays in effect across multiple database
> connections and after closing and reopening the database."
>
> When using 'single shot' access to the database, with no other
> connections, I see a wal file being created and deleted. Just for my
> understanding, would it be of advantage to have a second persistent
> connection just for keeping the wal alive?
>
> (I have no real world scenario for this, I just saw this happen and
> wondered while setting up SQLTools on Sublime for SQLite. It
> doesn't/can't create a persistent connection)
>
> Ingo
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
[hidden email]
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wal

Warren Young
In reply to this post by ingo
On Jun 28, 2019, at 2:12 AM, ingo <[hidden email]> wrote:
>
> I see a wal file being created and deleted. Just for my
> understanding, would it be of advantage to have a second persistent
> connection just for keeping the wal alive?

You’ve basically got it backwards.

It’s a *good thing* when the WAL file disappears: it means the prior connection was able to push everything from the WAL into the DB file, so the WAL isn’t needed on the next DB conn until more writes are attempted.

Conversely, if the last DB conn closes and the WAL is left behind, it means someone didn’t clean SQLite up nicely, so on the first subsequent connection to the DB, that SQLite instance will start trying to recover.  A WAL file left behind is a sign of a problem in the app which should be corrected.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wal

Thomas Kurz
> A WAL file left behind is a sign of a problem in the app which should be corrected.

I have exactly this problem and don't like the SHM and WAL files being left behind. I have even tried "pragma wal_checkpoint(full)" before closing the connection, but there are still situations where the WAL file remains.

Is there anything else I can do ensure that these files are removed?

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

Re: wal

ingo
In reply to this post by Warren Young
Haha :) Thanks for the insight. Hadn't looked at it that way.

Ingo

On 28-6-2019 11:52, Warren Young wrote:
> You’ve basically got it backwards.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wal

Keith Medcalf
In reply to this post by Thomas Kurz

On Friday, 28 June, 2019 07:37, Thomas Kurz <[hidden email]> wrote:

>> A WAL file left behind is a sign of a problem in the app which
>should be corrected.

>I have exactly this problem and don't like the SHM and WAL files
>being left behind. I have even tried "pragma wal_checkpoint(full)"
>before closing the connection, but there are still situations where
>the WAL file remains.

>Is there anything else I can do ensure that these files are removed?

https://sqlite.org/c3ref/close.html

This would mean that you have not finalized all prepared statements, closed all blob handles, and finished all backups -- ie, that you are exiting the program while there are still open objects which are connected to the database (ie, you are in effect crashing the program, not exiting cleanly).

Unless of course you have set the sqlite_db_config option that does not checkpoint on close ...

Change the sqlite3_close_v2 to sqlite3_close and check the return code is SQLITE_OK.  If not, you can use sqlite3_next_stmt to find the statements that are not finalized and try again.

https://sqlite.org/c3ref/next_stmt.html


--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users