Exit value of "PRAGMA integrity_check" in command line

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Exit value of "PRAGMA integrity_check" in command line

林自均
Hi folks,

The document of "PRAGMA integrity_check" says:

> If the integrity_check pragma finds problems, strings are returned (as
multiple rows with a single column per row) which describe the problems.

But it doesn't specify the exit value of:

$ sqlite some-db.sqlite "PRAGMA integrity_check"

Usually, if the database corrupts, the exit value is non-zero. However, I
recently bumped into a corrupted sqlite file that makes the previous
command exit with zero.

Here is the execution result:

$ sqlite3 some-db.sqlite 'PRAGMA integrity_check'
*** in database main ***
Page 6153: btreeInitPage() returns error code 11
Page 6053: btreeInitPage() returns error code 11
Page 5953: btreeInitPage() returns error code 11
... (ignore similar lines)
Page 261: btreeInitPage() returns error code 11
Page 133: btreeInitPage() returns error code 11
Page 132: btreeInitPage() returns error code 11
Page 3 is never used
Page 4 is never used
Page 5 is never used
Page 6 is never used
... (ignore similar lines)
Page 38 is never used
Page 39 is never used
Page 40 is never used
Page 41 is never used
$ echo $?
0
$ sqlite3 some-db.sqlite 'SELECT * FROM task_status;'
Error: database disk image is malformed
$ echo $?
11

Thank you for your help!

Best,
John Lin
_______________________________________________
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: Exit value of "PRAGMA integrity_check" in command line

Richard Hipp-3
On 11/22/17, 林自均 <[hidden email]> wrote:
>
> Usually, if the database corrupts, the exit value is non-zero. However, I
> recently bumped into a corrupted sqlite file that makes the previous
> command exit with zero.
>

The behavior of PRAGMA integrity_check changed with release 3.21.0.
It should now always return error message text and an SQLITE_OK return
code.  See item 9 on the relase notes:
https://www.sqlite.org/releaselog/3_21_0.html

--
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
|

Re: Exit value of "PRAGMA integrity_check" in command line

林自均
Hi Richard,

After I update to 3.21.0, the situation doesn't change.

$ sqlite3 --version
3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827

For "some-db.sqlite" I mentioned in the previous mail, it exit with 0, like
you said. However, if I copy "some-db.sqlite" to "other-db.sqlite" and make
some arbitrary changes in "other-db.sqlite" using vim:

$ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
Error: database disk image is malformed
$ echo $?
11

Is that normal?

Best,
John Lin

Richard Hipp <[hidden email]> 於 2017年11月23日 週四 上午9:39寫道:

> On 11/22/17, 林自均 <[hidden email]> wrote:
> >
> > Usually, if the database corrupts, the exit value is non-zero. However, I
> > recently bumped into a corrupted sqlite file that makes the previous
> > command exit with zero.
> >
>
> The behavior of PRAGMA integrity_check changed with release 3.21.0.
> It should now always return error message text and an SQLITE_OK return
> code.  See item 9 on the relase notes:
> https://www.sqlite.org/releaselog/3_21_0.html
>
> --
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Exit value of "PRAGMA integrity_check" in command line

Richard Hipp-3
On 11/22/17, 林自均 <[hidden email]> wrote:
>
> $ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
> Error: database disk image is malformed
> $ echo $?
> 11
>
> Is that normal?
>

Yes.  The error is occurring as SQLite is trying to parse the schema,
not while running "PRAGMA integrity_chck".
--
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
|

Re: Exit value of "PRAGMA integrity_check" in command line

林自均
Hi Richard,

Thank you for the explanation!

In other word, to check if a sqlite file is corrupted, I have to check the
stdout instead of the exit value. Am I right?

Best,
John Lin

Richard Hipp <[hidden email]> 於 2017年11月23日 週四 上午10:53寫道:

> On 11/22/17, 林自均 <[hidden email]> wrote:
> >
> > $ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
> > Error: database disk image is malformed
> > $ echo $?
> > 11
> >
> > Is that normal?
> >
>
> Yes.  The error is occurring as SQLite is trying to parse the schema,
> not while running "PRAGMA integrity_chck".
> --
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Exit value of "PRAGMA integrity_check" in command line

Simon Slavin-3


On 23 Nov 2017, at 3:00am, 林自均 <[hidden email]> wrote:

> In other word, to check if a sqlite file is corrupted, I have to check the
> stdout instead of the exit value. Am I right?

Why are you checking for corrupted databases ?
What will you do if the database is corrupt ?

Simon.
_______________________________________________
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: Exit value of "PRAGMA integrity_check" in command line

林自均
Hi Simon,

It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
write new logs into a new sqlite file. Does that affect the way I check it?

Best,
John Lin

Simon Slavin <[hidden email]> 於 2017年11月23日 週四 上午11:04寫道:

>
>
> On 23 Nov 2017, at 3:00am, 林自均 <[hidden email]> wrote:
>
> > In other word, to check if a sqlite file is corrupted, I have to check
> the
> > stdout instead of the exit value. Am I right?
>
> Why are you checking for corrupted databases ?
> What will you do if the database is corrupt ?
>
> Simon.
> _______________________________________________
> 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
|

Re: Exit value of "PRAGMA integrity_check" in command line

Simon Slavin-3


On 23 Nov 2017, at 3:11am, 林自均 <[hidden email]> wrote:

> It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
> write new logs into a new sqlite file. Does that affect the way I check it?

I was interested why you were checking for corruption, so your answer just inspires more questions.

Why are your sqlite database files being corrupted so frequently that it’s worth checking for corruption ?  SQLite is a very good database system.  It does not corrupt its files.  You might spend your time better finding out why you keep getting corrupt log files.  Maybe there is a fault in your programming, or in your hardware.

Why are you not just deleting the corrupt database files ?  They’re corrupt.  You cannot trust the contents at all.  You do not know if they have one row missing or hundreds.  They do not tell you anything useful

ALso, why not just create a new log each time ?  It can take a very long time to execute "PRAGMA integrity_check".  The longer your file goes uncorrupted, the longer the check will take.  Eventually you will get a pause of many minutes every time you start up your program.

Simon.
_______________________________________________
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: Exit value of "PRAGMA integrity_check" in command line

林自均
Hi Simon,

Thank you for the suggestions. Yes, we already found the reason why the
database would corrupt and fixed it. However, we still need to check users'
database in our newer version of software in order to write new logs. We
don't check this very often.

The choice between deleting and renaming is not really that important. We
keep the corrupt database only for the developers to debug, not for anyone
else to use.

Best,
John Lin

Simon Slavin <[hidden email]> 於 2017年11月23日 週四 下午12:03寫道:

>
>
> On 23 Nov 2017, at 3:11am, 林自均 <[hidden email]> wrote:
>
> > It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
> > write new logs into a new sqlite file. Does that affect the way I check
> it?
>
> I was interested why you were checking for corruption, so your answer just
> inspires more questions.
>
> Why are your sqlite database files being corrupted so frequently that it’s
> worth checking for corruption ?  SQLite is a very good database system.  It
> does not corrupt its files.  You might spend your time better finding out
> why you keep getting corrupt log files.  Maybe there is a fault in your
> programming, or in your hardware.
>
> Why are you not just deleting the corrupt database files ?  They’re
> corrupt.  You cannot trust the contents at all.  You do not know if they
> have one row missing or hundreds.  They do not tell you anything useful
>
> ALso, why not just create a new log each time ?  It can take a very long
> time to execute "PRAGMA integrity_check".  The longer your file goes
> uncorrupted, the longer the check will take.  Eventually you will get a
> pause of many minutes every time you start up your program.
>
> Simon.
> _______________________________________________
> 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