Contacts Sync DavDroid - DBAL Error and HTTP 500 - after update NC17

[nexxy]

Hi nextcloud users,
…
Issue:
My Sync of Calendar and Contacts is not working anymore => contacts and calendar is not synced.
DavDroid/DAVx gives HTTP 500 error.

Nextcloud gives a very long FATAL ERROR “Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_activity
[…]
Verursacht vonDoctrine\DBAL\Driver\PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’
[…]”
…
What has changed:
I just upgraded from NC 15 to NC 17 (over NC 16).
I had issues in NC 15 with file locking and disabled file locking + deleted all entries in mysql “file_lock” and “filecache” (recommendations in this forum). After that I did occ commands to repair filesystem and filecache. After login to NC 15 worked again I enabled file locking again and updated NC. In NC 17 all files are there. Only Sync is not working.

One thing got my eye: In “oc_cards” all the “uri” have a domain ending “@ssl-account.com” that was an old way to use https at my webhost. The url is not in use anymore. I think it has to do with well-known uri. Maybe this has some impact?
…

Any ideas how to solve this?
Thanks in advance.

edit: if I disable Activity App sync works again. But the app and NC17 is completely new. Any ideas?
…

[j-ed]

@nexxy

But the app and OC17 is completely new. Any ideas?
…

… but the database content is the same

@nexxy

Verursacht vonDoctrine\DBAL\Driver\PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’

I would assume, that you need to fix the database error to get running again. See the following link for detailed information how to do it:

MySQL 1062 - Duplicate entry '0' for key 'PRIMARY'

[nexxy]

Hi j-ed, thanks for your answer! That looks promising.

I have to say I do not exactly understand what to do, as the table oc_activity already exists (not as in the article).

I assume I have to do the following?:

1. ALTER TABLE oc_activity DROP PRIMARY KEY
2. ALTER TABLE oc_activity ADD id INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

Or do I need a foreign key? (“SHOW CREATE TABLE oc_activity” gives no constraints)
Thanks in advance!

PS: Exactly, the database is several years old (owncloud) and maybe I should find a way to make a fresh installation and transfer all the user data. Not easy as far as I know.

[j-ed]

I think that would be the right command. You can also try to identify the duplicate entry “0” by executing the following command and delete on of it:

 SELECT activity_id,user,app FROM oc_activity WHERE `activity_id` = "0";

[nexxy]

THANKS! As it seems at the moment it was enough to delete the entry ‘0’ from oc_activity.

edit: Ah but any new “Activity” produces a key ‘0’ again. So I have to give it the right key, which is 83470 at the moment (counting down from 84159!).
Maybe I just delete all activity and everything is done.

solution: I need to set the primary key to 83469 and auto increment by -1. I will try your hint to get this working. will report back.

No chance!
I tried:

1. alter table oc_activity modify column activity_id bigint(20) unsigned Primary KEY AUTO_INCREMENT = 83469
2. alter table oc_activity modify column activity_id bigint(20) unsigned AUTO_INCREMENT = 83469
3. ALTER TABLE oc_activity modify activity_id INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT = 83469
4. ALTER TABLE oc_activity modify activity_id INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

The SQL always gives “wrong syntax”, but I read it everywhere on stackoverflow.

I’m missing sql knowledge, does anyone have an idea how to set primary key in column activity_id to a specific value and autoincrement by -1?

PS: My oc_activity table: