Update Talk 8.0.6 to 8.0.7 in Nextcloud 18.03 failed

ℹ️ Support
,
1

Has anyone seen this before and possibly a solution:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘CREATE UNIQUE INDEX tp_ident ON oc_talk_participants (room_id, user_id, session_id)’: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3-CA-0’ for key ‘oc_talk_participants.tp_ident’
Detailed logs

Preparing update

Set log level to debug

Turned on maintenance mode

Repair step: Repair MySQL collation

Repair info: All tables already have the correct collation -> nothing to do

Repair step: Repair SQLite autoincrement

Repair step: Copy data from accounts table when migrating from ownCloud

Repair step: Drop account terms table when migrating from ownCloud

Updating database schema

Updated database

Repair step: Fix the namespace in database tables

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘CREATE UNIQUE INDEX tp_ident ON oc_talk_participants (room_id, user_id, session_id)’: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3-CA-0’ for key ‘oc_talk_participants.tp_ident’

2 Likes
2

Same error on my instance. Had to disable the app from occ, before Nextcloud was usable again.

I tried removing and reinstalling the app, but got an error. Hope its fixed soon.

3

I also ran into this issue. It looks like at some point rows were created in the *_talk_participants table with duplicate room_id + user_id + session_id. Because of this, the creation of the desired index on the table fails. If you are able to run SQL statements in your database, try this to locate which rows are duplicates:
SELECT count(*), user_id, room_id, session_id FROM oc_talk_participants GROUP BY user_id, room_id, session_id;
Anything with a number larger than 1 in the first column will break the update. AT YOUR OWN RISK - BACKUP THE DB FIRST, you will need to go in and run a SQL delete with a WHERE clause specifying the offending user_id/room_id/session_id combination with a LIMIT of 1 (assuming there were two duplicate rows). Then try the update again.

4

I have the same Issue. I am unable to activate Nextcloud Talk due to this error.

5

Had the same issue and did solve it but probably not the way you want. As the Talk upgrade seems wanting to create an index with fields making it to big a size, i went for the solution to somehow get past the upgrade fail and then delete Talk.

Having control over the database, i just logged in and created the index it complains about as a dummy index. Once this was done I logged into the docker container as www-data and did run the ‘occ app:update’ to let it finish whatever update it was doing. Once done, I could log into nextcloud again and there within I delete the Talk plugin.

6

Thanks for your help @dec0de,
I just found a solution, too and it worked out quite perfectly for my case, using mysql, Ubuntu 18 and Nextcloud 18.04.

Just follow those instructions:

  1. Go to mysql and enrol as nextcloud
    sudo mysql -u root -p
    use nextcloud
  2. type in following lines
    CREATE TABLE oc_talk_participants_temp LIKE oc_talk_participants;
    CREATE UNIQUE INDEX tp_ident ON oc_talk_participants_temp (room_id, user_id, session_id);
    INSERT IGNORE INTO oc_talk_participants_temp SELECT * FROM oc_talk_participants;
    DROP TABLE oc_talk_participants;
    ALTER TABLE oc_talk_participants_temp RENAME TO oc_talk_participants;
  3. exit mysql
2 Likes
7

Hello, in my case where I adopted the suggestion of encoding the database to utf8mb4_bin, I solved the problem by reducing the size of ‘user_id’ and ‘session_id’ which were both 255 long before creating the index.
ALTER TABLE oc_talk_participants CHANGE user_id user_id VARCHAR(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;
ALTER TABLE oc_talk_participants CHANGE session_id session_id VARCHAR(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;
And then I could go on the upgrade process.