avoid database corruption
Submitted by an unknown user
Link to original bug (#717653)
Description
---- Reported by shotwell-maint@gnome.bugs 2011-04-02 07:10:00 -0700 ----
Original Redmine bug id: 3458
Original URL: http://redmine.yorba.org/issues/3458
Searchable id: yorba-bug-3458
Original author: Jani Monoses
Original description:
Using stock shotwell 0.9.0 on natty/i686.
Natty froze when inserting SD card (some kernel bug most likely) and while shotwell was running. Had to reboot the system, but this apparently affected shotwell:
**
ERROR:i686-linux-gnu/db/VersionTable.c:104:version_table_construct: assertion failed: (res == SQLITE_OK)
Aborted
---- Additional Comments From shotwell-maint@gnome.bugs 2013-05-01 11:39:00 -0700 ----
History
Comment 1
Updated by Jani Monoses over 2 years ago
$sqlite3 /home/jani/.shotwell/data/photo.db
…
sqlite> .tables
Error: database disk image is malformed
Comment 2
Updated by Eric Gregory over 2 years ago
- Status changed from Open to 5
- Resolution set to invalid
- % Done changed from 0 to 0
Sorry to hear that you lost your Shotwell database. Data corruption is one of the many dangers of running a pre-release OS.
Unfortunately, there's no way to fix a broken database. We do recommend backing it up just for these kinds of issues which can occur due to kernel bugs and disk crashes. See our FAQ:
http://trac.yorba.org/wiki/Shotwell/%(=caps)FAQ%#Backup
We're also planning on eventually adding a tool of some kind to facilitate db backups, see ticket #1963.
The good news of course is that your photos should all still be there, and you can always re-create the database from scratch.
Marking this bug as invalid because the crash resulting in file corruption was not caused by Shotwell.
Comment 3
Updated by Adam Dingle over 2 years ago
- Status changed from 5 to 4
-
Resolution deleted (
<strike>
_invalid_</strike>
) - % Done changed from 0 to 0
- Priority set to High
- Subject changed from crash on startup after unclean shutdown to avoid database corruption
Also sorry to hear about the database corruption, though actually I think that has little to do with running a pre-release Ubuntu. In SQLite, there are three possible values for the 'pragma synchronous' setting with a tradeoff between speed and safety. From the SQLite web site:
*PRAGMA synchronous; *
*PRAGMA synchronous = *
0 | OFF | 1 | NORMAL | 2 | FULL;
Query or change the setting of the “synchronous†flag. The first (query) form will return the synchronous setting as an integer. When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.
Shotwell currently uses synchronous = OFF. This does mean there is some risk of data loss if the operating system crashes, and this did indeed happen to you. We could consider synchronous = NORMAL instead, though we'd need to do performance testing to see how much slower it would be. Alternately, we could simply create a backup copy of the database automatically once in a while so that you wouldn't lose all data in the case of a system crash or power failure; that would have helped this user. Reopening.
Comment 4
Updated by Ian MacDonald over 2 years ago
This is terrible.. I made some tagging changes, and was just browsing the new tag and had a crash that rebooted my computer.. been a long time since anything like that has happened. Over 10K photos organized .. now staring at a rebuild. This totally sucks. IMHO it is irresponsible to take performance over risk without an automated backup. 0.9.3 stable on Natty here. If it takes me 3 days to figure out the database corruption it will save me time… on to that now. I vote to change this until the backup issue is resolved.
Comment 5
Updated by Adam Dingle over 2 years ago
- Target version set to 0.11
Agreed: losing data sucks. This has burned only a small number of users, but the consequences are painful for users who are affected. Too late to do anything about this for 0.10, but we should address this for 0.11.
Comment 6
Updated by Vincent Henninot over 2 years ago
Hello,
Same problem for me with Maverick Meerkat and shotwell 0.9.3.1
I had 30.000 in my database when shotwell crashed during photo import…
I have to re import all my photos ???
Vincent
Comment 7
Updated by Adam Dingle over 2 years ago
- Target version changed from 0.11 to 0.10
Clinton has sent a patch which we can possibly consider for 0.10.
Comment 8
Updated by Lucas Beeler over 2 years ago
Back at ya, Clinton!
Comment 9
Updated by Clinton Rogers over 2 years ago
Reason: revised patch has been sent.
Comment 10
Updated by Clinton Rogers over 2 years ago
Reason: another revised patch sent.
Comment 11
Updated by Clinton Rogers over 2 years ago
- Resolution set to fixed
- % Done changed from 0 to 100
Fixed in 8388a134 and will be released as part of 0.10.0.
What we do is make a copy of photo.db when Shotwell exits after a successful session, then on the next session, we try a quick 'sanity check' query against the database. If this fails, we assume that the database has become corrupted, and we copy the backup over it and try again.
This way, if the unthinkable happens, the program will now try to roll back to the end of the previous session, minimizing the amount of lost time and work.
Comment 12
Updated by Roumano - over 2 years ago
Hi,
Can it make sense to have a option in shotwell to change have PRAGMA synchronous for shotwell database ?
SQLITE security synchronisation : “0 | OFF | 1 | NORMAL | 2 | FULL;†?
Comment 13
Updated by Adam Dingle over 2 years ago
Hi,
Can it make sense to have a option in shotwell to change have PRAGMA synchronous for shotwell database ?
SQLITE security synchronisation : “0 | OFF | 1 | NORMAL | 2 | FULL;†?
In general we don't want options like this which won't make sense for non- technical users. A GConf key that toggles this might be OK. Note that importing will be much slower with PRAGMA synchronous=full (or probably even normal), which is why we turned it off in the first place. I hope that our new system which automatically backs up the database will prevent any user from ever losing their Shotwell database again.
Comment 14
Updated by Charles Lindsay 7 months ago
- Status changed from 5 to Fixed
--- Bug imported by chaz@yorba.org 2013-11-25 21:53 UTC ---
This bug was previously known as bug 3458 at http://redmine.yorba.org/show_bug.cgi?id=3458
Unknown Component Using default product and component set in Parameters Unknown milestone "unknown in product shotwell. Setting to default milestone for this product, "---". Setting qa contact to the default for this product. This bug either had no qa contact or an invalid one.
Version: 0.10
Resolution: RESOLVED FIXED