[SOLVED] Truncate `oc_cards` for specific user?

ℹ️ Support
, ,
1

I’ve had issues with Davx5 syncing for 1 year now, and I’ve finally decided to fix it. Question is can you truncate data for a specific user - or do you have to truncate the whole table (oc_cards and recreate it from scratch?

My logs are filled with:

Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation "oc_cards" violates not-null constraint DETAIL: Failing row contains (null, 24, \x424547494e3a56434152440d0a56455253494f4e3a332e300d0a50524f4449..., 825c3fa6-aeed-4359-8def-b3d16dda5b21.vcf, 1670262420, d688008ac2a6ac0b594a7600c754a36f, 325, 825c3fa6-aeed-4359-8def-b3d16dda5b21).

I can add stuff to CardDAV, but as soon as Davx5 is trying to sync it, it tells me that the contacts doesn’t exist. That tells me it can’t write to the database - they only seem to exist locally on the phone.

So far I’ve tried to clean up my exported .vcf and import it clean again, but the issue remains. The funny thing is that it’s only my user that has the issue, it works perfectly fine on my wife’s phone.

So far my efforts has been:

  • Export all contacts from my Android phone. Cleaned up the backup to make it VCAR 3.0 compatible (for some strange reason Android exports it in 2.1)
  • Imported the cleaned up contacts to Thunderbird, but it’s refusing to sync up to the server
  • Imported the contacts back on my phone with the new shiny and cleaned format
  • Tried to import it directly in the contacts GUI, but it just throws a lot of errors and the end result looks like this:
    image
    image1481×607 70.6 KB
  • No matter what I do, I can’t seem to get the contacts to sync to the server, and now I’m thinking that the only option left is to destroy the oc_cards table with a big sledge hammer, jump on it a bit, and then ask it to resurrect from the dead so that I can import my stuff to it. :slight_smile:

Since my other users don’t have any issues, I want only want to delete my own stuff. I looked in the DB, but there doesn’t seem to be a specific row column for user:

image
image1920×395 99.6 KB

My last option would be to impersonate all the users, export their contacts, then truncate the table, and import everything again to the newly created table, but I would like to avoid those extra steps.

Anyone?

2

From my understanding one of the contact records seems to be formatted incorrectly, so that an empty field is being uploaded to the server, whereas it isn’t allowed to use an empty field.
A possible way to go would be to mount your personal addressbook to a directory on your server using e.g. davfs2. Once you’ve mounted the addressbook, all contact records are listed as vcf files, which you can search, edit, delete or move to a different directory which is similar to purging it.
You can also search for “825c3fa6-aeed-4359-8def-b3d16dda5b21” to identify the erroneous contact record which is causing the problems. I personally used this attempt in the past to identify and fix e.g. entries where the photo attribute was incorrectly formatted etc.

3

I don’t know why you need that when you can just open the .vcf and edit? This is one example:

BEGIN:VCARD
VERSION:3.0
FN:A-kassan
N:;A-kassan;;;
TEL;TYPE=cell:0771-435060
END:VCARD

Also I just truncated the whole oc_cards table and even if I restore contacts from a vcf I just exported, nothing ends up in DB. :frowning:

Either the contacts app is broken, or I’m doing something wrong.

4

Anyway, went ahead and mounted with dav2fs as you suggested, and it’s empty:

root@cloud:/mnt/dav# ls -l
total 0
drwx------ 2 root root 0 Dec  5 22:04 lost+found
5

Ok. Now I would expect that you could add your contacts one by one or in groups e.g. of 10 to the address book again, excluding the vCard which is causing the trouble. Woudn’t this be a way to go?

6

I tried adding a single Vcard, and it failed with a lot of web log errors. I’m a long time ownCloud/Nextcloud user, and I suspect a bug, but I don’t know.

Firefox 107.0.1 but also tried other browsers. Cache and history + cookies cleaned of course.

The invalid URL is from an auto generated contact in “Recently Contacted” which I tried to remove in Web UI but can’t. Also tried to delete the addressbook from Thunderbird and sync to server, but that doesn’t work either.

You need to fill either the text or the ariaLabel props in the button component. 
Object { text: null, ariaLabel: null }
 
Object { _uid: 67, _isVue: true, "$options": {…}, _renderProxy: {…}, _self: {…}, "$parent": {…}, "$root": {…}, "$children": (1) […], "$refs": {}, _watcher: {…}, … }
​
"$attrs": 
​
"$children": Array [ {…} ]
​
"$createElement": function $createElement(t, n, a, i)​
"$el": <a class="button-vue addressbook__…tton-vue--vue-secondary" data-v-7a8f3349="" data-v-8374a1ea="" type="button" href="#" title="">​
"$listeners": 
​
"$options": Object { parent: {…}, _parentVnode: {…}, _componentTag: "Button", … }
​
"$parent": Object { _uid: 65, _isVue: true, "$options": {…}, … }
​
"$refs": Object {  }
​
"$root": Object { _uid: 4, _isVue: true, "$options": {…}, … }
​
"$scopedSlots": Object { icon: a()
, … }
​
"$slots": Object { icon: Getter, … }
​
"$store": Object { _committing: false, _actions: {…}, _actionSubscribers: [], … }
​
"$vnode": Object { tag: "vue-component-53-NcButton", data: {…}, children: undefined, … }
​
_c: function _c(t, n, a, i)​
_computedWatchers: Object { rootElement: {…}, hasText: {…}, hasIcon: {…}, … }
​
_data: Object { slots: Getter & Setter, … }
​
_directInactive: false
​
_events: Object { click: (1) […], "hook:destroyed": (1) […] }
​
_hasHookEvent: true
​
_inactive: null
​
_isBeingDestroyed: false
​
_isDestroyed: false
​
_isMounted: true
​
_isVue: true
​
_props: Object { disabled: Getter & Setter, type: Getter & Setter, nativeType: Getter & Setter, … }
​
_renderProxy: Object { _uid: 67, _isVue: true, "$options": {…}, … }
​
_routerRoot: Object { _uid: 4, _isVue: true, "$options": {…}, … }
​
_self: Object { _uid: 67, _isVue: true, "$options": {…}, … }
​
_staticTrees: null
​
_uid: 67
​
_vnode: Object { tag: "a", data: {…}, children: (1) […], … }
​
_watcher: Object { deep: false, user: false, lazy: false, … }
​
_watchers: Array(9) [ {…}, {…}, {…}, … ]
​
slots: 
​
<get $attrs()>: function get()​
<set $attrs()>: function set(t)​
<get $listeners()>: function get()​
<set $listeners()>: function set(t)​
<get slots()>: function get()​
<set slots()>: function set(e)​
<prototype>: Object { constructor: o(e)
, disabled: Getter & Setter, type: Getter & Setter, … }
NcButton.js:1:20661
The following contact has been repaired: invalid REV 
Object { jCal: Getter & Setter, addressbook: Getter & Setter, vCard: Getter & Setter, conflict: Getter & Setter, dav: Getter & Setter, … }
​
__ob__: Object { value: {…}, dep: {…}, vmCount: 0 }
​
addressbook: 
​
conflict: 
​
dav: 
​
jCal: 
​
vCard: 
​
<get addressbook()>: function get()​
<set addressbook()>: function set(t)​
<get conflict()>: function get()​
<set conflict()>: function set(t)​
<get dav()>: function get()​
<set dav()>: function set(t)​
<get jCal()>: function get()​
<set jCal()>: function set(t)​
<get vCard()>: function get()​
<set vCard()>: function set(t)​
<prototype>: Object { … }
validate.js:48:6
Invalid avatar url /remote.php/dav/addressbooks/users/daniel/z-app-generated--contactsinteraction--recent/146?photo 3 NcAvatar.js:2:151823
The following contact has been repaired: invalid REV 
Object { jCal: Getter & Setter, addressbook: Getter & Setter, vCard: Getter & Setter, conflict: Getter & Setter, dav: Getter & Setter, … }
​
__ob__: Object { value: {…}, dep: {…}, vmCount: 0 }
​
addressbook: 
​
conflict: 
​
dav: 
​
jCal: 
​
vCard: 
​
<get addressbook()>: function get()​
<set addressbook()>: function set(t)​
<get conflict()>: function get()​
<set conflict()>: function set(t)​
<get dav()>: function get()​
<set dav()>: function set(t)​
<get jCal()>: function get()​
<set jCal()>: function set(t)​
<get vCard()>: function get()​
<set vCard()>: function set(t)​
<prototype>: Object { … }
validate.js:48:6
Invalid avatar url /remote.php/dav/addressbooks/users/daniel/z-app-generated--contactsinteraction--recent/145?photo NcAvatar.js:2:151823
Masonry reflow ContactDetails.vue:798
You need to fill either the text or the ariaLabel props in the button component. 
Object { text: "", ariaLabel: null }
 
Object { _uid: 204, _isVue: true, "$options": {…}, _renderProxy: {…}, _self: {…}, "$parent": {…}, "$root": {…}, "$children": (2) […], "$refs": {}, _watcher: {…}, … }
NcButton.js:1:20661
Scroll anchoring was disabled in a scroll container because of too many consecutive adjustments (10) with too little total distance (-0.839999961853027 px average, -8.4 px total). sabre-vobject-50962c75-6792-4bb6-96b8-9730b9b917b4~z-app-generated--contactsinteraction--recent
Error: Your focus-trap must have at least one container with at least one tabbable node in it at all times
    p focus-trap.esm.js:361
    activate focus-trap.esm.js:644
    useFocusTrap NcModal.js:2
    VueJS 2
vue.runtime.esm.js:1897:4
    VueJS 5
Object { jCal: (3) […], addressbook: {…}, vCard: {…}, conflict: false }
addressbooks.js:420:3
Error: 
    D dist.js:1
    a dist.js:1
    i dist.js:1
    r dist.js:1
    i dist.js:1
    r dist.js:1
    i dist.js:1
    r dist.js:1
    onreadystatechange dist.js:1
addressbooks.js:440:6
    t addressbooks.js:440
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 184 globals.js:60:15
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 80 globals.js:60:15
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 8 globals.js:60:15
No new notification data received NotificationsApp.vue:372
Polling interval updated to 30000 NotificationsApp.vue:404
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 4 globals.js:60:15
No new notification data received NotificationsApp.vue:372
Polling interval updated to 30000 NotificationsApp.vue:404
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 4 globals.js:60:15
No new notification data received NotificationsApp.vue:372
Polling interval updated to 30000 NotificationsApp.vue:404
jQuery is deprecated: The global jQuery is deprecated. It will be removed in a later versions without another warning. Please ship your own. 36 globals.js:60:15

​
7

OK, some deep digging tonight and I solved it. I also found this which I think may be related to everything since I now remember I probably messed up my contacts db in efforts to salvage it the last time it got messed up.

There’s a lot of commands involved here, but in case it happens again. here are the basics:

  1. Fire up a new Nextcloud with the same version as you have on your production (old) NC.
  2. Once up and running, create the users manually from the GUI that you want to import contacts to. This is to get the same names in oc_addressbooks table
  3. Import all the .vcf for every user separately by logging in to their account in GUI on the new server
  4. Now the DB export begins… read below:
  5. Log in to the newly created server via SSH and dump the whole DB
  6. Transfer that DB to the old prod server
  7. Create a new database with a new name but same user as in your old DB.
  8. Import the oc_cards table from the new DB to the old DB. Don’t forget to drop the old one first. Repeat the same thing for oc_cards_properties. In both cases, use CASCADE to get rid of the SEQUENCE
  9. Since both addressbooks and addressbookchanges are populated, we need to hand pick the ID’s we want to export and import. That’s done with a COPY command:

Kellys oc_adressbooks:
Export: COPY (SELECT * FROM oc_addressbooks WHERE id = '5') TO '/tmp/kelly_export.csv';
Import: COPY oc_addressbooks FROM '/tmp/kelly_export.csv';

Everything from oc_adressbookchanges:
Export: COPY (SELECT * FROM oc_addressbookchanges) TO /tmp/addressbookchanges_export.csv';
Import:

  1. First delete all the rows with the same id as in the new table. In my case 1 - 572: delete from oc_addressbookchanges where id between 1 and 572;
  2. Import the whole table with the exported file to the new DB:COPY oc_addressbookchanges FROM '/tmp/addressbookchanges_export.csv'
  3. Now you have handpicked the ids you want to import, and if you’re lucky like in my case, the id on the new server didn’t match the old server.

Everything is back to normal and no more 500 errors. No more notifications from my phone Davx5. Piece of mind that everything is working. :smiley:

2 Likes