[sqlite] Corruption of incremental_vacuum databases
Hello,
after seeing the recent change #6413 and ticket #3761 I finally
decided to write about a corruption issue we have.
This is the environment of our application:
- We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
- Several database files. Each file is opened in it's own connection
and never shared across them.
- Some of these connections have another database attached to it
(containing mostly BLOB data).
- In all cases the connections are opened on program start and closed
on program shutdown.
- There's a low-priority thread that executes pragma
incremental_vacuum when the application is idle and there is enough
free pages. Code of the thread is listed below.
- journal_mode=persist is used on all databases in all connections
(to workaround a bug in the journal deletion logic on Windows, search
for TortoiseSVN in the mailing list archive for details)
- synchronous=off is used on all databases in all connections. This
setting is likely to change in future, but in no case of the
corruption a system crash was involved.
Since we started using the incremental_vacuum mode we were getting
database corruption errors pretty often (sometimes as often as once a
day in 3 people). Most, if not all, of these corruptions happened
following a ROLLBACK (caused by constraint violation). pragma
integrity_check; on the already corrupted databases usually reported
few Page XXX is never used error.
Unfortunately I don't have any of the corrupted databases at hand and
I have no deterministic way to create them. My question is if these
could be related to the just fixed problem (in ticket 3761) or if it
could be another issue?
Best regards,
Filip Navara
WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
int timeout = -1;
int pagesPerIteration = 32;
// Wait for thread shutdown and wakeup event. The shutdown event
// is used to stop the thread on application exit. The wakeup event is
// fired on startup if there are free pages in the database or if a DELETE
// statement was executed.
while (WaitHandle.WaitAny(handles, timeout, false) != 0)
{
long totalFreePages = 0, freePages;
lock (this.repositories)
{
stopWatch.Reset();
stopWatch.Start();
foreach (IRepositoryBase repository in this.repositories)
{
// wrapper around pragma freelist_count;
freePages = repository.GetFreePageCount();
totalFreePages += freePages;
if (freePages 0)
// wrapper around pragma incremental_vacuum(x)
repository.Compact(pagesPerIteration);
}
stopWatch.Stop();
}
// We start by freeing 32 pages per one iteration of the loop for
// each database. After each iteration the number is recalculated
// based on the time spent on the operation and then it's
// truncated to the 24;4096 range.
pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);
// If there are still free pages in the databases then schedule the
// thread to wake up in 200ms and continue vacuuming them.
if (totalFreePages 0)
timeout = 200;
else
timeout = -1;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unnecessary line breaks in .dump output
Perhaps the OP should file a ticket to have this corrected in the SQLite command line tool. What's the OP? Just want to know if I must log the bug or whether you're referring to somebody else. If the OP is somebody else, please log it and let me know what the ticket number is so that I can follow it. thanks, Francois ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unnecessary line breaks in .dump output
On Tue, Mar 31, 2009 at 5:46 AM, Francois Botha igitur+sql...@gmail.com wrote: Perhaps the OP should file a ticket to have this corrected in the SQLite command line tool. What's the OP? Just want to know if I must log the bug or whether you're referring to somebody else. If the OP is somebody else, please log it and let me know what the ticket number is so that I can follow it. You are the OP, the original poster of the message that started this thread. thanks, Francois ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] export table to csv
All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 .import Temp.csv iofromexcel Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv select * from iotemplate; .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite .import not ignoring extra fields
With sqlite 2.8, the copy command would ignore extra fields at the end. How can I do this with .import in sqlite 3.11? Many of the files I am importing have trailing field separators. Others may have extra data. The files are too big for me to want a separate pass to trim off the trailing fields. This email, and any documents or data attached hereto, is intended for the addressee(s) only. It may contain confidential and/or privileged information and no rights or obligations have been waived by the sender. Any copying, distribution, taking of action in reliance on, other use of the information contain in this email by persons other than the intended addressee(s) is prohibited. If you have received this email in error, please reply to the sender by email and immediately delete or destroy all copies of this email, along with any attached documents or data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting records by INDEXED key
Hi
I am trying to select (list) records by their index sequence BUT keep
getting the SQL error.
SQL error can't use index: Surname
. Indices showes the index
;
;Sample INDEX program
;
CREATE TABLE APPLE (MyName, MySurname, MyAge, PRIMARY KEY (MyAge));
CREATE INDEX Surname on APPLE (MySurname);
INSERT INTO APPLE VALUES ('Greg','Hooper', 54);
INSERT INTO APPLE VALUES ('Daniel','Hooper', 30);
INSERT INTO APPLE VALUES ('Nicole','Lvester', 32);
INSERT INTO APPLE VALUES ('Rhonda','Grin', 50);
Select * From APPLE INDEX BY Surname;
Regards Greg.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting records by INDEXED key
Hi,
your select statement does not include a where or order by - clause
for which the index can be used, which is the cause of the error.
If you want to select the records by their index sequence, you should use
Select * From APPLE ORDER BY MySurname;
The index will then be used automatically.
Also, i see that you enclose column names and table names by double
quotes. This is not necessary.
HTH
Martin
ggcoo...@clearmail.com.au wrote:
Hi
I am trying to select (list) records by their index sequence BUT keep
getting the SQL error.
SQL error can't use index: Surname
. Indices showes the index
;
;Sample INDEX program
;
CREATE TABLE APPLE (MyName, MySurname, MyAge, PRIMARY KEY (MyAge));
CREATE INDEX Surname on APPLE (MySurname);
INSERT INTO APPLE VALUES ('Greg','Hooper', 54);
INSERT INTO APPLE VALUES ('Daniel','Hooper', 30);
INSERT INTO APPLE VALUES ('Nicole','Lvester', 32);
INSERT INTO APPLE VALUES ('Rhonda','Grin', 50);
Select * From APPLE INDEX BY Surname;
Regards Greg.
___
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
[sqlite] SQLite version 3.6.12
SQLite version 3.6.12 is now available on the SQLite website http://www.sqlite.org/ During release testing for 3.6.12, we stumbled over a critical bug that has existed in SQLite since version 3.4.0. This bug will cause corruption of in-memory databases if an incremental vacuum is rolled back. The corruption is virtually guaranteed if you rollback an incremental vacuum in an in-memory database. Presumably not many people are doing that or we would have seen this sooner. As far as we are aware, incremental vacuum works correctly for on-disk databases. We will continue to study this issue over the next weeks and release additional patches if we find new problems. A manual code review uncovered another bug in the lookaside memory allocator which can result in a segfault when shared cache is enabled. At the very least, users of shared cache should disable lookaside memory. A better solution is to update to version 3.6.12. New features added to version 3.6.12 include the sqlite3_unlock_notify() interface and the reverse_unordered_selects PRAGMA. Additional information about both is available on the website. Because of the incremental vacuum and lookaside memory allocator issues identified above, we recommend that everyone update to SQLite version 3.6.12. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export table to csv
Hi Richard, The sqlite3 utility allows a *single* dot-command on the command line itself. You can have an unlimited number of commands in a separate text file, though. http://www.sqlite.org/sqlite.html So, using Windows syntax, you can have something like: === Set MyTempFile=%temp%\Mytemp.tmp echo .separator ,%myTempFile% Echo .output thisisit.csv %myTempFile% Echo select * from iotemplate;%myTempFile% Echo .output stdout %myTempFile% Sqlite3.exe ioimport.db3 .read %myTempFile% Del %mytempfile% nul If you're using linux/unix, you can redirect input to the your shell script and avoid the temporary file. Hope this helps, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Nero Sent: Monday, March 30, 2009 11:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] export table to csv All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 .import Temp.csv iofromexcel Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv select * from iotemplate; .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ 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] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp scratched on the wall: SQLite version 3.6.12 is now available on the SQLite website New features added to version 3.6.12 include the sqlite3_unlock_notify() interface and the reverse_unordered_selects PRAGMA. Additional information about both is available on the website. Might be nice if reverse_unordered_selects had a =2 value that would turn it on or off randomly (or simply alternated) for each select that is issued. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export table to csv
You can only use one command in the command-line. Use options for the others, like this: Sqlite3.exe -csv -separator ',' ioimport.db3 select * from iotemplate; thisisit.csv -Original Message- From: Richard Nero [mailto:rich...@rlnero.com] Sent: Monday, March 30, 2009 10:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] export table to csv All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 .import Temp.csv iofromexcel Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv select * from iotemplate; .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to call c/c++ function in trigger
Hi all, I have a question regarding calling c/c++ functions in sqlite triggers. What I want is: - whenever I insert/update/delete a table entry, the specified c/c++ function is called - if the function returns true, the db action can proceed; otherwise, the db action should be rolled-back. Is there any online instruction about how to do this? Thanks! -Simon -- View this message in context: http://www.nabble.com/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22815938.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: SQLite version 3.6.12 is now available on the SQLite website ...and once again TCL module is flawed: #v+ % package require sqlite3 attempt to provide package sqlite3 3.6.12 failed: package sqlite3 3.6.11 % provided instead #v- -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
Hello, I'd like to ask how should the SQLITE_LOCKED_SHAREDCACHE be processed? Is it like a normal SQLITE_BUSY/SQLITE_LOCKED and I should sleep a bit and try again? Or is this a fatal error and requires me to abort the transaction? Thanks in advance, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:06 PM, Zbigniew Baniewski wrote: On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: SQLite version 3.6.12 is now available on the SQLite website ...and once again TCL module is flawed: #v+ % package require sqlite3 attempt to provide package sqlite3 3.6.12 failed: package sqlite3 3.6.11 % provided instead #v- I don't understand that. I can grep in the package for 3.6.11 and I get no hits: grep 3.6.11 * */* On the other hand, if I grep from 3.6.12, I get lots of hits. So I do not know where the package gets the idea that it is providing version 3.6.11. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
On Mar 31, 2009, at 7:14 PM, Dennis Volodomanov wrote: Hello, I'd like to ask how should the SQLITE_LOCKED_SHAREDCACHE be processed? Is it like a normal SQLITE_BUSY/SQLITE_LOCKED and I should sleep a bit and try again? Or is this a fatal error and requires me to abort the transaction? SQLITE_LOCKED_SHAREDCACHE is like SQLITE_LOCKED and can be processed in the same way, if you want. But if you get a SQLITE_LOCK_SHAREDCACHE you also have the option of calling sqlite3_unlock_notify(). You should not use sqlite3_unlock_notify() on an ordinary SQLITE_LOCKED. Additional information at http://www.sqlite.org/unlock_notify.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:15:57PM -0400, D. Richard Hipp wrote: I don't understand that. I can grep in the package for 3.6.11 and I get no hits: grep 3.6.11 * */* On the other hand, if I grep from 3.6.12, I get lots of hits. So I do not know where the package gets the idea that it is providing version 3.6.11. #v+ # grep -i 3.6.11 tclsqlite.o # Binary file tclsqlite.o matches # grep -i 3.6.12 tclsqlite.o # #v- But OK - I'll try to check it once again, and I'll make another compilation. Perhaps I missed something(?) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
SQLITE_LOCKED_SHAREDCACHE is like SQLITE_LOCKED and can be processed in the same way, if you want. But if you get a SQLITE_LOCK_SHAREDCACHE you also have the option of calling sqlite3_unlock_notify(). You should not use sqlite3_unlock_notify() on an ordinary SQLITE_LOCKED. Additional information at http://www.sqlite.org/unlock_notify.html Thank you! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:27 PM, Mike Frysinger wrote: On Tuesday 31 March 2009 19:15:57 D. Richard Hipp wrote: On Mar 31, 2009, at 7:06 PM, Zbigniew Baniewski wrote: On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: SQLite version 3.6.12 is now available on the SQLite website ...and once again TCL module is flawed: #v+ % package require sqlite3 attempt to provide package sqlite3 3.6.12 failed: package sqlite3 3.6.11 % provided instead #v- I don't understand that. I can grep in the package for 3.6.11 and I get no hits: grep 3.6.11 * */* On the other hand, if I grep from 3.6.12, I get lots of hits. So I do not know where the package gets the idea that it is providing version 3.6.11. the configure script isnt regenerated before the dist tarball is made. not sure how you're making releases, but `autoconf` needs to be part of the process ... -mike I think I did rerun autoconf. Certainly if I grep 3.6.12 configure I get multiple hits, but if I run grep 3.6.11 configure I get nothing. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
The configure script included in tarball has entries, like f.e.: #! /bin/sh # Guess values for system-dependent variables and create Makefiles. # Generated by GNU Autoconf 2.63 for sqlite 3.6.11. # [..] # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' PACKAGE_VERSION='3.6.11' PACKAGE_STRING='sqlite 3.6.11' PACKAGE_BUGREPORT='' [..] \`configure' configures sqlite 3.6.11 to adapt to many kinds of systems. [..] if test -n $ac_init_help; then case $ac_init_help in short | recursive ) echo Configuration of sqlite 3.6.11:;; esac cat \_ACEOF [..] sqlite configure 3.6.11 generated by GNU Autoconf 2.63 [..] ...and so on... (a few more) Could the above have negative influence? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, and it contains no 3.6.11 text. Only 3.6.12. I think you have somehow obtained the wrong configure script. On Mar 31, 2009, at 7:34 PM, Zbigniew Baniewski wrote: The configure script included in tarball has entries, like f.e.: #! /bin/sh # Guess values for system-dependent variables and create Makefiles. # Generated by GNU Autoconf 2.63 for sqlite 3.6.11. # [..] # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' PACKAGE_VERSION='3.6.11' PACKAGE_STRING='sqlite 3.6.11' PACKAGE_BUGREPORT='' [..] \`configure' configures sqlite 3.6.11 to adapt to many kinds of systems. [..] if test -n $ac_init_help; then case $ac_init_help in short | recursive ) echo Configuration of sqlite 3.6.11:;; esac cat \_ACEOF [..] sqlite configure 3.6.11 generated by GNU Autoconf 2.63 [..] ...and so on... (a few more) Could the above have negative influence? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:42:49PM -0400, D. Richard Hipp wrote: I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, and it contains no 3.6.11 text. Only 3.6.12. I think you have somehow obtained the wrong configure script. I meant sqlite-3.6.12.tar.gz package. Is it possible to just use the Makefile from sqlite-3_6_12-tea.tar.gz with sources provided by sqlite-3.6.12.tar.gz ? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:42 PM, Zbigniew Baniewski wrote: On Tue, Mar 31, 2009 at 07:42:49PM -0400, D. Richard Hipp wrote: I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, and it contains no 3.6.11 text. Only 3.6.12. I think you have somehow obtained the wrong configure script. I meant sqlite-3.6.12.tar.gz package. Is it possible to just use the Makefile from sqlite-3_6_12-tea.tar.gz with sources provided by sqlite-3.6.12.tar.gz ? No, it is not possible to mix configure scripts from one package with sources for another. The TEA configure script is correct but the sqlite-3.6.12.tar.gz configure script is messed up. See http://www.sqlite.org/cvstrac/chngview?cn=6419 and remember that vapier==Mike Frysinger. I'll try to fix the tarball soon. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
Sorry, checked it again, and noticed now, that this kind of tarball seems to be kinda abandoned: :( The Makefile and configure script in this tarball are not supported -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Simon Chen simonche...@gmail.com wrote: What I want is: - whenever I insert/update/delete a table entry, the specified c/c++ function is called - if the function returns true, the db action can proceed; otherwise, the db action should be rolled-back. create trigger triggerName before insert on tableName when not myfunction() begin select RAISE(ROLLBACK, 'error message'); end; -- or create trigger triggerName before insert on tableName begin select RAISE(ROLLBACK, 'error message') where not myfunction(); end; Similarly for update and delete. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert performance in 3.6.11 vs. 3.5.5
Well, seems that was a false alarm. We were not able to reproduce this on other systems - there the 3.6.11 release even performed slightly better than 3.5.5. Still no idea what caused this, as now even the original system no longer shows this effect, but it's very probably not SQLite. Best regards, Günter -- Günter Obiltschnig Applied Informatics Software Engineering GmbH A-9184 St. Jakob im Rosental | St. Peter 33 | www.appinf.com P: +43 4253 32596 M: +43 676 5166737 F: +43 676 32096 Company Registration: FN 276491 f | Landesgericht Klagenfurt Managing Director: DI Günter Obiltschnig APPLIED INFORMATICS - SMARTER DEVICE NETWORKING On Mar 25, 2009, at 15:24 , Günter Obiltschnig wrote: Hi there, I have just upgraded SQLite in our application from 3.5.5 to 3.6.11 (we are using the amalgamation), and I have noticed a sharp drop in insert performance to more than half the speed that we had with 3.5.5. We are using SQLite in an embedded Linux device, and the database files are on a CompactFlash device. The inserts are being done into an initially empty table with 28 columns, and all inserts (can be more than 10) are done within one large transaction, using a prepared insert statement. One additional unique index on a single column is used on the table as well. With 3.5.5, inserting 1000 rows into that table took about 7 seconds, with 3.6.11 it takes 14-16 seconds. We are using PRAGMA synchronous = OFF and a cache size of 6000 pages. The main reason why we updated was because we experienced memory issues with 3.5.5. Reducing the cache size (PRAGMA cache_size) would not release memory. Any ideas what causes this? Apart from that we are very happy with sqlite. A big thank you to D. Richard Hipp and everyone who contributed to this great peace of software. Thanks and best regards, Günter ___ 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
[sqlite] inserting blob data
Hi, I'm using 3.2.2. I have a binary data in a variable which I want to store as a blob in my database table. I have defined a blob field in my table. Since this binary data varies in size every time, hence I want to store it as blob. Please let me know how can I insert a blob data using the function - sqlite_exec_printf(). I also want to later read that data using sqlite_get_table_printf(). Can anybody give an example. Thanks in advance. Regards Mayura ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
