Re: [sqlite] Data loss during the disk full condition

[Paul Sanderson]

I haven't seen anything to say what journalling is being used
(Rollback, WAL or none). If the latter then SQLite will have nothing
to revert to on error.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 30 October 2016 at 00:22, Richard Hipp  wrote:
> On 10/29/16, Yuri  wrote:
>
>>> What does "PRAGMA integrity_check" say about your database?
>>
>> It returns "ok", but this file has been opened and written into again.
>>
>
> This suggests that the problem may be somewhere besides in SQLite.  If
> SQLite were getting confused and zeroing content as a result of the
> disk-full error, then almost certainly integrity_check would report
> errors.
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data loss during the disk full condition

[Richard Hipp]

On 10/29/16, Yuri  wrote:

>> What does "PRAGMA integrity_check" say about your database?
>
> It returns "ok", but this file has been opened and written into again.
>

This suggests that the problem may be somewhere besides in SQLite.  If
SQLite were getting confused and zeroing content as a result of the
disk-full error, then almost certainly integrity_check would report
errors.

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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 11:47pm, Yuri  wrote:

> It opens the DB, applies updates without the explicit transaction, some of 
> them fail, messages are printed, DB is closed. DB should be left in the valid 
> state. Is this not what is supposed to happen?

I would argue that you should close the database after the first error result.  
But even if you don't, the database should definitely not be corrupted.  So 
there is definitely something bad going on.  Perhaps the development team can 
help.

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

Re: [sqlite] Data loss during the disk full condition

[Yuri]

On 10/29/2016 15:15, Simon Slavin wrote:

I believe SQLite handled the failure correctly.  It returned SQLITE_FULL and it 
did not corrupt its database.  SQLITE_FULL is documented as an error code, not 
a warning, in section 1 of




My program prints errors, but doesn't terminate the process. For some 
reason, Qt's db.lastError() doesn't print that text, but SQLite library 
prints it into stderr:
> /usr/home/yuri/.config/in-mem-fs-to-test-disk-full: write failed, 
filesystem is full
> QSqlSqliteSettingsFormat: database error occurred during ' "update 
(key=key-X16)" ':  QSqlError(-1, "", "")



> /usr/home/yuri/.config/in-mem-fs-to-test-disk-full: write failed, 
filesystem is full
> QSqlSqliteSettingsFormat: database error occurred during ' "update 
(key=key-X16)" ':  QSqlError(-1, "", "")


So I didn't ignore them.




 From what you describe, your application didn't handle this error correctly, 
it ignored it.  At minimum the programmer needs to consider what to do with the 
data it was trying to write, given that it couldn't write it to the database.  
Even if that data isn't important, SQLite documentation has recommendations:



"It is recommended that applications respond to the errors listed above by 
explicitly issuing a ROLLBACK command. If the transaction has already been rolled back 
automatically by the error response, then the ROLLBACK command will fail with an error, 
but no harm is caused by this."

And of course, if the ROLLBACK command itself fails, the program needs to 
handle that.  Since it means that recovery from the error condition failed.



It opens the DB, applies updates without the explicit transaction, some 
of them fail, messages are printed, DB is closed. DB should be left in 
the valid state. Is this not what is supposed to happen?



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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 10:42pm, Yuri  wrote:

> On a busy system one write operation might fail with disk-full, while the 
> rest are fine, because other processes will write and delete data 
> continuously. This is what I suspect: that SQLite failed to handle one or a 
> few such failures.

I believe SQLite handled the failure correctly.  It returned SQLITE_FULL and it 
did not corrupt its database.  SQLITE_FULL is documented as an error code, not 
a warning, in section 1 of



From what you describe, your application didn't handle this error correctly, it 
ignored it.  At minimum the programmer needs to consider what to do with the 
data it was trying to write, given that it couldn't write it to the database.  
Even if that data isn't important, SQLite documentation has recommendations:



"It is recommended that applications respond to the errors listed above by 
explicitly issuing a ROLLBACK command. If the transaction has already been 
rolled back automatically by the error response, then the ROLLBACK command will 
fail with an error, but no harm is caused by this."

And of course, if the ROLLBACK command itself fails, the program needs to 
handle that.  Since it means that recovery from the error condition failed.

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

Re: [sqlite] Data loss during the disk full condition

[Yuri]

On 10/29/2016 14:32, Simon Slavin wrote:

On 29 Oct 2016, at 10:21pm, Yuri  wrote:


I think you need to have such testcase: On the table with a lot of key/value 
pairs you run a set of updates and inserts. Random file operations should fail 
with some low probability with various error codes, like disk full.

You would have to simulate this on a wide range of platforms.  Because what each platform 
does under "disk full" conditions is different.


On a busy system one write operation might fail with disk-full, while 
the rest are fine, because other processes will write and delete data 
continuously. This is what I suspect: that SQLite failed to handle one 
or a few such failures. POSIX file API is the most popular and most OSes 
use it, but Windows is probably different.


Yuri

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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 10:21pm, Yuri  wrote:

> I think you need to have such testcase: On the table with a lot of key/value 
> pairs you run a set of updates and inserts. Random file operations should 
> fail with some low probability with various error codes, like disk full.

You would have to simulate this on a wide range of platforms.  Because what 
each platform does under "disk full" conditions is different.

The one possible enhancement to SQLite I might suggest is to add a test for 
"disk full" before it tries to recover from a "file not closed properly" 
condition.  Perhaps it should try to grab an extra block of disk first.  If 
that fails it shouldn't attempt a rescue, just immediately return "disk full".

Unfortunately this isn't done at the sqlite3_open() stage because this doesn't 
actually access the file.  Not sure how to cope with that.

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

Re: [sqlite] Data loss during the disk full condition

[Yuri]

On 10/29/2016 13:06, Richard Hipp wrote:

>SQLite should preserve data in such case, it should fail operations that
>can't be performed, but the old data should absolutely stay intact.

That is what is suppose to happen - everything should be preserved.
This is tested extensively and there are no known problems in SQLite
that can cause the behavior you observe.  But perhaps you have come
across something nobody has yet seen or thought of.  (Or perhaps the
problem is with some other component other than SQLite - we don't know
yet.)

Do you have a way to reproduce the problem?  Do you have any
additional information that might help us to reproduce it here?



I spent all morning trying to reproduce the problem with the in-memory 
disk having disk-full and couldn't reproduce it. I didn't see any 
'delete' operations issued by my program, this was as expected. But no 
row disappearance.



I think you need to have such testcase: On the table with a lot of 
key/value pairs you run a set of updates and inserts. Random file 
operations should fail with some low probability with various error 
codes, like disk full. The test should check that all updated values 
still have either old or new values, and not anything else. All inserted 
values should be checked to be either there or not there, and values 
untouched by queries should stay intact. Nothing should disappear.


The tricky part might be to simulate file failures. But sqlite library 
is essentially a convertor between the SQLite API executing SQL 
statements, and the file access API provided by OS. You can't guarantee 
the behavior of the file API that you don't control, so random failures 
in it should be processed properly.



Only if such case passes every time after a lot of tries with random 
file failure this can be an assurance that there are no such bugs in SQLite.



Software often relies on certain behavior of the outside API, and when 
those calls once in a while behave differently



What does "PRAGMA integrity_check" say about your database?


It returns "ok", but this file has been opened and written into again.

Yuri

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

Re: [sqlite] Data loss during the disk full condition

[Richard Hipp]

On 10/29/16, Yuri  wrote:
>
> After the reboot the table still existed but was blank. The file was
> about the same size, but had large zeroed areas, and no rows in this
> table. Other table still had rows.

What does "PRAGMA integrity_check" say about your database?

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

Re: [sqlite] Data loss during the disk full condition

[Richard Hipp]

On 10/29/16, Yuri  wrote:
>
> After the reboot the table still existed but was blank. The file was
> about the same size, but had large zeroed areas, and no rows in this
> table. Other table still had rows.
>
>
> How could this have happened that data could get lost like that? I was
> under the impression that SQLite is safe during such special conditions?
>
>
> SQLite should preserve data in such case, it should fail operations that
> can't be performed, but the old data should absolutely stay intact.

That is what is suppose to happen - everything should be preserved.
This is tested extensively and there are no known problems in SQLite
that can cause the behavior you observe.  But perhaps you have come
across something nobody has yet seen or thought of.  (Or perhaps the
problem is with some other component other than SQLite - we don't know
yet.)

Do you have a way to reproduce the problem?  Do you have any
additional information that might help us to reproduce it here?

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

Re: [sqlite] Data loss during the disk full condition

[Jens Alfke]

> On Oct 29, 2016, at 11:34 AM, Simon Slavin  wrote:
> 
> Really ?  An interactive program (or any program) gets a result code it wan't 
> expecting and you don't want it to shut down ?

Really. Apps aren’t supposed to crash. How would you feel if Photoshop or Word 
or Logic crashed and lost your work because it ran into some error trying to 
save? An application may abort if it hits an assertion failure, i.e. a logic 
error in the program itself; but errors from system APIs, especially I/O, must 
be recoverable. The error alert might advise the user to quit ASAP, but it has 
to be up to the user, so that they can somehow save/export/copy their work.

(I worked at Apple for 16 years, and I definitely remember some high-priority 
bugs involving failure conditions like this. Back in the olden days an 
especially nasty one was running out of memory trying to save. Users’ computers 
tended to have only a few megabytes of RAM and there was no virtual memory. 
Disk-full errors were not uncommon either, especially when trying to save to a 
floppy.)

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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 7:23pm, Jens Alfke  wrote:

> On Oct 29, 2016, at 10:44 AM, Simon Slavin  wrote:
> 
>> To minimise problems like the one you reported it needs to quit the program 
>> (preferably with a non-zero exit code) when it gets any unexpected result.
> 
> That may be true for a server or a command-line tool, but it's almost never 
> appropriate for an interactive application.

Really ?  An interactive program (or any program) gets a result code it wan't 
expecting and you don't want it to shut down ?

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

Re: [sqlite] Data loss during the disk full condition

[Jens Alfke]

> On Oct 29, 2016, at 10:44 AM, Simon Slavin  wrote:
> 
> To minimise problems like the one you reported it needs to quit the program 
> (preferably with a non-zero exit code) when it gets any unexpected result.

That may be true for a server or a command-line tool, but it's almost never 
appropriate for an interactive application.

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

Re: [sqlite] Data loss during the disk full condition

[Yuri]

On 10/29/2016 10:44, Simon Slavin wrote:

To minimise problems like the one you reported it needs to quit the program 
(preferably with a non-zero exit code) when it gets any unexpected result.


This particular application, SQLite QSettings backend, can't quit on the 
disk-full condition because the condition can be, and is often 
transient. Quitting will also potentially mean losing the data when it 
could have been saved later.



I'm not certain that this would have completely prevented the problem you 
reported, but repeated attempts to make changes to a database with no free 
space aren't going to lead to anything good.

Another factor is whether you had free space when you tried to open the 
database after the faults.  Each time you use the SQLite API to open a database 
SQLite checks for to see if it was closed correctly last time.  If not, it 
tries to recover an uncorrupted database.  [1] But if the disk is full this 
will fail.  Though it shouldn't lead to further corruption.

[1] I don't know whether it checks to see whether there is space free first.



My program only issues deletes to mirror the in-app map deletions. So I 
am very confident it didn't just delete all records.



This leaves SQLite/OS. The system with disk-full shut down, this caused 
the signal to the app, this caused some failed attempts to update some 
rows in the SQLite table, then the system has shut down. The question is 
what zeroed the file.



It is possible that the disk-full interjects in between some 
open/read/write operations, so that some of them succeed and some 
subsequent ones fail, leaving something in unexpectedly wrong state.



Yuri

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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 6:32pm, Yuri  wrote:

> It does check error codes, and it prints warnings, but errors don't fail the 
> overall app. It will keep updating/inserting again and again later.

To minimise problems like the one you reported it needs to quit the program 
(preferably with a non-zero exit code) when it gets any unexpected result.

I'm not certain that this would have completely prevented the problem you 
reported, but repeated attempts to make changes to a database with no free 
space aren't going to lead to anything good.

Another factor is whether you had free space when you tried to open the 
database after the faults.  Each time you use the SQLite API to open a database 
SQLite checks for to see if it was closed correctly last time.  If not, it 
tries to recover an uncorrupted database.  [1] But if the disk is full this 
will fail.  Though it shouldn't lead to further corruption.

[1] I don't know whether it checks to see whether there is space free first.

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

Re: [sqlite] Data loss during the disk full condition

[Yuri]

On 10/29/2016 06:08, Simon Slavin wrote:

On 29 Oct 2016, at 8:05am, Yuri  wrote:


>The application is synchronizing the in-memory key-value table with the disk 
one using insert/update/delete statements using that key. The in-memory table was 
full at the moment of reboot,

You're going to have to explain that.  There can be no such thing as an 
in-memory table when you reboot.  Booting involved having nothing in memory.


By "in-memory table" I mean the app-level STL std::map 
that the app saves as an SQLite table. That map had data at the time of 
the app shutdown. The code in question has been working fine for over a 
year. 
https://github.com/yurivict/qt-additions/blob/master/QSqlSqliteSettingsFormat 
It is the SQLite Qt QSettings backend. I wrote it to specifically 
safeguard against this kind of situation. (Not in Qt yet.)



>[snip]
>
>SQLite should preserve data in such case, it should fail operations that can't 
be performed, but the old data should absolutely stay intact.

It should do.  But bad programming can prevent things from happening properly.

For instance, does the program which makes changes to the database check the 
result values returned by each operation ?  And does it correctly crash if the 
return value indicates something went wrong with the operation ?  Many 
quickly-written simple programs just proceed with the next operation.



It does check error codes, and it prints warnings, but errors don't fail 
the overall app. It will keep updating/inserting again and again later.



Now I created the small in-memory disk with disk-full condition for 
testing. The same program keeps printing 'update/insert failed' but it 
never issues 'delete' statements. I also can't reproduce the table 
erasure. I am still at a loss what went wrong, what erased the table in 
the file.



Yuri

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

Re: [sqlite] Data loss during the disk full condition

[Simon Slavin]

On 29 Oct 2016, at 8:05am, Yuri  wrote:

> The application is synchronizing the in-memory key-value table with the disk 
> one using insert/update/delete statements using that key. The in-memory table 
> was full at the moment of reboot,

You're going to have to explain that.  There can be no such thing as an 
in-memory table when you reboot.  Booting involved having nothing in memory.

> [snip]
> 
> SQLite should preserve data in such case, it should fail operations that 
> can't be performed, but the old data should absolutely stay intact.

It should do.  But bad programming can prevent things from happening properly.

For instance, does the program which makes changes to the database check the 
result values returned by each operation ?  And does it correctly crash if the 
return value indicates something went wrong with the operation ?  Many 
quickly-written simple programs just proceed with the next operation.

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

[sqlite] Data loss during the disk full condition

[Yuri]

My disk was full when the system rebooted, and the important SQLite 
table got blanked at that time. The table is a simple key/value table 
with the primary key. The application is synchronizing the in-memory 
key-value table with the disk one using insert/update/delete statements 
using that key. The in-memory table was full at the moment of reboot, 
and there is no way it could run any delete operations at all.



After the reboot the table still existed but was blank. The file was 
about the same size, but had large zeroed areas, and no rows in this 
table. Other table still had rows.



How could this have happened that data could get lost like that? I was 
under the impression that SQLite is safe during such special conditions?



SQLite should preserve data in such case, it should fail operations that 
can't be performed, but the old data should absolutely stay intact.



sqlite-3.14.1 on FreeBSD.


Yuri

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