[sqlite] Re: [OT] Re: [sqlite] locking via windows nfs client
On Fri, 20 Aug 2004, Ara.T.Howard wrote: > > NFS locks can get stale if you have network problems. The server loses the > > client, the lock remains on the server, nobody can lock the file. Everyone > > using NFS eventually runs into this, but good network setup and good kernel > > choices can often mitigate it. > > ours is pretty good but i have that exact situation right now. it's happened > in a peice of code i've designed to try and break nfs locking. it forks > children which get a lock and don't release it, but simply '_exit'. also, the > parent randomly sends SIGKILL to the children after forking them. this loops > as fast as possible on many clients locking the same file. it takes a few > days, but i can create the situation you describe. i've got a stale lock now > that i can see in /proc/locks - and can see the pid of it, but cannot find > this pid on any system. do you know if there is a way to find out which host > the nfs sever thinks the lock is on? i've got a thread going on the nfs list > regarding this > but have gotten no help on the specific issue. On a netapp you can show locked inodes and it lists them by host. I have no idea about other implementations. I suspect this is very platform dependant. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] Question: Advice for DAO/ADO user
Hi,
I'm just evaluating sqlite for several potential projects. I like it so far.
But, I have a question. In dao/ado the process of changing recordings is
normally something like (not code):
Recordset = "select * from table"
While( not recordset.eof )
{
Recordset.edit
Recordset.field( "blah" ) = "test"
Recordset.update
}
(sorry if my example sucks and blows but I tried to keep it as simple and as
language neutral as possible)
But in sqlite I can find no similar functions.
Is the only alternative to the ado/dao mechanism to use the "UPDATE"
statement?
Russ.
Re: [sqlite] Tcl interface updated to require TCL version 8.4
Mauricio Piacentini wrote: Mosley, Harlan R. wrote: I have been considering sqlite for my windows application, and I am an individual who has not the resources to upgrade, so I continue to use TclPro 1.4 (8.3.2) because of the tools in the TcLPro package. I would not be able to use the latest sqlite because of this, but probably could still consider 2.8.15. Just to let you know you don't necessarily need TCL to use the base SQLite code and packages, to the best of my knowledge. I at least do not have it installed on Windows (but I compile the sources embedded into my project.) Just remove the tclsqlite.c source file from your build. Of course you will not be able to run the TCL tests, but you can definately run your SQLite-enabled program without having TCL installed. Regards, Mauricio True, I compile my sqlite3.dll without the TCL stuff just fine. -Andy
Re: [sqlite] Tcl interface updated to require TCL version 8.4
Mosley, Harlan R. wrote: I have been considering sqlite for my windows application, and I am an individual who has not the resources to upgrade, so I continue to use TclPro 1.4 (8.3.2) because of the tools in the TcLPro package. I would not be able to use the latest sqlite because of this, but probably could still consider 2.8.15. Just to let you know you don't necessarily need TCL to use the base SQLite code and packages, to the best of my knowledge. I at least do not have it installed on Windows (but I compile the sources embedded into my project.) Just remove the tclsqlite.c source file from your build. Of course you will not be able to run the TCL tests, but you can definately run your SQLite-enabled program without having TCL installed. Regards, Mauricio
Re: [sqlite] Tcl interface updated to require TCL version 8.4
At 2:38 PM -0400 8/20/04, D. Richard Hipp wrote: I have modified the TCL interface to SQLite version 3.0 so that it now requires TCL 8.4 or later. Who will be troubled by this? If the TCL interface was a separate distribution then anything you do with it won't affect me. -- Darren Duncan
[sqlite] Tcl interface updated to require TCL version 8.4
I have modified the TCL interface to SQLite version 3.0 so that it now requires TCL 8.4 or later. Who will be troubled by this? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Temporary tables
Keith Herold wrote:
I am using a temporary table for some results, and I wanted to have the
rowids reset every time the table is used, so I drop the table if it is
already created, and then recreate it and fill it with results .
I thought I could use a SELECT along the lines of
SELECT 1 FROM sqlite_master WHERE (type = 'table') AND ('name =
tblTest') ;
But this doesn't seem to work. Is there another place to check for a
temporary table's existence (sqlite 2.8.13)?
TEMP tables appear in sqlite_temp_master, not sqlite_master.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Temporary tables
I am using a temporary table for some results, and I wanted to have the
rowids reset every time the table is used, so I drop the table if it is
already created, and then recreate it and fill it with results .
I thought I could use a SELECT along the lines of
SELECT 1 FROM sqlite_master WHERE (type = 'table') AND ('name =
tblTest') ;
But this doesn't seem to work. Is there another place to check for a
temporary table's existence (sqlite 2.8.13)?
--Keith
**
- I'm not a professional; I just get paid to do this.
- Good writing lets the reader think about 'what'
was written, not 'how' it was written.
**
Re: [sqlite] locking via windows nfs client
On Fri, 20 Aug 2004, Elmar Haneke wrote: ssh only. period. nothing gets in or out except on 22. You can forward the database-access through an SSH-tunnel. can you do this using passphrases in a manner that works across reboots w/o embedding passphrases? i can't figure out how to - at some point (ssh-agent, or whatever) you'll need to type a passphrase. this means the systems cannot reboot on a weekend and come up without assistance... i'd be happy to learn that i was wrong. additionally this system must be 24/7 and setting up HA setups for postgresql is tricky (i've done it twice before). Perhaps you should look at FirebirdSQL it is suitable for 24/7 without tricks. i'll look. so it's already a single point of failure - i simply don't want to ADD a point of failure. You still have two points of failiure: a failiure of the NFS-Server itself and an stale-NFS-lock that might also tear down the system while the NFS-Server itself is still ok. true - but one is already unavoidable. all the ha db systems i have setup would introduce a much hight change of failure than these. my system is resistent to stale nfs lock failures already. cheers. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
[sqlite] Foreign key constraint trigger generator cleanups
Ive put up a new version of my foreign key constraint trigger generation tool, the parser now better understands supported syntax, and the way is better paved to support generating CHECK constraint triggers in the next release Download it at: http://odin.nmxs.com/~cpisto/sqlite_fk.tgz windows/linux binaries included, public domain source -Cody
Re: [sqlite] locking via windows nfs client
> ssh only. period. nothing gets in or out except on 22. You can forward the database-access through an SSH-tunnel. additionally this system must be 24/7 and setting up HA setups for postgresql is tricky (i've done it twice before). Perhaps you should look at FirebirdSQL it is suitable for 24/7 without tricks. so it's already a single point of failure - i simply don't want to ADD a point of failure. You still have two points of failiure: a failiure of the NFS-Server itself and an stale-NFS-lock that might also tear down the system while the NFS-Server itself is still ok. Elmar
[sqlite] [OT] Re: [sqlite] locking via windows nfs client
On Fri, 20 Aug 2004, Matt Sergeant wrote: Right, but regardless of what locking you ADD to the system, sqlite is still going to do the lockd NFS locking itself. At least until you provide patches to make NFS locking safe :-) my current scheme, which may not be entirly fool proof, i first obtain a read or write lockfile. because i have already obtained the 'correct' type of lock the fcntl call in sqlite should ALWAYS return success - eg i should never see 'database locked' or, in my case using ruby, have a SQLite::Database::LockedError thrown. iff i do see this error/exception then i know that nfs locking has broken (stale lock) and upgrade my lock to write (if i have a read) and then do mv db db.tmp mv db.tmp db this clears up any stale locks. i assume here that in the case of broken locking - fcntl, when used with non-blocking flags, will still return an error and not block. if fcntl does block with flags set to non-blocking then i'll simply shoot myself and anyone who says the word 'posix'. ;-) make sense? NFS locks can get stale if you have network problems. The server loses the client, the lock remains on the server, nobody can lock the file. Everyone using NFS eventually runs into this, but good network setup and good kernel choices can often mitigate it. ours is pretty good but i have that exact situation right now. it's happened in a peice of code i've designed to try and break nfs locking. it forks children which get a lock and don't release it, but simply '_exit'. also, the parent randomly sends SIGKILL to the children after forking them. this loops as fast as possible on many clients locking the same file. it takes a few days, but i can create the situation you describe. i've got a stale lock now that i can see in /proc/locks - and can see the pid of it, but cannot find this pid on any system. do you know if there is a way to find out which host the nfs sever thinks the lock is on? i've got a thread going on the nfs list regarding this but have gotten no help on the specific issue. thanks again for your helpful comments. kinds regards. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] sqlite3_set_auxdata and sqlite3_get_auxdata?
Michael Roth wrote: what are the purpose of sqlite3_set_auxdata() and sqlite3_get_auxdata()? Are they part of the API? They are don't mentioned in the documentation, IIRC. These routines are designed to provide persistence of meta-data on arguments to SQL functions. For example, to implement a REGEXP function, we'd like to compile the regular expression into some kind of binary data structure. And we'd like to reuse that compiled binary structure on subsequent calls to REGEXP for which the regular expression does not change. sqlite3_set_auxdata() and sqlite3_get_auxdata() are designed to facilitate that. They are not currently used. I'll document them when they come into use. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] sqlite3_set_auxdata and sqlite3_get_auxdata?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello list, what are the purpose of sqlite3_set_auxdata() and sqlite3_get_auxdata()? Are they part of the API? They are don't mentioned in the documentation, IIRC. Thank you. Michael Roth -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBJi9tSIrOxc3jOmoRAh40AKCCb/UWyTTfkn24JX/pm2uW1XxkEQCgj2wG 7t9DBX1B/7Ulpo48y3im5eY= =KjV/ -END PGP SIGNATURE-
Re: [sqlite] locking via windows nfs client
On Fri, 20 Aug 2004, Andrew Piskorski wrote: On Fri, Aug 20, 2004 at 08:45:06AM -0600, Ara.T.Howard wrote: On Fri, 20 Aug 2004, Matt Sergeant wrote: As far as linux -> nfs access goes it all depends on the quality of the NFS implementation, and the kernel drivers you're using. It's not easy to create a stable NFS locking system. We've had lots of problems with it. If you can, go with local disk. i cannot - the system is a priority queue system where the queue db is accessed via around 30 nodes. i am wrapping any code that will grab a write lock with an nfs safe lockfile creation to ensure only So, why are using embedded SQLite code on each of the 30 nodes to access the single database file over NFS? Isn't your situation EXACTLY what the client-server RDBMS was invented for? Why don't you simply run PostgreSQL (or your own little custom RDBMS using SQLite) on one machine, and have all 30 clients talk to it over the network? we're a dot.gov and, since sept. 11th our host a clamped down to an unbelieveable level - ssh only. period. nothing gets in or out except on 22. additionally this system must be 24/7 and setting up HA setups for postgresql is tricky (i've done it twice before). i'm interested in the new mysql cluster for this, but it's pretty new. the thing is, all processing stops w/o this particular nfs server (it's where all the code lives) so it's already a single point of failure - i simply don't want to ADD a point of failure. your point, however, is well taken. the other problem is the security involved with connecting to and rdbms with code running all over the place - our security rules are totally dranconian and even this is a daunting task. nfs is the last refuge! ;-) -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] locking via windows nfs client
On Fri, Aug 20, 2004 at 08:45:06AM -0600, Ara.T.Howard wrote: > On Fri, 20 Aug 2004, Matt Sergeant wrote: > > >As far as linux -> nfs access goes it all depends on the quality of > >the NFS implementation, and the kernel drivers you're using. It's > >not easy to create a stable NFS locking system. We've had lots of > >problems with it. If you can, go with local disk. > > i cannot - the system is a priority queue system where the queue db > is accessed via around 30 nodes. i am wrapping any code that will > grab a write lock with an nfs safe lockfile creation to ensure only So, why are using embedded SQLite code on each of the 30 nodes to access the single database file over NFS? Isn't your situation EXACTLY what the client-server RDBMS was invented for? Why don't you simply run PostgreSQL (or your own little custom RDBMS using SQLite) on one machine, and have all 30 clients talk to it over the network? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] locking via windows nfs client
On Fri, 20 Aug 2004, Matt Sergeant wrote: As far as linux -> nfs access goes it all depends on the quality of the NFS implementation, and the kernel drivers you're using. It's not easy to create a stable NFS locking system. We've had lots of problems with it. If you can, go with local disk. i cannot - the system is a priority queue system where the queue db is accessed via around 30 nodes. i am wrapping any code that will grab a write lock with an nfs safe lockfile creation to ensure only one process on the system will even ask for write locks at a time. this, suprisingly, also gives a massive performance boost as my timeout algorithim seems to be much better than the one in lockd. if you are interested check out http://raa.ruby-lang.org/project/lockfile/ it includes both an ruby api and a command line tool to it useful even from other programs. (download site is down at the moment however - should be up tomorrow). it has been tested very extensively and no bugs have yet been found. anyhow, i'm very interested to hear what sort of locking issues you've had - our system has been working fine for months but it does make me nervous. any information you could provide regarding issues would be most apprecicated! kind regards. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
[sqlite] Problem with UPDATE-statement
Hi,
This is the relevant information in the database:
/*
Contains most of the info static on a player.
FirstUpdate and LastUpdate is a convenience to avoid JOIN if I can,
the information will be presented very often with the static info.
*/
CREATE TABLE Player (
ID integer primary key,
FirstUpdate integer,
LastUpdate integer
);
/*
Contains all dynamic info on a player.
Related to Player thru PlayerUpdate.PlayerID == Player.ID.
Date is between Player.FirstUpdate and Player.LastUpdate
where PlayerUpdate.PlayerID == Player.ID.
*/
CREATE TABLE PlayerUpdate (
ID integer primary key,
PlayerID integer,
Date integer
);
Now, I have removed all rows in PlayerUpdate where Date is for example
0 and want to make sure I update FirstUpdate in the Player table.
However this statement fails due to the Player.ID connection which is
unknown.. Is there any way to do it somewhat like this:
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID ==
Player.ID ORDER BY Date ASC)
WHERE FirstUpdate == 0;
Rather than this:
SELECT ID FROM Player WHERE FirstUpdate == 0; -- Returns: {1, 3, 6}
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID
== 1)
WHERE ID == 1;
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID
== 3)
WHERE ID == 3;
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID
== 6)
WHERE ID == 6;
I really want to avoid the loop over all the ID's to be updated. It
just seems to inefficient. If I'm unclear on some point, let me know.
I'm happy to send any info or to explain more.
-- kent
Re: [sqlite] locking via windows nfs client
On 18 Aug 2004, at 23:13, Ara.T.Howard wrote: has anyone out there used sqlite from a windows machine when the db resided on an nfs filesystem mounted using the windows nfs client? if so, does it work? have you attempted concurrent access from other windows machines? other *nix machines? i'm considering an application where process from both linux and window machines may access an sqlite db located on a shared nfs fs. As far as linux -> nfs access goes it all depends on the quality of the NFS implementation, and the kernel drivers you're using. It's not easy to create a stable NFS locking system. We've had lots of problems with it. If you can, go with local disk. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] sqlite3_last_insert_rowid questions
Hello, Can I force sqlite to start count ROWID from 0? By default it starts from 1. Regards, Andrei -- Andrei Emeltchenko [EMAIL PROTECTED]
Re: [sqlite] Upgrading SQLite versions
Steven Van Ingelgem wrote: Hi, 1/ I am currently using 2.8.13 and I wanted to upgrade it to 2.8.15... Now when I do that, my program crashes when I try to open (sqlite_open) the database on the following function: rc = sqliteBtreeFactory(db, zFilename, 0, MAX_PAGES, >aDb[0].pBt); Is that "normal" behaviour (for instance: no binary compatibilities between 2.8.x versions?). Of course that would be better then to immediatly upgrade to 3.0 if i have to convert all the databases ofcourse. But then the same question stays... Is there compatibility between those versions? (again I mean: just update the source & I can still work on the same databases without doing something special). 2/ I use wxSQLite to access databases. When 1 person has my program open (just open, that means not doing anything). Then another person opens a program from a different location, and access the same databases. He can read it without a problem, but he can't do any insert/update/delete ? (so I think he can acquire readlock, but no writelock). [using 2.8.13]. Is this a feature or a bug? (as far as I understood, sqlite locks the database file when it is needed, but not all the time?). Could someone answer those questions, I would be very gratefull. BTW, if I am unclear somewhere, please request more information! Thx, KaReL (aka Steven) *Main Webpage* : http://www.karels0ft.be/ *ICQ #*: 35217584 Sqlite minor versions should have compatible database formats, so if you're having crashes, there's probably a problem somewhere else. For the other question, how are the two people accessing the database file? NFS, samba, or just different terminals on the same box? This can make a difference, as locking on network filesystems has varying levels of success(although it is never reccomended). John LeSueur
[sqlite] Upgrading SQLite versions
Hi, 1/ I am currently using 2.8.13 and I wanted to upgrade it to 2.8.15... Now when I do that, my program crashes when I try to open (sqlite_open) the database on the following function: rc = sqliteBtreeFactory(db, zFilename, 0, MAX_PAGES, >aDb[0].pBt); Is that "normal" behaviour (for instance: no binary compatibilities between 2.8.x versions?). Of course that would be better then to immediatly upgrade to 3.0 if i have to convert all the databases ofcourse. But then the same question stays... Is there compatibility between those versions? (again I mean: just update the source & I can still work on the same databases without doing something special). 2/ I use wxSQLite to access databases. When 1 person has my program open (just open, that means not doing anything). Then another person opens a program from a different location, and access the same databases. He can read it without a problem, but he can't do any insert/update/delete ? (so I think he can acquire readlock, but no writelock). [using 2.8.13]. Is this a feature or a bug? (as far as I understood, sqlite locks the database file when it is needed, but not all the time?). Could someone answer those questions, I would be very gratefull. BTW, if I am unclear somewhere, please request more information! Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584
