Index on expression goes corrupt from valid SQL

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Index on expression goes corrupt from valid SQL

Deon Brewis
I was just reading through this issue:
https://www.sqlite.org/src/info/343634942dd54ab

Does this bug have any other symptoms other than as specified in the report above?

Reason I'm asking is that we are facing quite a bit of database corruption right now. We use a lot of expression indexes (written in custom C++ code), which now sounds suspicious.

However, what we see doesn't generally exhibit like the bug describes. The bug as reported gives errors like this:
"row 1 missing from index idx1"

Where we instead see things like:
"database disk image is malformed"

Can it still be related to the same issue?


In general, is there anything dangerous that you can do in a custom function inside an indexed expression that we need to watch out for?

Most of the time when the database gets corrupted, we don't crash, it corrupts midway through valid SQL (no pattern to it - completely unrelated SQL). I was thinking if the expression functions have bugs in them it could cause corruption, but can't really imagine how. We use sqlite3_result_* functions to report results, and even if we read invalid memory or pass invalid memory to SQLITE, it should crash - not corrupt.

- Deon
_______________________________________________
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: Index on expression goes corrupt from valid SQL

Richard Hipp-3
On 3/22/18, Deon Brewis <[hidden email]> wrote:
>
> In general, is there anything dangerous that you can do in a custom function
> inside an indexed expression that we need to watch out for?

Yes:  the UDF must give the same answer from the same inputs every
single time.  If it does not (if the function is not "pure") then the
index can go corrupt.


--
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: Index on expression goes corrupt from valid SQL

Simon Slavin-3
In reply to this post by Deon Brewis
On 23 Mar 2018, at 12:54am, Deon Brewis <[hidden email]> wrote:

> However, what we see doesn't generally exhibit like the bug describes. The bug as reported gives errors like this:
> "row 1 missing from index idx1"
>
> Where we instead see things like:
> "database disk image is malformed"
>
> Can it still be related to the same issue?

I don't think so.  That particular error was not at all caught by the 'malformed' error you're seeing.  It was reported as a corrupt index.

> In general, is there anything dangerous that you can do in a custom function inside an indexed expression that we need to watch out for?
>
> Most of the time when the database gets corrupted, we don't crash, it corrupts midway through valid SQL (no pattern to it - completely unrelated SQL). I was thinking if the expression functions have bugs in them it could cause corruption, but can't really imagine how.

Is the function deterministic ?  Is it marked as deterministic when you define it ?

<https://www.sqlite.org/c3ref/c_deterministic.html>

> We use sqlite3_result_* functions to report results, and even if we read invalid memory or pass invalid memory to SQLITE, it should crash - not corrupt.

None of this should be happening unless -- please excuse my boldness -- the fault is in your own code.  Can you run your code under a tool which checks memory allocation and release, just to see if memory really is your problem ?

> We use sqlite3_result_* functions to report results, and even if we read invalid memory or pass invalid memory to SQLITE, it should crash - not corrupt.

To keep things fast, SQLite tends to assume that it itself is sane and doesn't do all the checking it could do.  But errors of this sort aren't usually difficult to track down.

A possibility you haven't mentioned is bad hardware.  It possible you have no software problems at all and your corruption is purely a bad sector in your storage medium.  Other than that, you might want to read

<https://www.sqlite.org/howtocorrupt.html>

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: Index on expression goes corrupt from valid SQL

Deon Brewis
The expression index functions behave deterministic and are marked as such.

We run clean under both AVRF and Sanitizer. We also allocate our own objects on our own separate heap (Windows) or zone (MAC) - so we're unlikely to ever get a pointer that points into the SQLITE memory space, even with an allocation/free bug. I know this is the most likely cause of corruption, and that's in our own code, but so far I can't find it.

Bad hardware is extremely unlikely since we've hit this hundreds of times over the last couple of months on 100s of different physical hardware and on different hardware platforms (PC, MAC, iOS, Android).

I sleep with "How To Corrupt An SQLite Database File" under my pillow. I'd be able to tell if you moved a comma on that page from one day to the next... I've looked for every one of those items multiple times over.

PS: I did send an example corrupted file to Richard - I can send it to you as well if you like?

- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
Sent: Thursday, March 22, 2018 7:20 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 Mar 2018, at 12:54am, Deon Brewis <[hidden email]> wrote:

> However, what we see doesn't generally exhibit like the bug describes. The bug as reported gives errors like this:
> "row 1 missing from index idx1"
>
> Where we instead see things like:
> "database disk image is malformed"
>
> Can it still be related to the same issue?

I don't think so.  That particular error was not at all caught by the 'malformed' error you're seeing.  It was reported as a corrupt index.

> In general, is there anything dangerous that you can do in a custom function inside an indexed expression that we need to watch out for?
>
> Most of the time when the database gets corrupted, we don't crash, it corrupts midway through valid SQL (no pattern to it - completely unrelated SQL). I was thinking if the expression functions have bugs in them it could cause corruption, but can't really imagine how.

Is the function deterministic ?  Is it marked as deterministic when you define it ?

<https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Fc3ref%2Fc_deterministic.html&data=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636573684383940382&sdata=CTO5yW6oacPBwn%2F3xpGn0yKsANWdqVvqsmROq%2Bmbvsk%3D&reserved=0>

> We use sqlite3_result_* functions to report results, and even if we read invalid memory or pass invalid memory to SQLITE, it should crash - not corrupt.

None of this should be happening unless -- please excuse my boldness -- the fault is in your own code.  Can you run your code under a tool which checks memory allocation and release, just to see if memory really is your problem ?

> We use sqlite3_result_* functions to report results, and even if we read invalid memory or pass invalid memory to SQLITE, it should crash - not corrupt.

To keep things fast, SQLite tends to assume that it itself is sane and doesn't do all the checking it could do.  But errors of this sort aren't usually difficult to track down.

A possibility you haven't mentioned is bad hardware.  It possible you have no software problems at all and your corruption is purely a bad sector in your storage medium.  Other than that, you might want to read

<https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Fhowtocorrupt.html&data=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636573684383940382&sdata=bGBq77I7HjZW8Yqq%2B1wOOQifc1r9jkN2m1%2F5lSq1H7w%3D&reserved=0>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636573684383940382&sdata=4kf58cJlAUKQJeXAIT7CMXP8mLhhqxYWzoCx%2F7mjCRk%3D&reserved=0
_______________________________________________
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: Index on expression goes corrupt from valid SQL

Simon Slavin-3
On 23 Mar 2018, at 3:02am, Deon Brewis <[hidden email]> wrote:

> PS: I did send an example corrupted file to Richard - I can send it to you as well if you like?

Richard will find anything there is to find.  I do hope the problem gets solved since you seem to have put so much work into it and done everything correctly.

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: Index on expression goes corrupt from valid SQL

Rowan Worth-2
In reply to this post by Deon Brewis
On 23 March 2018 at 08:54, Deon Brewis <[hidden email]> wrote:

> Most of the time when the database gets corrupted, we don't crash, it
> corrupts midway through valid SQL (no pattern to it - completely unrelated
> SQL). I was thinking if the expression functions have bugs in them it could
> cause corruption, but can't really imagine how. We use sqlite3_result_*
> functions to report results, and even if we read invalid memory or pass
> invalid memory to SQLITE, it should crash - not corrupt.
>

Do you have an sqlite logging callback installed
(sqlite3_config(SQLITE_CONFIG_LOG, ...))?
The messages on that channel include more details (eg. the line number of
the sqlite3 source where the corruption was first noticed) for some
instances of corruption. Might help to pin down the issue.

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