Today we found the poll application on one of our nextcloud instance to be completely broken. Attempting to create a new poll or add date options to an existing one resulted in HTTP 500 errors. In nextcloud logs, there were errors like:<\/p>\n
\r\nduplicate key value violates unique constraint \"oc_polls_polls_pkey\"\r\nDETAIL: Key (id)=(9) already exists.\r\n<\/pre>\nor when adding options to an existing poll:<\/p>\n
\r\nOCP\\AppFramework\\Db\\DoesNotExistException: Did expect one result but found none\r\n<\/pre>\nAs a clue, this happened after a migration of the nextcloud DB from MySQL to PostgreSQL. My guess is that the PostgreSQL sequences were not updated correctly during the migration. So it was not possible to create either new rows for options in a poll or even a poll itself after a certain point, when the auto generated ID conflicted with an existing ID in the tables, which is the unique constraint violation you see in the error.<\/p>\n
So the behavior was, we could add some options and some polls, but as soon as the sequence catches up to an existing ID, any INSERT fails with a primary key conflict. In our case only the polls tables seemed to be affected, but this could happen on other tables too because of the migration.<\/p>\n
The fix was to reset the sequences to the current maximum ID for each table of the polls application:<\/p>\n
\r\nSELECT setval('oc_polls_polls_id_seq', (SELECT MAX(id) FROM oc_polls_polls));\r\nSELECT setval('oc_polls_options_id_seq', (SELECT MAX(id) FROM oc_polls_options));\r\nSELECT setval('oc_polls_votes_id_seq', (SELECT MAX(id) FROM oc_polls_votes));\r\nSELECT setval('oc_polls_share_id_seq', (SELECT MAX(id) FROM oc_polls_share));\r\nSELECT setval('oc_polls_comments_id_seq', (SELECT MAX(id) FROM oc_polls_comments));\r\nSELECT setval('oc_polls_log_id_seq', (SELECT MAX(id) FROM oc_polls_log));\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Today we found the poll application on one of our nextcloud instance to be completely broken. Attempting to create a new poll or add date options to an existing one resulted in HTTP 500 errors. In nextcloud logs, there were … Continue reading