Re: [sqlite] problems with shared cache?

[Damien Elmes]

I can define the primary key column as not null if you think that will
help, but dumping the table reveals the ids are being assigned
sequential integers.

On Tue, Mar 24, 2009 at 11:34 PM, Jim Wilcoxson  wrote:
> Not sure if it will make a difference, but in your trigger stuff you
> explicitly coded null for the primary key value.  Have you tried
> changing that so that you don't specify the primary key field at all?
> I can't remember from the previous post, but I think it was (or should
> be) set up as autoincrement.
>
> I think SQLite allows using multiple nulls for the primary key, but
> according to their docs, it is non-standard and it says something
> about "this may change in the future".  Maybe you are getting caught
> in the middle of a change that is going to occur across multiple
> revisions of SQLite.
>
> Jim
>
>
> On 3/24/09, Damien Elmes  wrote:
>> Sorry, my application's files are called decks, and I unwittingly used
>> the wrong terminology.
>>
>> Any ideas about the problem?
>>
>> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald
>>  wrote:
>>>
 However, when I ask the user to send me their deck, I find that:

 sqlite> pragma integrity_check;
 integrity_check
 ---
 ok
 sqlite> select id, count(id) from cards group by id having
 count(id)
> 1;
 sqlite>

 Any ideas?
>>>
>>> Obviously, that user is not playing with a full deck.   ;-)
>>>
>>>
>>> ___
>>> 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
>>
>
>
> --
> Software first.  Software lasts!
> ___
> 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] problems with shared cache?

[Damien Elmes]

No, it's a flashcard application. http://ichi2.net/anki/

On Wed, Mar 25, 2009 at 9:30 AM, John Machin <sjmac...@lexicon.net> wrote:
> On 25/03/2009 1:16 AM, Griggs, Donald wrote:
>>
>>
>> -Original Message-
>>
>>
>> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald 
>> <donald.gri...@allscripts.com> wrote:
>>>>>>>> However, when I ask the user to send me their deck, I find that:
>>>>>>>>
>>>>>>>> sqlite> pragma integrity_check;
>>>>>>>> integrity_check
>>>>>>>> ---
>>>>>>>> ok
>>>>>>>> sqlite> select id, count(id) from cards group by id having
>>>>>>>> count(id)
>>>>>>>>> 1;
>>>>>>>> sqlite>
>>>>>>>>
>>>>>>>> Any ideas?
>>> Obviously, that user is not playing with a full deck.   ;-)
>>>
>>
>> =
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes
>> Sent: Tuesday, March 24, 2009 2:52 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] problems with shared cache?
>>
>> Sorry, my application's files are called decks, and I unwittingly used the 
>> wrong terminology.
>>
>> Any ideas about the problem?
>> 
>>
>> Damien,
>>
>> I, if anyone, should apologize -- was just an attempt at humor using an 
>> English language idiom metaphor for card games.
>
> Presumably the terminology "decks" refers to decks of punched cards. It
> might have been more pertinent to remark on the presumed longevity of
> the application.
>
>
>
> ___
> 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] problems with shared cache?

[John Machin]

On 25/03/2009 1:16 AM, Griggs, Donald wrote:
>  
> 
> -Original Message-
> 
> 
> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald 
> <donald.gri...@allscripts.com> wrote:
>>>>>>> However, when I ask the user to send me their deck, I find that:
>>>>>>>
>>>>>>> sqlite> pragma integrity_check;
>>>>>>> integrity_check
>>>>>>> ---
>>>>>>> ok
>>>>>>> sqlite> select id, count(id) from cards group by id having
>>>>>>> count(id)
>>>>>>>> 1;
>>>>>>> sqlite>
>>>>>>>
>>>>>>> Any ideas?
>> Obviously, that user is not playing with a full deck.   ;-)
>>
> 
> =
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes
> Sent: Tuesday, March 24, 2009 2:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] problems with shared cache?
> 
> Sorry, my application's files are called decks, and I unwittingly used the 
> wrong terminology.
> 
> Any ideas about the problem?
> 
> 
> Damien,
> 
> I, if anyone, should apologize -- was just an attempt at humor using an 
> English language idiom metaphor for card games.

Presumably the terminology "decks" refers to decks of punched cards. It 
might have been more pertinent to remark on the presumed longevity of 
the application.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Jim Wilcoxson]

Not sure if it will make a difference, but in your trigger stuff you
explicitly coded null for the primary key value.  Have you tried
changing that so that you don't specify the primary key field at all?
I can't remember from the previous post, but I think it was (or should
be) set up as autoincrement.

I think SQLite allows using multiple nulls for the primary key, but
according to their docs, it is non-standard and it says something
about "this may change in the future".  Maybe you are getting caught
in the middle of a change that is going to occur across multiple
revisions of SQLite.

Jim


On 3/24/09, Damien Elmes  wrote:
> Sorry, my application's files are called decks, and I unwittingly used
> the wrong terminology.
>
> Any ideas about the problem?
>
> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald
>  wrote:
>>
>>> However, when I ask the user to send me their deck, I find that:
>>>
>>> sqlite> pragma integrity_check;
>>> integrity_check
>>> ---
>>> ok
>>> sqlite> select id, count(id) from cards group by id having
>>> count(id)
 1;
>>> sqlite>
>>>
>>> Any ideas?
>>
>> Obviously, that user is not playing with a full deck.   ;-)
>>
>>
>> ___
>> 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
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Griggs, Donald]

 

-Original Message-


On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald <donald.gri...@allscripts.com> 
wrote:
>>>>>
>>>>>> However, when I ask the user to send me their deck, I find that:
>>>>>>
>>>>>> sqlite> pragma integrity_check;
>>>>>> integrity_check
>>>>>> ---
>>>>>> ok
>>>>>> sqlite> select id, count(id) from cards group by id having
>>>>>> count(id)
>>>>>>> 1;
>>>>>> sqlite>
>>>>>>
>>>>>> Any ideas?
>
> Obviously, that user is not playing with a full deck.   ;-)
>

=============
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Damien Elmes
Sent: Tuesday, March 24, 2009 2:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] problems with shared cache?

Sorry, my application's files are called decks, and I unwittingly used the 
wrong terminology.

Any ideas about the problem?


Damien,

I, if anyone, should apologize -- was just an attempt at humor using an English 
language idiom metaphor for card games.

I'm afraid I don't know enough to help with your shared cache problem (posted 
19 March), sorry.  It's fortunate, I suppose, that your particular application 
didn't really need it.

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Damien Elmes]

Sorry, my application's files are called decks, and I unwittingly used
the wrong terminology.

Any ideas about the problem?

On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald
 wrote:
>
>> However, when I ask the user to send me their deck, I find that:
>>
>> sqlite> pragma integrity_check;
>> integrity_check
>> ---
>> ok
>> sqlite> select id, count(id) from cards group by id having
>> count(id)
>>> 1;
>> sqlite>
>>
>> Any ideas?
>
> Obviously, that user is not playing with a full deck.   ;-)
>
>
> ___
> 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] problems with shared cache?

[Griggs, Donald]

> However, when I ask the user to send me their deck, I find that:
>
> sqlite> pragma integrity_check;
> integrity_check
> ---
> ok
> sqlite> select id, count(id) from cards group by id having
> count(id)
>> 1;
> sqlite>
>
> Any ideas? 

Obviously, that user is not playing with a full deck.   ;-)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Damien Elmes]

Yes, but I always insert with null:

self.s.statement(
"create temporary table undoLog (seq integer primary key,
sql text)")

after insert on %(t)s begin
insert into undoLog values
(null, ...)

after update on %(t)s begin
insert into undoLog values (null, 'update %(t)s ..

etc


On Fri, Mar 20, 2009 at 8:16 PM, Dan  wrote:
>
> Does the "undolog" table, or whatever you're using in its place, have
> a primary key?
>
> On Mar 20, 2009, at 1:20 PM, Damien Elmes wrote:
>
>> Well, those 3 x the number of tables in the Db.
>>
>> On Fri, Mar 20, 2009 at 3:17 PM, Damien Elmes 
>> wrote:
>>> Yep - but only the three listed on this page:
>>>
>>> http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>>>
>>> So they shouldn't be modifying anything in the main DB, only the
>>> temporary DB.
>>>
>>> On Fri, Mar 20, 2009 at 3:13 PM, Dan  wrote:

 On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote:

> Unfortunately, a user has just reported the same primary key error
> message with shared cache disabled, although the freezing appears
> to
> have been fixed.
>
> However, when I ask the user to send me their deck, I find that:
>
> sqlite> pragma integrity_check;
> integrity_check
> ---
> ok
> sqlite> select id, count(id) from cards group by id having
> count(id)
>> 1;
> sqlite>
>
> Any ideas?

 Triggers?


>
>
> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes 
> wrote:
>> Hi all,
>>
>> Some of my users have been reporting strange database problems
>> recently, which seem to have gone away when I removed a call to
>> enable_shared_cache(). The problems were noticeable in at least
>> 3.6.1
>> and 3.6.11, when using databases of 30MB+, and doing large updates
>> using pysqlite.
>>
>> There were two distinct reported problems. One was that the
>> program
>> would just freeze, with no disk access and CPU usage, seemingly in
>> the
>> middle of a DB query, on Win32. I wasn't able to reproduce this on
>> Linux.
>>
>> The other problem was reported by both win32 and mac users, and
>> again
>> I wasn't able to reproduce it. It resulted in errors like this:
>>
>> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
>> must be unique 'update cards set isDue = 0 where type in (0,1,2)
>> and
>> priority = 0 and isDue = 1' {}
>>
>> .. which is strange, because the primary key on that table is
>> called
>> 'id' and is not affected by the update call.
>>
>> I also had some reports of DB corruption on OSX, but I'm not
>> sure if
>> that occurred since I upgrade to 3.6.11.
>>
>> One other hint is that while I'd been using shared cache mode
>> for at
>> least 6 months or more, these problems seem to have only surfaced
>> recently. I'm not sure if that's due to a change in the queries
>> I've
>> been doing, or the fact that I changed the cache size to a bigger
>> number, and changed the page size to 4096.
>>
>> Anyway, disabling the shared cache appears to have fixed the
>> problem,
>> and since my program is single threaded and has no need for the
>> shared
>> cache, it's not an issue for us anymore. But I thought it's worth
>> reporting. Have there been any other instances of problems with
>> the
>> shared cache mode?
>>
>> Cheers,
>>
>> Damien
>>
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Dan]

Does the "undolog" table, or whatever you're using in its place, have  
a primary key?

On Mar 20, 2009, at 1:20 PM, Damien Elmes wrote:

> Well, those 3 x the number of tables in the Db.
>
> On Fri, Mar 20, 2009 at 3:17 PM, Damien Elmes   
> wrote:
>> Yep - but only the three listed on this page:
>>
>> http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>>
>> So they shouldn't be modifying anything in the main DB, only the  
>> temporary DB.
>>
>> On Fri, Mar 20, 2009 at 3:13 PM, Dan  wrote:
>>>
>>> On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote:
>>>
 Unfortunately, a user has just reported the same primary key error
 message with shared cache disabled, although the freezing appears  
 to
 have been fixed.

 However, when I ask the user to send me their deck, I find that:

 sqlite> pragma integrity_check;
 integrity_check
 ---
 ok
 sqlite> select id, count(id) from cards group by id having  
 count(id)
> 1;
 sqlite>

 Any ideas?
>>>
>>> Triggers?
>>>
>>>


 On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes 
 wrote:
> Hi all,
>
> Some of my users have been reporting strange database problems
> recently, which seem to have gone away when I removed a call to
> enable_shared_cache(). The problems were noticeable in at least  
> 3.6.1
> and 3.6.11, when using databases of 30MB+, and doing large updates
> using pysqlite.
>
> There were two distinct reported problems. One was that the  
> program
> would just freeze, with no disk access and CPU usage, seemingly in
> the
> middle of a DB query, on Win32. I wasn't able to reproduce this on
> Linux.
>
> The other problem was reported by both win32 and mac users, and  
> again
> I wasn't able to reproduce it. It resulted in errors like this:
>
> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
> must be unique 'update cards set isDue = 0 where type in (0,1,2)  
> and
> priority = 0 and isDue = 1' {}
>
> .. which is strange, because the primary key on that table is  
> called
> 'id' and is not affected by the update call.
>
> I also had some reports of DB corruption on OSX, but I'm not  
> sure if
> that occurred since I upgrade to 3.6.11.
>
> One other hint is that while I'd been using shared cache mode  
> for at
> least 6 months or more, these problems seem to have only surfaced
> recently. I'm not sure if that's due to a change in the queries  
> I've
> been doing, or the fact that I changed the cache size to a bigger
> number, and changed the page size to 4096.
>
> Anyway, disabling the shared cache appears to have fixed the  
> problem,
> and since my program is single threaded and has no need for the
> shared
> cache, it's not an issue for us anymore. But I thought it's worth
> reporting. Have there been any other instances of problems with  
> the
> shared cache mode?
>
> Cheers,
>
> Damien
>
 ___
 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] problems with shared cache?

[Damien Elmes]

Well, those 3 x the number of tables in the Db.

On Fri, Mar 20, 2009 at 3:17 PM, Damien Elmes  wrote:
> Yep - but only the three listed on this page:
>
> http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>
> So they shouldn't be modifying anything in the main DB, only the temporary DB.
>
> On Fri, Mar 20, 2009 at 3:13 PM, Dan  wrote:
>>
>> On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote:
>>
>>> Unfortunately, a user has just reported the same primary key error
>>> message with shared cache disabled, although the freezing appears to
>>> have been fixed.
>>>
>>> However, when I ask the user to send me their deck, I find that:
>>>
>>> sqlite> pragma integrity_check;
>>> integrity_check
>>> ---
>>> ok
>>> sqlite> select id, count(id) from cards group by id having count(id)
>>> > 1;
>>> sqlite>
>>>
>>> Any ideas?
>>
>> Triggers?
>>
>>
>>>
>>>
>>> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes 
>>> wrote:
 Hi all,

 Some of my users have been reporting strange database problems
 recently, which seem to have gone away when I removed a call to
 enable_shared_cache(). The problems were noticeable in at least 3.6.1
 and 3.6.11, when using databases of 30MB+, and doing large updates
 using pysqlite.

 There were two distinct reported problems. One was that the program
 would just freeze, with no disk access and CPU usage, seemingly in
 the
 middle of a DB query, on Win32. I wasn't able to reproduce this on
 Linux.

 The other problem was reported by both win32 and mac users, and again
 I wasn't able to reproduce it. It resulted in errors like this:

 sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
 must be unique 'update cards set isDue = 0 where type in (0,1,2) and
 priority = 0 and isDue = 1' {}

 .. which is strange, because the primary key on that table is called
 'id' and is not affected by the update call.

 I also had some reports of DB corruption on OSX, but I'm not sure if
 that occurred since I upgrade to 3.6.11.

 One other hint is that while I'd been using shared cache mode for at
 least 6 months or more, these problems seem to have only surfaced
 recently. I'm not sure if that's due to a change in the queries I've
 been doing, or the fact that I changed the cache size to a bigger
 number, and changed the page size to 4096.

 Anyway, disabling the shared cache appears to have fixed the problem,
 and since my program is single threaded and has no need for the
 shared
 cache, it's not an issue for us anymore. But I thought it's worth
 reporting. Have there been any other instances of problems with the
 shared cache mode?

 Cheers,

 Damien

>>> ___
>>> 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] problems with shared cache?

[Damien Elmes]

Yep - but only the three listed on this page:

http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

So they shouldn't be modifying anything in the main DB, only the temporary DB.

On Fri, Mar 20, 2009 at 3:13 PM, Dan  wrote:
>
> On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote:
>
>> Unfortunately, a user has just reported the same primary key error
>> message with shared cache disabled, although the freezing appears to
>> have been fixed.
>>
>> However, when I ask the user to send me their deck, I find that:
>>
>> sqlite> pragma integrity_check;
>> integrity_check
>> ---
>> ok
>> sqlite> select id, count(id) from cards group by id having count(id)
>> > 1;
>> sqlite>
>>
>> Any ideas?
>
> Triggers?
>
>
>>
>>
>> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes 
>> wrote:
>>> Hi all,
>>>
>>> Some of my users have been reporting strange database problems
>>> recently, which seem to have gone away when I removed a call to
>>> enable_shared_cache(). The problems were noticeable in at least 3.6.1
>>> and 3.6.11, when using databases of 30MB+, and doing large updates
>>> using pysqlite.
>>>
>>> There were two distinct reported problems. One was that the program
>>> would just freeze, with no disk access and CPU usage, seemingly in
>>> the
>>> middle of a DB query, on Win32. I wasn't able to reproduce this on
>>> Linux.
>>>
>>> The other problem was reported by both win32 and mac users, and again
>>> I wasn't able to reproduce it. It resulted in errors like this:
>>>
>>> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
>>> must be unique 'update cards set isDue = 0 where type in (0,1,2) and
>>> priority = 0 and isDue = 1' {}
>>>
>>> .. which is strange, because the primary key on that table is called
>>> 'id' and is not affected by the update call.
>>>
>>> I also had some reports of DB corruption on OSX, but I'm not sure if
>>> that occurred since I upgrade to 3.6.11.
>>>
>>> One other hint is that while I'd been using shared cache mode for at
>>> least 6 months or more, these problems seem to have only surfaced
>>> recently. I'm not sure if that's due to a change in the queries I've
>>> been doing, or the fact that I changed the cache size to a bigger
>>> number, and changed the page size to 4096.
>>>
>>> Anyway, disabling the shared cache appears to have fixed the problem,
>>> and since my program is single threaded and has no need for the
>>> shared
>>> cache, it's not an issue for us anymore. But I thought it's worth
>>> reporting. Have there been any other instances of problems with the
>>> shared cache mode?
>>>
>>> Cheers,
>>>
>>> Damien
>>>
>> ___
>> 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] problems with shared cache?

[Dan]

On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote:

> Unfortunately, a user has just reported the same primary key error
> message with shared cache disabled, although the freezing appears to
> have been fixed.
>
> However, when I ask the user to send me their deck, I find that:
>
> sqlite> pragma integrity_check;
> integrity_check
> ---
> ok
> sqlite> select id, count(id) from cards group by id having count(id)  
> > 1;
> sqlite>
>
> Any ideas?

Triggers?


>
>
> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes   
> wrote:
>> Hi all,
>>
>> Some of my users have been reporting strange database problems
>> recently, which seem to have gone away when I removed a call to
>> enable_shared_cache(). The problems were noticeable in at least 3.6.1
>> and 3.6.11, when using databases of 30MB+, and doing large updates
>> using pysqlite.
>>
>> There were two distinct reported problems. One was that the program
>> would just freeze, with no disk access and CPU usage, seemingly in  
>> the
>> middle of a DB query, on Win32. I wasn't able to reproduce this on
>> Linux.
>>
>> The other problem was reported by both win32 and mac users, and again
>> I wasn't able to reproduce it. It resulted in errors like this:
>>
>> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
>> must be unique 'update cards set isDue = 0 where type in (0,1,2) and
>> priority = 0 and isDue = 1' {}
>>
>> .. which is strange, because the primary key on that table is called
>> 'id' and is not affected by the update call.
>>
>> I also had some reports of DB corruption on OSX, but I'm not sure if
>> that occurred since I upgrade to 3.6.11.
>>
>> One other hint is that while I'd been using shared cache mode for at
>> least 6 months or more, these problems seem to have only surfaced
>> recently. I'm not sure if that's due to a change in the queries I've
>> been doing, or the fact that I changed the cache size to a bigger
>> number, and changed the page size to 4096.
>>
>> Anyway, disabling the shared cache appears to have fixed the problem,
>> and since my program is single threaded and has no need for the  
>> shared
>> cache, it's not an issue for us anymore. But I thought it's worth
>> reporting. Have there been any other instances of problems with the
>> shared cache mode?
>>
>> Cheers,
>>
>> Damien
>>
> ___
> 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] problems with shared cache?

[Damien Elmes]

Unfortunately, a user has just reported the same primary key error
message with shared cache disabled, although the freezing appears to
have been fixed.

However, when I ask the user to send me their deck, I find that:

sqlite> pragma integrity_check;
integrity_check
---
ok
sqlite> select id, count(id) from cards group by id having count(id) > 1;
sqlite>

Any ideas?

On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes  wrote:
> Hi all,
>
> Some of my users have been reporting strange database problems
> recently, which seem to have gone away when I removed a call to
> enable_shared_cache(). The problems were noticeable in at least 3.6.1
> and 3.6.11, when using databases of 30MB+, and doing large updates
> using pysqlite.
>
> There were two distinct reported problems. One was that the program
> would just freeze, with no disk access and CPU usage, seemingly in the
> middle of a DB query, on Win32. I wasn't able to reproduce this on
> Linux.
>
> The other problem was reported by both win32 and mac users, and again
> I wasn't able to reproduce it. It resulted in errors like this:
>
> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
> must be unique 'update cards set isDue = 0 where type in (0,1,2) and
> priority = 0 and isDue = 1' {}
>
> .. which is strange, because the primary key on that table is called
> 'id' and is not affected by the update call.
>
> I also had some reports of DB corruption on OSX, but I'm not sure if
> that occurred since I upgrade to 3.6.11.
>
> One other hint is that while I'd been using shared cache mode for at
> least 6 months or more, these problems seem to have only surfaced
> recently. I'm not sure if that's due to a change in the queries I've
> been doing, or the fact that I changed the cache size to a bigger
> number, and changed the page size to 4096.
>
> Anyway, disabling the shared cache appears to have fixed the problem,
> and since my program is single threaded and has no need for the shared
> cache, it's not an issue for us anymore. But I thought it's worth
> reporting. Have there been any other instances of problems with the
> shared cache mode?
>
> Cheers,
>
> Damien
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problems with shared cache?

[Damien Elmes]

Hi all,

Some of my users have been reporting strange database problems
recently, which seem to have gone away when I removed a call to
enable_shared_cache(). The problems were noticeable in at least 3.6.1
and 3.6.11, when using databases of 30MB+, and doing large updates
using pysqlite.

There were two distinct reported problems. One was that the program
would just freeze, with no disk access and CPU usage, seemingly in the
middle of a DB query, on Win32. I wasn't able to reproduce this on
Linux.

The other problem was reported by both win32 and mac users, and again
I wasn't able to reproduce it. It resulted in errors like this:

sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY
must be unique 'update cards set isDue = 0 where type in (0,1,2) and
priority = 0 and isDue = 1' {}

.. which is strange, because the primary key on that table is called
'id' and is not affected by the update call.

I also had some reports of DB corruption on OSX, but I'm not sure if
that occurred since I upgrade to 3.6.11.

One other hint is that while I'd been using shared cache mode for at
least 6 months or more, these problems seem to have only surfaced
recently. I'm not sure if that's due to a change in the queries I've
been doing, or the fact that I changed the cache size to a bigger
number, and changed the page size to 4096.

Anyway, disabling the shared cache appears to have fixed the problem,
and since my program is single threaded and has no need for the shared
cache, it's not an issue for us anymore. But I thought it's worth
reporting. Have there been any other instances of problems with the
shared cache mode?

Cheers,

Damien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users