Can i check database corruption by select statement instead of quick_check?

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

Can i check database corruption by select statement instead of quick_check?

Junyoung Park
Hi,

I am currently testing to check database corrupted / malformed.
Database size :1G
table num : 1
index num : 1

The database can be malformed or normal.

When I executing quick_check against a normal database, it takes about
3 minutes from the time of the first execution since opening.
If database is malformed, it will be checked faster than 3 minutes
based on what part of the file is malformed.

On the other hands, when i execute the "select max (rowid) from
table_name" command on the database when it is malformed or normal,
get results according to the situation and get results within about 1
second.

================== TEST CASE ================
* malformed DB checking by select statement *
sqlite> select max(rowid) from test_t;
Run Time: real 0.003 user 0.000000 sys 0.000000
Error: database disk image is malformed

* normal DB checking by select statement *
sqlite> select max(rowid) from test_t;
4644298
Run Time: real 0.074 user 0.000000 sys 0.000000

* malformed DB checking by "quick_check" *
sqlite> pragma quick_check;
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: database disk image is malformed

* normal DB checking by "quick_check" *
sqlite> pragma quick_check;
ok
Run Time: real 201.368 user 1.350000 sys 1.570000
===============================================

The main point of the question is that if the "quick_check" is slow
according to the size of the database,
in order to confirm the checking of malformed of the DB I would like
to ask if I can replace it with the above select statement instead of
"integrity_check" or "quick_check".

Thank you very much.
_______________________________________________
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: Can i check database corruption by select statement instead of quick_check?

David Raymond
You can't replace it with a select, no. There are plenty of different ways in which a database can be corrupt. When you run a select query you'll only find ones that are fatal errors, and only in places that deal with your query.

The type of corruption you seem to have there is fatal, but there are plenty of ways for a database to be corrupt and yet still give results back from a select query. Quick_check and integrity_check look for all these different types of corruption.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Junyoung Park
Sent: Thursday, January 04, 2018 12:56 AM
To: [hidden email]
Subject: [sqlite] Can i check database corruption by select statement instead of quick_check?

Hi,

I am currently testing to check database corrupted / malformed.
Database size :1G
table num : 1
index num : 1

The database can be malformed or normal.

When I executing quick_check against a normal database, it takes about
3 minutes from the time of the first execution since opening.
If database is malformed, it will be checked faster than 3 minutes
based on what part of the file is malformed.

On the other hands, when i execute the "select max (rowid) from
table_name" command on the database when it is malformed or normal,
get results according to the situation and get results within about 1
second.

================== TEST CASE ================
* malformed DB checking by select statement *
sqlite> select max(rowid) from test_t;
Run Time: real 0.003 user 0.000000 sys 0.000000
Error: database disk image is malformed

* normal DB checking by select statement *
sqlite> select max(rowid) from test_t;
4644298
Run Time: real 0.074 user 0.000000 sys 0.000000

* malformed DB checking by "quick_check" *
sqlite> pragma quick_check;
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: database disk image is malformed

* normal DB checking by "quick_check" *
sqlite> pragma quick_check;
ok
Run Time: real 201.368 user 1.350000 sys 1.570000
===============================================

The main point of the question is that if the "quick_check" is slow
according to the size of the database,
in order to confirm the checking of malformed of the DB I would like
to ask if I can replace it with the above select statement instead of
"integrity_check" or "quick_check".

Thank you very much.
_______________________________________________
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: Can i check database corruption by select statement instead of quick_check?

R Smith-2
In reply to this post by Junyoung Park

On 2018/01/04 7:56 AM, Junyoung Park wrote:
> Hi,
>
> I am currently testing to check database corrupted / malformed.
> Database size :1G

> .....
> The main point of the question is that if the "quick_check" is slow
> according to the size of the database,
> in order to confirm the checking of malformed of the DB I would like
> to ask if I can replace it with the above select statement instead of
> "integrity_check" or "quick_check".

In addition to David's clear description, consider also that SELECT
MAX(rowid) FROM... or similar things like SELECT COUNT(*) FROM... that
the DB engine shortcuts to an optimization reading typically only a page
or two from the best Index (with rowid the Index is implicit) and in no
way checks any integrity.  (One could possibly deduce, should the
operation not error out AND return quick, that the Index is present, at
the least).

On the other hand, during an integrity check the engine needs to check a
lot more. It needs to verify that the rows referenced by the Index, all
Indexes in fact, are indeed present and pointing correctly. It further
has to ensure the Index(es) itself, even if correctly pointing, do not
violate any Index specific constraints (Uniqueness etc.), and a few more
things - all of which simply illustrates that testing DB integrity, even
the quick kind, is a laborious exercise and quick-scanning the Index in
any way would defeat the purpose.

Why the frequent need to test for corruption? Do you often get
corruption? If so, then perhaps solving that problem is a more worthy
exercise?

Cheers,
Ryan

_______________________________________________
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: Can i check database corruption by select statement instead of quick_check?

Keith Medcalf
In reply to this post by David Raymond

Of course you can:

select * from pragma_quick_check;
or
select * from pragma_integrity_check;



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

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Thursday, 4 January, 2018 09:59
>To: SQLite mailing list
>Subject: Re: [sqlite] Can i check database corruption by select
>statement instead of quick_check?
>
>You can't replace it with a select, no. There are plenty of different
>ways in which a database can be corrupt. When you run a select query
>you'll only find ones that are fatal errors, and only in places that
>deal with your query.
>
>The type of corruption you seem to have there is fatal, but there are
>plenty of ways for a database to be corrupt and yet still give
>results back from a select query. Quick_check and integrity_check
>look for all these different types of corruption.
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Junyoung Park
>Sent: Thursday, January 04, 2018 12:56 AM
>To: [hidden email]
>Subject: [sqlite] Can i check database corruption by select statement
>instead of quick_check?
>
>Hi,
>
>I am currently testing to check database corrupted / malformed.
>Database size :1G
>table num : 1
>index num : 1
>
>The database can be malformed or normal.
>
>When I executing quick_check against a normal database, it takes
>about
>3 minutes from the time of the first execution since opening.
>If database is malformed, it will be checked faster than 3 minutes
>based on what part of the file is malformed.
>
>On the other hands, when i execute the "select max (rowid) from
>table_name" command on the database when it is malformed or normal,
>get results according to the situation and get results within about 1
>second.
>
>================== TEST CASE ================
>* malformed DB checking by select statement *
>sqlite> select max(rowid) from test_t;
>Run Time: real 0.003 user 0.000000 sys 0.000000
>Error: database disk image is malformed
>
>* normal DB checking by select statement *
>sqlite> select max(rowid) from test_t;
>4644298
>Run Time: real 0.074 user 0.000000 sys 0.000000
>
>* malformed DB checking by "quick_check" *
>sqlite> pragma quick_check;
>Run Time: real 0.000 user 0.000000 sys 0.000000
>Error: database disk image is malformed
>
>* normal DB checking by "quick_check" *
>sqlite> pragma quick_check;
>ok
>Run Time: real 201.368 user 1.350000 sys 1.570000
>===============================================
>
>The main point of the question is that if the "quick_check" is slow
>according to the size of the database,
>in order to confirm the checking of malformed of the DB I would like
>to ask if I can replace it with the above select statement instead of
>"integrity_check" or "quick_check".
>
>Thank you very much.
>_______________________________________________
>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



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