Fixing the Contacts DB

asked 2014-10-10 02:17:45 +0200

Keeper-of-the-Keys
5049 ●77 ●101 ●104

I noticed my contacts db has quite a few corrupt entries in it (probably all/mostly sourced in [failed?] google-syncs), for instance hundreds(!) of duplicate entries. On my n900 I used to sometimes clean things up using sql (at the time it was mainly culling old call history), I would like to do the same thing now, but I am afarid that if I remove parent entries without removing any potential children in other tables I will end up with an even corrupter database.

So my question is two fold: on the one hand I would like a schematic of the database that shows how the different tables relate to each other (and the different databases, for instance e-mail/sms and contacts).

On the other hand if someone else already wrote cleanup scripts or if there is a command by which I can batch-remove all contacts that match a certain criterium that would be awesome.

As a side question: where is the sms (and e-mail) database located on the phone?

Thanks in advance!

edit retag flag offensive close delete

Comments

Just to head of the obvious: According to sqliteman the tables in contacts.db were not created using foreign keys, so just mass deleting the bad entries from the contacts table may leave me with a ton of bad entries in other tables...

And to illustrate just how many bad entries I must have: contacts.db is 15MB large while my 'real' contact count should be no more then 1000...

Keeper-of-the-Keys ( 2014-10-10 02:30:12 +0200 )edit

The contacts.db uses a trigger to keep entries consistent. On delete the details are removed from the db and a record is stored in DeletedContacts to keep track of deletion in order to propagate that to sync targets.

Eierkopp ( 2014-10-10 09:18:52 +0200 )edit

That is great when sync hasn't added in the order of 5000 bad entries, I want to remove all the bad entries while preserving the good ones, I'm not afraid of sql and I don't want them to stick around in the DeletedContacts table because this very heavy db is making my phone perform badly for no good reason.

The huge db makes both the phone and the sms app hang up to even crash regularly, I have one-way sync with my gmail account and the db was originally imported from an n9 and in the mean time restored from a jolla backup so I'm not 100% sure at what point it became corrupt.

Keeper-of-the-Keys ( 2014-10-10 12:33:39 +0200 )edit

Consider cleaning up the whole db and starting over by importing your contacts again.

-Disable your social media accounts (not only Google) so that they will not sync anything.
-Delete the contacts db in the phone:

systemctl --user stop contactsd.service
devel-su
rm -rf /home/nemo/.local/share/system/privileged/Contacts/qtcontacts-sqlite/*
reboot

-Visit contacts.google.com and check that your are ok. Remove duplicates if any. Chect that no contact has duplicate content (e.g. the same phone number twice). Edit if needed.
-Let the phone sync the contacts down from Google.

Best of luck!

jovirkku ( 2014-10-13 17:19:30 +0200 )edit

That would be great if I kept all my contacts on Google, which I don't out of respect for the privacy of my contacts, that is why I only have one-way google-sync.

I basically have a contacts db which moved from a symbian phone to n900 to n9 to jolla and now there seems to be no proper way to clean up the dirty database?

Keeper-of-the-Keys ( 2014-10-14 01:46:03 +0200 )edit

answered 2014-10-14 23:13:55 +0200

Keeper-of-the-Keys
5049 ●77 ●101 ●104

updated 2014-10-14 23:15:25 +0200

So I partially adopted the above solution as an in-between:

Stopped contactsd as described above to have a consistent disk image of the database.
Copied contacts.db to my pc & backed it up.
Ran the following query to find all contacts with an above 'normal' amount of rows: select displayLabel, count() from contacts group by displayLabel having count() > 4; For now I used 4: local, aggregate and up to 2 google entries, in the future I may also delete more google entries.
For all the contacts that had an above normal amount of entries I ran: delete from contacts where displayLabel = '[bad entrys' name]' and syncTarget = 'google';
Ran vacuum on all the tables in the file.
Copied file back to phone, set ownership etc. correctly & rebooted phone (I suspect a mere contactsd restart should have sufficed but I wanted to see a full boot).

Now I went back from 14MB to 6.7MB, contacts still has 2.9k rows while deletedContacts has over 4.5k rows, I believe there is still some room for improvement but it will take some more work, for now I want to see how the phone does.

What is annoying to me is that quite a few contacts that should have been local-only had google-sync targets, I suspect that when I added my google account it was at first set to two-way so now I need to check what was copied...

Seems to me two-way should be a conscious decision before turning it on....

edit flag offensive delete publish link

Comments

Well I had hoped that this would be good enough, but performance of the sms app was still not great and the db size jumped back to 10MB instantly and to top things all of I now updates to 1.1.0.39 and now all my contacts show without phone numbers though they are still present in the database, so I'll open a second question shortly probably unless I solve it.

Keeper-of-the-Keys ( 2014-10-27 19:07:02 +0200 )edit

The problem with the upgrade was a schema update that never happened I performed it manually.

Keeper-of-the-Keys ( 2014-11-02 03:04:17 +0200 )edit

Fixing the Contacts DB

Comments

1 Answer

Comments

Question tools

Stats

Related questions

Fixing the Contacts DB

Comments

1 Answer

Comments

Question tools

Public thread

Stats

Related questions