C# pragma integrity_check call throwing exception

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

C# pragma integrity_check call throwing exception

Roberts, Barry (FINTL)
Hi,

I asked the following question a few weeks ago, but did not get any responses, hoping someone may have an idea.

We are currently running System.Data.SQLite.dll 1.0.80.0 and would like to upgrade to a newer version. However there are a variety of issues we have run into, most of which I have now resolved, however one remains. Assume I have a database which is "malformed" due to rowid xxx missing from a table index. Using the 1.0.80.0 driver the following code would return the results from the pragma integrity_check call.

        private static IEnumerable<string> IntegrityCheck(SQLiteConnection connection)
        {
            using (var command = new SQLiteCommand(connection))
            {
                command.CommandTimeout = 0;
                command.CommandText = "pragma integrity_check";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return reader.GetString(0);
                    }
                }
            }
        }

Our system would get a list of the rowid problems allowing it to log them and inform the user. I am currently testing using the 1.0.105.1 driver, however the ExecuteReader() call (above) throws an exception saying the database is malformed. That is not very helpful and effectively stops the system obtaining integrity check information. If I replace the integrity_check with quick_check it works and returns ok, so access to the file is ok, just some internal indexes are messed up. Is there any way to get the above working in the later drivers, or can someone tell me which driver update changed this behaviour?

Kind Regards,
Fugro Intersite.

Barry Roberts.
[hidden email]<mailto:[hidden email]> | www.fugro.com

_______________________________________________
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: C# pragma integrity_check call throwing exception

Simon Slavin-3


On 24 Oct 2017, at 4:03pm, Roberts, Barry (FINTL) <[hidden email]> wrote:

> Our system would get a list of the rowid problems allowing it to log them and inform the user. I am currently testing using the 1.0.105.1 driver, however the ExecuteReader() call (above) throws an exception saying the database is malformed.

You originally wrote

> In the System.Data.SQLite.dll 1.0.80.0 C# driver the following code would return the reason, I would get a list of the rowid problems allowing me to log them.

The output of "PRAGMA integrity_check" just shows what SQLite found using a simple method of finding faults.  SQLite does not exhaustively investigate every byte of the file trying to figure out what it might mean.  The PRAGMA is just to give you an idea how much corruption there is: whether it looks like a crash occurred during the writing of one row [1] or whether a corrupt database has been in use for some time and or had multiple sectors overwritten.

Please also note that by default "PRAGMA integrity_check" stops after the first 100 errors.  If it finds 100 faults in one index it won’t show you any problems with another table.

You are attempting to use standard SQLite API calls to investigate a corrupt database.  There is nothing we can do to help with this.  There is no requirement for consistency in handling corrupt databases in SQLite: if a SQLite call returns SQLITE_CORRUPT, or if "PRAGMA integrity_check" returns any faults at all, SQLite has done its job.  It’s a "yes or no" thing.

If you want software which can forensically investigate SQLite database corruption and tell you every little thing it finds, there are such programs on the market and the writer of at least one of them read this mailing list.  But it can’t be done with just SQLite API calls.

> If I replace the integrity_check with quick_check it works and returns ok, so access to the file is ok, just some internal indexes are messed up.

Your text makes it look like you think that that kind of corruption affects only existing rows.  This is not the case.  If you continue to write to a database which shows this problem, you can lose more of the existing rows and/or the new data you’re trying to write.  The proper reaction to any such errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or REINDEX then check that the data is still consistent and plausible.

Simon.

[1] Under normal operation, if you did not intentionally turn off safety measures using PRAGMAs, even power failure should not cause corruption to a SQLite database once it has been reopened.  However, many people use PRAGMAs to turn off safety measures hoping for greater speed.  And a faulty storage module can also cause this kind of corruption.
_______________________________________________
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: C# pragma integrity_check call throwing exception

Keith Medcalf
In reply to this post by Roberts, Barry (FINTL)

Have you run "PRAGMA integrity_check;" from the command line shell against the same database and does it return rows or just throw the same exception?

---
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 Roberts, Barry (FINTL)
>Sent: Tuesday, 24 October, 2017 09:04
>To: [hidden email]
>Subject: [sqlite] C# pragma integrity_check call throwing exception
>
>Hi,
>
>I asked the following question a few weeks ago, but did not get any
>responses, hoping someone may have an idea.
>
>We are currently running System.Data.SQLite.dll 1.0.80.0 and would
>like to upgrade to a newer version. However there are a variety of
>issues we have run into, most of which I have now resolved, however
>one remains. Assume I have a database which is "malformed" due to
>rowid xxx missing from a table index. Using the 1.0.80.0 driver the
>following code would return the results from the pragma
>integrity_check call.
>
>        private static IEnumerable<string>
>IntegrityCheck(SQLiteConnection connection)
>        {
>            using (var command = new SQLiteCommand(connection))
>            {
>                command.CommandTimeout = 0;
>                command.CommandText = "pragma integrity_check";
>
>                using (var reader = command.ExecuteReader())
>                {
>                    while (reader.Read())
>                    {
>                        yield return reader.GetString(0);
>                    }
>                }
>            }
>        }
>
>Our system would get a list of the rowid problems allowing it to log
>them and inform the user. I am currently testing using the 1.0.105.1
>driver, however the ExecuteReader() call (above) throws an exception
>saying the database is malformed. That is not very helpful and
>effectively stops the system obtaining integrity check information.
>If I replace the integrity_check with quick_check it works and
>returns ok, so access to the file is ok, just some internal indexes
>are messed up. Is there any way to get the above working in the later
>drivers, or can someone tell me which driver update changed this
>behaviour?
>
>Kind Regards,
>Fugro Intersite.
>
>Barry Roberts.
>[hidden email]<mailto:[hidden email]> | www.fugro.com
>
>_______________________________________________
>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: C# pragma integrity_check call throwing exception

Roberts, Barry (FINTL)
In reply to this post by Roberts, Barry (FINTL)
Simon,


> Your text makes it look like you think that that kind of corruption affects only existing rows.  This is not the case.  If you continue to write to a database which shows this problem, you can lose more of the existing rows and/or the new data you're trying to write.  The proper reaction to any such errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or REINDEX then check that the data is still consistent and plausible.

I am aware that other issues may exist. If the integrity_check did not return "ok" we would then try and fix the issues using the command line tooling. Generally if/when there is an issue it is reasonably minor, and a VACUUM or REINDEX fixes it.  My concern was that the newer C# driver is showing different behaviour to the old driver when tested against the exact same sqlite file (I have unit tests covering this scenario).  I have to justify changing our source code because of it.

Keith,


> Have you run "PRAGMA integrity_check;" from the command line shell against the same database and does it return rows or just throw the same exception?


Thanks for the suggestion, I have just run a test database that is "corrupted" using the latest sqlite3 download

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open entities-corrupt.db3
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> pragma quick_check;
ok
sqlite> REINDEX;
sqlite> pragma quick_check;
ok
sqlite> pragma integrity_check;
ok
sqlite>

So it looks like the actual engine is doing this now. This helps me justify altering our code base, to work with the revised behaviour.

Kind Regards,
Fugro Intersite.

Barry Roberts.
[hidden email]<mailto:[hidden email]> | www.fugro.com

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