Crash Nextcloud!!!!

[poltrian]

Description
A clear and concise description of what the bug is.

Found these "An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '434501-Peter' for key 'names_recipe_idx'"

I share with group folder. How can i fix it? I guess same problem as 9.0?

[christianlupus]

Duplicate of #762

[christianlupus]

@poltrian sorry, this was an accident with the mobile phone.

If you have not yet taken steps, please wait a few minutes for me to explain. You can however safely disable the app to have a mostly running NC instance again.

[poltrian]

I have already deactivated the app and Nextcloud is running again. What must be changed in the database for an installation? For me it is also the primary keys by sharing the recipes via the Group Folder app. Somehow we have to get rid of them without losing data. Despite the problems, I would like to thank you guys for your great work. Coobok is one of the best Nexcloud apps

[christianlupus]

Please provide the output of occ migrations:status cookbook. This should show what step the problem was triggered within.

Do you have access to the database itself? Can you run a few SQL commands, to investigate the state of the database a bit further. Most important would be

SELECT * FROM oc_cookbook_names WHERE recipe_id=434501;

Also the following would be interesting:

SELECT recipe_id, user_id, COUNT(recipe_id) AS cnt FROM oc_cookbook_names GROUP BY recipe_id, user_id HAVING cnt>1;

Let's keep it with that. Further investigation will eventually follow.

In fact, the database is (current state and only for the cookbook app) only a cache of the files on the storage. So you could wipe the tables and everything can safely be rebuild.
However, this will destroy the evidence of the problem. As I thought I spotted the point of the problem and released the 0.9.1 yesterday. I was a bit surprised that there is still a problem. Therefore, if you wanted to downgrade and rebuild the database, please make a backup (SQL dump) at least of the cookbook tables for later investigation.

PS: I have to finish something for work now. Could be the case that I can only return later today or tomorrow.

[poltrian]

`
Output from SQL command SELECT * FROM oc_cookbook_names WHERE recipe_id=434501; ..

recipe_id	name	user_id
434501	Philadelphia Torte	Peter
434501	Philadelphia Torte	Peter
434501	Philadelphia Torte	Laura

Output from SQL command SELECT recipe_id, user_id, COUNT(recipe_id) AS cnt FROM oc_cookbook_names GROUP BY recipe_id, user_id HAVING cnt>1; ..

recipe_id	user_id	cnt
259665	Laura	2
262895	Laura	2
434501	Peter	2

    >> App:                                                cookbook
    >> Version Table Name:                                 oc_migrations
    >> Migrations Namespace:                               OCA\Cookbook\Migration
    >> Migrations Directory:                               /var/www/nextcloud/apps/cookbook/lib/Migration
    >> Previous Version:                                   Already at first migration step
    >> Current Version:                                    000000Date20210427082010
    >> Next Version:                                       Already at latest migration step
    >> Latest Version:                                     000000Date20200315121603
    >> Executed Migrations:                                5
    >> Executed Unavailable Migrations:                    5
    >> Available Migrations:                               4
    >> New Migrations:                                     4
    >> Pending Migrations:                                 None

Laura is a defunct user. Peter is no longer a member of the "Cookbook" group in the Group Folder. I think that's where we found our problem. If I were to remove these two from the database entries, should the problem be resolved? I think this can happen again and again with long-term Nexcloud installations. Maybe you can check it out and clean it up?

[christianlupus]

Yes, that seems to be the culprit. I think that in older installations there was something missing (fixed bug or a check) that avoided a recipe to be inserted multiple times. Normally, that should be automatically cleaned up from regular scripts but these are running only when a user accesses the cookbook app (background action was not implemented).

We will have to check if it is possible to check if a certain user has ceased. Also, the group folder issue should be able to be tackled but was not anticipated (at least for me). We will have to work this out and get the DB cleaned out better in an upcoming release to support the long-running instances.

[MrHighVoltage]

Maybe i have a small update with a workaround for this:
I removed the app with "occ app:remove cookbook" (using the command line interface) and reinstalled it with "occ app:install cookbook". Simply disable and enable didn't work because of this duplicate primary key issue.
It is now working again, all the recipes are back again. But before doing that procedure, i copied the recipes directory as a backup.

EDIT: i did not test the update to Nextcloud 21.0.3. I am running 21.0.2.
Further EDIT: I was also able to upgrade to 21.0.3! Works fine.