[SailfishDevel] Contacts db issue

[Matthew Vogt]

Hi Tone.
Yes, it sounds like you have a corrupted contacts database.  Any idea how that might have happened?

In any case, you can try to recreate the database to fix the issue.  You need to do something along the lines of the following (but please sanity-check these instructions before running them, I'm typing them into email, not having tested them on device!):

You will need to have a privileged user session:
$ devel-su -p

Then stop everything that might be writing to the contacts database:
$ systemctl --user stop lipstick
$ systemctl --user stop msyncd
$ systemctl --user stop contactsd
$ systemctl --user stop as-daemon

Change to the directory where the database resides:
$ cd ~/.local/share/system/privileged/Contacts

Check that DB is actually corrupted; this will say 'ok' if there are no problems, or report a bunch of error messages:
$ sqlite3 qtcontacts-sqlite/contacts.db "pragma integrity_check;"

If there are problems, continue:

Make a backup copy of the broken DB just in case we lose something:
$ mkdir bkp
$ cp -R qtcontacts-sqlite bkp/

Dump the DB contents:
$ mkdir temp
$ sqlite3 qtcontacts-sqlite/contacts.db .dump > temp/dump.sql

Record the current schema version of the DB, which is reported by the following command (it is probably 15 or 16 or something similar):
$ sqlite3 qtcontacts-sqlite/contacts.db "pragma user_version;"

Recreate the database from the dump:
$ mkdir new-db
$ sqlite3 new-db/contacts.db < temp/dump.sql

Set the schema version in the new database, where VER is the number reported above:
$ sqlite3 new-db/contacts.db "pragma user_version = <VER>;"

Move the new database to be the device database:
$ mv qtcontacts-sqlite old-db
$ mv new-db qtcontacts-sqlite

Then reboot, and the device will restart using the new version of the DB.

You can then remove the bkp, temp and old-db directories, assuming things went well.  If things did not go well, you can restore the old-db directory to the 'qtcontacts-sqlite' location (after stopping everything as we did earlier) and contact me directly.

Thanks,
Matt

________________________________
From: devel-bounces at lists.sailfishos.org [devel-bounces at lists.sailfishos.org] on behalf of Tone Kastlunger [users.giulietta at gmail.com]
Sent: Tuesday, October 27, 2015 5:47 AM
To: devel at lists.sailfishos.org
Subject: [SailfishDevel] Contacts db issue

Hey;
looking through the logs (for something else) on my Jolla I happened to find the
following error; it appears to be realated to the contacts db. Any way to fix it?


 "Failed to prepare query for joined details:
                              database disk image is malformed Unable to fetch row
                              Query:
                              SELECT Details.detailId,Details.contactId,Details.detail,Details.detailUri,Details.linkedDetailUris,Details.contexts,Details.accessConstraints,Details.provenance,COALESCE(Details.modifiable, 0),COALESCE(Details.nonexportable, 0),Addresses.*,Anniversaries.*,Avatars.*,Birthdays.*,EmailAddresses.*,Families.*,GeoLocations.*,Guids.*,Hobbies.*,Nicknames.*,Notes.*,OnlineAccounts.*,Organizations.*,PhoneNumbers.*,Presences.*,Ringtones.*,Tags.*,Urls.*,OriginMetadata.*,GlobalPresences.*,ExtendedDetails.* FROM temp.RegenerateAggregate CROSS JOIN Details ON Details.contactId = temp.RegenerateAggregate.contactId LEFT JOIN Addresses ON Addresses.detailId = Details.detailId LEFT JOIN Anniversaries ON Anniversaries.detailId = Details.detailId LEFT JOIN Avatars ON Avatars.detailId = Details.detailId LEFT JOIN Birthdays ON Birthdays.detailId = Details.detailId LEFT JOIN EmailAddresses ON EmailAddresses.detailId = Details.detailId LEFT JOIN Families ON Families.detailId = Details.detailId LEFT JOIN GeoLocations ON GeoLocations.detailId = Details.detailId LEFT JOIN Guids ON Guids.detailId = Details.detailId LEFT JOIN Hobbies ON Hobbies.detailId = Details.detailId LEFT JOIN Nicknames ON Nicknames.detailId = Details.detailId LEFT JOIN Notes ON Notes.detailId = Details.detailId LEFT JOIN OnlineAccounts ON OnlineAccounts.detailId = Details.detailId LEFT JOIN Organizations ON Organizations.detailId = Details.detailId LEFT JOIN PhoneNumbers ON PhoneNumbers.detailId = Details.detailId LEFT JOIN Presences ON Presences.detailId = Details.detailId LEFT JOIN Ringtones ON Ringtones.detailId = Details.detailId LEFT JOIN Tags ON Tags.detailId = Details.detailId LEFT JOIN Urls ON Urls.detailId = Details.detailId LEFT JOIN OriginMetadata ON OriginMetadata.detailId = Details.detailId LEFT JOIN GlobalPresences ON GlobalPresences.detailId = Details.detailId LEFT JOIN ExtendedDetails ON ExtendedDetails.detailI
Oct 26 21:38:30 Jolla [1156]: [W] ContactWriter::regenerateAggregates:3652 - "Failed to read constituent contacts for aggregate 8487 during regenerate"



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.sailfishos.org/pipermail/devel/attachments/20151026/8d94715f/attachment-0001.html>