[ 8 posts ] 
Author Message
 Post subject: sqlite on webOS is unreliable
PostPosted: Thu May 13, 2010 9:27 pm 

Joined: Fri Jul 17, 2009 7:12 am
Posts: 1047
Location: Germany
HI!

Has anyone else noticed database corruptions or other "strange" things happen to their sql databases?

For example, we have NOT NULL constraints on most columns in our database. Yet, we have seen databases from users, where there were NULLs in those columns! How is that possible? Then, when the rows are updated, sqlite throws a constraint error.

Also, we had at least one case, where a complete table was suddenly missing.

Please see also here: viewtopic.php?f=11&t=6546

Unfortunately, we never had such problems with the databases on our phones or emulators. So, we cannot reproduce it. We only see it, when users report a problem.

We are using "ext:" prefix to put the database on the media partition. Maybe that is the problem? FAT32 filesystem corruption or something like that?

We had these problems at least since 1.4.0.

Thanks for any hints or insights.

Thomas

_________________
VivaLV Software - http://software.vivalv.de/ - http://twitter.com/vivalvsoftware


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Fri May 14, 2010 2:18 am 

Joined: Sun Sep 20, 2009 8:03 pm
Posts: 393
Location: Albany NY
sidamos wrote:
HI!

Has anyone else noticed database corruptions or other "strange" things happen to their sql databases?

For example, we have NOT NULL constraints on most columns in our database. Yet, we have seen databases from users, where there were NULLs in those columns! How is that possible? Then, when the rows are updated, sqlite throws a constraint error.

Also, we had at least one case, where a complete table was suddenly missing.

Please see also here: viewtopic.php?f=11&t=6546

Unfortunately, we never had such problems with the databases on our phones or emulators. So, we cannot reproduce it. We only see it, when users report a problem.

We are using "ext:" prefix to put the database on the media partition. Maybe that is the problem? FAT32 filesystem corruption or something like that?

We had these problems at least since 1.4.0.

Thanks for any hints or insights.

Thomas


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Fri May 14, 2010 9:20 pm 

Joined: Tue Sep 22, 2009 2:56 pm
Posts: 758
Location: Pensacola, FL
I had this issue a LONG time ago. At the time, I was creating a new connection to the database on every scene. On occasion, a scene would be popped and a slight delay would cause two connections, and thus a corrupted database. Very rare, but it happened.

I move over to a global object holding the database connection, and the problem magically disappeared. Rookie mistake.

Are you creating a new object on each scene?

Never had the null issue.

_________________
- James Harris

* FlashCards & FlashCards Lite
* ZIP Code Tools
* WhitePages
* Mobile Florist


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Sat May 15, 2010 8:15 am 

Joined: Fri Jul 17, 2009 7:12 am
Posts: 1047
Location: Germany
Thanks for sharing this information.

In fact, you are right. We really had one connection per scene, sometimes even per class. We suspected, that could be a problem (but we never read about that), so we changed it to one global connection in the last version. But there still are corrupted databases out there, I guess.

What kind of corruptions did you have?

How did you fix corrupted databases?

_________________
VivaLV Software - http://software.vivalv.de/ - http://twitter.com/vivalvsoftware


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Tue May 18, 2010 7:03 pm 

Joined: Tue Sep 22, 2009 2:56 pm
Posts: 758
Location: Pensacola, FL
I am unsure what kind of corruption, as I never got detailed info from these individuals that experienced it. No fix really for it either other than deleting and reinstalling the app. My solution was to simply move it over to the singular connection. From that moment on, I have not received a report of a corrupted database from a user. With 80,000+ active users, I am sure I would have heard something by now if they did experience it.

_________________
- James Harris

* FlashCards & FlashCards Lite
* ZIP Code Tools
* WhitePages
* Mobile Florist


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Wed May 19, 2010 10:57 pm 
User avatar

Joined: Thu Jul 16, 2009 2:48 pm
Posts: 351
Location: U.S.A.
See now, if everyone had read my book you'd know this ;) LOL

I found this same thing out the hard way while writing my book... I remember a few hours' worth of hair-pulling as a result! I pointed it out to the reader in an effort to save others from premature, self-induced balding :)

_________________
Frank W. Zammetti
Author of:
Practical Palm Pre webOS Projects,
Practical Ext JS Projects with Gears,
Practical Dojo Projects,
Practical DWR 2 Projects,
Practical JavaScript, DOM Scripting & Ajax Projects
For info (and more): zammetti.com


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Fri May 21, 2010 9:13 pm 
User avatar

Joined: Thu Jul 16, 2009 2:37 pm
Posts: 5177
Location: HP (Palm), Austin, TX (@unwiredben on Twitter)
Can someone put up a simple IPK that shows this corruption problem using multiple connections? I just talked with engineering, and they know of one possible issue with SQLite 3.6.20 that could have been fixed in a newer version, but they'd really like a test case.

You can either post a URL to the IPK here or send me a PM.


Top
 Profile  
 
 Post subject: Re: sqlite on webOS is unreliable
PostPosted: Fri May 21, 2010 11:04 pm 

Joined: Tue Sep 22, 2009 2:56 pm
Posts: 758
Location: Pensacola, FL
I could send you an older version of my app that did this. I personally could not duplicate this issue, but several users could. You would have to work with it for a bit probably before it could be reproduced. Want that?

_________________
- James Harris

* FlashCards & FlashCards Lite
* ZIP Code Tools
* WhitePages
* Mobile Florist


Top
 Profile  
 
Display posts from previous:  Sort by  
   [ 8 posts ] 


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron