server
server copied to clipboard
nextcloud
Hundreds of odd oc_authtoken entries for a user slow down performance
This is a summary of https://help.nextcloud.com/t/hundreds-of-odd-oc-authtoken-entries-slow-down-performance-for-a-user-bug-report/117742
Expected behaviour
1 oc_authtoken per user+device
$ curl -s -X PROPFIND "https://SERVER/remote.php/webdav/Handy/DCIM/Camera/IMG_2021-0602_202145.jpg" -u user2 → 1-2 seconds
Actual behaviour
hundreds of oc_authtoken entries: $ mysql mycloud -B -e "select count(*) from oc_authtoken where uid='andy'" count(*) 167
$ curl -X PROPFIND "https://SERVER/remote.php/webdav/Handy/DCIM/Camera/IMG_20210602_223758.jpg" -u andy → between 12-17 seconds
After "DELETE * from oc_authtoken where uid=‘andy’" the server responds quick again. The curl command needs less than a second.
But this was not a permanent solution. 157 new(!) entries were back the next day.
Here is just a short part of it:
Questions/Doubts
- Where did all these extra DB lines come from?
- Why do they reappear after a while
- Why did they have no effect until the update to NC 21?
Server configuration
Operating system: 4.19.0-16-amd64 #1 SMP Debian 4.19.181-1 (2021-03-19) x86_64 GNU/Linux
Web server: Apache 2.4.38-3+deb10u4
Database: mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
PHP version: PHP version (eg, 7.4): 2:7.3+69
Nextcloud version: (see Nextcloud admin page) 21.0.2.1
Updated from an older Nextcloud/ownCloud or fresh install: latest v20
Where did you install Nextcloud from: with the internal NC updater
Signing status:
Signing status
Login as admin user into your Nextcloud and access
http://example.com/index.php/settings/integrity/failed
paste the results here.
No errors have been found.
List of activated apps:
App list
Enabled:
- accessibility: 1.7.0
- activity: 2.14.3
- admin_audit: 1.11.0
- apporder: 0.12.0
- audioplayer: 3.1.0
- bbb: 1.4.1
- bookmarks: 4.2.2
- bruteforcesettings: 2.2.0
- calendar: 2.2.2
- carnet: 0.24.1
- cloud_federation_api: 1.4.0
- cms_pico: 1.0.15
- comments: 1.11.0
- contacts: 3.5.1
- contactsinteraction: 1.2.0
- dashboard: 7.1.0
- data_request: 1.8.0
- dav: 1.17.1
- deck: 1.4.2
- drawio: 1.0.0
- federatedfilesharing: 1.11.0
- federation: 1.11.0
- files: 1.16.0
- files_external: 1.12.0
- files_markdown: 2.3.3
- files_pdfviewer: 2.1.0
- files_retention: 1.10.1
- files_rightclick: 1.0.0
- files_sharing: 1.13.1
- files_trashbin: 1.11.0
- files_versions: 1.14.0
- files_videoplayer: 1.10.0
- firstrunwizard: 2.10.0
- forms: 2.2.4
- gpxmotion: 0.1.0
- gpxpod: 4.2.8
- impersonate: 1.8.0
- integration_google: 1.0.2
- integration_whiteboard: 0.0.14
- integration_zammad: 1.0.1
- keeweb: 0.6.5
- logreader: 2.6.0
- lookup_server_connector: 1.9.0
- mail: 1.9.5
- nextcloud_announcements: 1.10.0
- notes: 4.0.4
- notifications: 2.9.0
- oauth2: 1.9.0
- openhab: 0.9.5
- password_policy: 1.11.0
- photos: 1.3.0
- privacy: 1.5.0
- provisioning_api: 1.11.0
- rainloop: 7.1.2
- recommendations: 1.0.0
- serverinfo: 1.11.0
- settings: 1.3.0
- sharebymail: 1.11.0
- socialsharing_email: 2.2.0
- spreed: 11.2.2
- support: 1.4.0
- survey_client: 1.9.0
- systemtags: 1.11.0
- tasks: 0.13.6
- text: 3.2.0
- theming: 1.12.0
- twofactor_backupcodes: 1.10.0
- updatenotification: 1.11.0
- user_status: 1.1.1
- user_usage_report: 1.5.0
- viewer: 1.5.0
- weather_status: 1.1.0
- workflowengine: 2.3.0
Disabled:
- encryption
- passwords
- user_ldap
Nextcloud configuration:
Config report
{
"system": {
"passwordsalt": "***REMOVED SENSITIVE VALUE***",
"datadirectory": "***REMOVED SENSITIVE VALUE***",
"dbtype": "mysql",
"version": "21.0.2.1",
"dbname": "***REMOVED SENSITIVE VALUE***",
"dbhost": "***REMOVED SENSITIVE VALUE***",
"dbtableprefix": "oc_",
"dbuser": "***REMOVED SENSITIVE VALUE***",
"dbpassword": "***REMOVED SENSITIVE VALUE***",
"installed": true,
"instanceid": "***REMOVED SENSITIVE VALUE***",
"maintenance": false,
"loglevel": 2,
"theme": "",
"trusted_domains": [
"cloud.mydomain.de",
],
"share_folder": "\/Shared",
"defaultapp": "calendar",
"trashbin_retention_obligation": "auto, 14",
"versions_retention_obligation": "auto, 14",
"default_language": "en",
"default_phone_region": "DE",
"secret": "***REMOVED SENSITIVE VALUE***",
"memcache.local": "\\OC\\Memcache\\APCu",
"mail_domain": "***REMOVED SENSITIVE VALUE***",
"mail_smtpmode": "smtp",
"mail_from_address": "***REMOVED SENSITIVE VALUE***",
"updater.release.channel": "stable",
"overwrite.cli.url": "https:\/\/cloud.mydomain.de",
"blacklisted_files": [
"._*",
".DS_Store",
".DS_STORE",
".ds_store"
],
"integrity.check.disabled": false,
"mysql.utf8mb4": true,
"mail_smtpauthtype": "LOGIN",
"mail_sendmailmode": "smtp",
"mail_smtphost": "***REMOVED SENSITIVE VALUE***",
"mail_smtpport": "25",
"mail_smtpauth": 1,
"mail_smtpsecure": "tls",
"mail_smtpname": "***REMOVED SENSITIVE VALUE***",
"mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
"app_install_overwrite": [
"apporder",
"calendar",
"bookmarks"
],
"has_rebuilt_cache": true,
"encryption.legacy_format_support": false,
"encryption.key_storage_migrated": false
}
}
Are you using external storage, if yes which one: local/smb/sftp/... No
Are you using encryption: yes/no No
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/... No
Client configuration
irrelevant
Logs
Web server error log
Web server error log
(only irrelevant lines like:)
[Tue Jun 22 16:57:47.274268 2021] [access_compat:error] [pid 32647] [client 62.216.xx.yy:64788] AH01797: client denied by server configuration: /var/www/nextcloud/config
Nextcloud log (data/nextcloud.log)
Nextcloud log
{"reqId":"YNIMvrdbUoVOcoItzNhK1QAAAAw","level":3,"time":"2021-06-22T16:15:59+00:00","remoteAddr":"138.246.3.189","user":"andy","app":"PHP","method":"PROPFIND","url":"/remote.php/dav/files/andy/","message":"Module 'mbstring' already loaded at Unknown#0","userAgent":"Mozilla/5.0 (Linux) mirall/3.2.2-20210531.142805.04afaa1fe-1.0~focal1 (Nextcloud, ubuntu-5.4.0-74-generic ClientArchitecture: x86_64 OsArchitecture: x86_64)","version":"21.0.2.1"}
The output of your Nextcloud log in Admin > Logging:
Error PHP Module 'mbstring' already loaded at Unknown#0
(shows up every time when I call curl)
It's actually getting exponentially worse with the number of tokens.
Yes, definitely. I'd have to delete the tokens every two days. So, you are experiencing this also?
Yes, running every 5 minutes.
But I think I have a new clue: I compared the "name" field of the oc_authtoken lines and the majority of the entries looks like this:
Mozilla/5.0 (Linux) mirall/3.2.2-20210531.142805.04afaa1fe-1.0~focal1 (nextcloudcmd, ubuntu-5.4.0-73-generic ClientArchitecture: x86_64 OsArchitecture: x86_64)
or
CalDAV-Sync/0.4.32 (Acer; a500_emea_de; Android 4.0.3; de_DE; org.dmfs.caldav.lib/748; like iOS/5.0.1 (9A405) dataaccessd/1.0)
I do have nextcloudcmd running every 15 minutes on an ubuntu server. And I have a pretty old Acer A500 Android (version 4.0.3) tablet synching.
Could it be that these two devices are creating the multiple new entries? Then the problem must be a combination of NC 21 with nextcloudcmd and with (an old version of) the Android app "CardDAV Sync".
So, you are experiencing this also?
Yes, but I did it on purpose by connecting about 800 devices to the same account to see if that was a working option if creation of separate accounts was not possible.
I can reproduce the problem with nextcloudcmd. Every time I run it a new token is added:
$ /usr/bin/nextcloudcmd --user andy -h -n --non-interactive /PATH-TO-ownCloud-DIR https://MYCLOUDSERVER.de/
# mysql clouddb -B -e "select name from oc_authtoken where name like '%nextcloudcmd%'"
Mozilla/5.0 (Linux) mirall/3.2.2-20210531.142805.04afaa1fe-1.0~focal1 (nextcloudcmd, ubuntu-5.4.0-73-generic ClientArchitecture: x86_64 OsArchitecture: x86_64)
Mozilla/5.0 (Linux) mirall/3.2.2-20210531.142805.04afaa1fe-1.0~focal1 (nextcloudcmd, ubuntu-5.4.0-73-generic ClientArchitecture: x86_64 OsArchitecture: x86_64)
Good idea.
But I'm having trouble authenticating for nextcloudcmd with an app-token.
Maybe I'm misunderstanding this concept? I created a new app-token "ncc" and then ran:
$ nextcloudcmd --user andy --password the-just-created-token-passwd ...
I cannot even log into the web UI with this token password. Tried it with different users and different NC servers. Where is my mistake in thinking?
It should work like this: https://github.com/nextcloud/desktop/issues/1779#issuecomment-619388046
Thanks, that's how I did it. :-)
But I found the cause why it didn't work: bruteforcesettings kicked in. I had to whitelist the IP of my test computer. Now nextcloudcmd runs fine with an app-token AND it doesn't create extra oc_authtoken at every run anymore.
So, thanks, this is a good workaround for the problem which I still believe is a bug in NC 21.
Please split this issue: -- Tokens can multiply like rabbits -- Too many tokens slow down authentication exponentially
Adding the feature request for nuking all existing tokens at ones was a great idea.
This is the "Nextcloud is slowing down exponentially with the number of tokens" thread, tight? Maybe a "Tokens are replicating like rabbits on drugs due to defective clients" would be a separate topic. "Add a button to delete more than one/all tokens" would be a third.
Hi, I currently have a similar problem also discussed here
In my case it seems that the cron.php is not working correctly. Calling cron.php returns success but does not delete the old sessions from the oc_authtoken
This behavior leads to the same problem described by @raid1 first.
Hi,
I had the same problem. Hundreds of oc_authtokens have polluted the database and caused significant delays. I have now manually cleared it, but how can this be avoided?
but how can this be avoided?
Usually by using an app password for caldav/carddav.
Usually by using an app password for caldav/carddav.
~~But do I understand it correctly that oc_authtoken entries should be removed after session_lifetime (defaulting to one day)? On my server there are oc_authtokens since 2018 (although cronjob is running regularly).~~
Edit: the old tokens have type=1. Tokens with type=0 seem to be deleted by the cronjob:
2021-09-28T09:21:39.214204Z 8750 Query DELETE FROM `oc_authtoken` WHERE (`last_activity` < 1632734499) AND (`type` = 0) AND (`remember` = 0) AND (`version` = 1)
2021-09-28T09:21:39.216259Z 8750 Query DELETE FROM `oc_authtoken` WHERE (`last_activity` < 1631524899) AND (`type` = 0) AND (`remember` = 1) AND (`version` = 1)
2021-09-28T09:21:39.218310Z 8750 Query DELETE FROM `oc_authtoken` WHERE (`last_activity` < 1632734499) AND (`type` = 0) AND (`remember` = 0) AND (`version` = 2)
2021-09-28T09:21:39.220046Z 8750 Query DELETE FROM `oc_authtoken` WHERE (`last_activity` < 1631524899) AND (`type` = 0) AND (`remember` = 1) AND (`version` = 2)
Seems also be related to https://github.com/nextcloud/server/issues/19247
some times the cron.php script does not work well, especially if you have an old often upgraded installation.
try running the script on the command line sudo -u www-data php cron.php in your nextcloud directory.
If it throws an error with something like "apc .." try to add apc.enable_cli=1 in you php.ini
In my case this worked. But this didn't delete the old entries in oc_authtoken. So I deleted them manually.
I will check if @rfc2822 is right, currently I have not enough entries ;-)
@rfc2822 in my case this doesn't work either. I have table entries which are older than 48 hours with type=0 and type=1 I ran a cron job as described above. Nothing deleted :-( Where is the problem!!??
@AlfredoCubitos I activated the SQL query log and manually run the cronjob to see what it does. Maybe this would help in your case too.
Hi @rfc2822 I found out that there is a query
DELETE FROM oc_authtoken WHERE (last_activity < 1631879405) AND (type = 0) AND (remember = 1) AND (version = 2)
Where last_activity is 15 days ago, this may lead to a huge table entry on sites with a lot of traffic.
While in this query
DELETE FROM oc_authtoken WHERE (last_activity < 1633089005) AND (type = 0) AND (remember = 0) AND (version = 2)
last_activity is only 1 day ago
So, I had a lot of entries with remember = 1 that were not deleted because the time was not expired
May be some one can explain when remember=1 is set and why
Same issue here. I've just run sudo -u apache bash -c '/usr/bin/php -f /usr/share/nextcloud/cron.php' and I still have 492 (the same number I had before running the cron.php job) oc_authtoken entries dating back to: Fri 11 Mar 2022 05:29:15 PM EST on NC 24.0.0.
Looking at mysqld with strace, it seems to just get hammered with UPDATE `oc_authtoken` SET `password` = ... queries and is pegging the disk with the mysql database on it.
I have not (until today, as I just changed one app) been using app passwords. Is my understanding correct that using them will alleviate this hammering of mysqld and make the oc_authtoken reasonable?
I keep running into the same problem. Recently with a twist: the oc_authtoken table grew to 108,4 MiB, with a couple of hunderd entries. I emptied it (TRUNCATE), since in my experience that's the only thing that works. Now everyone has to re-login, but at least the Nextcloud is still working. And after I logged in again, I checked the oc_authtoken table again – it contained one entry, with a size of 108,4 MiB.
I am not an overly experienced user of databases, but that seems rather odd to me. Maybe I am missing something very obvious, I would be grateful for being pointed into the right direction.
I am using version 23.0.4
I am using 24.0.2 (Installation is an old ownCloud one with lots of upgrades since than). And I am having the same issue.
Running php cron.php as the right user did not result in any error messages.
SELECT COUNT (*)
FROM `oc_authtoken`
WHERE `uid` = '<user>'
--- Result: 147
Would it be an option to create an occ command section to "remove authtoken older than x days"?
Is the following sql statement worth a cronjob? Did I miss something? I have no idea what protocol and version column is good for.
DELETE FROM `oc_authtoken`
WHERE
`uid` = <user>
AND `last_activity` < UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 16 DAY));
Important side note. The majority of the entries are from Firefox like Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Firefox/91.0, but it looks like the OS does not matter since I also have a lot of Mozilla/5.0 (X11; Linux x86_64; rv:101.0) Gecko/20100101 Firefox/101.0.
@perdittmann
After removing entries from your database table, your need to "squeeze out the air" of the smaller table.
Removing entries from a database table is like removing some books from a bookshelf. The size of the bookshelf is the same after the removal. What you have to do is to shrink the size of the bookshelf to reclaim disk space.
You have to run an OPTIMIZE TABLE oc_authtoken to shrink the size of this table.
Hopefully my explanation is good enough.
@stevleibelt
Is the following sql statement worth a cronjob? Did I miss something? I have no idea what
protocolandversioncolumn is good for.
Just to chime in with my current method of cleaning up the table: I'm simply running delete from oc_authtoken where name not like '%(Nextcloud iOS)';
This keeps the tokens from the iOS app (which are required, otherwise users have to log in in the app again), but removes all unnecessary tokens that are created by WebDAV/CardDAV/CalDAV and other clients which log in by password anyway.
Of course, depending on the use cases it might be necessary to exclude other names from deletion as well.
i suddenly couldn't use my server machine anymore and had a look at it to see what was wrong. The space was filled up by 63 GB of mariadb binary logs created by nextcloud having commands like:
UPDATE oc_authtokenSETpassword= '3FOXzJ9xZR1kHURCpkbJ6HEAQ/g3ObwOQXSwM1qsjjwQlSdWtVw4DmzuHIRZBmljH3bXVrK4D/SiW2rtVb3fmR0urjfplE3ohcaaXRHLLfHjOf3Uyi8lQJWO9bdYwO0tvDVi8Pmz9+/Q1ilHu/hwU+/THFNiZDu31rJUGUTV3pFs6BY3wP5TaTvrwF4gWG5Cf2b8UedjMuhSOLbVbI7yhJyU7HTnFnzj92sxLt1D0MGYfhXb+ZPfS2yL7B5pmBK4LZxbfBl8VIIRjDlIEoTZfX8PGA7w1+PU3eetvm1LXMwNoqCksh+9a+18A6yiicTLaiyn70iSyDt5/DaQy0xT4w==' WHEREid = 13525
