Re: database problems

[Jack <ostroffjh sbcglobal net>]

First - please note that I actually found the "correct" backup copy,  
which was not corrupt, so I'm back in business without having to  
recreate all my tags.  However, I'll respond to some of your points  
just to clarify what happened and what I was asking.  Sorry to be so  
long-winded about it - feel free to ignore if you don't care about the  
details.

On 2012.05.23 13:53, Adam Tauno Williams wrote:
> On Wed, 2012-05-23 at 11:04 -0400, Jack wrote:
> > I've been using f-spot for quite a while, with about 3500 photos.   
> > I'm on Gentoo Linux, and a recent set of updates left f-spot not  
> > working. One of the libraries it uses got updated, but not f-spot  
> > itself, and the error on startup included "f-spot caught an  
> > exception - hyena.data.sqlite.sqliteexception: sqlite error 1: no  
> > such table: tags".
> I doubt this is due to a library update.  It sounds like a straight  
> forward database problem.  Why do you think "no such table: tags"  
> does not accurately describe the problem?
I'm not completely sure of the full order of problems, but I do suspect  
I fell victim to more than one distinct issue.  If the above simply  
indicated the absense of a tags table - them I'm curious where it went,  
although I don't really expect to find out.  I do know the issue that  
caused most of my problems yesterday was that libicu got upgraded, and  
so many things that depended on it, including both other libraries and  
applications, failed to run until recompiled.

> If you look at the F-Spot sqlite database does it have a tags table?
By the time I looked, the database was empty except for a meta table.   
I thought that was the first error message I noticed, but I can imagine  
I simply glossed over the first failure (which trashed the database)  
and that was the second.

[snip advice on creating the tags table ...]

> >   I assumed that once I got all the other updates done, and I  
> > recompiled f-spot, it would work again.  (The only reason I have  
> > that error message is that I extracted it from the google search in  
> > my browser history.)
> Eh?  I have no idea what this means.
Sorry - it wasn't very clear.  Normally, if I see an error I don't  
understand, I copy and save a bunch of the text for reference during  
troubleshooting.  If I jump to a conclusion, I don't save it, which  
often gives me trouble later, if the error changes because of partial  
or incorrect "fixes."  In this case, I didn't save the error, but I had  
googled it, so I was able to pull up the google page from my browser  
history and recover the error string I had searched on.  However - it  
does appear that this was not really my underlying problem and only a  
secondary effect.

> > Well, after all the recompiling, my next attempt to run f-spot said  
> > something about a bad database.  Unfortunately, EVERY f-spot .db  
> > file I could find (scattered in several directories, probably due to  
> > some attempts at command line start) were empty except for a meta  
> > table.  The only f-spot backup .db files were from 2009.
> I don't believe there is a relevant database anywhere other than  
> $HOME/.config/f-spot/
Well, that is clearly the only place f-spot looks for the database  
(photos.db).  However, under circumstances which I don't fully  
understand, it seems f-spot (or maybe sqlite) thinks the file is  
corrupt, so copies it to photos-yyyymmmdd-n.db before creating a new  
one.  Normally, if launched from an icon or start menu, that file gets  
created in the same directory, since it would normally be set as the  
working directory.  However, if f-spot is launched from the command  
line, it drops that file in the current working directory.  That is why  
I found a bunch of those files in various places.  Most of them were  
just copies of the essentially empty database.  The "good" copy  
happened to be in /var/spool/mail, since I was also dealing with mail  
problems at the same time.  (yes - my own fault for trying to fix too  
many problems at the same time.)

> Perhaps you have very old database lying about as a result of the  
> location being moved over time and a real migration having not  
> occurred.
No - all the db files other than in the right directory were those  
backup dated copies.

> > I know it's my own fault for not having a recent backup, but I'm  
> > really surprised that f-spot would overwrite the database unless it  
> > started successfully and had something to change.  Is there  
> > potential here for an enhancement request that a backup is made of  
> > the .db file before any attempt to open it?
> I dunno; photos.db can be pretty big.   I personally don't see a need  
> for this.
I'll certainly agree with you.  Actually, it seems that a backup IS  
made if the db file is thought to be corrupt.  My problem was that I  
hadn't yet figured out where to look for it, complicated by my command  
line launching in the midst of lots of cd'ing around to fix other  
problems.

> (a) Users should make backups.
Obviously.  Or maybe definitely, but not sufficiently obvious?

> (b) Developers [you are compiling - THIS MEAN YOU!] take full  
> responsibility for what they are doing.  Developers certainly know  
> enough to make backups;  otherwise they shouldn't be playing with  
> those toys.
While I do basically admit that I should behave like a developer, do  
you claim that every Gentoo user is inherently a developer?  I suppose  
it is a valid viewpoint, but I think you would get some disagreement.   
Just because I'm compiling from source does not automatically imply I'm  
touching anything in the code.  However, I do agree that a Gentoo user  
should (and usually does) take responsibility for their actions.  Note  
that I didn't say "F-spot destroyed my database.  I want you to fix  
it!."  At least I hope nothing I said was taken that way.  I was trying  
to ask for help tracking down the likely order of events that resulted  
in my database being apparently trashed.

> Better yet, IMNSHO, do not use Gentoo.  Use a distribution that has a  
> QC workflow and builds packages in a consistent manner.
Every distribution I have used has some problem or other.  I prefer the  
control over my system I get with Gentoo, and yes, I do accept the  
issues (such as this one) that introduces.

As someone's sig line says (misquoted, I'm sure) "There are two types  
of people, those who do backups, and those who have never had a hard  
drive fail."

> >   At this point I'm really just looking to understand when/why/how  
> > the .db got overwritten, as well as seeing if f-spot can avoid such  
> > problems in the future - even if the problem is actually in one of  
> > the libraries and not f-spot itself.
> I have a hard time believing F-Spot overwrote your database.  I'd  
> suspect your build got screwed up, F-Spot couldn't open the database,  
> and you did 'something' to make it work.  That 'something' deleted  
> your database.
I agree it was not likely to be an explicit action by f-spot.  However,  
the last error message I got said something to the effect of "Can't  
open the database, probably corrupt.  Backing it up as  
photos-20120522-0.db and starting over."  That's clearly a paraphrase  
from memory and not a direct quote - I'll have to hunt through the  
source to find the message.  It may also be something inherent to  
sqlite3, but again, I won't know until I dig through source (in my  
copious free time...)  By the way - since copying that backup file to  
~/.config/f-spot/photos.db seemed to restore my data, it obviously was  
not actually corrupt, thus my wondering what triggered the copy and  
creation of a new one.

> > Thanks for any relevant information, suggestions, or advice (other  
> > than to do more backups).
> :)  Make more backups!
Definitely.