Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10
Roger Binns wrote [on Sun, 03 Jan 2010 09:56:46 -0800]:
> Adam Kennedy wrote:
>> Unfortunately, we neither have the ability to run configure (as we
>> don't have reliable access to /bin/sh or any of the other stuff it
>> needs) or the ability to use a pregenerated static configuration
>> across all platforms.
>
> Well, you already pre-generate -DHAVE_USLEEP which doesn't exist on Windows
> or older Unixen! Your only workaround is to read/run the real configure to
> see what kind of stuff it generates and then write your own tests to
> generate the same flags.
>
> GMTIME_R/LOCALTIME_R will affect performance if doing date/time code - not
> having them means SQLite internally uses a mutex around calls to
> gmtime/localtime which still leaves you vulnerable to bogus data if any
> other non-SQLite thread in the program calls those functions.
>
> The other flags mainly cover header file presence and you'll generally get
> away without defines for them (unistd.h likely has everything anyway). The
> only likely gotcha is if you have extension loading enabled in which case
> SQLite needs to know which header contains dlopen and friends.
In response to the above message, following is Adam Kennedy's reply of
yesterday, which I am forwarding to the sqlite-users list.
Anyone on sqlite-users who wants to respond on this thread, please cross-post
to
dbd-sql...@lists.scsys.co.uk (which I moderate) and I will let it in. Anyone
on
dbd-sqlite only that is interested in such issues should also join sqlite-users
so they can post to it directly.
-- Darren Duncan
Original Message
Subject: Re: [Fwd: Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build
fails on sun4-solaris-64int 2.10]
Date: Mon, 4 Jan 2010 10:40:21 +1100
From: Adam Kennedy
Reply-To: a...@ali.as
To: Darren Duncan
CC: DBD::SQLite Mailing List
References: <4b41113f.1060...@darrenduncan.net>
(Darren can you bump this onto the SQLite list? Thanks)
The HAVE_USLEEP flag is provided by the following.
if ( $Config{d_usleep} || $Config{osname} =~ m/linux/ ) {
push @CC_DEFINE, '-DHAVE_USLEEP=1';
}
The %Config hash exposes the internal flags and settings that the
underlying Perl language was originally compiled with.
The $Config{d_usleep} flag should be true if the original Perl
./Configure run detected it.
I can confirm that when I build on Windows we don't use HAVE_USLEEP
Adam K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG REPORT: 3.6.21;
On Jan 5, 2010, at 6:25 AM, Noah Hart wrote: > Using the command line tools from the website > 3.6.18 reports the error correctly; > > SQLite version 3.6.18 > sqlite> PRAGMA recursive_triggers = on; > sqlite> CREATE TABLE t5 (a primary key, b, c); > sqlite> INSERT INTO t5 values (1, 2, 3); > sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR > IGNORE > t5 SET a = new.a, c = 10; END; > sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ; > SQL error: too many levels of trigger recursion > > > 3.6.21 does not handle it properly > > SQLite version 3.6.21 > sqlite> PRAGMA recursive_triggers = on; > sqlite> CREATE TABLE t5 (a primary key, b, c); > sqlite> INSERT INTO t5 values (1, 2, 3); > sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR > IGNORE > t5 SET a = new.a, c = 10; END; > sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ; > Error: SQL logic error or missing database Thanks. Turns out this was just a shell tool problem, not a problem with the library. Fixed here: http://www.sqlite.org/src/vinfo/e5d07045fa > BACKGROUND: > I was trying to get the test triggerC-1.11 in triggerC.test to work > correctly; > The assert in btree.c at line 3699 in the routine sqlite3BtreeRollback > was > failing returning a 2000, rather than 0 > > assert( countWriteCursors(pBt)==0 ); How did you make this happen? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG REPORT: 3.6.21;
Using the command line tools from the website 3.6.18 reports the error correctly; SQLite version 3.6.18 sqlite> PRAGMA recursive_triggers = on; sqlite> CREATE TABLE t5 (a primary key, b, c); sqlite> INSERT INTO t5 values (1, 2, 3); sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR IGNORE t5 SET a = new.a, c = 10; END; sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ; SQL error: too many levels of trigger recursion 3.6.21 does not handle it properly SQLite version 3.6.21 sqlite> PRAGMA recursive_triggers = on; sqlite> CREATE TABLE t5 (a primary key, b, c); sqlite> INSERT INTO t5 values (1, 2, 3); sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR IGNORE t5 SET a = new.a, c = 10; END; sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ; Error: SQL logic error or missing database BACKGROUND: I was trying to get the test triggerC-1.11 in triggerC.test to work correctly; The assert in btree.c at line 3699 in the routine sqlite3BtreeRollback was failing returning a 2000, rather than 0 assert( countWriteCursors(pBt)==0 ); Regards, Noah Hart CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bugreport: .echo ON in command line tool doesn't echo all statements
Between versions 3.6.19 and 3.6.20 something has changed which causes the command line tool to ignore the .echo ON command for some statements. CREATE and INSERT statements aren't echoed anymore, but SELECT statements are echoed correctly. Needless to say that this makes it a bit harder to debug SQL scripts or prepare demo output to help other people. Platform tested: MS Windows (Vista 32-bit). I wish the SQLite development team and the list members a happy 2010. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
Jeremy Zeiber wrote: > SELECT headerid, > (SELECT COUNT(data) FROM detail AS outerdetail WHERE > headerid=header.headerid AND data NOT IN (SELECT DISTINCT data FROM > detail WHERE headerid FROM header GROUP BY headerid; Try this: select headerid, count(*) from (select data, min(headerid) first_occured from detail group by data) d_first join header on (header.headerid = d_first.first_occured) group by headerid; An index on detail(data) or detail(data, headerid) may be beneficial (an index on detail(headerid, data) not so much). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In Memory Usage
If you were wanting to copy all of the in-memory database tables to a file, rather than just one, then the backup api might be another way to go. http://www.sqlite.org/backup.html (I haven't used it myself but the documentation lists copying from memory database instances to file databases as an application !) cheers Owen -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Doug Currie Sent: Monday, January 04, 2010 4:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] In Memory Usage On Jan 4, 2010, at 6:35 AM, sasikuma...@tcs.com wrote: > I'm using SQLite DB version 3.6.12. I recently read about the feature of > In-Memory Database and tried to implement it. I was able to create a new > DB connection in memory, able to create a table and insert some set of > records into the memory DB. > > How should I now transfer those records into the real table in the real > DB, someone kindly assist and guide me. Adding to suggestions by Igor and Simon... You can use the ATTACH command to attach a disk based db to your memory based db. Then, using (CREATE and) INSERT statements you can copy records from the memory based db to the disk based db. http://www.sqlite.org/lang_attach.html http://www.sqlite.org/lang_insert.html e ___ 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] In Memory Usage
On Jan 4, 2010, at 6:35 AM, sasikuma...@tcs.com wrote: > I'm using SQLite DB version 3.6.12. I recently read about the feature of > In-Memory Database and tried to implement it. I was able to create a new > DB connection in memory, able to create a table and insert some set of > records into the memory DB. > > How should I now transfer those records into the real table in the real > DB, someone kindly assist and guide me. Adding to suggestions by Igor and Simon... You can use the ATTACH command to attach a disk based db to your memory based db. Then, using (CREATE and) INSERT statements you can copy records from the memory based db to the disk based db. http://www.sqlite.org/lang_attach.html http://www.sqlite.org/lang_insert.html e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
On 4 Jan 2010, at 3:44pm, Jeremy Zeiber wrote: > Believe it or not, these indexes made the query run the fastest: > CREATE UNIQUE INDEX idx1 ON detail(headerid,data); > CREATE UNIQUE INDEX idx2 ON detail(data,headerid); That makes perfect sense since your SELECT command does require sorting by both fields. Another approach would be to temporarily create those two indexes /and/ indexes on the two columns individually, /and/ any other indexes you think might help, and then to use the 'EXPLAIN' (or is it 'EXPLAIN QUERY PLAN' ?) command to see which indexes are being used by your SELECT commands. Then you can just delete the indexes which are not being used. http://www.sqlite.org/lang_explain.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Encryption Extension
On Jan 4, 2010, at 10:36 AM, Olivier Roger wrote: > Hello, > for some project we need an encryption on our database. SQLite > Encryption Extension seems to fit our need perfectly but some > questions > remain. > > After having paid the license fee we can download the entire source > code > of the extension, right ? not only a dll (or equivalent) > The encrypted db can be read either from C++ software or a php web > application. Does php have a native support for encrypted db (or > with a > pdo driver) ? On php documentation a parameter refer to an encryption > key : http://www.php.net/manual/en/sqlite3.open.php > If not, did someone have some article about recompiling php with SEE ? Licensees for SEE are given a login and password to the Fossil repository that contains the SEE source code, so that they can login and download the latest source code whenever they like. Yes, you get full source code. And your login never expires so you also get all future updates to the source code as well. In order to use SEE with PHP, you'll have to replace PHP's SQLite DLL with a new DLL that contains SEE. You are responsible for compiling the SEE DLL yourself. But after you replace the SQLite DLL with the SEE DLL, you should then have full encryption capability. Note that SEE is a superset of SQLite. SEE will read and write ordinary (unencrypted) SQLite database files just like public domain SQLite. SEE simply adds the capability to optionally encrypt/decrypt the database as it is written/read. So replacing the PHP SQLite DLL with an SEE DLL will not break legacy code - it simply gives you a few extra pragmas that allow you to turn encryption on and off. 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 3.6.21 - slow query
Simon Slavin wrote: > On 4 Jan 2010, at 3:02pm, Simon Davies wrote: > > >> 2010/1/4 Simon Slavin : >> >>> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >>> >>> SELECT headerid, (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one INNER JOIN detail AS two ON one.data=two.data WHERE two.headerid>>> FROM header GROUP BY headerid; >>> Do you have the appropriate indexes defined ? I see lots of matching and >>> WHERE clauses and your query may not be finding an index that can do all >>> that work for it. >>> >> indeed: >> create index i on detail( data ); >> >> seems to improve performance >> > > But you're also matching on headerid and using the same field in GROUP BY. I > can't get my head around your data structure but if the correct indexes to > define aren't clear I think you should experiment with defining indexes in > data and headerid both as separate indexes and as combination indexes in each > order. Once you've done that, see if the SELECT gets faster. If it does, > you can figure out which index it uses and delete the others. > The data structure is basically sets of data elements. Each set has a record in the header table, and each data element the belongs to a set has a record in the detail table. Data elements may or may not appear in more than one set. The query above is getting the count of data elements in each set that does not appear in any previous set. I need to do similar queries to data elements that don't appear in any later set and data elements that don't appear in a previous or later set. These are trivial to do once one of them is done, but I want to make sure I have the best performing query to start with. There's only two candidate fields for an index - detail.headerid and detail.data. I took your advice and tried every combination of index. Believe it or not, these indexes made the query run the fastest: CREATE UNIQUE INDEX idx1 ON detail(headerid,data); CREATE UNIQUE INDEX idx2 ON detail(data,headerid); This wasn't as fast: CREATE UNIQUE INDEX idx1 ON detail(headerid,data); CREATE UNIQUE INDEX idx2 ON detail(data); And this wasn't as fast either: CREATE UNIQUE INDEX idx1 ON detail(headerid); CREATE UNIQUE INDEX idx2 ON detail(data,headerid); Single field indexes were the slowest. I suppose this is the fastest I can make this query. > Simon. > ___ > 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 Encryption Extension
Hello, for some project we need an encryption on our database. SQLite Encryption Extension seems to fit our need perfectly but some questions remain. After having paid the license fee we can download the entire source code of the extension, right ? not only a dll (or equivalent) The encrypted db can be read either from C++ software or a php web application. Does php have a native support for encrypted db (or with a pdo driver) ? On php documentation a parameter refer to an encryption key : http://www.php.net/manual/en/sqlite3.open.php If not, did someone have some article about recompiling php with SEE ? Thanks for your answers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
Simon Davies wrote: > 2010/1/4 Simon Slavin : > >> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >> >> >>> That particular query runs in ~ 30 seconds with outerdetail.header or >>> header.headerid. I do have another query which gives the same result >>> that doesn't quite run as fast as the first, but it is certainly faster >>> than the second: >>> >>> SELECT headerid, >>> (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE >>> headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one >>> INNER JOIN detail AS two ON one.data=two.data WHERE >>> two.headerid>> FROM header GROUP BY headerid; >>> >>> That runs in about 3 seconds. Still, there are only a few thousand rows >>> in the test database, and the real data is going to have hundreds of >>> thousands of rows, and this is just a small portion of the query. Is >>> there any way to rewrite the query to a better performing one? >>> >> Do you have the appropriate indexes defined ? I see lots of matching and >> WHERE clauses and your query may not be finding an index that can do all >> that work for it. >> > > indeed: > create index i on detail( data ); > > seems to improve performance > Yes, creating an index on detail(data) does speed the query. It is still pretty slow when there are tens of thousands of records in the database. > >> Simon. >> > > Regards, > Simon > ___ > 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 3.6.21 - slow query
On 4 Jan 2010, at 3:02pm, Simon Davies wrote: > 2010/1/4 Simon Slavin : >> >> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >> >>> SELECT headerid, >>> (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE >>> headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one >>> INNER JOIN detail AS two ON one.data=two.data WHERE >>> two.headerid>> FROM header GROUP BY headerid; >> >> Do you have the appropriate indexes defined ? I see lots of matching and >> WHERE clauses and your query may not be finding an index that can do all >> that work for it. > > indeed: > create index i on detail( data ); > > seems to improve performance But you're also matching on headerid and using the same field in GROUP BY. I can't get my head around your data structure but if the correct indexes to define aren't clear I think you should experiment with defining indexes in data and headerid both as separate indexes and as combination indexes in each order. Once you've done that, see if the SELECT gets faster. If it does, you can figure out which index it uses and delete the others. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
2010/1/4 Simon Slavin : > > On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > >> That particular query runs in ~ 30 seconds with outerdetail.header or >> header.headerid. I do have another query which gives the same result >> that doesn't quite run as fast as the first, but it is certainly faster >> than the second: >> >> SELECT headerid, >> (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE >> headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one >> INNER JOIN detail AS two ON one.data=two.data WHERE >> two.headerid> FROM header GROUP BY headerid; >> >> That runs in about 3 seconds. Still, there are only a few thousand rows >> in the test database, and the real data is going to have hundreds of >> thousands of rows, and this is just a small portion of the query. Is >> there any way to rewrite the query to a better performing one? > > Do you have the appropriate indexes defined ? I see lots of matching and > WHERE clauses and your query may not be finding an index that can do all that > work for it. indeed: create index i on detail( data ); seems to improve performance > > Simon. Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > That particular query runs in ~ 30 seconds with outerdetail.header or > header.headerid. I do have another query which gives the same result > that doesn't quite run as fast as the first, but it is certainly faster > than the second: > > SELECT headerid, > (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE > headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one > INNER JOIN detail AS two ON one.data=two.data WHERE > two.headerid FROM header GROUP BY headerid; > > That runs in about 3 seconds. Still, there are only a few thousand rows > in the test database, and the real data is going to have hundreds of > thousands of rows, and this is just a small portion of the query. Is > there any way to rewrite the query to a better performing one? Do you have the appropriate indexes defined ? I see lots of matching and WHERE clauses and your query may not be finding an index that can do all that work for it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.21 - slow query
Igor Tandetnik wrote: > Jeremy Zeiber wrote: > >> This query runs in ~ 17 ms: >> SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data >> NOT IN (SELECT DISTINCT data FROM detail WHERE headerid<4) >> > > Here the subquery is not coordinated. It is run once, the results are stored > in an ephemeral table, then the check in the main query is performed against > that table. > > >> This query runs in ~ 15s: >> SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data >> NOT IN (SELECT DISTINCT data FROM detail WHERE >> headerid> > > Here the subquery is coordinated: since it depends on values in the current > row of the main query, it has to be rerun for each row. So you end up with > quadratic complexity. > > >> Why is there such a huge difference in the query time for two very >> similar queries? Is there anything I can do with the second query to >> make it perform more like the first? >> > > The second query is equivalent to the first, it's just written in the form > that makes it difficult for SQLite to optimize. Why don't you just use the > first query? > > >> This query is part of a larger >> query that aggregates detail data such as the following, so I can't hard >> code the headerid in the query. >> >> SELECT headerid, >> (SELECT COUNT(data) FROM detail AS outerdetail WHERE >> headerid=header.headerid AND data NOT IN (SELECT DISTINCT data FROM >> detail WHERE headerid> FROM header GROUP BY headerid; >> > > Try replacing outerdetail.headerid with header.headerid. > That particular query runs in ~ 30 seconds with outerdetail.header or header.headerid. I do have another query which gives the same result that doesn't quite run as fast as the first, but it is certainly faster than the second: SELECT headerid, (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one INNER JOIN detail AS two ON one.data=two.data WHERE two.headerid Igor Tandetnik > > ___ > 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] memory temp storage and general caching share the same memory pool?
I noticed that setting temp_store to DISK surprisingly improved the performance in some cases comparing to the same operation when it was set to MEMORY. Looking at the numbers I noticed that in case of MEMORY one operation that actually does select spread over a large table always led to big data read although in case of DISK it appeared that the table completely went to cache after the first iteration. So do memory temp_store and general caching share the same memory pool? I prefer to use MEMORY temp_store for some reasons. If they share the memory, what is the strategy should be on my side to choose the right cache size if the amount of data that I plan to transfer through temp tables is unpredictable. Should I always DELETE or DROP temp table data asap in order to increase the probability that the disposed memory would be used for caching in the next SELECT? Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unexplained "disk i/o error", Unix
Summary: * At the very least a documentation change is needed on the subject of the errno value from disk i/o errors. * Ticket #2398 (on the subject of EINTR) should probably be reopened. I have recently had an apparently isolated failure of a program making some updates to a sqlite database. The only information I have is this error message: DBD::SQLite::st execute failed: disk I/O error(10) at dbdimp.c line 423 [for Statement "SELECT * FROM sell NATURAL LEFT JOIN commods WHERE commodname IS NULL"] at CommodsDatabase.pm line 158. PROCESSING FAILED CommodsDatabase.pm is my code. That part is doing a consistency check before saying COMMIT. I don't know exactly what sqlite was doing, but I was alarmed. I checked my system logs and there are no reports of problems with the disks. There are no reports of the filesystem having been full and while possible it doesn't seem likely. Unfortunately I'm at a dead-end investigating this because of the lack of an errno value or other detail from sqlite. To know that a system call fail is not by itself sufficient; we need to know the errno value at the very least (knowing which system call and on what kind of object would be nice but is much less important). If SQLITE_IOERROR means that a system call failed, it is in my view essential that either the errno value is somehow incorporated in sqlite's response, or the documentation makes it clear that the calling application must also report errno. Additionally, sqlite must ensure that any subsequent syscalls it makes before returning to the caller don't overwrite the value of errno (or other equivalent on other platforms), for example by saving and restoring it. This and a related matter of an apparently-buggy check for disk full are the subject of Ticket #3107. If the problem wasn't disk full then the only other candidate cause that I found in my searches was Ticket #2398, regarding sqlite's handling of syscalls which return EINTR. The submitter of #2398 is entirely correct. Many of the comments have misunderstood the issue, muddied the waters, and so on. SA_RESTART is a red herring, because sqlite is not entitled to assume that the calling application has set it. I'm not wholly convinced that this is the cause of my problem, but the bug reported in #2398 would definitely produce that the kind of apparently random lossage, if my program happened to get a signal at the wrong moment. Ian. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In Memory Usage
sasikuma...@tcs.com wrote: > I'm using SQLite DB version 3.6.12. I recently read about the feature of > In-Memory Database and tried to implement it. I was able to create a new > DB connection in memory, able to create a table and insert some set of > records into the memory DB. > > How should I now transfer those records into the real table in the real > DB, someone kindly assist and guide me. http://www.sqlite.org/backup.html Note that Backup API is an experimental feature introduced in some very recent SQLite version (I don't remember which exacly). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In Memory Usage
On 4 Jan 2010, at 11:35am, sasikuma...@tcs.com wrote: > I'm using SQLite DB version 3.6.12. I recently read about the feature of > In-Memory Database and tried to implement it. I was able to create a new > DB connection in memory, able to create a table and insert some set of > records into the memory DB. > > How should I now transfer those records into the real table in the real > DB, someone kindly assist and guide me. The database in memory /is/ the real database. That's where you wanted your data, and that's where it is. You can write, change and read records in those tables. If you want your data saved in a file on disk you have to do separate CREATE and INSERT commands to make that happen. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In Memory Usage
Hi All, I'm using SQLite DB version 3.6.12. I recently read about the feature of In-Memory Database and tried to implement it. I was able to create a new DB connection in memory, able to create a table and insert some set of records into the memory DB. How should I now transfer those records into the real table in the real DB, someone kindly assist and guide me. Regards, Sasikumar U =-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
