Re: [sqlite] transaction commit is successful despite I/O error
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
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 efim.dyad...@pdgm.com 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
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
On 19 Oct 2012, at 9:40pm, Efim Dyadkin efim.dyad...@pdgm.com 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(). http://www.sqlite.org/pragma.html#pragma_journal_mode 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
On 10/20/2012 09:14 PM, Simon Slavin wrote: On 19 Oct 2012, at 9:40pm, Efim Dyadkinefim.dyad...@pdgm.com 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
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
[sqlite] transaction commit is successful despite I/O error
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
Re: [sqlite] transaction commit is successful despite I/O error
On Wed, Oct 17, 2012 at 4:11 PM, Efim Dyadkin efim.dyad...@pdgm.com 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
Re: [sqlite] transaction commit is successful despite I/O error
On 18/10/2012 8:45 AM, Richard Hipp wrote: On Wed, Oct 17, 2012 at 4:11 PM, Efim Dyadkin efim.dyad...@pdgm.com 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