Re: [sqlite] transaction commit is successful despite I/O error

[Pavel Ivanov]

I guess this can stand as one more argument for not using SQLite over
NFS. Another argument could be found here:
http://www.sqlite.org/lockingv3.html#how_to_corrupt.

Pavel


On Wed, Oct 24, 2012 at 2:01 PM, Efim Dyadkin  wrote:
> I was probably not quite specific. So I would like to rephrase the problem 
> and give more details.
>
> I run a default configuration of Sqlite on Linux and I don't use WAL or 
> anything that changes transaction behavior. A database is located on a remote 
> file server and accessed via NFS. There is a single process only that 
> accesses the database. The problem is when auto-mounter is stopped (on a 
> machine where the process is running) in the middle of database transaction, 
> commit returns successfully though it leaves a hot journal on disk because it 
> is not seen for unlink (ENOENT)!!!
>
> Note, when auto-mounter is down, you still can operate on a database file and 
> its journal if they were opened before the stop.
>
> The problem can be reproduced very easily with an Sqlite command shell:
> 1) Run the shell
> 2) Begin exclusive transaction
> 3) Insert a few records
> 4) Stop auto-mounter
> 5) Optionally insert a few more records (stopped auto-mounter does not affect 
> these inserts because database and journal are opened)
> 6) Commit (no errors!!!)
> 7) Quit the shell
>
> If you then restart auto-mounter and check the database directory you will 
> find a hot journal! If you reopen the database the transaction will be played 
> back and recently commited changes will be gone!
>
> Thanks for your feedbacks,
> Efim
>
>
>
> 
>  This e-mail, including any attached files, may contain confidential and 
> privileged information for the sole use of the intended recipient. Any 
> review, use, distribution, or disclosure by others is strictly prohibited. If 
> you are not the intended recipient (or authorized to receive information for 
> the intended recipient), please contact the sender by reply e-mail and delete 
> all copies of this message.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction commit is successful despite I/O error

[Efim Dyadkin]

I was probably not quite specific. So I would like to rephrase the problem and 
give more details.

I run a default configuration of Sqlite on Linux and I don't use WAL or 
anything that changes transaction behavior. A database is located on a remote 
file server and accessed via NFS. There is a single process only that accesses 
the database. The problem is when auto-mounter is stopped (on a machine where 
the process is running) in the middle of database transaction, commit returns 
successfully though it leaves a hot journal on disk because it is not seen for 
unlink (ENOENT)!!!

Note, when auto-mounter is down, you still can operate on a database file and 
its journal if they were opened before the stop.

The problem can be reproduced very easily with an Sqlite command shell:
1) Run the shell
2) Begin exclusive transaction
3) Insert a few records
4) Stop auto-mounter
5) Optionally insert a few more records (stopped auto-mounter does not affect 
these inserts because database and journal are opened)
6) Commit (no errors!!!)
7) Quit the shell

If you then restart auto-mounter and check the database directory you will find 
a hot journal! If you reopen the database the transaction will be played back 
and recently commited changes will be gone!

Thanks for your feedbacks,
Efim




 This e-mail, including any attached files, may contain confidential and 
privileged information for the sole use of the intended recipient. Any review, 
use, distribution, or disclosure by others is strictly prohibited. If you are 
not the intended recipient (or authorized to receive information for the 
intended recipient), please contact the sender by reply e-mail and delete all 
copies of this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction commit is successful despite I/O error

[Ryan Johnson]

On 19/10/2012 4:40 PM, Efim Dyadkin wrote:


Hi Richard,

You are right about the purpose of unlink but it is out of context. 
There are a transaction in progress and hot journal on disk. If 
journal can't be deleted by the end of transaction, the transaction 
can't be considered to be successfully finished. But current 
implementation of Sqlite considers this transaction as successfully 
committed!


The problem with file system going down during a transaction is a real 
problem that occurred to our customers. For them it was absolutely a 
disaster because they lost data they had successfully saved.


I guess unixDelete must propagate ENOENT error when it runs in the 
context of transaction. For applications it will be like "transaction 
failed due to IO error".


For a test I tried to remove "errno != ENOENT" unconditionally from 
unixDelete but immediately encountered a problem. When I tried to 
execute any command on an empty database e.g. "create table a (a1 
text)" I got I/O error. This is because Sqlite tried to remove 
non-existsing "--wal" file


I think you're on the right track: pagerOpenWalIfPresent() attempts to 
delete a file that may not exist (the comments say so!), so it should 
have been expecting the occasional ENOENT.


Ryan

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

Re: [sqlite] transaction commit is successful despite I/O error

[Dan Kennedy]

On 10/20/2012 09:14 PM, Simon Slavin wrote:


On 19 Oct 2012, at 9:40pm, Efim Dyadkin
wrote:


You are right about the purpose of unlink but it is out of context.
There are a transaction in progress and hot journal on disk. If
journal can't be deleted by the end of transaction, the transaction
can't be considered to be successfully finished.


This is not correct.  SQLite does not close the journal file at the
end of every transaction unless you have only a single connection to
the database and the journal mode set to DELETE, and that is not
common these days because creating and deleting files is so slow.
The times you should see a journal file deleted is when all
connections to the database have been closed: you've done a _close()
for every _open().


In WAL mode, the WAL file is held open by a connection until it
is closed. And only deleted after the number of connections to the
database drops to zero (the last connection usually unlinks it as
part of the sqlite3_close() call).

If you're running with journal_mode=DELETE (the default), the journal
file is always closed and unlinked at the end of each write
transaction.

If using journal_mode=TRUNCATE or journal_mode=PERSIST on unix, then
the journal file is always closed at the end of a write transaction.
However, on win32 (or any other system where it is impossible to
delete a file while the current or some other process has it open),
the journal file file descriptor may be held open between transactions.



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

Re: [sqlite] transaction commit is successful despite I/O error

[Simon Slavin]

On 19 Oct 2012, at 9:40pm, Efim Dyadkin  wrote:

> You are right about the purpose of unlink but it is out of context. There are 
> a transaction in progress and hot journal on disk. If journal can't be 
> deleted by the end of transaction, the transaction can't be considered to be 
> successfully finished.

This is not correct.  SQLite does not close the journal file at the end of 
every transaction unless you have only a single connection to the database and 
the journal mode set to DELETE, and that is not common these days because 
creating and deleting files is so slow.  The times you should see a journal 
file deleted is when all connections to the database have been closed: you've 
done a _close() for every _open().



You can ignore things like journal files, which file data is stored in, and 
where in the file it's stored.  SQLite should be creating and deleting its own 
files as it sees fit, and if you create, edit or delete any of them yourself 
then you can consider your database to be potentially wrong or corrupt.  Don't 
mess with any SQLite files.

>  But current implementation of Sqlite considers this transaction as 
> successfully committed!

This is how to know that a transaction is finished: you execute the SQL command 
"COMMIT" and the function call returns with the code SQLITE_OK rather than an 
error.  If this has happened, the transaction is successfully committed.  
Whether you happen to notice files on disk being created, resized, deleted or 
deletable is useful for monitoring whether database connections are open, but 
not to individual transactions.

> The problem with file system going down during a transaction is a real 
> problem that occurred to our customers. For them it was absolutely a disaster 
> because they lost data they had successfully saved.

If the file system goes down /during/ a transaction, your application did not 
save data.  Your application's user interface should not be indicating to the 
user that data is saved during a transaction.  It should do that only once the 
"COMMIT" is reported successful.

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

Re: [sqlite] transaction commit is successful despite I/O error

[Efim Dyadkin]

Hi Richard,

You are right about the purpose of unlink but it is out of context. There are a 
transaction in progress and hot journal on disk. If journal can't be deleted by 
the end of transaction, the transaction can't be considered to be successfully 
finished. But current implementation of Sqlite considers this transaction as 
successfully committed!

The problem with file system going down during a transaction is a real problem 
that occurred to our customers. For them it was absolutely a disaster because 
they lost data they had successfully saved.

I guess unixDelete must propagate ENOENT error when it runs in the context of 
transaction. For applications it will be like "transaction failed due to IO 
error".

For a test I tried to remove "errno != ENOENT" unconditionally from unixDelete 
but immediately encountered a problem. When I tried to execute any command on 
an empty database e.g. "create table a (a1 text)" I got I/O error. This is 
because Sqlite tried to remove non-existsing "-wal" file with this stack trace:

unixDelete,  FP=7fff44f133e0
sqlite3OsDelete, FP=7fff44f13420
pagerOpenWalIfPresent,   FP=7fff44f13460
sqlite3PagerSharedLock,  FP=7fff44f13520
lockBtree,   FP=7fff44f135d0
sqlite3BtreeBeginTrans,  FP=7fff44f13650
sqlite3InitOne,  FP=7fff44f13770
sqlite3Init, FP=7fff44f137b0
sqlite3ReadSchema,   FP=7fff44f137e0
sqlite3StartTable,   FP=7fff44f13980
yy_reduce,   FP=7fff44f140f0
sqlite3Parser,   FP=7fff44f14170
sqlite3RunParser,FP=7fff44f14220
sqlite3Prepare,  FP=7fff44f14300
sqlite3LockAndPrepare,   FP=7fff44f143a0
sqlite3_prepare, FP=7fff44f14410
sqlite3_exec,FP=7fff44f14520

Thanks,
Efim Dyadkin

 This e-mail, including any attached files, may contain confidential and 
privileged information for the sole use of the intended recipient. Any review, 
use, distribution, or disclosure by others is strictly prohibited. If you are 
not the intended recipient (or authorized to receive information for the 
intended recipient), please contact the sender by reply e-mail and delete all 
copies of this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction commit is successful despite I/O error

[Ryan Johnson]

On 18/10/2012 8:45 AM, Richard Hipp wrote:

On Wed, Oct 17, 2012 at 4:11 PM, Efim Dyadkin  wrote:


Hi,

I am testing loss of data in Sqlite database correlated to auto-mounter
malfunction. I am running Sqlite on Linux and my database file is located
on network disk. For a test I stop the auto-mounter right before
transaction is committed. Surprisingly commit succeeds without any error
although hot journal remains on disk. When I get auto-mounter back and open
my database again the transaction is rolled back.

Apparently Sqlite cannot remove the journal due to unmounted path but it
ignores this error because Linux classifies it as ENOENT and unixDelete
function disregards it:

if( unlink(zPath)==(-1)) && errno!=ENOENT ){
 return unixLogError(SQLITE_IOERR_DELETE,
"unlink", zPath);
}

Can somebody please explain why "errno!=ENOENT" is required in here?


The purpose of unlink() is to make it so that the file does not exist.
ENOENT indicates that the file does not exist, and so the purpose of the
unlink() call has been fulfilled.

Suppose SQLite did treat ENOENT as an error.  What could it do about it?
It cannot roll the transaction back because the rollback journal (and
indeed the entire database) has disappeared.  I guess we could return
SQLITE_CORRUPT.  Would that somehow be more useful to the application?
Is there some plausible scenario for which an active journal file gone 
AWOL does *not* indicate a serious problem? To me it indicates that Bad 
Things are going on that sqlite is ill-equipped to deal with, so the 
best it can do is avoid causing any collateral damage by attempting to 
continue normally. Especially if the filesystem went down: it's not like 
any future transaction would succeed anyway...


If a less heavy-handed approach is desirable, perhaps a failed unlink() 
call should trigger an fstat() or seek() on the offending file 
descriptor; that might distinguish whether the file itself is 
inaccessible (as in OP's case) or whether it's just unreachable at the 
expected path (e.g. due to interference from an external agent).


I would still favor a fail-fast approach that returns a scary error 
message, though, the same kind that would be returned if a write failed.


$0.02
Ryan

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

Re: [sqlite] transaction commit is successful despite I/O error

[Richard Hipp]

On Wed, Oct 17, 2012 at 4:11 PM, Efim Dyadkin  wrote:

> Hi,
>
> I am testing loss of data in Sqlite database correlated to auto-mounter
> malfunction. I am running Sqlite on Linux and my database file is located
> on network disk. For a test I stop the auto-mounter right before
> transaction is committed. Surprisingly commit succeeds without any error
> although hot journal remains on disk. When I get auto-mounter back and open
> my database again the transaction is rolled back.
>
> Apparently Sqlite cannot remove the journal due to unmounted path but it
> ignores this error because Linux classifies it as ENOENT and unixDelete
> function disregards it:
>
> if( unlink(zPath)==(-1)) && errno!=ENOENT ){
> return unixLogError(SQLITE_IOERR_DELETE,
> "unlink", zPath);
> }
>
> Can somebody please explain why "errno!=ENOENT" is required in here?
>

The purpose of unlink() is to make it so that the file does not exist.
ENOENT indicates that the file does not exist, and so the purpose of the
unlink() call has been fulfilled.

Suppose SQLite did treat ENOENT as an error.  What could it do about it?
It cannot roll the transaction back because the rollback journal (and
indeed the entire database) has disappeared.  I guess we could return
SQLITE_CORRUPT.  Would that somehow be more useful to the application?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] transaction commit is successful despite I/O error

[Efim Dyadkin]

Hi,

I am testing loss of data in Sqlite database correlated to auto-mounter 
malfunction. I am running Sqlite on Linux and my database file is located on 
network disk. For a test I stop the auto-mounter right before transaction is 
committed. Surprisingly commit succeeds without any error although hot journal 
remains on disk. When I get auto-mounter back and open my database again the 
transaction is rolled back.

Apparently Sqlite cannot remove the journal due to unmounted path but it 
ignores this error because Linux classifies it as ENOENT and unixDelete 
function disregards it:

if( unlink(zPath)==(-1)) && errno!=ENOENT ){
return unixLogError(SQLITE_IOERR_DELETE, 
"unlink", zPath);
}

Can somebody please explain why "errno!=ENOENT" is required in here?

Thanks for any help,
Efim Dyadkin


 This e-mail, including any attached files, may contain confidential and 
privileged information for the sole use of the intended recipient. Any review, 
use, distribution, or disclosure by others is strictly prohibited. If you are 
not the intended recipient (or authorized to receive information for the 
intended recipient), please contact the sender by reply e-mail and delete all 
copies of this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users