Re: [sqlite] Disconnected machine with database, SELECT still works !
On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote:
>How exactly are you accessing the database over the network?
Two machines with Windows XP on a LAN.
Using file sharing a place my app.exe on one machine and
instructs it to use the file (database) on the other machine,
sqlite3_open("\\LW75\SHARE\data.sq3", )
Ok. Perhaps Offline Files is enabled (Folder Options control panel)?
That does a local cache. It will also screw with locks, since it
obviously can't contact the server to guarantee exclusivity (or
release held locks, for that matter).
I also echo the general advice to stay away from concurrent users on
network shares, if you can. The entire concept is sufficiently
complex to make me nervous.
I know some people have worked on a client/server version of sqlite,
so you may be able to find a drop-in replacement without having to
change your configuration (or even code, if they kept the same API).
I don't have links to any offhand though.
>From: Trevor Talbot <[EMAIL PROTECTED]>
>Subject: Re: Disconnected machine with database, SELECT still works !
>Newsgroups: gmane.comp.db.sqlite.general
>Date: 2006-09-23 15:12:09 GMT (38 minutes ago)
>On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote:
>> Tried to solve a problem and what I did was to open the
>> database over the net and then physically disconnect
>> the machine.
>>
>> I can still SELECT !
>> sqlite3_prepare() works and gives SQLITE_OK
>> sqlite3_step() gives SQLITE_ROW but is very, very slow
>> sqlite3_finalize() works.
>>
>> First when I do INSERT/UPDATE/DELETE I get an error.
>How exactly are you accessing the database over the network?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite3_close doesn't release always the file handle.
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- [EMAIL PROTECTED] wrote: > > > Ran <[EMAIL PROTECTED]> wrote: > > > > Hi all, > > > > > > > > I *think* that sqlite3_close behave strangly. > > > > > > > > I use version 3.3.7 on Linux (Fedora Core 5). > > > > > > > > What I do is to open a database, and start a transaction in it. Then, > > > > without ending the transaction, open again the database and simply > > > > close it. > > > > > > > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > > > > file handle is not released. So if I do it too many times, I run out of > > > > file > > > > handles. > > > > > > > > > > This behavior is intentional. It is a work-around for a bug > > > > SQLite should find the inode of the database file via stat()'s st_ino > > field prior to open() and if it is the same as an already opened > > database file, it should use the same (refcounted) file descriptor, > > eliminating the need for open() in this case. > > > > That would only work in a single-threaded application. > Imagine the trouble that would ensue if two different > threads tried to seek to different places and read, at > the same time, on the same file descriptor. Yes, I also realized that about 30 seconds after I sent the email. > One solution that might work, however, it to satisfy new > open requests with file descriptors that are in the queue > of file descriptors that are waiting to be closed. Assuming they stat() to the same inode, sure. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_close doesn't release always the file handle.
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Ran <[EMAIL PROTECTED]> wrote: > > > Hi all, > > > > > > I *think* that sqlite3_close behave strangly. > > > > > > I use version 3.3.7 on Linux (Fedora Core 5). > > > > > > What I do is to open a database, and start a transaction in it. Then, > > > without ending the transaction, open again the database and simply close > > > it. > > > > > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > > > file handle is not released. So if I do it too many times, I run out of > > > file > > > handles. > > > > > > > This behavior is intentional. It is a work-around for a bug > > SQLite should find the inode of the database file via stat()'s st_ino > field prior to open() and if it is the same as an already opened > database file, it should use the same (refcounted) file descriptor, > eliminating the need for open() in this case. > That would only work in a single-threaded application. Imagine the trouble that would ensue if two different threads tried to seek to different places and read, at the same time, on the same file descriptor. One solution that might work, however, it to satisfy new open requests with file descriptors that are in the queue of file descriptors that are waiting to be closed. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_close doesn't release always the file handle.
--- Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Ran <[EMAIL PROTECTED]> wrote: > > > Hi all, > > > > > > I *think* that sqlite3_close behave strangly. > > > > > > I use version 3.3.7 on Linux (Fedora Core 5). > > > > > > What I do is to open a database, and start a transaction in it. Then, > > > without ending the transaction, open again the database and simply close > > > it. > > > > > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > > > file handle is not released. So if I do it too many times, I run out of > > > file > > > handles. > > > > > > > This behavior is intentional. It is a work-around for a bug > > SQLite should find the inode of the database file via stat()'s st_ino > field prior to open() and if it is the same as an already opened > database file, it should use the same (refcounted) file descriptor, > eliminating the need for open() in this case. st_dev is needed to be checked as well... st_ino and st_dev are both required to uniquely identify regular files. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_close doesn't release always the file handle.
--- [EMAIL PROTECTED] wrote: > Ran <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I *think* that sqlite3_close behave strangly. > > > > I use version 3.3.7 on Linux (Fedora Core 5). > > > > What I do is to open a database, and start a transaction in it. Then, > > without ending the transaction, open again the database and simply close it. > > > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > > file handle is not released. So if I do it too many times, I run out of file > > handles. > > > > This behavior is intentional. It is a work-around for a bug SQLite should find the inode of the database file via stat()'s st_ino field prior to open() and if it is the same as an already opened database file, it should use the same (refcounted) file descriptor, eliminating the need for open() in this case. > in the design of posix advisory locks. With posix advisory locks, > if you open the same file twice, giving two file descriptors, > then you close one of the file descriptors, it deletes all the > locks on the other file descriptor. (No, I am not making this > up - I wish I were. This is proscribed behavior in the posix > standard.) > > So if you have a database open and in a transaction, it that > database connection is holding a lock. If you opened a second > connection and immediately closed it, and if that close operation > actually closed the file handle, then the locks held by the > first connection would be released. This would free other > processes to start reading and writing the database while an > update was still going on - resulting in database corruption. > > In order to prevent database corruption, SQLite embargos all > file close operations until all locks on that same file (possibly > held by different threads) have cleared. > > See http://www.sqlite.org/cvstrac/tktview?tn=561 and > http://www.sqlite.org/cvstrac/chngview?cn=1171 for additional > information. > -- > D. Richard Hipp <[EMAIL PROTECTED]> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disconnected machine with database, SELECT still works !
Martin Alfredsson wrote: Hi ! Tried to solve a problem and what I did was to open the database over the net and then physically disconnect the machine. I can still SELECT ! sqlite3_prepare() works and gives SQLITE_OK sqlite3_step() gives SQLITE_ROW but is very, very slow sqlite3_finalize() works. First when I do INSERT/UPDATE/DELETE I get an error. Is this normal ? I understand that parts of the database could be cached but then the time to do sqlite3_step() should not be affected. Or is the code doing sqlite_stop() and when it does not get a reply it takes a cached copy /Martin SQLite shouldn't be used in a network share. Sometimes it works, sometimes it doesn't. From http://www.sqlite.org/whentouse.html Situations Where Another RDBMS May Work Better * Client/Server Applications If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disconnected machine with database, SELECT still works !
>How exactly are you accessing the database over the network?
>
By that you mean ?
Two machines with Windows XP on a LAN.
Using file sharing a place my app.exe on one machine and
instructs it to use the file (database) on the other machine,
sqlite3_open("\\LW75\SHARE\data.sq3", )
I start my app, do a few selects and updates (my app does
them when it starts) and then disconnect the network cable.
>From: Trevor Talbot <[EMAIL PROTECTED]>
>Subject: Re: Disconnected machine with database, SELECT still works !
>Newsgroups: gmane.comp.db.sqlite.general
>Date: 2006-09-23 15:12:09 GMT (38 minutes ago)
>On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote:
>> Tried to solve a problem and what I did was to open the
>> database over the net and then physically disconnect
>> the machine.
>>
>> I can still SELECT !
>> sqlite3_prepare() works and gives SQLITE_OK
>> sqlite3_step() gives SQLITE_ROW but is very, very slow
>> sqlite3_finalize() works.
>>
>> First when I do INSERT/UPDATE/DELETE I get an error.
>How exactly are you accessing the database over the network?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite3_close doesn't release always the file handle.
Ran <[EMAIL PROTECTED]> wrote: > Hi all, > > I *think* that sqlite3_close behave strangly. > > I use version 3.3.7 on Linux (Fedora Core 5). > > What I do is to open a database, and start a transaction in it. Then, > without ending the transaction, open again the database and simply close it. > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > file handle is not released. So if I do it too many times, I run out of file > handles. > This behavior is intentional. It is a work-around for a bug in the design of posix advisory locks. With posix advisory locks, if you open the same file twice, giving two file descriptors, then you close one of the file descriptors, it deletes all the locks on the other file descriptor. (No, I am not making this up - I wish I were. This is proscribed behavior in the posix standard.) So if you have a database open and in a transaction, it that database connection is holding a lock. If you opened a second connection and immediately closed it, and if that close operation actually closed the file handle, then the locks held by the first connection would be released. This would free other processes to start reading and writing the database while an update was still going on - resulting in database corruption. In order to prevent database corruption, SQLite embargos all file close operations until all locks on that same file (possibly held by different threads) have cleared. See http://www.sqlite.org/cvstrac/tktview?tn=561 and http://www.sqlite.org/cvstrac/chngview?cn=1171 for additional information. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disconnected machine with database, SELECT still works !
On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote: Tried to solve a problem and what I did was to open the database over the net and then physically disconnect the machine. I can still SELECT ! sqlite3_prepare() works and gives SQLITE_OK sqlite3_step() gives SQLITE_ROW but is very, very slow sqlite3_finalize() works. First when I do INSERT/UPDATE/DELETE I get an error. How exactly are you accessing the database over the network? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_close doesn't release always the file handle.
I filed the bug report as you suggested: #1990. Ran. On 9/23/06, Will Leshner <[EMAIL PROTECTED]> wrote: On 9/22/06, Ran <[EMAIL PROTECTED]> wrote: > What I do is to open a database, and start a transaction in it. Then, > without ending the transaction, open again the database and simply close it. > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > file handle is not released. So if I do it too many times, I run out of file > handles. Would you be willing to file a bug report about this? I have a "customer" who is claiming a similar problem and if you can demonstrate the problem with your code it might be worth having it checked out by the SQLite team. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disconnected machine with database, SELECT still works !
Hi ! Tried to solve a problem and what I did was to open the database over the net and then physically disconnect the machine. I can still SELECT ! sqlite3_prepare() works and gives SQLITE_OK sqlite3_step() gives SQLITE_ROW but is very, very slow sqlite3_finalize() works. First when I do INSERT/UPDATE/DELETE I get an error. Is this normal ? I understand that parts of the database could be cached but then the time to do sqlite3_step() should not be affected. Or is the code doing sqlite_stop() and when it does not get a reply it takes a cached copy /Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] version 3.3.4
On 9/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi all, Anyone knows where I could download the precompiled binary of version 3.3.4 from? Just go to http://www.sqlite.org/download.html and find a package that you need. Copy its link and replace current version number in it with the one you need. Following that logic, precompiled DLL for 3.3.4 would be at: http://www.sqlite.org/sqlitedll-3_3_4.zip -- Nemanja Corlija <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Locking problems
>Usually, the SQLITE_LOCKED means that you tried to prepare or execute a >new SQL statement without resetting or finalizing a previous one on a >single connection. The statements should be prepared and executed this I'm NOT getting SQLITE_LOCKED. The error I get is SQLITE_BUSY (5). For some reason sqlite3_errmsg() returns the string "database is locked" when the error is SQLITE_BUSY. And, yes I have tripplechecked all calls. What I need to know is how to avoid this error and how to ensure the database is not locked when the app exits. /Martin > Hi ! > > I'm getting a lots of "database is locked" (code:5). > > My app keeps giving up on one machine, it might be that > there is a network problem but I not 100% sure. > > Anyway, when the app hangs all the other machines > including the machine where the database file is get > the "database is locked" (code:5) error. > To get rid of the error I have to close my app on all > machines (five total) and sometimes reboot the machine > with the database. As far as I can tell there are no > processes still running. > > The database is as far as I know not corrupted and seems > ok after "restarting the network". > > Why do I get "database is locked" (code:5) and what do I > have to do to avoid it ? > I can try to ensure that there is a try/catch block but > I'm unsure how to unlock the database, Is it enough to > do a sqlite3_close() or do I need to ROLLBACK TRANSACTION > and sqlite3_finalize() before I sqlite3_close() ? > > Windows XP, SQLite 3.3.4. > > /Martin > ma1999ATjmaDOTse > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Schema change hook API
Hello, I'm SQLiteDBMS developer. SQLiteDBMS is minor F/OSS project for SQLite3 via TCP/IP network. http://sqlitedbms.sf.net SQLiteDBMS will support replication on next release. Normal SQL and prepared statement was captured by sqltie3_*_hook. But, "CREATE ...", "DROP ..." and "ALTER ..." statement cannot hook. I attempted it using sqlite3_set_authorizer. But, has other problem. CREATE TABLE test (a int primary key, b int, c int) Above SQL calls sqlite3_set_authorizer callback twice. CREATE TABLE and CREATE INDEX I want schema change hooking APIs. Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
