[sqlite] System.Data.SQLite, Virtual Tables, and ThreadAbortException Issues
Yes, this branch does resolve the issue, thanks much! This branch looks to be from the current mainline; I'll apply the fix locally to the 1.0.96.0 released version until the next release of System.Data.SQLite. Thanks, MikeN -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: Tuesday, May 12, 2015 8:43 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite, Virtual Tables, and ThreadAbortException Issues Mike Nicolino wrote: > > The issue is isolated to ThreadAbortException since you can't catch it > and prevent it from being re-thrown. I did find a 'fix' for a similar > issue in SystemData.SQLite back on 10/11/2012: > Thanks for the excellent analysis of the issue. I believe you are completely correct. The previous "fix" was for sqlite3_prepare(), to prevent any leakage of native handles prior to their ownership being "transferred" into an official critical handle object. I've now implemented the necessary changes on a branch, pending more testing, here: https://system.data.sqlite.org/index.html/timeline?r=stepNoThreadAbort Could you test these changes in your environment and let us know if they clear the issue? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] xBestIndex() implementation question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/13/2015 08:06 PM, Jilong Kuang wrote: > Sure, that is one option. But I'm just wondering if inside the > xBestIndex() we can get the value field, we can do a much better > job to give the cardinality info. Note that the value could be something other than a constant (eg an expression like price > delivery_charge*0.9) You are just helping SQLite pick the best index out of all the possibilities, so as long as they compare sensibly it is fine. SQLite's analyze command builds statistical information that is useful (eg how many different values there are, how (un)evenly they are distributed). You could do something similar. Or just follow Eric's suggestion :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlVUGasACgkQmOOfHg372QSPcACgpmvvhwEEmj9+ccVgI+qTaw0B y/YAn2WtrppfLn0PvDpa+n1loqFYgzQW =vskS -END PGP SIGNATURE-
[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.
Hi sir, Thanks a lot. It is working fine with the same path and with multiprocess. Thanks and regards Deepak On Wednesday 13 May 2015 06:44 PM, Richard Hipp wrote: > On 5/13/15, Deepak Hegde wrote: >> I have gone through the document and it says, it is not thread safe. >> What if the same database is used in the multiple processes? >> Can we have actual database path and this temporary path same? >> > process!=thread. It is perfectly safe to run PRAGMA > temp_store_directory and point it to the same directory on multiple > processes. It is perfectly safe to have temp_store_directory and the > main database directory be the same. -- Thanks and Regards Deepak
[sqlite] Tests regarding custom build of SQLite
In order to run .test files(sqlite test codes) in SQLite, I used ./testfixture NAMEOFFILE.test Can anyone kindly tell which build of sqlite will TESTFIXTURE access? Because even when I make modifications to sqlite3.c source code, the output of the test file under test has no effect. And one more question is that, In order to test my custom build of sqlite3.c, I installed both TCL and SQLite, and gave the command ./configure After that the source file sqlite3.c file is formed and I replaced the original sqlite3.c file with my customized sqlite3.c with the same name. But when I ran make test command, the program again replaces my customized sqlite3.c with the original sqlite3.c source file and tests it. So I am not able to run tests on my modified file. So can anyone tell why it replaces with original file and what to do in order to test my custom build.
[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.
Hi, Thanks for the response. I didn't use index since it was a one time execution. After making the change to a temporary path with 64MB it is working fine. I have some queries: I have gone through the document and it says, it is not thread safe. What if the same database is used in the multiple processes? Can we have actual database path and this temporary path same? Thanks a lot. Thanks and Regards Deepak On Wednesday 13 May 2015 10:45 AM, Richard Hipp wrote: > On 5/12/15, Deepak Hegde wrote: >> Hi everyone, >> >> I am facing a issue and details are as below: >> >> 1) Database table have around 15 table. One of the table have around >> 66000 entries and this table have about 25 columns. >> 2) This table with 66000 entry have field called TITLE with TEXT type >> with table name AUDIO. >> 3) When I run a query like: SELECT TITLE FROM AUDIO WHERE VALID!=0 ORDER >> BY TITLE ASC; (here VALID is also a column), >> sqlite3_step() is returning error SQLITE_FULL. > I assume you have no index on AUDIO.TITLE, correct? > > What is the result of: > > SELECT sum(length(title)) FROM audio WHERE valid!=0; > > I'm guessing that result will be more than 2MB. Without an index on > audio.title, SQLite will need to create some temporary files used for > sorting. Probably those temporary files are still in your 2MB tempfs > and are filling it up. > > You could create an index on audio.title to avoid the sort. > > You might also want to use "PRAGMA temp_store_directory" > (https://www.sqlite.org/pragma.html#pragma_temp_store_directory) to > force SQLite to use your larger temporary storage volume. > >> 4) Database is created in a tempfs path with 64MB space (This is tmpfs >> is not a default one it is created only for the database creation). >> And default tempfs have 2MB of space. >> 5) Created database size is at around 7.5MB. >> >> please can anyone help me in understanding how this query works? Is this >> error is due to no enough cache space for database to prepare the query? >> >> Please help me in resolving this problem. Thanks. >> >> Thanks and Regards >> Deepak >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > -- Thanks and Regards Deepak
[sqlite] Query on Sample SQLite Application
Hello, We are using Sample Sql Lite Project provided by Telerik App Builder: http://docs.telerik.com/platform/appbuilder/sample-apps/sample-sqlite We are using sql lite plugin 1.0.1 suggested by the Telerik as it works all fine on the ANDROID AND Apple. Following scenario compiles our problem. We have basic crud operation expect that we do not update. Our each records amounts about approx 1.10kb 1 rec = 1.10Kb. We Inserted 10 rec which worked all fine on Android and Apple , we were able to read and delete We Inserted 1000 rec, where we were able to insert all records, but not able to fetch it. To fetch the record,we had to exit out the application and restart it. This was same for apple and android After that we tried 4000 rec where Android application crashed while apple application we had to exit out and relaunch app to read and delete On 5000 rec it crashed for both android and Apple. I have attached the complete project with this Query. We are using Cordova 3.7 and SQL Lite 1.0.1 . Appreciate your feedback on this. Thanks, Raj
[sqlite] Multiple connections and page cache(s) reusability
Thanks for the reply. Therefore my expectation that the approach 3 should be the best is correct, isn't it? I mean, using a dedicated connection for each table should cause better reusablility of cached pages? Or what approach would you propose to get the best performance? Does SQLite have any official (or internal) performance tests related to multiple connections or SQLite cache mechanism? Is it possible that managing of multiple connections can cause slowdown in SQLite itself? Regards, Milan
[sqlite] Multiple connections and page cache(s) reusability
Milan K??? wrote: > My understanding was that each connection has its own page cache. This is correct. > [...] Does SQLite have yet some other global cache? No. That other global cache is part of the OS. It might be possible that opening a file two times changes the OS's caching algorithm. Regards, Clemens
[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.
Hi everyone, I am facing a issue and details are as below: 1) Database table have around 15 table. One of the table have around 66000 entries and this table have about 25 columns. 2) This table with 66000 entry have field called TITLE with TEXT type with table name AUDIO. 3) When I run a query like: SELECT TITLE FROM AUDIO WHERE VALID!=0 ORDER BY TITLE ASC; (here VALID is also a column), sqlite3_step() is returning error SQLITE_FULL. 4) Database is created in a tempfs path with 64MB space (This is tmpfs is not a default one it is created only for the database creation). And default tempfs have 2MB of space. 5) Created database size is at around 7.5MB. please can anyone help me in understanding how this query works? Is this error is due to no enough cache space for database to prepare the query? Please help me in resolving this problem. Thanks. Thanks and Regards Deepak
[sqlite] Tests regarding custom build of SQLite
On 5/13/15, Sairam Gaddam wrote: > In order to run .test files(sqlite test codes) in SQLite, I used > > ./testfixture NAMEOFFILE.test > > Can anyone kindly tell which build of sqlite will TESTFIXTURE access? > > Because even when I make modifications to sqlite3.c source code, the output > of the test file under test has no effect. testfixture.exe builds from individual source files in the src/ directory, not from the amalgamation file sqlite3.c. So you'll need to make your changes to individual source files then incorporate those changes into sqlite3.c by running "make sqlite3.c" and into testfixture.exe by running "make test". > > > And one more question is that, In order to test my custom build of > sqlite3.c, > I installed both TCL and SQLite, and gave the command ./configure > After that the source file sqlite3.c file is formed and I replaced the > original sqlite3.c file with my customized sqlite3.c with the same name. > But when I ran make test command, the program again replaces my customized > sqlite3.c with the original sqlite3.c source file and tests it. So I am not > able to run tests on my modified file. So can anyone tell why it replaces > with original file and what to do in order to test my custom build. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.
On 5/13/15, Deepak Hegde wrote: > I have gone through the document and it says, it is not thread safe. > What if the same database is used in the multiple processes? > Can we have actual database path and this temporary path same? > process!=thread. It is perfectly safe to run PRAGMA temp_store_directory and point it to the same directory on multiple processes. It is perfectly safe to have temp_store_directory and the main database directory be the same. -- D. Richard Hipp drh at sqlite.org
[sqlite] emptying tables
>You turn off auto_vacuum but do a manual VACUUM once you've deleted all the >data from /all/ the tables. It's faster >before VACUUM takes time roughly >proportional to the amount of data left in the database. > >Simon. Thank you for the hint. Rene Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
[sqlite] emptying tables
>Are you running all your delete statements within a single transaction, or a >separate transaction for each? A single transaction as written before in this thread. Rene Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
[sqlite] System.Data.SQLite, Virtual Tables, and ThreadAbortException Issues
Hey Everyone, For reference I'm using version 1.0.95.0 of System.Data.SQLite. I've got System.Data.SQLite embedded in our cloud web service using virtual table modules to access our various data sources. Our IIS is configured to abort requests that exceed a maximum time threshold (required for among other things to prevent a malicious user from sucking up web resources by spawning repeated long running requests). The effect is IIS will abort threads that exceed the time threshold. I'm seeing the GC finalizer get stuck trying to finalize an object in SQLite.Interop, blocking on a critical section (which eventually leads to memory exhaustion). After a lot of digging, I believe the situation is as follows: - Request for a long running query via SQLite starts - SQLite 'step' is called, drops into unmanaged code, enters a critical section, then calls back to managed code for virtual table processing - IIS aborts the thread causing a ThreadAbortException in managed code (Virtual table processing) - Stack starts unrolling due to the ThreadAbortException, which prevents the unmanaged code from releasing the critical section - Finalizer gets stuck trying to acquire the critical section when the underlying System.Data.SQLite object(s) are getting finalized. The issue is isolated to ThreadAbortException since you can't catch it and prevent it from being re-thrown. I did find a 'fix' for a similar issue in SystemData.SQLite back on 10/11/2012: https://system.data.sqlite.org/index.html/fdiff?v1=3994ed2958c14a11=d05529e749a4f10b=1 I don't think the 'step' code is protected in the manner of the above fix (likely unneeded at the time, since virtual tables were not supported and there wasn't a case of 'step' dropping into unmanaged code and then back to managed code). Questions: - Have I evaluated this issue correctly or is there potentially something wrong with my integration? - Presuming this is a bug, does anyone have a workaround suggestion? I'm somewhat hesitant to change the 'step' source in System.Data.SQLite to use the same approach as the 10/11/2012 fix, worrying about other potential side effects. Thanks, MikeN
