[sqlite] Proxy locking and NFS
Hello. The new Proxy Locking code for OS X says it is intended for AFP filesystems, but it seems it also addresses a cache coherency issue on NFS in general. Is that the case, and if so, should the option be made available for other UNIX systems? I am thinking of modifying my copy to allow it. Jim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
Hi Ken,
yes, I do check the return values also for the
sqlite3_prepare_v2 call. This is allways
successful, the first sqlite_step call right after
this one returns the magic MISSUSE.
Yes, I also tried to call sqlite_reset right
after this first error and in this case
sqlite_reset return the LOCK state. I didn't
yet go on to handle this...
I can't post the code of the original implementation here,
but also for my debugging and tests I'll produce
a simplified test application. Just to see if that
can be used to reproduce the effect. If that one runs
without any problem, it's my fault, if not: I'll post
it here for further discussions... ;)
Thanks for your feedback
Marcus
>
> Marcus,
>
> I'm not sure if this will help or not...
>
> But I noticed your sample code does not test the return value from the
> sqlite3_prepare_v2 call. Could the code be entering the do loop when an
> error was returned from prepare?
>
> Just an idea.
>
> Have you tried the reset call as DRH had suggested?
> Your prior post indicated you had more code and another loop. Can you post
> the full code for both loops?
>
> HTH
>
>
>
> --- On Wed, 3/4/09, Marcus Grimm wrote:
>
>> From: Marcus Grimm
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite
>> Database"
>> Date: Wednesday, March 4, 2009, 4:31 PM
>> Ken,
>>
>> you are of course right that it needs some checks
>> for locks and busy states. I left that out to
>> simplify the code given below. My original code
>> checks that and it usually works quite well.
>> that's basically the reason why I was puzzled
>> by the randomly MISUSE results after I added the shared
>> cache.
>>
>> I'm not yet finished with my debugging since it is a
>> multithreading/collision issue - ugly to trace.
>>
>> My feeling is that it is related to the condition when
>> one thread is attempting or holding an exclusive lock
>> while another thread is just doing an sqlite_step (read
>> only)
>> on an allready created statement. Both threads use their
>> own
>> DB connections. For example: when I do a sqlite_reset
>> right after sqlite_step returns SQLITE_MISUSE, as Richard
>> suggest,
>> I get immediately a SQLITE_LOCK return code from
>> sqlite_reset in this case. Why I didn't get that before
>> or
>> from the sqlite_prepare ?
>>
>> Anyway, I'm going on to workaround this ALso I
>> would like
>> to mention once more that it is only during an artificial
>> stress
>> test, mainly to verify my implementation. Under normal
>> usercondition it is very unlikely to happend and sqlite
>> works perfect as expected.
>>
>> Thanks
>>
>> Marcus
>>
>> >
>> > Marcus,
>> >
>> > You might want to also add some checks in for
>> sqlite_busy as on the result
>> > of the prepare and the first call to sqlite_step.
>> >
>> >
>> > On the inner loop test for the most common case first
>> (SQLITE_ROW) then
>> > test for errors... Slight performance improvement...
>> >
>> >
>> > --- On Wed, 3/4/09, Marcus Grimm
>> wrote:
>> >
>> >> From: Marcus Grimm
>> >> Subject: Re: [sqlite] shared cache and
>> SQLITE_MISUSE on sqlite3_step()
>> >> To: "General Discussion of SQLite
>> Database"
>> >> Date: Wednesday, March 4, 2009, 10:25 AM
>> >> Richard, thanks again for the feedback.
>> >>
>> >> However, I don't see how it can happend that
>> the
>> >> statement
>> >> is completed internally without returning
>> SQLITE_DONE.
>> >> In the particular code of the "reading
>> thread" I
>> >> do something like:
>> >>
>> >> --
>> >> sqlite3_prepare_v2(db, "SELECT * FROM
>> TableA",
>> >> -1, &stmt, 0);
>> >> /** step throu table result **/
>> >> do
>> >> {
>> >>rc = sqlite3_step(stmt);
>> >>if( rc == SQLITE_MISUSE )
>> >>{ fprintf(stderr, "ERROR...\n");
>> break; }
>> >>else
>> >>if( rc == SQLITE_ROW )
>> >> read_data_etc();
>> >>else
>> >> break;
>> >> }while( rc != SQLITE_DONE );
>> >>
>> >> sqlite3_finalize(stmt);
>> >> --
>> >>
>> >> The prepare statement pointer is defined locally
>> and no
>> >> other
>> >> thread can access it, except sqlite internal
>> maybe.
>> >> To me it looks that in case a parallel thread is
>> inserting
>> >> or
>> >> updating data, the above loop is somehow affected
>> and
>> >> returns the
>> >> MISUSE.
>> >>
>> >> Your reply so far indicates either a bug on my
>> side or a
>> >> missusage.
>> >> I'll go on and try to find the reason why the
>> magic
>> >> number is resetted
>> >> without knowing from calling functions.
>> >>
>> >> calling sqlite3_reset in these cases is difficult
>> for me
>> >> since it might
>> >> not be the first step and previous data is
>> allready in use
>> >> in the upper loop.
>> >> But maybe an option for the very first step if
>> that
>> >> fails... hm... I'll try that.
>> >>
>> >>
>> >> Thanks again
>> >>
>> >> kind regards
>> >>
>> >> Marcus Grimm
>> >>
>> >>
>> >>
>> >> D. Richard Hipp wrote:
>> >> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm
>> wrote:
>> >
Re: [sqlite] Foreign key support
Hi Dan, >> According to the readme: >> http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README >> I should be able to run genfkey directly from within the command >> line tool, but it doesn't seem to work > The change to add the genfkey functionality to the shell is still in > cvs. It will be available as part of 3.6.12. Oh, I see. It's a case of premature documentation ;-) I'll try again when 3.6.12 comes out. Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key support
On Mar 5, 2009, at 10:25 AM, BareFeet wrote: > Hi all, > >>> It is true that triggers can be used to achieve referential >>> integrity. >>> However you don't have to hand craft them. > >>> the SQLite team has already done all the work for you. The SQLite >>> source includes a program named 'genfkey' that will create the >>> triggers. > >> So the next step is probably that the SQLite engine does all that on >> its >> own, how would that be? :-) > > Good question. I'd like to know that myself. > > In the mean time, I tried genfkey. I downloaded the latest sqlite3 > command line program for Mac OS X. According to the readme: > http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README > I should be able to run genfkey directly from within the command line > tool, but it doesn't seem to work, as shown here: > > sqlite> select sqlite_version(); > 3.6.11 > sqlite> .genfkey > unknown command or invalid arguments: "genfkey". Enter ".help" for > help > sqlite> .genfkey --exec > unknown command or invalid arguments: "genfkey". Enter ".help" for > help > > What am I missing? The change to add the genfkey functionality to the shell is still in cvs. It will be available as part of 3.6.12. Easiest thing might be to get the new shell.c file from cvs: http://www.sqlite.org/cvstrac/getfile?f=sqlite/src/shell.c&v=1.205 Compile it with the amalgamation source: gcc -O2 sqlite3.c shell.c -o sqlite3 Or you could get the full 3.6.11 tarball and compile the standalone version of genfkey it includes: http://www.sqlite.org/sqlite-3.6.11.tar.gz Dan. > Thanks, > Tom > BareFeet > > -- > Comparison of SQLite GUI applications: > http://www.tandb.com.au/sqlite/compare/?ml > > ___ > 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] shared cache and SQLITE_MISUSE on sqlite3_step()
Marcus,
I'm not sure if this will help or not...
But I noticed your sample code does not test the return value from the
sqlite3_prepare_v2 call. Could the code be entering the do loop when an error
was returned from prepare?
Just an idea.
Have you tried the reset call as DRH had suggested?
Your prior post indicated you had more code and another loop. Can you post the
full code for both loops?
HTH
--- On Wed, 3/4/09, Marcus Grimm wrote:
> From: Marcus Grimm
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database"
>
> Date: Wednesday, March 4, 2009, 4:31 PM
> Ken,
>
> you are of course right that it needs some checks
> for locks and busy states. I left that out to
> simplify the code given below. My original code
> checks that and it usually works quite well.
> that's basically the reason why I was puzzled
> by the randomly MISUSE results after I added the shared
> cache.
>
> I'm not yet finished with my debugging since it is a
> multithreading/collision issue - ugly to trace.
>
> My feeling is that it is related to the condition when
> one thread is attempting or holding an exclusive lock
> while another thread is just doing an sqlite_step (read
> only)
> on an allready created statement. Both threads use their
> own
> DB connections. For example: when I do a sqlite_reset
> right after sqlite_step returns SQLITE_MISUSE, as Richard
> suggest,
> I get immediately a SQLITE_LOCK return code from
> sqlite_reset in this case. Why I didn't get that before
> or
> from the sqlite_prepare ?
>
> Anyway, I'm going on to workaround this ALso I
> would like
> to mention once more that it is only during an artificial
> stress
> test, mainly to verify my implementation. Under normal
> usercondition it is very unlikely to happend and sqlite
> works perfect as expected.
>
> Thanks
>
> Marcus
>
> >
> > Marcus,
> >
> > You might want to also add some checks in for
> sqlite_busy as on the result
> > of the prepare and the first call to sqlite_step.
> >
> >
> > On the inner loop test for the most common case first
> (SQLITE_ROW) then
> > test for errors... Slight performance improvement...
> >
> >
> > --- On Wed, 3/4/09, Marcus Grimm
> wrote:
> >
> >> From: Marcus Grimm
> >> Subject: Re: [sqlite] shared cache and
> SQLITE_MISUSE on sqlite3_step()
> >> To: "General Discussion of SQLite
> Database"
> >> Date: Wednesday, March 4, 2009, 10:25 AM
> >> Richard, thanks again for the feedback.
> >>
> >> However, I don't see how it can happend that
> the
> >> statement
> >> is completed internally without returning
> SQLITE_DONE.
> >> In the particular code of the "reading
> thread" I
> >> do something like:
> >>
> >> --
> >> sqlite3_prepare_v2(db, "SELECT * FROM
> TableA",
> >> -1, &stmt, 0);
> >> /** step throu table result **/
> >> do
> >> {
> >>rc = sqlite3_step(stmt);
> >>if( rc == SQLITE_MISUSE )
> >>{ fprintf(stderr, "ERROR...\n");
> break; }
> >>else
> >>if( rc == SQLITE_ROW )
> >> read_data_etc();
> >>else
> >> break;
> >> }while( rc != SQLITE_DONE );
> >>
> >> sqlite3_finalize(stmt);
> >> --
> >>
> >> The prepare statement pointer is defined locally
> and no
> >> other
> >> thread can access it, except sqlite internal
> maybe.
> >> To me it looks that in case a parallel thread is
> inserting
> >> or
> >> updating data, the above loop is somehow affected
> and
> >> returns the
> >> MISUSE.
> >>
> >> Your reply so far indicates either a bug on my
> side or a
> >> missusage.
> >> I'll go on and try to find the reason why the
> magic
> >> number is resetted
> >> without knowing from calling functions.
> >>
> >> calling sqlite3_reset in these cases is difficult
> for me
> >> since it might
> >> not be the first step and previous data is
> allready in use
> >> in the upper loop.
> >> But maybe an option for the very first step if
> that
> >> fails... hm... I'll try that.
> >>
> >>
> >> Thanks again
> >>
> >> kind regards
> >>
> >> Marcus Grimm
> >>
> >>
> >>
> >> D. Richard Hipp wrote:
> >> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm
> wrote:
> >> >
> >> >> hi,
> >> >>
> >> >> OK, the value of p->magic is 519C2973
> >> (VDBE_MAGIC_HALT)
> >> >
> >> > That means the prepared statement has run to
> >> completion and needs to
> >> > be reset using sqlite3_reset() before you
> continue.
> >> >
> >> > 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
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> >
> http://sqlite.org:8080/cgi-
Re: [sqlite] Double entry bookkeeping
Hi all,
Below the basic SQL schema I have so far for double entry bookkeeping.
Does this look like a viable schema? I think I'm interpreting the
general structure of double entry bookkeeping and earlier discussion
here correctly. I welcome any comments specifically on the schema.
Basically each Transaction can have multiple Entries and each Entry
refers to one Account. An Entry may have an optional Narrative, stored
in the "Entries Narrative" table. Each Entry may have none, one or
more References, such as invoice numbers, order numbers, cheque
numbers, stored in the "Entries References" table.
create table Accounts
(
ID integer
primary key
, Code text
collate nocase
unique
not null
references "Entries" ("Account Code")
, Name text -- brief name
collate nocase
)
;
create table "Transactions"
(
ID integer
primary key
references "Entries" ("Transaction ID")
, Date date -- julianday of the
date that the transaction occurred
)
;
create table "Entries"
(
ID integer
primary key
references "Entries Narrative" (ID)
, "Transaction ID" integer
references Transactions (ID)
on delete cascade
on update cascade
, "Account Code" text
collate nocase
references Accounts (Code)
on delete restrict
on update cascade
, Amount integer-- amount in cents,
positive or negative
)
;
create table "Entries References" -- Optional reference(s) for
each
Entry
(
ID integer
primary key
, "Entry ID" integer
references Entries (ID)
on delete cascade
on update cascade
, "Reference" text -- internal or external
reference such as
invoice or cheque number
collate nocase
not null
)
;
create table "Entries Narrative"-- Optional description for
each Entry
(
ID integer
primary key
references "Entries" (ID)
on delete cascade
on update cascade
, Narrative text
not null
)
;
-- To prevent deletion of Transactions and Entries:
create trigger "Entries delete"
before delete
on "Entries"
begin
select raise(rollback, 'You cannot delete Entries. You must instead
reverse out the Entries.');
end
;
-- And here is a view showing all the Entries grouped by Transaction:
create view "Entries Report"
as
select
Entries.ID as ID
, Transactions.ID as "Transaction ID"
, date(Transactions.Date, '0.1 seconds', 'localtime') as Date
, case when Amount < 0 then round(-Amount/100.0,2) end as Debit
, case when Amount >= 0 then round(Amount/100.0,2) end as Credit
, Accounts.Code
, Accounts.Name
, Narrative
, group_concat("Reference", ', ') as "References"
from Entries
left join "Entries Narrative" on Entries.ID = "Entries Narrative".ID
left join "Entries References" on Entries.ID = "Entries
References"."Entry ID"
left join Transactions on Entries."Transaction ID" = Transactions.ID
left join Accounts on Entries."Account Code" = Accounts.Code
group by "Transaction ID", ID
;
I realize that the foreign keys (eg references ... on delete restrict)
aren't currently implemented by SQLite, but they do parse and can be
implemented by triggers, such as via the genkey utility:
http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README
By the way, is there a way to post colored text to this mail list? The
above schema is a lot easier to read in color.
Thanks,
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key support
Hi all, >> It is true that triggers can be used to achieve referential >> integrity. >> However you don't have to hand craft them. >> the SQLite team has already done all the work for you. The SQLite >> source includes a program named 'genfkey' that will create the >> triggers. > So the next step is probably that the SQLite engine does all that on > its > own, how would that be? :-) Good question. I'd like to know that myself. In the mean time, I tried genfkey. I downloaded the latest sqlite3 command line program for Mac OS X. According to the readme: http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README I should be able to run genfkey directly from within the command line tool, but it doesn't seem to work, as shown here: sqlite> select sqlite_version(); 3.6.11 sqlite> .genfkey unknown command or invalid arguments: "genfkey". Enter ".help" for help sqlite> .genfkey --exec unknown command or invalid arguments: "genfkey". Enter ".help" for help What am I missing? Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
Ken,
you are of course right that it needs some checks
for locks and busy states. I left that out to
simplify the code given below. My original code
checks that and it usually works quite well.
that's basically the reason why I was puzzled
by the randomly MISUSE results after I added the shared cache.
I'm not yet finished with my debugging since it is a
multithreading/collision issue - ugly to trace.
My feeling is that it is related to the condition when
one thread is attempting or holding an exclusive lock
while another thread is just doing an sqlite_step (read only)
on an allready created statement. Both threads use their own
DB connections. For example: when I do a sqlite_reset
right after sqlite_step returns SQLITE_MISUSE, as Richard suggest,
I get immediately a SQLITE_LOCK return code from
sqlite_reset in this case. Why I didn't get that before or
from the sqlite_prepare ?
Anyway, I'm going on to workaround this ALso I would like
to mention once more that it is only during an artificial stress
test, mainly to verify my implementation. Under normal
usercondition it is very unlikely to happend and sqlite
works perfect as expected.
Thanks
Marcus
>
> Marcus,
>
> You might want to also add some checks in for sqlite_busy as on the result
> of the prepare and the first call to sqlite_step.
>
>
> On the inner loop test for the most common case first (SQLITE_ROW) then
> test for errors... Slight performance improvement...
>
>
> --- On Wed, 3/4/09, Marcus Grimm wrote:
>
>> From: Marcus Grimm
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>> To: "General Discussion of SQLite Database"
>> Date: Wednesday, March 4, 2009, 10:25 AM
>> Richard, thanks again for the feedback.
>>
>> However, I don't see how it can happend that the
>> statement
>> is completed internally without returning SQLITE_DONE.
>> In the particular code of the "reading thread" I
>> do something like:
>>
>> --
>> sqlite3_prepare_v2(db, "SELECT * FROM TableA",
>> -1, &stmt, 0);
>> /** step throu table result **/
>> do
>> {
>>rc = sqlite3_step(stmt);
>>if( rc == SQLITE_MISUSE )
>>{ fprintf(stderr, "ERROR...\n"); break; }
>>else
>>if( rc == SQLITE_ROW )
>> read_data_etc();
>>else
>> break;
>> }while( rc != SQLITE_DONE );
>>
>> sqlite3_finalize(stmt);
>> --
>>
>> The prepare statement pointer is defined locally and no
>> other
>> thread can access it, except sqlite internal maybe.
>> To me it looks that in case a parallel thread is inserting
>> or
>> updating data, the above loop is somehow affected and
>> returns the
>> MISUSE.
>>
>> Your reply so far indicates either a bug on my side or a
>> missusage.
>> I'll go on and try to find the reason why the magic
>> number is resetted
>> without knowing from calling functions.
>>
>> calling sqlite3_reset in these cases is difficult for me
>> since it might
>> not be the first step and previous data is allready in use
>> in the upper loop.
>> But maybe an option for the very first step if that
>> fails... hm... I'll try that.
>>
>>
>> Thanks again
>>
>> kind regards
>>
>> Marcus Grimm
>>
>>
>>
>> D. Richard Hipp wrote:
>> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
>> >
>> >> hi,
>> >>
>> >> OK, the value of p->magic is 519C2973
>> (VDBE_MAGIC_HALT)
>> >
>> > That means the prepared statement has run to
>> completion and needs to
>> > be reset using sqlite3_reset() before you continue.
>> >
>> > 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
> ___
> 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] shared cache and SQLITE_MISUSE on sqlite3_step()
Marcus,
You might want to also add some checks in for sqlite_busy as on the result of
the prepare and the first call to sqlite_step.
On the inner loop test for the most common case first (SQLITE_ROW) then test
for errors... Slight performance improvement...
--- On Wed, 3/4/09, Marcus Grimm wrote:
> From: Marcus Grimm
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
> To: "General Discussion of SQLite Database"
> Date: Wednesday, March 4, 2009, 10:25 AM
> Richard, thanks again for the feedback.
>
> However, I don't see how it can happend that the
> statement
> is completed internally without returning SQLITE_DONE.
> In the particular code of the "reading thread" I
> do something like:
>
> --
> sqlite3_prepare_v2(db, "SELECT * FROM TableA",
> -1, &stmt, 0);
> /** step throu table result **/
> do
> {
>rc = sqlite3_step(stmt);
>if( rc == SQLITE_MISUSE )
>{ fprintf(stderr, "ERROR...\n"); break; }
>else
>if( rc == SQLITE_ROW )
> read_data_etc();
>else
> break;
> }while( rc != SQLITE_DONE );
>
> sqlite3_finalize(stmt);
> --
>
> The prepare statement pointer is defined locally and no
> other
> thread can access it, except sqlite internal maybe.
> To me it looks that in case a parallel thread is inserting
> or
> updating data, the above loop is somehow affected and
> returns the
> MISUSE.
>
> Your reply so far indicates either a bug on my side or a
> missusage.
> I'll go on and try to find the reason why the magic
> number is resetted
> without knowing from calling functions.
>
> calling sqlite3_reset in these cases is difficult for me
> since it might
> not be the first step and previous data is allready in use
> in the upper loop.
> But maybe an option for the very first step if that
> fails... hm... I'll try that.
>
>
> Thanks again
>
> kind regards
>
> Marcus Grimm
>
>
>
> D. Richard Hipp wrote:
> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
> >
> >> hi,
> >>
> >> OK, the value of p->magic is 519C2973
> (VDBE_MAGIC_HALT)
> >
> > That means the prepared statement has run to
> completion and needs to
> > be reset using sqlite3_reset() before you continue.
> >
> > 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 datbase disk image malformed
Hi Dan, On Mar 4, 2009, at 9:21 AM, Dan wrote: > > On Mar 5, 2009, at 12:10 AM, Tito Ciuro wrote: > >> Hello, >> >> On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote: >> >>> See http://www.sqlite.org/atomiccommit.html and especially section >>> 9.0 >>> "Things That Can Go Wrong" >> >> Reading the above link, I'm curious about a specific case: 4.2 Hot >> Rollback Journals. It states that: >> >> [...] >> The first time that any SQLite process attempts to access the >> database >> file, it obtains a shared lock as described in section 3.2 above. But >> then it notices that there is a rollback journal file present. SQLite >> then checks to see if the rollback journal is a "hot journal". >> [...] >> >> SQLite's documentation in http://www.sqlite.org/lockingv3.html states >> the following about a shared lock: >> >> [...] >> The database may be read but not written. Any number of processes can >> hold SHARED locks at the same time, hence there can be many >> simultaneous readers. But no other thread or process is allowed to >> write to the database file while one or more SHARED locks are active. >> [...] >> >> So, if when SQLite attempts to access the data file for the first >> time >> (thread T1) and obtains a shared lock, it seems that there's a window >> of opportunity for secondary thread (T2) to obtain another shared >> lock. In this case, T1 would not obtain an exclusive lock until T2 >> has >> completed reading. This would potentially leave T2 with damaged/ >> inconsistent data. Once T2's shared lock was relinquished, T1 would >> proceed to rollback the hot journal. >> >> Shouldn't the first connection obtain an exclusive lock right away >> instead and then perform the testing for the existence of a hot >> journal? I'm probably mistaken, but this is what I gather from the >> documentation mentioned above. > > After obtaining a shared-lock, SQLite tests for the existence of > a hot-journal file. The test for a hot-journal file is that the > journal file exists and that no other connection holds a RESERVED > or PENDING lock on the database file. We know no other process > is holding an EXCLUSIVE lock on the database file, since we are > holding a SHARED lock. > > If it determines that there is a hot-journal file in the file system, > SQLite obtains an EXCLUSIVE lock on the database file. It does > not obtain a RESERVED or PENDING lock first like it does normally, > but jumps straight to EXCLUSIVE. > > If the EXCLUSIVE lock is obtained Ok, roll back the journal file. > If not, then release all locks and return SQLITE_BUSY. If the > EXCLUSIVE lock cannot be obtained, then some other process must > have obtained a SHARED lock. The other process will also try to > roll back the hot-journal. By releasing all locks, hopefully > we can get out of the other processes way fast enough to allow it > to obtain the EXCLUSIVE lock and roll back the journal file. > > The key is that at no point is it possible for a second process > to conclude that the database is valid when there is really a > hot-journal file that requires rollback in the file-system. If > there are multiple clients all trying to access the database at > once then a few operations might return SQLITE_BUSY, but eventually > one of the clients will successfully obtain the EXCLUSIVE lock > and roll back the hot-journal. > > Dan. Makes perfect sense (especially the last paragraph). Thanks for taking the time to write a detailed explanation. Regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 datbase disk image malformed
On Mar 5, 2009, at 12:10 AM, Tito Ciuro wrote: > Hello, > > On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote: > >> See http://www.sqlite.org/atomiccommit.html and especially section >> 9.0 >> "Things That Can Go Wrong" > > Reading the above link, I'm curious about a specific case: 4.2 Hot > Rollback Journals. It states that: > > [...] > The first time that any SQLite process attempts to access the database > file, it obtains a shared lock as described in section 3.2 above. But > then it notices that there is a rollback journal file present. SQLite > then checks to see if the rollback journal is a "hot journal". > [...] > > SQLite's documentation in http://www.sqlite.org/lockingv3.html states > the following about a shared lock: > > [...] > The database may be read but not written. Any number of processes can > hold SHARED locks at the same time, hence there can be many > simultaneous readers. But no other thread or process is allowed to > write to the database file while one or more SHARED locks are active. > [...] > > So, if when SQLite attempts to access the data file for the first time > (thread T1) and obtains a shared lock, it seems that there's a window > of opportunity for secondary thread (T2) to obtain another shared > lock. In this case, T1 would not obtain an exclusive lock until T2 has > completed reading. This would potentially leave T2 with damaged/ > inconsistent data. Once T2's shared lock was relinquished, T1 would > proceed to rollback the hot journal. > > Shouldn't the first connection obtain an exclusive lock right away > instead and then perform the testing for the existence of a hot > journal? I'm probably mistaken, but this is what I gather from the > documentation mentioned above. After obtaining a shared-lock, SQLite tests for the existence of a hot-journal file. The test for a hot-journal file is that the journal file exists and that no other connection holds a RESERVED or PENDING lock on the database file. We know no other process is holding an EXCLUSIVE lock on the database file, since we are holding a SHARED lock. If it determines that there is a hot-journal file in the file system, SQLite obtains an EXCLUSIVE lock on the database file. It does not obtain a RESERVED or PENDING lock first like it does normally, but jumps straight to EXCLUSIVE. If the EXCLUSIVE lock is obtained Ok, roll back the journal file. If not, then release all locks and return SQLITE_BUSY. If the EXCLUSIVE lock cannot be obtained, then some other process must have obtained a SHARED lock. The other process will also try to roll back the hot-journal. By releasing all locks, hopefully we can get out of the other processes way fast enough to allow it to obtain the EXCLUSIVE lock and roll back the journal file. The key is that at no point is it possible for a second process to conclude that the database is valid when there is really a hot-journal file that requires rollback in the file-system. If there are multiple clients all trying to access the database at once then a few operations might return SQLITE_BUSY, but eventually one of the clients will successfully obtain the EXCLUSIVE lock and roll back the hot-journal. Dan. > Regards, > > -- Tito > ___ > 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] sqlite3 datbase disk image malformed
Hello, On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote: > See http://www.sqlite.org/atomiccommit.html and especially section 9.0 > "Things That Can Go Wrong" Reading the above link, I'm curious about a specific case: 4.2 Hot Rollback Journals. It states that: [...] The first time that any SQLite process attempts to access the database file, it obtains a shared lock as described in section 3.2 above. But then it notices that there is a rollback journal file present. SQLite then checks to see if the rollback journal is a "hot journal". [...] SQLite's documentation in http://www.sqlite.org/lockingv3.html states the following about a shared lock: [...] The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active. [...] So, if when SQLite attempts to access the data file for the first time (thread T1) and obtains a shared lock, it seems that there's a window of opportunity for secondary thread (T2) to obtain another shared lock. In this case, T1 would not obtain an exclusive lock until T2 has completed reading. This would potentially leave T2 with damaged/ inconsistent data. Once T2's shared lock was relinquished, T1 would proceed to rollback the hot journal. Shouldn't the first connection obtain an exclusive lock right away instead and then perform the testing for the existence of a hot journal? I'm probably mistaken, but this is what I gather from the documentation mentioned above. Regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
Richard, thanks again for the feedback.
However, I don't see how it can happend that the statement
is completed internally without returning SQLITE_DONE.
In the particular code of the "reading thread" I do something like:
--
sqlite3_prepare_v2(db, "SELECT * FROM TableA", -1, &stmt, 0);
/** step throu table result **/
do
{
rc = sqlite3_step(stmt);
if( rc == SQLITE_MISUSE )
{ fprintf(stderr, "ERROR...\n"); break; }
else
if( rc == SQLITE_ROW )
read_data_etc();
else
break;
}while( rc != SQLITE_DONE );
sqlite3_finalize(stmt);
--
The prepare statement pointer is defined locally and no other
thread can access it, except sqlite internal maybe.
To me it looks that in case a parallel thread is inserting or
updating data, the above loop is somehow affected and returns the
MISUSE.
Your reply so far indicates either a bug on my side or a missusage.
I'll go on and try to find the reason why the magic number is resetted
without knowing from calling functions.
calling sqlite3_reset in these cases is difficult for me since it might
not be the first step and previous data is allready in use in the upper loop.
But maybe an option for the very first step if that fails... hm... I'll try
that.
Thanks again
kind regards
Marcus Grimm
D. Richard Hipp wrote:
> On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
>
>> hi,
>>
>> OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT)
>
> That means the prepared statement has run to completion and needs to
> be reset using sqlite3_reset() before you continue.
>
> 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] Bug in SQLITE? "Joins + Order By" Changing row count!?!
Create an sql dump using the ".dump" command of the sqlite3 shell tool: $ echo .dump | sqlite3 database_file.db > dump.sql Or just put the database file up for download somewhere. Or if you prefer, send it to me by email. If this bug is present in current versions, we need to fix it. But it's much more difficult to figure out if it is still present or not without a database to run your queries against. Thanks, Dan. On Mar 4, 2009, at 9:17 PM, Jonathon wrote: > Thanks Dan for the reply, > > How would I go about creating a sql dump? > > As for the ORDER BY clause, I do this: > > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; > > Although a.some_other_value is not displayed in the tableA below, I > forgot > to add it. So, I add the ORDER BY clause on a.some_other_value and it > returns a ton of records (mostly duplicates). I am pretty sure this > is a > bug because if I wrap this query inside of a subquery and perform > the ORDER > BY on the subquery, everything works. > > This is what WORKS: > > SELECT * FROM ( > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; > > So for now, I am just leaving my original query inside the subquery > to fix > it. However, I wouldn't mind posting some debugging information if > it will > help fix the bug. > > Thanks, > J > > > On Tue, Mar 3, 2009 at 8:23 PM, Dan wrote: > >> >> On Mar 4, 2009, at 4:41 AM, Jonathon wrote: >> >>> Hello, >>> >>> I am executing a query such as: >> >> Can you post a database (or sql dump thereof) to run your queries >> against? Also say exactly what ORDER BY clause you are adding to >> the end of the query that causes it to return incorrect results? >> >> Thanks, >> Dan. >> >> >> >>> 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c >>> INNER >>> JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id >>> AND >>> a.d_id = d.id AND c.e_id = e.id; >>> 2. >>> 3. CREATE TABLE tableA ( >>> 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 5. b_idINTEGER NOT NULL, >>> 6. c_idINTEGER NOT NULL, >>> 7. d_idINTEGER NOT NULL >>> 8. ) >>> 9. >>> 10. CREATE TABLE tableB ( >>> 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 12. ); >>> 13. >>> 14. CREATE TABLE tableC ( >>> 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 16. e_idINTEGER NOT NULL >>> 17. ); >>> 18. >>> 19. CREATE TABLE tableD ( >>> 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 21. ); >>> 22. >>> 23. CREATE TABLE tableE ( >>> 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 25. ); >>> >>> >>> This seems to be returning the correct records. However, when I >>> append an >>> ORDER BY at the end of the query, it seems to be changing the number >>> of >>> records that are returned back to me. From my understanding, an >>> ORDER BY >>> can not change the number of records correct? If I do not use the >>> ORDER BY, >>> I get around 150 records. If I do an ORDER BY on any column that >>> is not in >>> tableA, the number of records blows up (~4k) and there are >>> duplicates. >>> >>> Any ideas? >>> >>> I thought that it was because of a buggy parser, so I wrote the >>> query again >>> as: >>> >>> >>> 1. SELECT * FROM tableA a >>> 2. INNER JOIN tableB b >>> 3. ON a.b_id = b.id >>> 4. INNER JOIN tableD d >>> 5. ON a.d_id = d.id >>> 6. INNER JOIN tableC c >>> 7. LEFT JOIN tableE e ON c.e_id = e.id >>> 8. ON ON a.c_id = c.id >>> >>> and it still gave me the same results... >>> >>> Thanks, >>> J >>> ___ >>> 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
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote: > hi, > > OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT) That means the prepared statement has run to completion and needs to be reset using sqlite3_reset() before you continue. 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] Slow performance with Sum function
Hello! On Wednesday 04 March 2009 17:19:09 Jim Wilcoxson wrote: > Have you tried changing the page size to 4096 or 8192? Doing this > with my SQLite application and increasing the transaction size > decreased runtime from over 4 hours to 75 minutes. The runtime for > my app writing the same amount of data to flat files was 55 minutes, > so the time penalty for building a database was about 35%, which > seemed reasonable. > > I haven't tried changing the cache size yet, because I like that my > app uses a small amount of memory. I have my own build of SQLite with default page size 4096 and increased caches for server applications. For huge databases and SAS disks i'm use page size 16384. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
hi,
OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT)
that should mean that "VDBE has completed execution"...
I don't know... in that case I should get a SQLITE_DONE
when stepping throu the result set, right ?
Just some additional info:
It is the last sqlite version, threadsafe is true and all
db connections are opened using SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX in
the flags
for sqlite3_open_v2, it is a dual core PC.
kind regards
Marcus
D. Richard Hipp wrote:
> On Mar 4, 2009, at 8:31 AM, Marcus Grimm wrote:
>
>> Richard,
>> Thanks for looking into this.
>>
>> I've placed some debug output in the sqlite2_step function
>> and I found that it returns SQLITE_MISUSE here:
>>
>> --
>> static int sqlite3Step(Vdbe *p){
>> sqlite3 *db;
>> int rc;
>>
>> assert(p);
>> if( p->magic!=VDBE_MAGIC_RUN )
>> {
>> return SQLITE_MISUSE;
>> }
>> --
>
> What is the value of p->magic at the point of failure? (In hex, please)
>
>>
>> I have no idea what this MAGIC_RUN means.
>>
>> Does it indicate that infact I'm using an allready released
>> statement ?
>>
>> Please note that I'm not able to reproduce the problem when
>> I switch the shared cache off.
>>
>> Thank you
>>
>> Kind regards
>>
>> Marcus Grimm
>>
>>
>> D. Richard Hipp wrote:
>>> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
>>>
Hi all,
I'm doing a little stress test on a server application and run into
a problem when two threads are trying to access the database.
Here is the background:
1. shared cache is enabled prior open any DB connection.
2. Each thread then opens a DB connection.
3. Thread A just reads table entries continuosly by
doing sqlite3_prepare_v2 and followed by some sqlite3_step to
parse the result set.
He then uses sqlite3_finalize and after a few ms he repeats
everything.
4. Thread B is triggered to update or insert some a new values
in some tables.
To do so I obtain an EXCLUSIVE transaction, do the insert/update
and COMIT.
Now, a problem arises occasionally that thread A gets an
SQLITE_MISUSE when
trying to call sqlite3_step, most likely because thread B currently
writes into
the DB, I guess.
Now, my question:
How to handle the SQLITE_MISUSE ?
>>> My guess is that the SQLITE_MISUSE is being returned because you are
>>> calling sqlite3_step() with a statement that has already been
>>> destroyed by sqlite3_finalize().
>>>
>>>
>>> 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
>
> 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] Slow performance with Sum function
Have you tried changing the page size to 4096 or 8192? Doing this with my SQLite application and increasing the transaction size decreased runtime from over 4 hours to 75 minutes.The runtime for my app writing the same amount of data to flat files was 55 minutes, so the time penalty for building a database was about 35%, which seemed reasonable. I haven't tried changing the cache size yet, because I like that my app uses a small amount of memory. Good luck! Jim On 3/4/09, Alexey Pechnikov wrote: > Can enough cache size prevent fragmentation? And how to calculate degree of > fragmentation and when is needed make vacuum of database? > > Best regards. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE? "Joins + Order By" Changing row count!?!
Thanks Dan for the reply, How would I go about creating a sql dump? As for the ORDER BY clause, I do this: SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; Although a.some_other_value is not displayed in the tableA below, I forgot to add it. So, I add the ORDER BY clause on a.some_other_value and it returns a ton of records (mostly duplicates). I am pretty sure this is a bug because if I wrap this query inside of a subquery and perform the ORDER BY on the subquery, everything works. This is what WORKS: SELECT * FROM ( SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; So for now, I am just leaving my original query inside the subquery to fix it. However, I wouldn't mind posting some debugging information if it will help fix the bug. Thanks, J On Tue, Mar 3, 2009 at 8:23 PM, Dan wrote: > > On Mar 4, 2009, at 4:41 AM, Jonathon wrote: > > > Hello, > > > > I am executing a query such as: > > Can you post a database (or sql dump thereof) to run your queries > against? Also say exactly what ORDER BY clause you are adding to > the end of the query that causes it to return incorrect results? > > Thanks, > Dan. > > > > > 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c > > INNER > > JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id > > AND > > a.d_id = d.id AND c.e_id = e.id; > > 2. > > 3. CREATE TABLE tableA ( > > 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 5. b_idINTEGER NOT NULL, > > 6. c_idINTEGER NOT NULL, > > 7. d_idINTEGER NOT NULL > > 8. ) > > 9. > > 10. CREATE TABLE tableB ( > > 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 12. ); > > 13. > > 14. CREATE TABLE tableC ( > > 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 16. e_idINTEGER NOT NULL > > 17. ); > > 18. > > 19. CREATE TABLE tableD ( > > 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 21. ); > > 22. > > 23. CREATE TABLE tableE ( > > 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 25. ); > > > > > > This seems to be returning the correct records. However, when I > > append an > > ORDER BY at the end of the query, it seems to be changing the number > > of > > records that are returned back to me. From my understanding, an > > ORDER BY > > can not change the number of records correct? If I do not use the > > ORDER BY, > > I get around 150 records. If I do an ORDER BY on any column that > > is not in > > tableA, the number of records blows up (~4k) and there are duplicates. > > > > Any ideas? > > > > I thought that it was because of a buggy parser, so I wrote the > > query again > > as: > > > > > > 1. SELECT * FROM tableA a > > 2. INNER JOIN tableB b > > 3. ON a.b_id = b.id > > 4. INNER JOIN tableD d > > 5. ON a.d_id = d.id > > 6. INNER JOIN tableC c > > 7. LEFT JOIN tableE e ON c.e_id = e.id > > 8. ON ON a.c_id = c.id > > > > and it still gave me the same results... > > > > Thanks, > > J > > ___ > > 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
Re: [sqlite] sqlite3 datbase disk image malformed
On Mar 4, 2009, at 7:41 AM, donnied wrote: > > I have a very small sql database (132K). I will often get error > messages > that the Disk image is malformed. I've looked over possible causes > cited > elsewhere and didn't see anything pertinent. I'm using an ext3 file > system > with 64 bit Debian. The files are created with Python (first one > script for > three tables and then two more scripts one additional table each). > Everything seems fine and then when I come back to the database > later I get > a message that the disk image is malformed. I'll create a cron job to > monitor the database with "pragma integrity_check" but I'm not sure > what > else to check. See http://www.sqlite.org/atomiccommit.html and especially section 9.0 "Things That Can Go Wrong" 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] shared cache and SQLITE_MISUSE on sqlite3_step()
On Mar 4, 2009, at 8:31 AM, Marcus Grimm wrote:
> Richard,
> Thanks for looking into this.
>
> I've placed some debug output in the sqlite2_step function
> and I found that it returns SQLITE_MISUSE here:
>
> --
> static int sqlite3Step(Vdbe *p){
> sqlite3 *db;
> int rc;
>
> assert(p);
> if( p->magic!=VDBE_MAGIC_RUN )
> {
> return SQLITE_MISUSE;
> }
> --
What is the value of p->magic at the point of failure? (In hex, please)
>
>
> I have no idea what this MAGIC_RUN means.
>
> Does it indicate that infact I'm using an allready released
> statement ?
>
> Please note that I'm not able to reproduce the problem when
> I switch the shared cache off.
>
> Thank you
>
> Kind regards
>
> Marcus Grimm
>
>
> D. Richard Hipp wrote:
>> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
>>
>>> Hi all,
>>>
>>> I'm doing a little stress test on a server application and run into
>>> a problem when two threads are trying to access the database.
>>> Here is the background:
>>> 1. shared cache is enabled prior open any DB connection.
>>> 2. Each thread then opens a DB connection.
>>> 3. Thread A just reads table entries continuosly by
>>> doing sqlite3_prepare_v2 and followed by some sqlite3_step to
>>> parse the result set.
>>> He then uses sqlite3_finalize and after a few ms he repeats
>>> everything.
>>> 4. Thread B is triggered to update or insert some a new values
>>> in some tables.
>>> To do so I obtain an EXCLUSIVE transaction, do the insert/update
>>> and COMIT.
>>>
>>> Now, a problem arises occasionally that thread A gets an
>>> SQLITE_MISUSE when
>>> trying to call sqlite3_step, most likely because thread B currently
>>> writes into
>>> the DB, I guess.
>>>
>>> Now, my question:
>>>
>>> How to handle the SQLITE_MISUSE ?
>>
>> My guess is that the SQLITE_MISUSE is being returned because you are
>> calling sqlite3_step() with a statement that has already been
>> destroyed by sqlite3_finalize().
>>
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
> Tel: +49(0)6151-95147-10
> Fax: +49(0)6151-95147-20
> --
> MedCom slogans of the month:
> "Vacation ? -- Every day at MedCom is a paid vacation!"
> "Friday I have monday in my mind."
> "MedCom -- Every week a vacation, every day an event, every hour a
> cliffhanger,
>every minute a climax."
> "Damned, it's weekend again!"
> ___
> 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] core dump with TKSQLite
Hello! On Wednesday 04 March 2009 15:38:39 rahed wrote: > I doubt. To my acknowledge tksqlite is a tcl/tk script run from shell. The > source doesn't distinguish the platform. tksqlite.tcl is tcl script but you did write about tksqlite. tksqlite is binary file with tcl interp and virtual file system consists a lot of tcl modules and compiled libraries. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
Richard,
Thanks for looking into this.
I've placed some debug output in the sqlite2_step function
and I found that it returns SQLITE_MISUSE here:
--
static int sqlite3Step(Vdbe *p){
sqlite3 *db;
int rc;
assert(p);
if( p->magic!=VDBE_MAGIC_RUN )
{
return SQLITE_MISUSE;
}
--
I have no idea what this MAGIC_RUN means.
Does it indicate that infact I'm using an allready released statement ?
Please note that I'm not able to reproduce the problem when
I switch the shared cache off.
Thank you
Kind regards
Marcus Grimm
D. Richard Hipp wrote:
> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
>
>> Hi all,
>>
>> I'm doing a little stress test on a server application and run into
>> a problem when two threads are trying to access the database.
>> Here is the background:
>> 1. shared cache is enabled prior open any DB connection.
>> 2. Each thread then opens a DB connection.
>> 3. Thread A just reads table entries continuosly by
>>doing sqlite3_prepare_v2 and followed by some sqlite3_step to
>> parse the result set.
>>He then uses sqlite3_finalize and after a few ms he repeats
>> everything.
>> 4. Thread B is triggered to update or insert some a new values
>>in some tables.
>>To do so I obtain an EXCLUSIVE transaction, do the insert/update
>> and COMIT.
>>
>> Now, a problem arises occasionally that thread A gets an
>> SQLITE_MISUSE when
>> trying to call sqlite3_step, most likely because thread B currently
>> writes into
>> the DB, I guess.
>>
>> Now, my question:
>>
>> How to handle the SQLITE_MISUSE ?
>
> My guess is that the SQLITE_MISUSE is being returned because you are
> calling sqlite3_step() with a statement that has already been
> destroyed by sqlite3_finalize().
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
--
MedCom slogans of the month:
"Vacation ? -- Every day at MedCom is a paid vacation!"
"Friday I have monday in my mind."
"MedCom -- Every week a vacation, every day an event, every hour a cliffhanger,
every minute a climax."
"Damned, it's weekend again!"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 datbase disk image malformed
I have a very small sql database (132K). I will often get error messages that the Disk image is malformed. I've looked over possible causes cited elsewhere and didn't see anything pertinent. I'm using an ext3 file system with 64 bit Debian. The files are created with Python (first one script for three tables and then two more scripts one additional table each). Everything seems fine and then when I come back to the database later I get a message that the disk image is malformed. I'll create a cron job to monitor the database with "pragma integrity_check" but I'm not sure what else to check. -- View this message in context: http://www.nabble.com/sqlite3-datbase-disk-image-malformed-tp22329261p22329261.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] core dump with TKSQLite
Alexey Pechnikov writes: > TkSQLite has it's own copies of SQLite2 and SQLite3 inside. I think you are > download TkSQLite for different platform. I doubt. To my acknowledge tksqlite is a tcl/tk script run from shell. The source doesn't distinguish the platform. I compiled all necessary libraries and tksqlite runs ok. The author of TkSQLite referred my problem to http://www.sqlite.org/cvstrac/tktview?tn=3553,22. -- Radek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is UPDATE with JOIN supported?
I am sorry, it is already solved. Finally I used the following syntax: UPDATE suppliers SET supplier_name =( SELECT customers.name FROM customers WHERE customers.customer_id =suppliers.supplier_id) Which works perfectly, needless to say. It was due to my SQL knowledge ignorance. P. On Wed, Mar 4, 2009 at 1:31 PM, Brad Stiles wrote: >> I am trying to do an UPDATE of one table based on the aggregate >> results of the different table. How can I do it in SQlite please? > > What have you tried that didn't work? > > /bs > ___ > 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] core dump with TKSQLite
Alexey Pechnikov writes: > Hello! > TkSQLite has it's own copies of SQLite2 and SQLite3 inside. I think you are > download TkSQLite for different platform. I doubt. To my acknowledge tksqlite is a tcl/tk script run from shell. The source doesn't distinguish the platform. I compiled all necessary libraries and tksqlite runs ok. The author of TkSQLite referred my problem to http://www.sqlite.org/cvstrac/tktview?tn=3553,22. -- Radek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is UPDATE with JOIN supported?
> I am trying to do an UPDATE of one table based on the aggregate > results of the different table. How can I do it in SQlite please? What have you tried that didn't work? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote: > Hi all, > > I'm doing a little stress test on a server application and run into > a problem when two threads are trying to access the database. > Here is the background: > 1. shared cache is enabled prior open any DB connection. > 2. Each thread then opens a DB connection. > 3. Thread A just reads table entries continuosly by >doing sqlite3_prepare_v2 and followed by some sqlite3_step to > parse the result set. >He then uses sqlite3_finalize and after a few ms he repeats > everything. > 4. Thread B is triggered to update or insert some a new values >in some tables. >To do so I obtain an EXCLUSIVE transaction, do the insert/update > and COMIT. > > Now, a problem arises occasionally that thread A gets an > SQLITE_MISUSE when > trying to call sqlite3_step, most likely because thread B currently > writes into > the DB, I guess. > > Now, my question: > > How to handle the SQLITE_MISUSE ? My guess is that the SQLITE_MISUSE is being returned because you are calling sqlite3_step() with a statement that has already been destroyed by sqlite3_finalize(). 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] WHERE clause analysis - find referenced columns
Hello list, I need to implement a security model where certain columns (e.g. named super_secret_key) will be hidden. This needs to be enforced not only for the requested result columns but also for the columns referenced in the WHERE clause. Is there a relatively simple way to either get each column referenced in the WHERE clause or build a query that would check if the WHERE clause (I could place it in a temporary table) contains the super_secret_key as a column name, but not as a value. Thank you, Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does anybody have a working version of php_sqlite3.dll?
Hello everyone. I hope I'm not violating any disclaimer posting this message. A few days ago I had to migrate my project from linux to windows. It is using the php sqlite3 extension, to access sqlite3 database files. Problem is, I can't find the extension dll (php_sqlite3.dll). I found one on a chinese website. It works but it is crashing on some very simple queries (SELECT * FROM table WHERE 1). I don't care which version of php5 I have to use as long as I can get that damn extension to work. I was used to finding my binaries on pecl4win, but since it's dead, I'm lost. Can somebody help me? PS: I don't want to use pdo to access my db files. -- Erwan MARTIN Président de l'association REZID Secrétaire de l'association APDVM +33632011501 pub...@fzwte.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
Hi all, I'm doing a little stress test on a server application and run into a problem when two threads are trying to access the database. Here is the background: 1. shared cache is enabled prior open any DB connection. 2. Each thread then opens a DB connection. 3. Thread A just reads table entries continuosly by doing sqlite3_prepare_v2 and followed by some sqlite3_step to parse the result set. He then uses sqlite3_finalize and after a few ms he repeats everything. 4. Thread B is triggered to update or insert some a new values in some tables. To do so I obtain an EXCLUSIVE transaction, do the insert/update and COMIT. Now, a problem arises occasionally that thread A gets an SQLITE_MISUSE when trying to call sqlite3_step, most likely because thread B currently writes into the DB, I guess. Now, my question: How to handle the SQLITE_MISUSE ? I'm prepared to deal with SQLITE_BUSY or SQLITE_LOCKED but I don't know what to do when a sqlite3_step returns the SQLITE_MISUSE error. Without the shared_cache enabled I don't get SQLITE_MISUSE but the usual busy or locked states and I can just wait until the locking dissapeared. Is it recommended to use additionally the "PRAGMA read_uncommitted = True;" ? Maybe I should use an exclusive transaction on the simple "SELECT * FROM table" calls but that will block any parallel reads, not very useful. Actually, I don't understand why I don't get a LOCK state in shared cache mode as well, although I think it is by design. thank you Marcus Grimm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Hello! On Wednesday 04 March 2009 04:44:05 D. Richard Hipp wrote: > One could envision future versions > of SQLite that allowed you to preallocate a large database files such > that the database always stayed less than 80% full. Then we could use > filesystem techniques to keep fragmentation down. The penalty, of > course, is that your database file is larger. Probably much larger. > And just to be clear: SQLite does not have that capability at this time. Can enough cache size prevent fragmentation? And how to calculate degree of fragmentation and when is needed make vacuum of database? Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] core dump with TKSQLite
Hello! On Tuesday 03 March 2009 17:04:32 rahed wrote: > I installed 3.6.11 on Solaris with 3_6_11-tea. > When I try to open sqlite db with tksqlite there is core dump: > > warning: core file may not match specified executable file. > Core was generated by `wish /export/home/user1/tksqlite'. > Program terminated with signal 10, Bus error. > [New process 79314 ] > #0 0xfe4b4b0c in ?? () > > sqlite from its command line is ok. > > Any hints? TkSQLite has it's own copies of SQLite2 and SQLite3 inside. I think you are download TkSQLite for different platform. TkSQLite is starpack and you can repack it by using sdx utility. But a lot of binaries must be replaced for create Solaris version from linux version. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is UPDATE with JOIN supported?
Hello, I am trying to do an UPDATE of one table based on the aggregate results of the different table. How can I do it in SQlite please? Background problem: Table1 contains many different values of different file names. I've created view (table2) which provides me information how many file names of the same name are included (SELECT + COUNT+GROUP BY FILENAME) and now I am trying to put this information back to the original table to the particular column via UPDATE statement. Something like: UPDATE table1 SET duplicates=table2.count FROM table2 WHERE table1.filename=table2.filename How can I do it with Sqlite, please? BTW: I don't want to do it via two selects+join because this operation will be preformed many times and so I would like to be able to update one particular column only once. Many thanks. Petr ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does anybody have a working version of php_sqlite3.dll?
On Wed, Mar 04, 2009 at 09:05:34AM +0100, ZeWaren / Erwan Martin wrote: > > A few days ago I had to migrate my project from linux to windows. It is > using the php sqlite3 extension, to access sqlite3 database files. Use the "external" version of the PDO-sqlite and use the normal DLL from the sqlite-homepage. Put it in the normal PHP-directory, parallel to the php-DLLs like php5isapi.dll. That works for me. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does anybody have a working version of php_sqlite3.dll?
Hello everyone. I hope I'm not violating any disclaimer posting this message. A few days ago I had to migrate my project from linux to windows. It is using the php sqlite3 extension, to access sqlite3 database files. Problem is, I can't find the extension dll (php_sqlite3.dll). I found one on a chinese website. It works but it is crashing on some very simple queries (SELECT * FROM table WHERE 1). I don't care which version of php5 I have to use as long as I can get that damn extension to work. I was used to finding my binaries on pecl4win, but since it's dead, I'm lost. Can somebody help me? PS: I don't want to use pdo to access my db files. -- Erwan MARTIN Président de l'association REZID Secrétaire de l'association APDVM +33632011501 pub...@fzwte.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
