Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Richard,

Many thanks for your input. My ideas about how sqlite works were indeed 
incorrect. 

When I say "re-opening" the database, I mean opening it and doing a few SELECT 
to it (as described in a previous email). So I do read it (especially the 
sqlite_master table), and still the journal file stays there. 

Max suggested that it might be normal behaviour... Please also note that I now 
do a PRGMA to force the journal mode to DELETE (although I am pretty sure this 
is what it was by default...)

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp <d...@sqlite.org>
Sender: drhsql...@gmail.com
Date: Wed, 26 Feb 2014 05:40:27 
To: <ftrib...@falcon-one.com>; General Discussion of SQLite 
Database<sqlite-users@sqlite.org>
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss

On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix <ftrib...@falcon-one.com>wrote:

> Hi Simon,
>
> If I run my program step-by-step, I can see that the sqlite database is
> not modified when I do an INSERT. It is modified only when I do a COMMIT.
>

Correct.  Changes are normally kept in memory (unless the change set grows
too large) until you COMMIT.  This allows other concurrent processes to
continue reading the unmodified database file.


>
> I read the page on the sqlite website detailing how sqlite deals with
> crashes and untimely power losses, and it says that the journal file is
> created just before modifying the database and is a copy of the areas that
> are to be modified. Once the journal file is committed to the disk, the
> database is modified.
> But this is not what I can see. What I can see is that the database file
> is not modified when I do the INSERT, it is modified only when I do the
> COMMIT.
>

Perhaps your mental model of INSERT and COMMIT is wrong.  The abstraction
that SQL (all SQL databases, not just SQLite) tries to present is this:
INSERT does not modify an SQL database; it merely queues up changes to be
made atomically when you COMMIT.


>
> And even then, that would not explain why the journal file lingers after
> re-opening the database.
>

Simply calling sqlite3_open() does not "re-open" a database.  It merely
saves the filename so that the database can be opened later when needed.
The "re-open" does not occur until you first try to read from the database
file.  The rollback journal is replayed and deleted at that point.


>
> Could anyone shed some light on the above?
>
> Thank you very much for any help!
>
> Best regards,
>
>   Fabrice
>
> --Original Message------
> From: Simon Slavin
> To: Fabrice Triboix
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Question about how sqlite recovers after a power loss
> Sent: 18 Feb 2014 08:57
>
>
> On 18 Feb 2014, at 8:13am, Fabrice Triboix <ftrib...@falcon-one.com>
> wrote:
>
> > Actually, the journal file does not get deleted when I do a SELECT just
> after opening the database.
>
> Depending on your journal mode (I think), the journal file should be
> deleted, but then it will immediately be recreated.  Are you sure that this
> is not what's happening ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Richard Hipp]

On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix <ftrib...@falcon-one.com>wrote:

> Hi Simon,
>
> If I run my program step-by-step, I can see that the sqlite database is
> not modified when I do an INSERT. It is modified only when I do a COMMIT.
>

Correct.  Changes are normally kept in memory (unless the change set grows
too large) until you COMMIT.  This allows other concurrent processes to
continue reading the unmodified database file.


>
> I read the page on the sqlite website detailing how sqlite deals with
> crashes and untimely power losses, and it says that the journal file is
> created just before modifying the database and is a copy of the areas that
> are to be modified. Once the journal file is committed to the disk, the
> database is modified.
> But this is not what I can see. What I can see is that the database file
> is not modified when I do the INSERT, it is modified only when I do the
> COMMIT.
>

Perhaps your mental model of INSERT and COMMIT is wrong.  The abstraction
that SQL (all SQL databases, not just SQLite) tries to present is this:
INSERT does not modify an SQL database; it merely queues up changes to be
made atomically when you COMMIT.


>
> And even then, that would not explain why the journal file lingers after
> re-opening the database.
>

Simply calling sqlite3_open() does not "re-open" a database.  It merely
saves the filename so that the database can be opened later when needed.
The "re-open" does not occur until you first try to read from the database
file.  The rollback journal is replayed and deleted at that point.


>
> Could anyone shed some light on the above?
>
> Thank you very much for any help!
>
> Best regards,
>
>   Fabrice
>
> --Original Message------
> From: Simon Slavin
> To: Fabrice Triboix
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Question about how sqlite recovers after a power loss
> Sent: 18 Feb 2014 08:57
>
>
> On 18 Feb 2014, at 8:13am, Fabrice Triboix <ftrib...@falcon-one.com>
> wrote:
>
> > Actually, the journal file does not get deleted when I do a SELECT just
> after opening the database.
>
> Depending on your journal mode (I think), the journal file should be
> deleted, but then it will immediately be recreated.  Are you sure that this
> is not what's happening ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Max,

Many thanks for your input. This is probably how it works. 

Best regards,

  Fabrice

--Original Message--
From: Max Vlasov
To: Fabrice Triboix
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss
Sent: 26 Feb 2014 10:24

On Wed, Feb 26, 2014 at 12:33 PM, Fabrice Triboix
<ftrib...@falcon-one.com> wrote:
>
> And even then, that would not explain why the journal file lingers after 
> re-opening the database.
>

I remember asking a similar question. As long as I remember, the main
logical implication is that journal file presence is not a mark to
force database into some actions. It's rather a supplement for the
error state of the base. So if the base is in error state and there's
no journal file, it's bad. But reverse is ok and might appear with
some scenarios.

Max


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Max Vlasov]

On Wed, Feb 26, 2014 at 12:33 PM, Fabrice Triboix
 wrote:
>
> And even then, that would not explain why the journal file lingers after 
> re-opening the database.
>

I remember asking a similar question. As long as I remember, the main
logical implication is that journal file presence is not a mark to
force database into some actions. It's rather a supplement for the
error state of the base. So if the base is in error state and there's
no journal file, it's bad. But reverse is ok and might appear with
some scenarios.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Simon,

If I run my program step-by-step, I can see that the sqlite database is not 
modified when I do an INSERT. It is modified only when I do a COMMIT. 

I read the page on the sqlite website detailing how sqlite deals with crashes 
and untimely power losses, and it says that the journal file is created just 
before modifying the database and is a copy of the areas that are to be 
modified. Once the journal file is committed to the disk, the database is 
modified. 
But this is not what I can see. What I can see is that the database file is not 
modified when I do the INSERT, it is modified only when I do the COMMIT. 

And even then, that would not explain why the journal file lingers after 
re-opening the database. 

Could anyone shed some light on the above?

Thank you very much for any help!

Best regards,

  Fabrice

--Original Message--
From: Simon Slavin
To: Fabrice Triboix
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss
Sent: 18 Feb 2014 08:57


On 18 Feb 2014, at 8:13am, Fabrice Triboix <ftrib...@falcon-one.com> wrote:

> Actually, the journal file does not get deleted when I do a SELECT just after 
> opening the database. 

Depending on your journal mode (I think), the journal file should be deleted, 
but then it will immediately be recreated.  Are you sure that this is not 
what's happening ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Simon Slavin]

On 18 Feb 2014, at 8:13am, Fabrice Triboix  wrote:

> Actually, the journal file does not get deleted when I do a SELECT just after 
> opening the database. 

Depending on your journal mode (I think), the journal file should be deleted, 
but then it will immediately be recreated.  Are you sure that this is not 
what's happening ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Richard,

Actually, the journal file does not get deleted when I do a SELECT just after 
opening the database. 

Why is that? 

NB: I do not set/modify any PRAGMA at all. 

Many thanks for your help!

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp <d...@sqlite.org>
Sender: drhsql...@gmail.com
Date: Sat, 15 Feb 2014 09:57:03 
To: <ftrib...@falcon-one.com>; General Discussion of SQLite 
Database<sqlite-users@sqlite.org>
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss

On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix <ftrib...@falcon-one.com>wrote:

> Hi Richard,
>
> All right, many thanks for that. So if I do, say, a SELECT just after
> opening the database, that's when the recovery will actually take place and
> the journal file will be replayed and deleted.
> Is my understanding correct?
>

yes.  "SELECT count(*) FROM sqlite_master" is a good query to run for this.

-- 
D. Richard Hipp
d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Kees Nuyt]

On Sat, 15 Feb 2014 14:55:48 +, "Fabrice Triboix"
 wrote:

> Hi Richard,
>
> All right, many thanks for that. So if I do, say, 
> a SELECT just after opening the database, that's
> when the recovery will actually take place and
> the journal file will be replayed and deleted. 
>
> Is my understanding correct?

Yes.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Richard Hipp]

On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix wrote:

> Hi Richard,
>
> All right, many thanks for that. So if I do, say, a SELECT just after
> opening the database, that's when the recovery will actually take place and
> the journal file will be replayed and deleted.
> Is my understanding correct?
>

yes.  "SELECT count(*) FROM sqlite_master" is a good query to run for this.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Richard,

All right, many thanks for that. So if I do, say, a SELECT just after opening 
the database, that's when the recovery will actually take place and the journal 
file will be replayed and deleted. 
Is my understanding correct?

Many thanks for your help!

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp <d...@sqlite.org>
Sender: sqlite-users-bounces@sqlite.orgDate: Sat, 15 Feb 2014 08:07:57 
To: General Discussion of SQLite Database<sqlite-users@sqlite.org>
Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss

On Fri, Feb 14, 2014 at 10:13 AM, <ftrib...@falcon-one.com> wrote:

> Hello,
>
> I would like to understand better at which moment does sqlite recovers
> from a previous power loss.
>
> Let's take the following scenario:
>  - sqlite3_open()
>  - BEGIN TRANSACTION;
>  - INSERT INTO SomeTable VALUES ("blah", "blah", blah");
>  => Power loss!
>  - sqlite3_open()
>
> What I notice, is that the 2nd sqlite3_open() succeeds, but the journal
> file is left intact and not deleted.
>
> I would like to know at what moment is the journal file actually deleted
> after a power loss.
>

When you first access the database.  sqlite3_open() does not actually open
the database file.  It just queues up the information.  The real open is
deferred until you actually try to read something out of the database.

The delay of open gives  the application time to run PRAGMA statements to
further setup the connection.  In particular, it allows the "key" PRAGMA to
run to set the encryption key for encrypted database files, which would
otherwise be unreadable.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about how sqlite recovers after a power loss

[Richard Hipp]

On Fri, Feb 14, 2014 at 10:13 AM,  wrote:

> Hello,
>
> I would like to understand better at which moment does sqlite recovers
> from a previous power loss.
>
> Let's take the following scenario:
>  - sqlite3_open()
>  - BEGIN TRANSACTION;
>  - INSERT INTO SomeTable VALUES ("blah", "blah", blah");
>  => Power loss!
>  - sqlite3_open()
>
> What I notice, is that the 2nd sqlite3_open() succeeds, but the journal
> file is left intact and not deleted.
>
> I would like to know at what moment is the journal file actually deleted
> after a power loss.
>

When you first access the database.  sqlite3_open() does not actually open
the database file.  It just queues up the information.  The real open is
deferred until you actually try to read something out of the database.

The delay of open gives  the application time to run PRAGMA statements to
further setup the connection.  In particular, it allows the "key" PRAGMA to
run to set the encryption key for encrypted database files, which would
otherwise be unreadable.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about how sqlite recovers after a power loss

[ftriboix]

Hello,

I would like to understand better at which moment does sqlite recovers
from a previous power loss.

Let's take the following scenario:
 - sqlite3_open()
 - BEGIN TRANSACTION;
 - INSERT INTO SomeTable VALUES ("blah", "blah", blah");
 => Power loss!
 - sqlite3_open()

What I notice, is that the 2nd sqlite3_open() succeeds, but the journal
file is left intact and not deleted.

I would like to know at what moment is the journal file actually deleted
after a power loss.

Many thanks!

  Fabrice


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users