[Bug]: Deleted DAVx^5 account on Android and Contacts missing from Nextcloud personal account (also a 666 connection :))

[WNYmathGuy]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

First I reported this to DAVx^5 to see if they could figure it out

Hey, maybe I'm just Mr. Edge Case.

I've been relying on Nextcloud since I spun up my own NC12 server. Not sure how long I've been relying on DAVx^5 but it's probably almost as long.

Recently, I had trouble with my NC27 server. A bunch of zombie SQL calls were stuck and threw a too many connections error preventing all access to the server via web interfaces, but not from the Android apps. I killed the zombies in my phpMyAdmin interface and things seemed to be fixed.

Steps to reproduce

The next day I was getting weird error messages from the DAVx^5 app, so I thought I could solve the problem by:

1. delete the account link in DAVx^5.
2. Clear cache & memory from the DAVx^5 in the Android OS.
3. Log back into my Nextcloud account.

Expected behavior

I expected the DAVx^5 to leave a copy of the contacts on the phone and/or just disconnect from Nextcloud. Instead, somehow the connection to contacts in the database is gone from all places; Thunderbird, Android, Nextcloud.

Installation method

Upgrade from previous installation

Nextcloud Server version

27

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Apache (supported)

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

See /index.php/settings/admin/support link below

List of activated Apps

See /index.php/settings/admin/support link below

Nextcloud Signing status

See /index.php/settings/admin/support link below

Nextcloud Logs

See /index.php/settings/admin/support link below

Additional info

Link: https://cloud.ruppssites.com/index.php/s/Rq7ia3J2RPFxnqP
Password: }IW@}K]6s

I know this is a total red-herring, but it's fun to note that I recently added what turns out to be the 666th contact on my list. Obviously, Satan didn't wipe my contacts.

Looking at the Database;

• oc_addressbooks has a record for the contact list that used to be linked to my personal use account on Nextcloud. Its synctoken field is 26646 for some reason and that looks weird to me. (minor note: long ago I changed the displayname field for it from the default of Contacts)
• oc_cards has 666 records matching the addressbookid field's value. The carddata BLOB field has the correct data for contacts that I checked.
• oc_cards_properties table has 5,949 records with the matching addressbookid field. They all look unmolested by the problem at hand.

So what is the missing link that has allowed DAVx^5 to unlink the personal contacts list of a user (with read/write access) from the host address book?

[WNYmathGuy]

What would happen if I used the Nextcloud interface to create a new default address book in the /index.php/apps/contacts/ module, and then did an update query to change the addressbookid field from the old one to the new one in oc_cards & oc_cards_properties?

[joshtrichards]

This isn't an area of the code I'm familiar with, so just a general "maybe this'll help narrow down the cause" idea: any chance you have a db dump from not too long before the deletion event? Maybe do a before/after comparison of the involved tables?

[WNYmathGuy]

This isn't an area of the code I'm familiar with, so just a general "maybe this'll help narrow down the cause" idea: any chance you have a db dump from not too long before the deletion event? Maybe do a before/after comparison of the involved tables?

Do you mean something like the oldest log from the rotating files of the Nextcloud system?

[joshtrichards]

No, like the output of a mysqldump of your Nextcloud database (or whatever approach you use to snapshot/backup your database).

[WNYmathGuy]

No, like the output of a mysqldump of your Nextcloud database (or whatever approach you use to snapshot/backup your database).

We're out of luck. I only have 7 days of logs for that MySQL server. Where can I see code specifically for the calendar interface?

[WNYmathGuy]

Okay, tonight I did it. I created a new addressbook with displayname = Contacts; assume it's id field = 2.
Also, assume the old addressbook with displayname = Mike Rupp has an id field = 1.

Within phpMyAdmin I queried this;

UPDATE `nextcloud`.`oc_cards` SET `addressbookid` = '2' WHERE `addressbookid` = '1'; 
UPDATE `nextcloud`.`oc_cards_properties` SET `addressbookid` = '2' WHERE `addressbookid` = '1'; 

After the success notifications, but not seeing any contacts load at /index.php/apps/contacts, I used the server's OS to force the cron jobs to run. That didn't change anything. If I use the search widget in the top right of the Nextcloud web interface, it will find contacts as I type in unique names, but clicking on them will throw a failed to load contact error. The new addressbook is not visible anymore, but the old one with 0 contacts is visible. I imported a VCF I made from one of the oc_cards BLOB fields and it came into the old addressbook just fine.
For giggles, I ran

UPDATE `oc_cards` SET `addressbookid` = '1' WHERE `oc_cards`.`id` = 1021; 
UPDATE `oc_cards_properties` SET `addressbookid` = '1' WHERE `cardid` = 1021; 

So now I have two contacts in my Mike Rupp addressbook.

WHY ARE THINGS ALWAYS SO WEIRD! Is it Turing's revenge?

[WNYmathGuy]

More tinkering around today. I think that substantial update queries break the system. When I disconnected the DAV^5, it would have synched all contacts I'm guessing. Today I was incrementally doing bigger and bigger update queries to see if I could quickly get all contacts to appear in the new addressbook. I did 2 then 4, then 25, then 50, then 100, then 200 and it broke again. I pushed them all back again and started with a transfer of 175 and it worked.

The queries looked a bit like this...

UPDATE `oc_cards` SET `addressbookid` = '2' WHERE `oc_cards`.`id` BETWEEN 1 AND 175 AND `addressbookid` NOT IN (12, 13, 14, 15); 
UPDATE `oc_cards_properties` SET `addressbookid` = '2' WHERE `cardid` BETWEEN 1 AND 175 AND `addressbookid` NOT IN (12, 13, 14, 15); 

I would just keep changing the upper bound on the BETWEEN parameters.

In the web interface (/index.php/apps/contacts/) the All contacts counter gave a wrong total after I got past 200 contacts in the new addressbook. I had over 500 actual contacts in the new main addressbook and then I transferred over the final 115 which made them all disappear again.

I'm very suspicious that the Contacts app can't do well with over 500 contacts total and over 400 in a single addressbook.

[WNYmathGuy]

OH JOY!!!

I finally found something useful for you!

The size of the bulk transfers I was doing had nothing to do with the problem. I've been told more than once that if I hear the sound of hoves to not look for Unicorns. I tend to look for that fictional animal anyway.

It was discovered today as I kept chipping away at my contacts list, and deleting old ones I no longer needed that I had one oddball. The details are below, and I can not get the BLOB record of it because phpMyAdmin won't let me access it in any way. Its oc_cards.size field has a value of 29,482,724 which is almost 4 times larger than the second largest BLOB. I can only assume the photo data from social media must have been huge. The only solution, once I found out which record number was the problem (like playing Battleship), was...

DELETE FROM `oc_cards` WHERE `id` = 1201;
DELETE FROM `oc_cards_properties` WHERE `cardid` = 1201; 

Below is the data that would have been displayed apart from any image scraped from social media.

<title></title>
<meta name="generator" content="LibreOffice 7.5.3.2 (Linux)"/>
<style type="text/css">
	body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small }
	a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em;  } 
	a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em;  } 
	comment { display:none;  } 
</style>

cardid	name	value
1201	FN	Wayne E Seguin
1201	UID	2cb3bb81-cbbc-4209-bbef-fe0b13e0fda7
1201	CATEGORIES	Database Meetup,Inve[n|s]t
1201	EMAIL	[email protected]
1201	N	Seguin;Wayne;E;;
1201	URL	https://starkandwayne.com/
1201	TITLE	CTO
1201	ORG	Stark & Wayne LLC
1201	X-SOCIALPROFILE	https://github.com/wayneeseguin
1201	X-SOCIALPROFILE	https://www.linkedin.com/in/wayneeseguin/

I'm not sure if you could do a call to image size reduction or if somehow I just got bad data in that BLOB on my own accord, but everything is fine now that that record is gone.

I must have been accidentally transferring it whenever things failed.

[WNYmathGuy]

Just a little follow-up.
That second highest-sized contact had a size of 7478772 until I manually deleted the image. Now it's size is 730.

I deleted the images from the top 12-sized contacts, and now Thunderbird loads really fast. I used to get a really long lag and assumed it was from the calendar synch, but it might have been the contacts list.

I'm pretty sure that I either added some of the offendingly sized images with the Android contacts manager or in my Nextcloud interface. I don't think I did all of them. It seems that some got pulled in via the "[*] Update avatars from social media (refresh once per week)" option.