Re: [sqlite] SQLl question
On 15/05/2009 2:37 PM, Dennis Cote wrote: > Evan Burkitt wrote: >> This isn't a Sqlite question per se, but I know there are some SQL gurus >> here who might have some insight into this problem. I apologize for >> being off-topic; I can be shameless when I need help. :)> >> >> I have three tables, N, P and E. N contains the fields id and name. The >> other two each contain the fields id, type and addr. P holds phone >> numbers, E email addresses. In P, the type field is always 'phone'; in >> the P it is always 'email'. They are all related on id. >> >> I want to build a single query that will return a result set consisting >> of N.name, P/E.type and P/E.addr. That is, it contains the like-named >> fields of both P and E. For example: >> >> -name -type--- -addr- >> "John Smith", "phone", "123-555-1212" >> "John Smith", "email", "john.sm...@domain.com" >> "Bill Jones", "phone", "123-555-1213" >> "Jane Johnson", "email", "j...@anotherdomain.com" >> >> and so forth. The order of the names and types is not important. >> >> Is this possible? >> >> > > This (untested) SQL should do what you want. > > select N.name as name, C.type as type, C.addr as addr > from N > join (select id, type, addr from P > union > select id, type, addr from E) as C > on C.id = N.id > order by N.name, C.type; or slightly differently: select N.name, P.type, P.addr from N join P on N.id = P.id union select N.name, E.type, E.addr from N join E on N.id = E.id order by 1, 2; > > The union combines all the data from your P and E tables so they can be > joined to the N table using the id filed. The result is sorted by the > order by clause which you could drop if you really don't care about the > order of the results. > > Your database would be simpler if you simply combined these two tables > into a single table in the first place. It would eliminate the need to > combine them for this type of query. The tables already have a type > field to distinguish the email adresses from the phone numbers, so there > is no need to put them in separate tables. Very good advice. Evan, consider what you would have to do if you wanted to distinguish what sub-type of phone number (work, home, cell aka mobile, work fax, home fax) and what type of e-mail (home, work) plus other contact methods (skype, pager, ...) --- this is not an unreasonable requirement at all for a contacts database, even a home-grown one. Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLl question
Evan Burkitt wrote: > This isn't a Sqlite question per se, but I know there are some SQL gurus > here who might have some insight into this problem. I apologize for > being off-topic; I can be shameless when I need help. :)> > > I have three tables, N, P and E. N contains the fields id and name. The > other two each contain the fields id, type and addr. P holds phone > numbers, E email addresses. In P, the type field is always 'phone'; in > the P it is always 'email'. They are all related on id. > > I want to build a single query that will return a result set consisting > of N.name, P/E.type and P/E.addr. That is, it contains the like-named > fields of both P and E. For example: > > -name -type--- -addr- > "John Smith", "phone", "123-555-1212" > "John Smith", "email", "john.sm...@domain.com" > "Bill Jones", "phone", "123-555-1213" > "Jane Johnson", "email", "j...@anotherdomain.com" > > and so forth. The order of the names and types is not important. > > Is this possible? > > This (untested) SQL should do what you want. select N.name as name, C.type as type, C.addr as addr from N join (select id, type, addr from P union select id, type, addr from E) as C on C.id = N.id order by N.name, C.type; The union combines all the data from your P and E tables so they can be joined to the N table using the id filed. The result is sorted by the order by clause which you could drop if you really don't care about the order of the results. Your database would be simpler if you simply combined these two tables into a single table in the first place. It would eliminate the need to combine them for this type of query. The tables already have a type field to distinguish the email adresses from the phone numbers, so there is no need to put them in separate tables. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLl question
This isn't a Sqlite question per se, but I know there are some SQL gurus here who might have some insight into this problem. I apologize for being off-topic; I can be shameless when I need help. :)> I have three tables, N, P and E. N contains the fields id and name. The other two each contain the fields id, type and addr. P holds phone numbers, E email addresses. In P, the type field is always 'phone'; in the P it is always 'email'. They are all related on id. I want to build a single query that will return a result set consisting of N.name, P/E.type and P/E.addr. That is, it contains the like-named fields of both P and E. For example: -name -type--- -addr- "John Smith", "phone", "123-555-1212" "John Smith", "email", "john.sm...@domain.com" "Bill Jones", "phone", "123-555-1213" "Jane Johnson", "email", "j...@anotherdomain.com" and so forth. The order of the names and types is not important. Is this possible? -evan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
Joanne Pham wrote: > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > > So I have to do this: > begin transaction > prepared statement >.. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > I believe that used to be the case with early versions of sqlite 3. It is no longer true. You can see that sqlite 3.6.14 generates exactly the same opcodes when it prepares a statement either inside or outside a transaction using the explain command. SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a,b); sqlite> .explain on sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> begin; sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> The older versions of sqlite generated different code in these two cases. If a statement was to be executed inside a transaction it was necessary to compile (i.e. prepare) it inside a transaction (thought not necessarily the same transaction that it was to be executed in) in order for sqlite to generate the correct code. If my memory serves me correctly, I seem to recall it added some kind of a COMMIT opcode to the end of a statement when it was compiled outside a transaction. This opcode would incorrectly close the transaction when executed inside a transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] heap corruption?
turns out that someone else was trampling the heap. problem solved. thanks tom -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Sent: Monday, April 06, 2009 9:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] heap corruption? On Apr 7, 2009, at 10:45 AM, Tom Broadbent wrote: > hello - > > i'm using SQLite in an embedded application (WinCE) and i'm running > into what appears to a heap corruption issue. the access violation > happens at the following line (in pcache1Fetch): > > for(pPage=pCache->apHash[h]; pPage&&pPage->iKey!=iKey; pPage=pPage- > >pNext); > > mostly i'm curious what others have experienced relating to heap > corruption and SQLite. the bad address that is causing the AV > appears to be UNICODE (ascii-range bytes, 0x00, ascii-range bytes, > 0x00). i realize this isn't much to work w/ but mostly i'm > interested in a) others' experiences w/ this type of problem, b) > hints to find the cause of the heap corruption, and c) tools that > work w/ WinCE for detecting heap corruption (i've looked into > CodeSnitch a bit). > > i'm running a vanilla version 3.6.6.2 w/ SQLITE_OS_WINCE, > SQLITE_OMIT_LOCALTIME. Compiling with both SQLITE_DEBUG and SQLITE_MEMDEBUG adds a bunch of checks to each call to malloc() and free() that SQLite makes. If SQLite is corrupting the heap itself it is likely an assert() will fail during one of these checks. The stack trace might make it clearer what is going on. Also, you could try using the SQLITE_CONFIG_HEAP feature to configure SQLite with its own private heap space by calling sqlite3_config() before any other API: static u8 heap[1024*1024]; sqlite3_config(heap, 1024*1024, 32); If it is not SQLite corrupting the heap (it could be other parts of the app), then the crash will likely occur in some other sub-system when SQLite is configured this way. Dan. > thanks > tom > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > ___ > 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 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should use prepare/bind or just sqlite_exec.
In addition to performance, consider bugs and security. Read up on:
http://en.wikipedia.org/wiki/SQL_injection
I strive to always use the bind version of whatever database API I'm
using, because constructing SQL leads to this kind of problem often
enough that it's just not worth it. That's not even considering how
often sprintf itself leads to security bugs! Additionally, even when
you aren't using user data to construct the SQL (so there's no direct
exploit), constructed SQL statements tend to be more prone to bugs
over time.
If it is routinely more convenient to construct SQL strings than it is
to use bind-style APIs, you should perhaps consider writing a wrapper
API to make things easier.
-scott
On Thu, May 14, 2009 at 10:29 AM, Joanne Pham wrote:
> Hi all,
> I would like to update the database and there are two ways to do it and I
> were wondering which way is better:
> 1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ...
> q = "UPDATE logTable SET stale = ? WHERE id = ?";
> rc = sqlite3_prepare(updateSqli q, -1, &pstmt, 0);
> if (rc != SQLITE_OK) {
> fprintf(stderr, "Error prepare: %s\n", __FUNCTION__);
> return -1;
> }
> ret = sqlite3_bind_int(pstmt, 1, 0);
> ret = sqlite3_bind_int64(pstmt, 2, rpid);
> rc = sqlite3_step(pstmt);
> rc = sqlite3_reset(pstmt);
> rc = sqlite3_finalize(pstmt);
>
> 2) Way #2
> q = "UPDATE logTable SET stale = 1 WHERE id = ";
> sprintf(sqlStmt,"%s%d ",q,rpid);
> sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, &errMsg) ;
> if (sqlSt != SQLITE_OK ) {
> // print out error message
> sqlite3_free(errMsg);
> }
> ...
>
> Which way is better in term of performance.
> Thanks
> JP
>
>
>
> ___
> 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] Should use prepare/bind or just sqlite_exec.
Joanne, Igor was saying: Assuming you are only doing one update either works and #2 is faster for you to implement. On the other hand, if there is a long list of id's to update, than #1 is faster if you only prepair the statement once and bind multiple times. On a side note, with respect to your code: q = "UPDATE logTable SET stale = 1 WHERE id = "; sprintf(sqlStmt,"%s%d ",q,rpid); I don't know the source of the ID, I assume it is the system not a user, but if it is a user, use Parameterized SQL rather than this approach of SQL concatenation. For details, Google: sql injection attack Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should use prepare/bind or just sqlite_exec.
Thanks Igor, So you prefer #1 instead of #2. Thanks, JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Thursday, May 14, 2009 11:13:23 AM Subject: Re: [sqlite] Should use prepare/bind or just sqlite_exec. Joanne Pham wrote: > I would like to update the database and there are two ways to do it > and I were wondering which way is better: > 1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ... > > 2) Way #2 > q = "UPDATE logTable SET stale = 1 WHERE id = "; > sprintf(sqlStmt,"%s%d ",q,rpid); > sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, &errMsg) ; > > Which way is better in term of performance. I doubt you would detect any measurable performance difference on one-time execution. #1 is better for reasons other than performance. It also improves performance if you need to run the same query many times, perhaps with different parameters. Realize that sqlite3_exec simply calls sqlite3_preare, sqlite3_step et al internally. 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
Re: [sqlite] Unable to call SQLITE_SEQUENCE from adbe air
I do see the table from command line, just that dont see it from Actionscript code thanks Suresh On Thu, May 14, 2009 at 12:05 PM, D. Richard Hipp wrote: > > On May 14, 2009, at 2:58 PM, Suresh Narasimhan wrote: > > > I tried calling select seq from SQLITE_SEQUENCE table from with > > actionscript > > and i get a Error 3115 no such table detailID :2013 > > > > You have to have an INTEGER PRIMARY KEY AUTOINCREMENT somewhere in > your schema or else the SQLITE_SEQUENCE table does not exist. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > 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] Unable to call SQLITE_SEQUENCE from adbe air
On May 14, 2009, at 2:58 PM, Suresh Narasimhan wrote: > I tried calling select seq from SQLITE_SEQUENCE table from with > actionscript > and i get a Error 3115 no such table detailID :2013 > You have to have an INTEGER PRIMARY KEY AUTOINCREMENT somewhere in your schema or else the SQLITE_SEQUENCE table does not exist. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to call SQLITE_SEQUENCE from adbe air
I tried calling select seq from SQLITE_SEQUENCE table from with actionscript and i get a Error 3115 no such table detailID :2013 I know this might be air specific i posted in their forum , but havent got any reply. So thought would post it here to see if people here know about this. thanks, Suresh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should use prepare/bind or just sqlite_exec.
Joanne Pham wrote: > I would like to update the database and there are two ways to do it > and I were wondering which way is better: > 1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ... > > 2) Way #2 > q = "UPDATE logTable SET stale = 1 WHERE id = "; > sprintf(sqlStmt,"%s%d ",q,rpid); > sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, &errMsg) ; > > Which way is better in term of performance. I doubt you would detect any measurable performance difference on one-time execution. #1 is better for reasons other than performance. It also improves performance if you need to run the same query many times, perhaps with different parameters. Realize that sqlite3_exec simply calls sqlite3_preare, sqlite3_step et al internally. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Should use prepare/bind or just sqlite_exec.
Hi all,
I would like to update the database and there are two ways to do it and I were
wondering which way is better:
1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ...
q = "UPDATE logTable SET stale = ? WHERE id = ?";
rc = sqlite3_prepare(updateSqli q, -1, &pstmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr, "Error prepare: %s\n", __FUNCTION__);
return -1;
}
ret = sqlite3_bind_int(pstmt, 1, 0);
ret = sqlite3_bind_int64(pstmt, 2, rpid);
rc = sqlite3_step(pstmt);
rc = sqlite3_reset(pstmt);
rc = sqlite3_finalize(pstmt);
2) Way #2
q = "UPDATE logTable SET stale = 1 WHERE id = ";
sprintf(sqlStmt,"%s%d ",q,rpid);
sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, &errMsg) ;
if (sqlSt != SQLITE_OK ) {
// print out error message
sqlite3_free(errMsg);
}
...
Which way is better in term of performance.
Thanks
JP
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] seg fault in sqlite3MemFree
On May 14, 2009, at 6:07 AM, Ala Qumsieh wrote:
> Hi all,
>
> I'm using the latest sqlite3 v3.6.14.
>
> I noticed that when using virtual tables (with the R*TREE extension),
> I'm getting sporadic core dumps. After some digging around, I traced
> this to the sqlite3MemFree function (during a call to disconnect):
>
> static void sqlite3MemFree(void *pPrior){
> sqlite3_int64 *p = (sqlite3_int64*)pPrior;
> assert( pPrior!=0 );
> p--;
> free(p);
> }
>
> Here, the call to free() is core dumping. My C skills aren't sharp
> enough to figure out why the pointer is decremented, but it seems this
> is causing core dumps only with virtual tables.
>
> Did anybody else see this? Am I doing something wrong?
Do you have a stack trace for the crash? On linux, open the core with
[gdb ] and type "where".
Dan.
> Thanks,
> --Ala
>
> PS. I'm unable to create a simple test case as I'm using this through
> Perl's DBD::SQLite module, but I can try if you really need one.
> ___
> 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] SQL logic error or missing database
Thanks a lot for the explanation Igor.
--
Marco Bambini
On May 14, 2009, at 1:55 PM, Igor Tandetnik wrote:
> "Marco Bambini" wrote
> in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net
>> I have two threads that are writing 2000 rows each to the same
>> database at the same time.
>> I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
>>
>> Each client executes this code (pseudo C code):
>> void write (sqlite3 *db) {
>> int i;
>>
>> for (i=1; i<=2000; i++) {
>> if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN
>> IMMEDIATE;", ...);
>> sqlite3_exec(db, "INSERT INTO", ...);
>> if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
>> "COMMIT;", ...);
>> }
>> }
>> and db is shared between the two clients.
>
> You have a race condition here: between the calls to
> sqlite3_get_autocommit and sqlite3_exec, the other thread could very
> well have issued a BEGIN or a COMMIT of its own. Between the time you
> check the condition and the time you act on it, the condition could
> have
> changed.
>
> Besides, the documentation on sqlite3_get_autocommit has this
> sentence:
> If another thread changes the autocommit status of the database
> connection while this routine is running, then the return value is
> undefined. In other words, sqlite3_get_autocommit is explicitly not
> thread-safe.
>
> Since you only run one INSERT per transaction anyway, why do you feel
> you need explicit BEGIN and COMMIT?
>
>> At the end of the loop, instead of having 4000 rows I have 3976 rows
>> (it's random, sometimes I have 3972 or 3974).
>> sqlite3_exec doesn't returns any error during the INSERT statement,
>> but I have some errors during the BEGIN IMMEDIATE, errors are all:
>> SQL logic error or missing database (printed with sqlite3_errmsg).
>
> Your use of sqlite3_errmsg is itself very likely a race. Between the
> time you detect an error and the time you retrieve error message, the
> other thread could have run some statements that modify the error
> message. Moreover, between the time you call sqlite3_errmsg and the
> time
> you actually print the string pointed to by the char* pointer the
> function returns, the string may be modified or even deallocated.
>
> 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
Re: [sqlite] PRAGMA vdbe_trace not working
"Maria" wrote in message news:92adf4ae0905140039o5b05785uf5f9ae9a0ece4...@mail.gmail.com > Hi, I'm using SQLite 3.6.13. > Although I turn on vdbe trace(> PRAGMA bdbe_trace=ON;) and type select > statement, it doesn't show any trace. PRAGMA vdbe_trace only works in a debug build (see http://sqlite.org/vdbe.html#trace). It won't work in pre-built binaries, you need to build SQLite yourself. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
"Marco Bambini" wrote
in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net
> I have two threads that are writing 2000 rows each to the same
> database at the same time.
> I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
>
> Each client executes this code (pseudo C code):
> void write (sqlite3 *db) {
> int i;
>
> for (i=1; i<=2000; i++) {
> if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN
> IMMEDIATE;", ...);
> sqlite3_exec(db, "INSERT INTO", ...);
> if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db, "COMMIT;", ...);
> }
> }
> and db is shared between the two clients.
You have a race condition here: between the calls to
sqlite3_get_autocommit and sqlite3_exec, the other thread could very
well have issued a BEGIN or a COMMIT of its own. Between the time you
check the condition and the time you act on it, the condition could have
changed.
Besides, the documentation on sqlite3_get_autocommit has this sentence:
If another thread changes the autocommit status of the database
connection while this routine is running, then the return value is
undefined. In other words, sqlite3_get_autocommit is explicitly not
thread-safe.
Since you only run one INSERT per transaction anyway, why do you feel
you need explicit BEGIN and COMMIT?
> At the end of the loop, instead of having 4000 rows I have 3976 rows
> (it's random, sometimes I have 3972 or 3974).
> sqlite3_exec doesn't returns any error during the INSERT statement,
> but I have some errors during the BEGIN IMMEDIATE, errors are all:
> SQL logic error or missing database (printed with sqlite3_errmsg).
Your use of sqlite3_errmsg is itself very likely a race. Between the
time you detect an error and the time you retrieve error message, the
other thread could have run some statements that modify the error
message. Moreover, between the time you call sqlite3_errmsg and the time
you actually print the string pointed to by the char* pointer the
function returns, the string may be modified or even deallocated.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA vdbe_trace not working
Hi, I'm using SQLite 3.6.13. Although I turn on vdbe trace(> PRAGMA bdbe_trace=ON;) and type select statement, it doesn't show any trace. Could anyone advise me why it's happening? By the way, 'explain' is working. But I want to see how the stack values change. Thanks so much. Cheers, Maria. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] seg fault in sqlite3MemFree
Hi all,
I'm using the latest sqlite3 v3.6.14.
I noticed that when using virtual tables (with the R*TREE extension),
I'm getting sporadic core dumps. After some digging around, I traced
this to the sqlite3MemFree function (during a call to disconnect):
static void sqlite3MemFree(void *pPrior){
sqlite3_int64 *p = (sqlite3_int64*)pPrior;
assert( pPrior!=0 );
p--;
free(p);
}
Here, the call to free() is core dumping. My C skills aren't sharp
enough to figure out why the pointer is decremented, but it seems this
is causing core dumps only with virtual tables.
Did anybody else see this? Am I doing something wrong?
Thanks,
--Ala
PS. I'm unable to create a simple test case as I'm using this through
Perl's DBD::SQLite module, but I can try if you really need one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked or is malformed
We use QNX 6.3 on a ppc with sqlite as database for our application. As
there was a speed problem in older sqlite versions we used the PRAGMA
SYNCHRONOUS = OFF command before creating an INSERT and the PRAGMA
SYNCHRONOUS = ON afterwards. We used version 3.4.2 for a long time, 2
month ago we changed to version 3.6.11 (now without pragma options).
On our machines there are several programs that access the databases (we
use 4 dbs) and also the programs have all several threads. This worked for
over a year now on 10 machines. I only saw two times a database that was
corrupted. Always messages like this: On page 6928 at right child: 2nd
reference to page 7003 Page 3805 is never used. But that never caused a
totally corruption of the database. I added a program to run the
integrity_check and performing a ".dump |" if a problem was found.
Today I had the first total crash. No program could start at all. All
programs (written in c) got database locked (11) and ended. I was lucky to
have a telnet connection to the customer, so I run an integrity_check and
got a long list with errors. I tried to .dump | but the most important
table was so much damaged, that it was empty after the .dump. Also there
was the data.s3db-journal file, but no connection to the database was
opened.
I dont have an idea how to find the reason for the problem. The customer
had problems with the machine, so he switched off/on very often the last
days. But I thought sqlite should be save enough to handle power fails. I
need to find a solution where quickly, if I have the same problem at a
customer without internet connection I will have a big problem.
I think my greatest risk of database corruptions are the inserts. As many
programs may access the database, it is often locked. So I wrote a
function that handles every insert to the database, it tries several time
to access it. Perhaps it is also wrong, that different threads inside a
program use the same sqlite3* handle for the database.
int sqlite3_exec_save(sqlite3* database, const char *command, int
(*callback)(void *ag1,int arg2,char **arg3,char **arg4), void *arg1 , char
**aErrmsg, const char *errString, int printMessage ){
int counter = 0;
int returnCode;
char help_str[500];
char *errmsg = 0;
srand (pthread_self());
//try up to 300 times in up to 9 sec
do{
returnCode = sqlite3_exec(database,
command, NULL, NULL, &errmsg);
if((returnCode == SQLITE_BUSY) || (errmsg
!= NULL && returnCode != SQLITE_ERROR)){
usleep(2 +
(rand()%1));//several threads wait different times
counter++;
}
}while ((returnCode == SQLITE_BUSY && counter < 300) ||
(errmsg != NULL && counter < 300 && returnCode != SQLITE_ERROR)); //only
repeat non SQL-Errors
if (errmsg != NULL ){
sprintf(help_str,"%s (%i:%s) [attempt
%i]:", errString, returnCode, errmsg, counter);
eprintf("could not insert to database");
logPoint(help_str);
logPoint(command);
}else if( counter > 5){
eprintf("needed %i attempts to insert to
database", counter);
}
//if somebody wants to use the error messagae outside the
function
if (aErrmsg != NULL && errmsg != NULL){
*aErrmsg = malloc(strlen(errmsg)+1);
strcpy(*aErrmsg,errmsg);
}
return returnCode;
}
Is it a good way to use a insert function like that?
Should every thread have an own sqlite* connetion?
Should a program hold the sqlite* connection opened or should it close
after every insert and open again?
Please give me some advice. Stefan
Best regards / Mit freundlichen Grüssen
Stefan Breitholz
---
Staeubli GmbH - Theodor-Schmidt-Str. 19
DE - 95448 Bayreuth
Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126
mailto:s.breith...@staubli.com
http://www.staubli.com
Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl
Kirschner
---
This e-mail and any attachment (the 'message') are confidential and privileged
and intended solely for the person or the entity to which it is adressed. If
you have received it in error, please advise the sender by return e-mail and
delete it immediately. Any use not in accordance with its purpose, any
dissemination or re
[sqlite] SQL logic error or missing database
I have two threads that are writing 2000 rows each to the same
database at the same time.
I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
Each client executes this code (pseudo C code):
void write (sqlite3 *db) {
int i;
for (i=1; i<=2000; i++) {
if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN
IMMEDIATE;", ...);
sqlite3_exec(db, "INSERT INTO", ...);
if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
"COMMIT;", ...);
}
}
and db is shared between the two clients.
At the end of the loop, instead of having 4000 rows I have 3976 rows
(it's random, sometimes I have 3972 or 3974).
sqlite3_exec doesn't returns any error during the INSERT statement,
but I have some errors during the BEGIN IMMEDIATE, errors are all:
SQL logic error or missing database (printed with sqlite3_errmsg).
Any explanation of the possible causes for the missed rows?
Thanks.
--
Marco Bambini
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad UPDATE performance
On May 14, 2009, at 2:33 PM, Ofir Neuman wrote: > Dan, > > Before I got your reply I checked it with Pcache1RemoveFromHash() > and it > fixed the problem. > > I will re-check this issue with pCache->nPage-- but I guess that it > will fix > the problem as well. > > Thanks for the help. > > Will this fix be included in the next build? Yes. > When a new build should be released? Unclear. Recently there has been a new release about once a month. Last release was about a week ago. > I have to release my application soon and can't due to this bug > (don't want > to compile sqlite3.dll by myself rather to use the precompiled for > windows) Maybe compile a patched sqlite3.c into your executable instead of using a dll. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad UPDATE performance
Dan, Before I got your reply I checked it with Pcache1RemoveFromHash() and it fixed the problem. I will re-check this issue with pCache->nPage-- but I guess that it will fix the problem as well. Thanks for the help. Will this fix be included in the next build? When a new build should be released? I have to release my application soon and can't due to this bug (don't want to compile sqlite3.dll by myself rather to use the precompiled for windows) Thanks, Ofir. On Thu, May 14, 2009 at 7:53 AM, Dan wrote: > > On May 14, 2009, at 4:08 AM, Ofir Neuman wrote: > > > Thanks Dan this was very helpful it seems that I have a similar > > problem. > > > > Didn't try the patch yet but I find out a scenario to reproduce the > > problem. > > > > 1. Process A perform UPDATE on bunch of records (within transaction) > > 2. Afterward, process B perform the same UPDATE operation but only > > on a > > single record > > 3. The next time process A will try to perform the same UPDATE again > > (bunch > > of records) the results will be bad. > > Looking at the task manager I can see that process A doesn't consume > > any CPU > > usage just I/O. (the CPU should increase like in step 1) > > > > One question regarding the patch, should I add only the following > > line as > > suggested? > > Pcache1RemoveFromHash(pPage); > > No. That is almost, but not quite, correct. Apply the patch to > pcache1.c as > shown here: > > http://www.sqlite.org/cvstrac/chngview?cn=6619 > > Or, if you want to do it by hand, add the line: > > pCache->nPage--; > > where the original ticket suggests Pcache1RemoveFromHash(). > > Dan. > > > > > Thanks, > > Ofir. > > > > > > ---Original Message--- > > > > From: Dan > > Date: 05/13/09 07:23:16 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Bad UPDATE performance > > > > On May 13, 2009, at 3:15 AM, Ofir Neuman wrote: > > > >> Hi, > >> > >> Using version 3.6.10.0. > >> > >> I have a problem that my application becomes slow after awhile with > >> no > >> specific reasonI got a chance to reproduce it on development > >> environment and > >> have noticed that UPDATE of 200 records within transaction takes > >> more than > >> 20 sec. > >> > >> When the application first run the same update for the same records > >> takes > >> something like 1-2 sec. > >> > >> Since I had the problem while debugging I try to figure out the > >> problem and > >> have noticed the following things: > >> > >> 1. When I suffer bad UPDATE performance the journal file is 3 times > >> bigger > >> than on regular run (when UPDATE command takes 1-2 sec) > >> 2. Most of the time spent on the UPDATE itself and not on the COMMIT > >> operation. > >> 3. I tried to open new db connection while the application still > >> running and > >> do the same operation and guess what... Everything works fine the > >> application is responsive and the same update took something like a > >> second. > >> > >> I didn't find a specific scenario but I can reproduce it if I do the > >> same > >> operation on the DB multiple times, after a while everything will > >> become > >> slow. > > > > Maybe try this patch: > > > > http://www.sqlite.org/cvstrac/chngview?cn=6619 > > > > Dan. > > > > ___ > > 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-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
