Re: [sqlite] does integrity check ever modify the db file?

[Richard Damon]

On 1/13/17 2:51 PM, Adam Smith wrote:

Hey all,
   can 'pragma integrity_check' ever modify the file? For instance in case
of a journal file laying around (which was journal file of the same schema
db but a bit different data)?

The following is what I think happened:

a.db and b.db are two sqlite dbs of size ~5mb which have same schema and
little difference in data.
the process writing to a.db is terminated or power loss or something so
a-journal.db left behind.

ls

a.db
a-journal.db
b.db

sqlite3 b.db 'pragma integrity_check'# b.db is healthy

ok

rm a.db
mv b.db a.db  # rename b.db as a.db
sqlite3 a.db 'pragma integrity_check'   # this is I think when integrity

check modifies a.db and corrupts it
failure

ls

a.db

sqlite3 a.db 'pragma integrity_check'

failure...


a.db is corrupted

Is this possible? If so how do we delete all temp files like -journal file?
Thanks in advance


That is (close to) one of the listed ways to corrupt a database file.

While sqlite mostly keeps all the data in a single file, there are 
situations where auxiliary files are temporarily created, and those MUST 
be kept related.


That says when you bypass sqlite and directly manipulate the database 
file, you need to understand the temporary auxiliary files and process 
those too, or make sure that you only are working with files that are in 
a clean state, so it is a single file.


--
Richard Damon

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

Re: [sqlite] does integrity check ever modify the db file?

[Rowan Worth]

On 14 January 2017 at 03:51, Adam Smith  wrote:

> Hey all,
>   can 'pragma integrity_check' ever modify the file? For instance in case
> of a journal file laying around (which was journal file of the same schema
> db but a bit different data)?
>
> The following is what I think happened:
>
> a.db and b.db are two sqlite dbs of size ~5mb which have same schema and
> little difference in data.
> the process writing to a.db is terminated or power loss or something so
> a-journal.db left behind.
> >> ls
> a.db
> a-journal.db
> b.db
> >> sqlite3 b.db 'pragma integrity_check'# b.db is healthy
> ok
> >> rm a.db
> >> mv b.db a.db  # rename b.db as a.db
> >> sqlite3 a.db 'pragma integrity_check'   # this is I think when integrity
> check modifies a.db and corrupts it
> failure
> >> ls
> a.db
> >> sqlite3 a.db 'pragma integrity_check'
> failure...
>
>
> a.db is corrupted
>
> Is this possible? If so how do we delete all temp files like -journal file?
> Thanks in advance
>

Yes and no. Integrity_check itself never modifies the the database file.
However, in the situation you describe the file _can_ be modified as a side
effect of reading the database (which of course integrity_check needs to
do).

The -journal file is a rollback journal - it contains the original content
of database pages before the transaction began. The scenario above arises
if power is lost while a transaction is only partway committed. Sqlite has
modified some of the database pages on disk but not necessarily all -- ie.
the main database file might not be consistent. The implication here is
that the main database file is not safe to access by itself in this state.

If you try to query an sqlite database in this state, it will notice that
there is a "hot" journal and rollback the partial transaction (by copying
the original page data stored in the rollback journal back into the
database).

From your description it looks like that's what happened here. You've run a
query (integrity_check) against the copied database, and sqlite has noticed
the -journal file, concluded that something must have gone wrong in the
middle of a transaction (correct), and rolled back the changes that
transaction made. Only problem is the partial transaction was against a
different database (the original a.db, not the copy of b.db) so the
rollback resulted in a corrupt state.

For more details see:
https://www.sqlite.org/howtocorrupt.html#_deleting_a_hot_journal
https://www.sqlite.org/atomiccommit.html#_deleting_or_renaming_a_hot_journal

Deleting a -journal file is not advisable in general because it contains
important information for ensuring the database is in a consistent state,
but when you're trying to delete an entire database it makes sense. The
list of possible files associated with an sqlite database is described here:

https://www.sqlite.org/tempfiles.html

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

[sqlite] does integrity check ever modify the db file?

[Adam Smith]

Hey all,
  can 'pragma integrity_check' ever modify the file? For instance in case
of a journal file laying around (which was journal file of the same schema
db but a bit different data)?

The following is what I think happened:

a.db and b.db are two sqlite dbs of size ~5mb which have same schema and
little difference in data.
the process writing to a.db is terminated or power loss or something so
a-journal.db left behind.
>> ls
a.db
a-journal.db
b.db
>> sqlite3 b.db 'pragma integrity_check'# b.db is healthy
ok
>> rm a.db
>> mv b.db a.db  # rename b.db as a.db
>> sqlite3 a.db 'pragma integrity_check'   # this is I think when integrity
check modifies a.db and corrupts it
failure
>> ls
a.db
>> sqlite3 a.db 'pragma integrity_check'
failure...


a.db is corrupted

Is this possible? If so how do we delete all temp files like -journal file?
Thanks in advance
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users