search

nextcloud / polls

🗳️ Polls app for Nextcloud
https://apps.nextcloud.com/apps/polls
GNU Affero General Public License v3.0
255 stars 73 forks source link

After Nextcloud or polls update, activating polls failed (Integrity constraint violation: 1062 Duplicate entry) #2884

Closed BurtGummer closed 1 year ago

BurtGummer commented 1 year ago

⚠️ This issue respects the following points: ⚠️

What went wrong, what did you observe?

I upgrade Nextcloud from 24.0.12 to 25.0.6 on a Debian 11 with MySQL, everything is OK. But polls (5.0.4) was disabled, if I enable it, this error occure:

[settings] Fehler: Exception: Database error when running migration 040102Date20230123072601 for app polls
An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2--0' for key 'UNIQ_options' at <<closure>>

0. /var/www/html/nextcloud/lib/private/Installer.php line 153
   OC\DB\MigrationService->migrate()
1. /var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php line 451
   OC\Installer->installApp()
2. /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 225
   OCA\Settings\Controller\AppSettingsController->enableApps()
3. /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 133
   OC\AppFramework\Http\Dispatcher->executeController()
4. /var/www/html/nextcloud/lib/private/AppFramework/App.php line 172
   OC\AppFramework\Http\Dispatcher->dispatch()
5. /var/www/html/nextcloud/lib/private/Route/Router.php line 298
   OC\AppFramework\App::main()
6. /var/www/html/nextcloud/lib/base.php line 1048
   OC\Route\Router->match()
7. /var/www/html/nextcloud/index.php line 36
   OC::handleRequest()

POST /cloud/settings/apps/enable
from 80.xxx.xxx.xxx by BurtGummer at 2023-04-27T12:03:25+02:00

I try to disable/enable it, it seems its the same problem from this issue: https://github.com/nextcloud/polls/issues/2853 but there is no change.

What did you expect, how polls should behave instead?

Just enable the app polls:

root@server /var/www/html/nextcloud # sudo -u www-data php occ app:update polls root@server /var/www/html/nextcloud # cd /var/www/html/nextcloud/ ; sudo -u www-data php occ app:enable polls Database error when running migration 040102Date20230123072601 for app polls An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2--0' for key 'UNIQ_options' root@server /var/www/html/nextcloud #

What steps does it need to replay this bug?

  1. before the update, polls works great.

Installation method

Installed/updated from the appstore (Apps section of your site)

Installation type

Updated from a minor version within same major version (i.e. 4.0.0 to 4.1.1)

Affected polls version

5.0.4

Which browser did you use, when experiencing the bug?

Which System did you use, when experiencing the bug?

Nextcloud 25 (Nextcloud Hub 3)

PHP engine version

PHP 7.3

Database engine

MySQL

Which user-backends are you using?

Configuration report

root@server /var/www/html/nextcloud # sudo -u www-data php occ config:list system
{
    "system": {
        "updatechecker": false,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "oc.example.org"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/oc.example.org\/cloud\/",
        "dbtype": "mysql",
        "version": "25.0.6.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "Europe\/Berlin",
        "installed": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "sendmail",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "loglevel": 3,
        "lost_password_link": "disabled",
        "default_phone_region": "DE",
        "maintenance": false,
        "ldapIgnoreNamingRules": false,
        "ldapUserCleanupInterval": 60,
        "htaccess.RewriteBase": "\/cloud",
        "updater.release.channel": "stable",
        "appstore.experimental.enabled": false,
        "overwriteprotocol": "https",
        "forcessl": true,
        "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory",
        "mail_sendmailmode": "smtp",
        "mysql.utf8mb4": true
    }
}

root@server /var/www/html/nextcloud # sudo -u www-data php occ app:list
Enabled:
  - activity: 2.17.0
  - admin_audit: 1.15.0
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contactsinteraction: 1.6.0
  - dav: 1.24.0
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_pdfviewer: 2.6.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - impersonate: 1.12.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - nextcloud_announcements: 1.14.0
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.1
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - recommendations: 1.4.0
  - related_resources: 1.0.4
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - support: 1.8.0
  - survey_client: 1.13.0
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - updatenotification: 1.15.0
  - user_ldap: 1.15.0
  - viewer: 1.9.0
  - workflowengine: 2.7.0
Disabled:
  - bruteforcesettings: 1.1.0
  - dashboard: 7.0.0
  - encryption
  - files_external
  - files_rightclick: 0.8.4
  - keeweb: 0.6.12
  - polls: 3.8.4
  - suspicious_login
  - systemtags: 1.15.0
  - twofactor_totp
  - user_status: 1.0.1
  - weather_status: 1.2.0
root@server /var/www/html/nextcloud
dartcafe commented 1 year ago

I don't understand the behavior and cannot reproduce this problem. On enabling of Polls the repair steps are beeing executed and all illegal duplicates get removed before creating the indexes.

Please try the following steps until the app is enabled properly

  1. Enable the app again.
  2. Remove polls and reinstall it via app store
  3. check your oc_polls_options for duplicates (poll_id and poll_option_text)
bencomp commented 1 year ago

I ran into the same issue and feel a little stuck. It looks like https://github.com/nextcloud/polls/blob/787e3e34e4c94ebcc597003c11a28eb0d246b222/lib/Migration/TableSchema.php#L59 shows that the index requires a unique combination of ['poll_id', 'poll_option_hash', 'timestamp'], not poll_id and poll_option_text. This was changed in #2777.

In my oc_polls_options the poll_option_hash column is empty and on many rows the timestamp column is set to 0. I don't know if this happened during the migration or if (some of) these values were already like this. With multiple options for different polls the DBMS is correctly seeing duplicate combinations.

Edit: continuing looking for the cause of this error, I see that during the upgrade to Nextcloud 25 (from the latest 24), the poll_option_hash column was created and most other columns were converted from integer to bigint. But this migration did not fill the hash column apparently.

bencomp commented 1 year ago

A look at the backup of the database indicates that the cells in the timestamp column were 0 before the migration. So to fix the table contents, setting the timestamp to a reasonable unique value for each option may solve this breaking error.

dartcafe commented 1 year ago

Thanks for your analysis. This helps. I will come back on this later today.

dartcafe commented 1 year ago

In my oc_polls_options the poll_option_hash column is empty

This is strange, because all hashes should get updated in a repairstep right after executing the migration. I guess, that the problem lies here.

on many rows the timestamp column is set to 0.

This is right for options of a text poll.

The request for checking poll_id and poll_option_text is because it is readable. The hash is calculated from poll_id and poll_option_text.

pReya commented 1 year ago

Same problem here. After NC update, the App was disabled. Can't enable it again, because it will fail with this error. Also, removing and downloading it again will yield the same result.

[settings] Error: Exception: Database error when running migration 040102Date20230123072601 for app polls
An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1--0' for key 'UNIQ_options' at <<closure>>

0. /var/www/virtual/wwnc/html/lib/private/Installer.php line 153
   OC\DB\MigrationService->migrate("latest", false)
1. /var/www/virtual/wwnc/html/apps/settings/lib/Controller/AppSettingsController.php line 451
   OC\Installer->installApp("polls")
2. /var/www/virtual/wwnc/html/lib/private/AppFramework/Http/Dispatcher.php line 230
   OCA\Settings\Controller\AppSettingsController->enableApps(["polls"], [])
3. /var/www/virtual/wwnc/html/lib/private/AppFramework/Http/Dispatcher.php line 137
   OC\AppFramework\Http\Dispatcher->executeController(["OCA\\Settings\ ... "], "enableApps")
4. /var/www/virtual/wwnc/html/lib/private/AppFramework/App.php line 183
   OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Settings\ ... "], "enableApps")
5. /var/www/virtual/wwnc/html/lib/private/Route/Router.php line 315
   OC\AppFramework\App::main("OCA\\Settings\\ ... r", "enableApps", ["OC\\AppFramewo ... "], ["settings.AppSettings.enableApps"])
6. /var/www/virtual/wwnc/html/lib/base.php line 1056
   OC\Route\Router->match("/settings/apps/enable")
7. /var/www/virtual/wwnc/html/index.php line 36
   OC::handleRequest()

In my Database oc_polls_options the poll_option_hash is 0 for all entries.

EDIT: I've tried to solve this via running this SQL query: 

UPDATE `oc_polls_options` SET poll_option_hash = MD5(CONCAT(poll_id,poll_option_text,timestamp)) WHERE poll_option_hash=0;

But the problem also seems to affect other tables, because now I get a similar error for another table:

Exception: Database error when running migration 040102Date20230123072601 for app polls An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-moritz-' for key 'UNIQ_votes'

EDIT2: After also running this SQL statement:

UPDATE `oc_polls_votes` SET vote_option_hash = MD5(CONCAT(poll_id,user_id,vote_option_text)) WHERE vote_option_hash=0;

Now everything seems like it's working again. So as a hotfix (at your own risk), try running these 2 SQL statements to create all the necessary hashes to be able to fulfill the unique constraints.

dartcafe commented 1 year ago

OMG. 🙈 I did not recognize it came from the migration routine. Fix will come along shortly with the next version.

p-sims commented 1 year ago

This issue seems to have re-appear in a slightly different form...

I've just updated from NC24 to NC25.0.10:

From looking at discussion above this looks like similar problem relating to poll_option_hash. My guess is that the migration fix added to 5.0.5 added as result of discussion above is failing because neither hash is present or column is null...

These are Nextcloud error log enteries. I'm going to try and have a look at the table in the database ...

p-sims commented 1 year ago

Just logged into the database, and it would appear that 'poll_option_hash' column doesn't exist...

Screenshot 2023-09-08 at 19 50 51

Screenshot 2023-09-08 at 19 56 28

So I can't try setting it with Hash manually. If anyone has any advise that would be much appreciated.

dartcafe commented 1 year ago

First of all: Please always open a new issue and try to avoid sharing new observations in a closed issue.

Try to fix it with occ polls:db:rebuild

p-sims commented 1 year ago

Thanks @dartcafe -- I've created new issue https://github.com/nextcloud/polls/issues/3067 for this and tried occ polls:db:rebuild but is say's command not found in name-space (possibility because polls app isn't enabled?)

github-actions[bot] commented 4 months ago

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.