Re: [sqlite] Multiple update processes
> Good point. I think the right fix for this is for me to change SQLite > so that it does not invoke the busy callback when it is trying to > acquire a RESERVED lock. If it fails to get a RESERVED lock, then > it returns SQLITE_BUSY right away. The busy callback will then only > be invoked when trying to get a PENDING lock. I just tried the modification you suggested and it appears to make things much better, thanks. I'd just like to confirm I'm handling the SQLITE_BUSY correctly with this modification: When I receive a SQLITE_BUSY from sqlite3_step and I am in a transaction, I am doing a rollback (I assume this releases any SHARED locks) before retrying the entire transaction. For an automatically started transaction I am just retrying the sqlite3_step. Thanks, Richard
Re: [sqlite] Compiling sqlite3.0.3 on dec-osf1 with gcc 3.04 failed
On Wednesday 04 August 2004 7:27 am, D. Richard Hipp wrote: > Detlef Groth wrote: > > Hello, > > > > I could recently compile the sqlite2.x code without problems but now I > > can't compile the the 3.0.3 branch. > > > > The main problem is in: > > sqliteInt.h:129: parse error before "uptr" > > sqliteInt.h:129: warning: data definition has no type or storage class > > Try adding '-DUINTPTR_TYPE=u64' to the compiler command line. Please let > me know if that helps. The tarball for 3.03 doesn't include fixes for 64 bit. The OP needs to grab the latest CVS and compile it, it compiles clean on my AMD64 whereas 3.03 doesn't. I'm running into this same problem with DBD::SQLite, which grabs the tarball. It would be nice if a new release would be issued soon. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
[sqlite] SQLite3 -journal
I have finally succeeded in getting DBD::SQLite v 1.02 compiled and installed on my AMD64 and have therefore started working with SQLite3. After reading the documentation about locking and concurrency here http://www.sqlite.org/lockingv3.html I am left with the impression that the life of the -journal file on disk should be relatively limited. IOW, once the transactions are committed, the -journal is deleted from disk. I'm not seeing that here, the -journal file seems to sit there throughout the life of the program (in this case, a deamon that runs in the background). In fact, it remains on disk even after the program is terminated and only disappears after the program is restarted (only to come back again, presumably after a change to the db). Is this the correct behavior or should I start looking for a problem with either the perl program itself or DBD::SQLite? Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] SQLite3 -journal
Scott Leighton wrote: I have finally succeeded in getting DBD::SQLite v 1.02 compiled and installed on my AMD64 and have therefore started working with SQLite3. After reading the documentation about locking and concurrency here http://www.sqlite.org/lockingv3.html I am left with the impression that the life of the -journal file on disk should be relatively limited. IOW, once the transactions are committed, the -journal is deleted from disk. I'm not seeing that here, the -journal file seems to sit there throughout the life of the program (in this case, a deamon that runs in the background). In fact, it remains on disk even after the program is terminated and only disappears after the program is restarted (only to come back again, presumably after a change to the db). Is this the correct behavior or should I start looking for a problem with either the perl program itself or DBD::SQLite? You should start looking for problems. The -journal file should be deleted the moment a transaction commits. In fact, transaction commit is defined as the moment when the journal file is deleted. If the journal is never deleted, then the commit never occurs. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 3:42 pm, D. Richard Hipp wrote: > > You should start looking for problems. The -journal file should be deleted > the moment a transaction commits. In fact, transaction commit is defined > as the moment when the journal file is deleted. If the journal is never > deleted, then the commit never occurs. Thanks, I was afraid of that. Off to search for the cause. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
RE: [sqlite] Building for Windows
Thank you all for answer - it seems to be working now for some reason :-/ I just got the new source out today and tried rebuilding and it worked. The only thing I changed is that I install Tcl into my Cygwin, which I didn't have before... Anyway, the good thing is that the problem is solved :-) Regards, Dennis // Software for animal shelters http://www.smartpethealth.com // -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 1:08 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Building for Windows Dennis Volodomanov wrote: > > Is there any way to rebuild the SQLite v3 for Windows? It seems that > the makefiles that come with it are for *nix-based machines only :-( > Dennis, I use the MinGW/MSYS build environment to build under Win XP. It generate native Win32 libraries, DLL, and import libs for use with other compilers. You can download and install MinGW and MSYS from http://www.mingw.org/. You will need to install the tcl/tk package as well to run the test suite. My build process is almost exactly as described in the How To Compile wiki page http://www.sqlite.org/cvstrac/wiki?p=HowToCompile I create the build directory beside the sqlite directory obtained from CVS. CD into the build directory and run ../sqlite/configure to generate a Makefile in the build directory. Then run the following make commands. "make" to build libraries and sqlite.exe "make test" to build and run the sqlite test suite "make dll" to build windows DLL "make implib" to build import libraries to allow the DLL to be used with Borland and/or Microsoft compilers "make install" to install sqlite.exe into the MSYS environment It has worked flawlessly for me since about version 2.8.12 or so. Good luck.
[sqlite] Version 3.0.4
Version 3.0.4 (beta) of SQLite is now available on the website. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Version 3.0.4
Hmm, it seems that the MAX_BYTES_PER_ROW define is gone from this release? So, are there any limits on the row size or am I missing something? Thanks, Dennis // Software for animal shelters http://www.smartpethealth.com // -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Monday, 9 August 2004 10:49 AM To: [EMAIL PROTECTED] Subject: [sqlite] Version 3.0.4 Version 3.0.4 (beta) of SQLite is now available on the website. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 3:53 pm, Scott Leighton wrote: > On Sunday 08 August 2004 3:42 pm, D. Richard Hipp wrote: > > You should start looking for problems. The -journal file should be > > deleted the moment a transaction commits. In fact, transaction commit is > > defined as the moment when the journal file is deleted. If the journal > > is never deleted, then the commit never occurs. > > Thanks, I was afraid of that. Off to search for the cause. > OK, I've located my problem. I think I need a better understanding of the 'locking'. Looks like my commits are failing due to earlier statements remaining 'in progress'. What I don't understand is this 'database table is locked' stuff. This is a single process (no threads) accessing the database, what is locking it? A previous SQL statement that hasn't finished or something? The identical code worked fine with SQLite 2.8.x so the problem has to be something related to the change in locking in 3.x.x but I'm not seeing it. BTW, this is using the just release 3.04 version. Scott POPFile Engine v0.21.1 running DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::db commit failed: cannot commit transaction - SQL statements in progress(1) at dbdimp.c line 540 at /usr/local/bin/popfile/Classifier/Bayes.pm line 1289. DBD::SQLite::db begin_work failed: Already in a transaction at /usr/local/bin/popfile/Classifier/Bayes.pm line 1260. DBD::SQLite::db commit failed: cannot commit transaction - SQL statements in progress(1) at dbdimp.c line 203 at /usr/local/bin/popfile/Classifier/Bayes.pm line 1289. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. helphand:/var/log/popfile # -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] Version 3.0.4
Dennis Volodomanov wrote: Hmm, it seems that the MAX_BYTES_PER_ROW define is gone from this release? So, are there any limits on the row size or am I missing something? There are no arbitrary limits on row size any more. The maximum row size is 18446744073709551616 (2**64) bytes, in theory. In practice, an entire row has to be able to fit in contiguous memory, so you are likely to run out of memory before you fill up a row. Also, performance will begin to fall off as the row size increases. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] SQLite3 -journal
Scott Leighton wrote: OK, I've located my problem. I think I need a better understanding of the 'locking'. Looks like my commits are failing due to earlier statements remaining 'in progress'. What I don't understand is this 'database table is locked' stuff. This is a single process (no threads) accessing the database, what is locking it? A previous SQL statement that hasn't finished or something? The identical code worked fine with SQLite 2.8.x so the problem has to be something related to the change in locking in 3.x.x but I'm not seeing it. Whenever you start a SELECT statement (by calling sqlite3_step()) but have not finalized that statement (using sqlite3_finalize() or sqlite3_reset()) the statement is probably holding a lock on the table(s) that are being queried. Other SELECT statements can read those tables, but no other statements can INSERT, UPDATE or DELETE those tables until after all the SELECTs have been finalized. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 6:26 pm, D. Richard Hipp wrote: > Scott Leighton wrote: > > Whenever you start a SELECT statement (by calling sqlite3_step()) but have > not finalized that statement (using sqlite3_finalize() or sqlite3_reset()) > the statement is probably holding a lock on the table(s) that are being > queried. Other SELECT statements can read those tables, but no other > statements can INSERT, UPDATE or DELETE those tables until after all the > SELECTs have been finalized. Once again, thank-you! That makes complete sense and explains exactly what I am seeing. You are a lifesaver, I was really struggling with this, now I have a lead to solve it! Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] SQLite3 -journal [SOLVED]
On Sunday 08 August 2004 6:52 pm, Scott Leighton wrote: > On Sunday 08 August 2004 6:26 pm, D. Richard Hipp wrote: > > Scott Leighton wrote: > > > > Whenever you start a SELECT statement (by calling sqlite3_step()) but > > have not finalized that statement (using sqlite3_finalize() or > > sqlite3_reset()) the statement is probably holding a lock on the table(s) > > that are being queried. Other SELECT statements can read those tables, > > but no other statements can INSERT, UPDATE or DELETE those tables until > > after all the SELECTs have been finalized. > > Once again, thank-you! That makes complete sense and explains > exactly what I am seeing. You are a lifesaver, I was really struggling with > this, now I have a lead to solve it! > Solved! Just to close the loop on this for the benefit of any other DBD::SQLite users who may be trying to upgrade from v 0.31 to v 1.x.x. Watch out for cases where your former working code left unfinished SELECT statements since under SQLite v 3.x.x they will hold locks on the database causing subsequent SQL calls to fail. In my case, the code had several instances where we knew there could only be one result to the query so we basically grabbed that result and moved on, e.g., $sth = $dbh->prepare($statement); $sth->execute; return $sth->fetchrow_arrayref; This left the SELECT statement in a not finalized state and the dataset locked. Worked fine under SQLite 2.8.xx but it fails under 3.x.x. We had to change the code to, $sth = $dbh->prepare($statement); $sth->execute; my $result= $sth->fetchrow_arrayref; $sth->finish(); return $result; to solve the problem by finalizing the SELECT so the lock came off the database. Hope that helps someone else from banging their head against the wall searching for the problem . Again, a big thanks to D. Richard Hipp for pointing me in the right direction! Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
