Changing a vote option take a lot longer (logged in user vs. user with a link that has no account/login)

[githubcmb]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• I agree to follow Nextcloud's Code of Conduct.

What went wrong, what did you observe?

I am logged in with my user and change an option from "yes" to "maybe" and this takes roughly 1 second or two.
When someone without account, that is not logged in and uses a direct link, changes an option, it takes roughly 8 seconds, so maybe 5 times longer. Problem is 1) takes a lot longer now than before and 2) user does not know that he klicked already and thinks after a few seconds, "haven't clicked, so I click agian"...

What did you expect, how polls should behave instead?

time elapsed for both cases approx. same, so not 6-8 seconds, but maybe that same 1 second or so.

NB: overall performance has significantly decreases lately, unfortunately.

What steps does it need to replay this bug?

1. This poll is reused since beginning. New dates added and old dates deleted. I usually shows the next 4 weeks for something that is always on a Wednesday

2. poll type:root@localhost [owncloud]> select * from oc_polls_polls where id ='1';+----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+
   | id | type | title | description | owner | created | expire | deleted | access | anonymous | allow_maybe | vote_limit | show_results | admin_access | option_limit | allow_comment | hide_booked_up | allow_proposals | use_no | proposals_expire | misc_settings | last_interaction | voting_variant |
   +----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+
   | 1 | datePoll | Mittwoch Training | Mittwochs - XXXXXXXXXXXx | localuser | 1634069437 | 0 | 0 | open | 0 | 1 | 0 | always | 0 | 0 | 0 | 1 | disallow | 1 | 0 | {"autoReminder":false} | 1752817300 | simple |
   +----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+

3. how many votes in the poll? (Are old ones not deleted when old dates are removed?)
   root@localhost [owncloud]> select count() from oc_polls_votes where poll_id ='1';
   +----------+
   | count() |
   +----------+
   | 979 |
   +----------+

root@localhost [owncloud]> select * from oc_polls_preferences where id = '1';
+----+---------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | user_id | timestamp | preferences |
+----+---------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | cmb | 1752820725 | {"calendarPeek":false,"checkCalendars":[],"checkCalendarsHoursBefore":0,"checkCalendarsHoursAfter":0,"defaultViewTextPoll":"table-view","defaultViewDatePoll":"table-view","pollCombo":[],"relevantOffset":30,"useNewPollDialogInNavigation":false,"useNewPollInPollist":false,"useCommentsAlternativeStyling":false,"useAlternativeStyling":false,"verbosePollsList":false,"performanceThreshold":1000} |
+----+---------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Link to poll send to developer directly for testing

Affected polls version

8.1.4

Installation method

Installed/updated using occ

Installation type

Updated from a much older version (more than one major version)

Can you rule out that any extension you use is involved in the issue?

• I have checked all browser extension

Which browser did you use, when experiencing the bug?

• Firefox
• Chrome
• Chromium/Chromium based (i.e. Edge)
• Safari
• Other/Don't know

Other browser

Opera on Macos

Add your browser log here

no errors

Additional client environment information

User that reported it, has a iphone / iOS (newest with safari).
I tested on Opera (link version) and firefox (logged in user) both on Macos

NC version

Nextcloud 30

Other Nextcloud version

No response

PHP engine version

PHP 8.2

Other PHP version

No response

Database engine

MariaDB

Database Engine version or other Database

10.6.22

Which user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other/Don't know

Add your nextcloud server log here

no error occurs (As far as I can see)

Additional environment informations

[root@beastly /usr/local/www/htdocs/nc]# sudo -u www php ./occ status

• installed: true
• version: 30.0.13.1
• versionstring: 30.0.13
• edition:
• maintenance: false
• needsDbUpgrade: false
• productname: Nextcloud
• extendedSupport: false
  [root@beastly /usr/local/www/htdocs/nc]# uname -a
  FreeBSD beastly.cmb.ch 14.2-RELEASE-p1 FreeBSD 14.2-RELEASE-p1 GENERIC amd64
  [root@beastly /usr/local/www/htdocs/nc]# pkg ver -v | grep mari
  mariadb106-client-10.6.22_1 = up-to-date with index
  mariadb106-server-10.6.22_1 = up-to-date with index

Configuration report

[root@beastly /usr/local/www/htdocs/nc]# sudo -u www php ./occ config:list system
{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cmb.ch"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/cmb.ch\/nc",
        "dbtype": "mysql",
        "version": "30.0.13.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "default_phone_region": "CH",
        "mail_smtpmode": "sendmail",
        "forcessl": true,
        "theme": "",
        "maintenance": false,
        "logfile": "\/var\/log\/nextcloud\/nextcloud.log",
        "logfile_audit": "\/var\/log\/nextcloud\/nextcloud.audit.log",
        "loglevel": 1,
        "trashbin_retention_obligation": "auto, 14",
        "log.condition": {
            "users": [
                "cmb"
            ],
            "apps": [
                "files"
            ]
        },
        "updater.release.channel": "stable",
        "filelocking.enabled": true,
        "localstorage.allowsymlinks": true,
        "enable_previews": true,
        "preview_max_x": 384,
        "preview_max_y": 512,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "mysql.utf8mb4": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "maintenance_window_start": 1
    }
}

List of activated Apps

[root@beastly /usr/local/www/htdocs/nc]# sudo -u www php ./occ app:list
Enabled:
  - activity: 3.0.0
  - admin_audit: 1.20.0
  - app_api: 4.0.6
  - bookmarks: 15.1.2
  - calendar: 5.3.6
  - cloud_federation_api: 1.13.0
  - comments: 1.20.1
  - contacts: 7.2.0
  - dashboard: 7.10.0
  - dav: 1.31.1
  - federatedfilesharing: 1.20.0
  - federation: 1.20.0
  - files: 2.2.0
  - files_downloadlimit: 3.0.0
  - files_pdfviewer: 3.0.0
  - files_reminders: 1.3.0
  - files_sharing: 1.22.0
  - files_trashbin: 1.20.1
  - files_versions: 1.23.0
  - firstrunwizard: 3.0.0
  - logreader: 3.0.0
  - lookup_server_connector: 1.18.0
  - nextcloud_announcements: 2.0.0
  - notifications: 3.0.0
  - oauth2: 1.18.1
  - password_policy: 2.0.0
  - photos: 3.0.2
  - polls: 8.1.4
  - privacy: 2.0.0
  - provisioning_api: 1.20.0
  - recommendations: 3.0.0
  - related_resources: 1.5.0
  - serverinfo: 2.0.0
  - settings: 1.13.0
  - sharebymail: 1.20.0
  - support: 2.0.0
  - systemtags: 1.20.0
  - text: 4.1.0
  - theming: 2.6.0
  - twofactor_backupcodes: 1.19.0
  - updatenotification: 1.20.0
  - user_pwauth: 2.8.3
  - user_status: 1.10.0
  - viewer: 3.0.0
  - webhook_listeners: 1.1.0-dev
  - workflowengine: 2.12.0
Disabled:
  - bruteforcesettings: 3.0.0 (installed 2.4.0)
  - circles: 30.0.0 (installed 0.14.2)
  - contactsinteraction: 1.11.0 (installed 1.9.0)
  - encryption: 2.18.0
  - files_external: 1.22.0
  - survey_client: 2.0.0 (installed 1.8.0)
  - suspicious_login: 8.0.0
  - twofactor_nextcloud_notification: 4.0.0
  - twofactor_totp: 12.0.0-dev
  - user_ldap: 1.21.0
  - weather_status: 1.10.0 (installed 1.0.0)

Nextcloud Signing status

No errors have been found.

Additional Information

No response

[githubcmb]

long running query:

root@localhost [owncloud]> SELECT polls_polls.*,
-> MAX(options.timestamp) AS max_date,
-> coalesce(user_shares.type, '') AS user_role,
-> user_shares.locked AS is_current_user_locked,
-> coalesce(user_shares.token, '') AS share_token,
-> group_concat(distinct group_shares.user_id SEPARATOR ",") AS group_shares,
-> group_concat(distinct poll_groups.group_id SEPARATOR ",") AS poll_groups,
-> group_concat(distinct poll_group_shares.type SEPARATOR ",") AS poll_group_user_shares,
-> COUNT(DISTINCT(votes.id)) AS current_user_votes, COUNT(DISTINCT(CASE WHEN votes.vote_answer = 'yes' THEN votes.id END)) AS current_user_votes_yes,
-> COUNT(DISTINCT(CASE WHEN votes.vote_answer = 'no' THEN votes.id END)) AS current_user_votes_no,
-> COUNT(DISTINCT(CASE WHEN votes.vote_answer = 'maybe' THEN votes.id END)) AS current_user_votes_maybe,
-> COUNT(DISTINCT(CASE WHEN vote_options_sub.id is NULL THEN votes.id END)) AS current_user_orphaned_votes,
-> COUNT(DISTINCT(participants.user_id)) AS participants_count
-> FROM oc_polls_polls polls_polls
-> LEFT JOIN oc_polls_options options ON (options.poll_id = polls_polls.id)
-> AND (options.deleted = '0') LEFT JOIN oc_polls_share user_shares ON (user_shares.poll_id = polls_polls.id)
-> AND (user_shares.user_id = 'Luca') AND (user_shares.deleted = '0')
-> LEFT JOIN oc_polls_share group_shares ON (group_shares.poll_id = polls_polls.id)
-> AND (group_shares.type = 'group')
-> AND (group_shares.deleted = '0')
-> LEFT JOIN oc_polls_groups_polls poll_groups ON polls_polls.id = poll_groups.poll_id
-> LEFT JOIN oc_polls_votes votes ON (votes.poll_id = polls_polls.id)
-> AND (votes.user_id = 'Xxxx')
-> LEFT JOIN oc_polls_votes participants ON participants.poll_id = polls_polls.id
-> LEFT JOIN oc_polls_share poll_group_shares ON (poll_group_shares.group_id = poll_groups.group_id)
-> AND (poll_group_shares.deleted = '0')
-> AND (poll_group_shares.user_id = 'Xxxx')
-> LEFT JOIN oc_polls_options vote_options_sub ON (vote_options_sub.poll_id = votes.poll_id)
-> AND (vote_options_sub.poll_option_text = votes.vote_option_text)
-> AND (vote_options_sub.deleted = '0')
-> WHERE polls_polls.id = 1
-> GROUP BY polls_polls.id, user_shares.type, user_shares.locked, user_shares.token;
+----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+------------+-----------+------------------------+------------------+--------------+-------------+------------------------+--------------------+------------------------+-----------------------+--------------------------+-----------------------------+--------------------+
| id | type | title | description | owner | created | expire | deleted | access | anonymous | allow_maybe | vote_limit | show_results | admin_access | option_limit | allow_comment | hide_booked_up | allow_proposals | use_no | proposals_expire | misc_settings | last_interaction | voting_variant | max_date | user_role | is_current_user_locked | share_token | group_shares | poll_groups | poll_group_user_shares | current_user_votes | current_user_votes_yes | current_user_votes_no | current_user_votes_maybe | current_user_orphaned_votes | participants_count |
+----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+------------+-----------+------------------------+------------------+--------------+-------------+------------------------+--------------------+------------------------+-----------------------+--------------------------+-----------------------------+--------------------+
| 1 | datePoll | Mittwoch Training | Mittwochs - XXXXXXXXXXXXXXX | localuser | 1634069437 | 0 | 0 | open | 0 | 1 | 0 | always | 0 | 0 | 0 | 1 | disallow | 1 | 0 | {"autoReminder":false} | 1752839040 | simple | 1756245600 | external | 0 | pU9V9CslwRvmOBYK | NULL | NULL | NULL | 150 | 110 | 39 | 1 | 146 | 15 |
+----+----------+-------------------+---------------------------------------------------+-------+------------+--------+---------+--------+-----------+-------------+------------+--------------+--------------+--------------+---------------+----------------+-----------------+--------+------------------+------------------------+------------------+----------------+------------+-----------+------------------------+------------------+--------------+-------------+------------------------+--------------------+------------------------+-----------------------+--------------------------+-----------------------------+--------------------+
1 row in set (6.092 sec)

[githubcmb]

with:
table rows
oc_polls_polls 1
oc_polls_options 4
oc_polls_votes 979
oc_polls_polls 1
oc_polls_share 24

[dartcafe]

It turned out that lot of orphaned votes have an impact on the performance of poll loading.
The use case describes a rolling usage of one single poll for a booking mechanism. Old entries get discarded once they are outdated and new entries get added.

This way a lot of orphaned votes cause long running SQL queries.

As a first action an option to delete orphaned votes was added to the poll configuration. see #4176

Following notes are mainly for dev, to not forget the considerations about further optimization.

Further research is planned. Possible changes:

• Replacing left joins with normal joins (which I believe have a better performance)
• add a cron job to delete orphaned votes together with their corresponding votes (deleted options get soft deleted and removed from the database after one day)
  • extend the deletion threshold and remove old votes immediate
• delete votes in date polls depending on if the option date is a past date

Explanation, why votes are not deleted immediately, when the option got removed:

An option may be removed accidentally. In this case the option can be restored and the foreign votes are still valid. This should prevent changing outcomes of the poll in case this happens.

[dartcafe]

Closing this one. The main problem is covered here #4186. Feel free to join there, if the problem still exists with 8.2.0-rc.2 or the upcoming release 8.2.0. Thanks for the high quality information, which helped a lot to track the things down.

[github-actions]

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.