Re: [sqlite] Index on expression goes corrupt from valid SQL

[Rowan Worth]

On 23 March 2018 at 08:54, Deon Brewis  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expression goes corrupt from valid SQL

[Simon Slavin]

On 23 Mar 2018, at 3:02am, Deon Brewis  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Simon Slavin
Sent: Thursday, March 22, 2018 7:20 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 Mar 2018, at 12:54am, Deon Brewis <de...@outlook.com> 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=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=CTO5yW6oacPBwn%2F3xpGn0yKsANWdqVvqsmROq%2Bmbvsk%3D=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=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=bGBq77I7HjZW8Yqq%2B1wOOQifc1r9jkN2m1%2F5lSq1H7w%3D=0>

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=4kf58cJlAUKQJeXAIT7CMXP8mLhhqxYWzoCx%2F7mjCRk%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expression goes corrupt from valid SQL

[Simon Slavin]

On 23 Mar 2018, at 12:54am, Deon Brewis  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 ?



> 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



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

Re: [sqlite] Index on expression goes corrupt from valid SQL

[Richard Hipp]

On 3/22/18, Deon Brewis  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users