oc_calendarchanges in MySQL is very big

[kriztan]

Steps to reproduce

This is not an issue, what is reproduceable at the moment.

Expected behaviour/actual behaviour

Is this quiet normal, that the MySQL table oc_calendarchanges is roundabout 430 MiB big and has more than 2 millions of rows? This is the biggest table of my nextcloud database.

Server configuration

Operating system: CentOS 6

Web server: Apache 2.2.15

Database: MySQL

PHP version: 5.6

Nextcloud version: 11.0.1

Updated from an older Nextcloud/ownCloud or fresh install: Updated from ownCloud some months ago.

Where did you install Nextcloud from: nextcloud website

List of activated apps:

App list

Enabled: - audioplayer: 1.4.1 - calendar: 1.5.0 - comments: 1.1.0 - contacts: 1.5.2 - dav: 1.1.1 - direct_menu: 0.10.0 - federatedfilesharing: 1.1.1 - federation: 1.1.1 - files: 1.6.1 - files_pdfviewer: 1.0.1 - files_sharing: 1.1.1 - files_texteditor: 2.2 - files_trashbin: 1.1.0 - files_versions: 1.4.0 - files_videoplayer: 1.0.0 - firstrunwizard: 2.0 - gallery: 16.0.0 - logreader: 2.0.0 - lookup_server_connector: 1.0.0 - nextcloud_announcements: 1.0 - notes: 2.2.0 - notifications: 1.0.1 - password_policy: 1.1.0 - provisioning_api: 1.1.0 - serverinfo: 1.1.1 - sharebymail: 1.0.1 - survey_client: 0.1.5 - systemtags: 1.1.3 - templateeditor: 0.2 - theming: 1.1.1 - twofactor_backupcodes: 1.0.0 - updatenotification: 1.1.1 - user_external: 0.4 - workflowengine: 1.1.1 Disabled: - activity - admin_audit - encryption - external - files_accesscontrol - files_automatedtagging - files_external - files_retention - user_ldap - user_saml

The content of config/config.php:

Config report

{ "system": { "instanceid": "ocy6zj5pzphs", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "cloud.pix-art.de" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "11.0.1.2", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "127.0.0.1", "dbtableprefix": "oc_", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "check_for_working_webdav": true, "check_for_working_htaccess": true, "updatechecker": true, "check_for_working_wellknown_setup": true, "forcessl": true, "forceSSLforSubdomains": true, "has_internet_connection": true, "user_backends": [ { "class": "OC_User_IMAP", "arguments": [ "{***REMOVED SENSITIVE VALUE***:143\/imap\/novalidate-cert\/readonly}INBOX" ] } ], "filelocking.enabled": "true", "memcache.locking": "\\OC\\Memcache\\Redis", "memcache.local": "\\OC\\Memcache\\Redis", "redis": { "host": "\/home\/pixart\/.redis\/sock", "port": 0, "timeout": 0 }, "filesystem_check_changes": 0, "trashbin_retention_obligation": "auto, 30", "versions_retention_obligation": "auto, 30", "mail_from_address": "info", "mail_smtpmode": "php", "mail_domain": "***REMOVED SENSITIVE VALUE***", "default_language": "de", "knowledgebaseenabled": false, "allow_user_to_change_display_name": true, "enable_avatars": true, "remember_login_cookie_lifetime": 2592000, "session_lifetime": 54000, "session_keepalive": false, "skeletondirectory": "***REMOVED SENSITIVE VALUE***", "asset-pipeline.enabled": false, "theme": "", "loglevel": 0, "logtimezone": "Europe\/Berlin", "logdateformat": "d.m.Y H:i:s", "cron_log": true, "log_rotate_size": 10485760, "blacklisted_files": [ ".htaccess", "thumbs.db", "desktop.ini" ], "enable_previews": true, "preview_max_x": 1920, "preview_max_y": 1920, "preview_max_scale_factor": 1, "preview_max_filesize_image": 15, "enabledPreviewProviders": [ "OC\\Preview\\PNG", "OC\\Preview\\JPEG", "OC\\Preview\\GIF", "OC\\Preview\\BMP", "OC\\Preview\\XBitmap", "OC\\Preview\\MP3", "OC\\Preview\\TXT", "OC\\Preview\\MarkDown", "OC\\Preview\\PDF" ], "maintenance": false, "debug": false, "appstore.experimental.enabled": true, "minimum.supported.desktop.version": "1.7.0", "htaccess.RewriteBase": "\/" } }

Are you using external storage, if yes which one: no

Are you using encryption: no

Are you using an external user-backend, if yes which one: IMAP

Any ideas? Why is the table oc_calendarchanges so big? Is there a way to "clean" this table?
I've also posted this in the forum: https://help.nextcloud.com/t/oc-calendarchanges-in-mysql-is-very-big/8061

[nickvergessen]

Well everytime you add, delete or modify an event an entry is generated in that table. This is done so your clients can sync the diff, instead of having to redownload it the hole time.

[laf0rge]

is there some kind of strategy to eventually garbage-collect this table? I am using nextcloud for a small ~10 user company and I already have ~ 500k entries in this table after 2 years or so.

What about in 10 years? What's the long-term strategy?

[tcitworld]

I've proposed to add a background job in #31064 to prune the table automatically, but you can TRUNCATE the table safely. The sync tokens are only there to speedup the synchronisation.