Nextcloud oc_filecache growing huge : too many rows (hence backup fails with : lost connection to MySQL)

truncate did run faster in my case too.
But the first time, it took days to remove a few million lines, and ~1 day to truncate.
Now truncating takes <1min, removing lines is still as slow.
I don’t know why. Maybe the storage is less overloaded.

works fine !

I do the trick…

LXC was too long and I stop it (it was the old instance)…
Docker look faster and the truncate take only some few seconds…

Thanks

To conclude my story with the oc_filecache:

YNH_APP_CURRENT_VERSION: 18.0.4~ynh1
YNH_APP_ID: nextcloud
YNH_APP_INSTANCE_NAME: nextcloud
YNH_APP_INSTANCE_NUMBER: '1'
YNH_APP_MANIFEST_VERSION: 22.1.1~ynh1
YNH_APP_UPGRADE_TYPE: UPGRADE_APP

....

2021-10-03 13:30:31,174: DEBUG - + echo '[####################] > Upgrade of nextcloud completed'
2021-10-03 13:30:31,174: DEBUG - + ynh_exit_properly
2021-10-03 13:30:31,174: DEBUG - + local exit_code=0
2021-10-03 13:30:31,175: DEBUG - + rm -rf /var/cache/yunohost/download/
2021-10-03 13:30:31,175: INFO - [####################] > Upgrade of nextcloud completed
2021-10-03 13:30:31,178: DEBUG - + '[' 0 -eq 0 ']'
2021-10-03 13:30:31,178: DEBUG - + exit 0
2021-10-03 13:30:32,180: DEBUG - Checking that required services are up and running...
2021-10-03 13:30:34,176: SUCCESS - nextcloud upgraded
success: true

Nextcloud did go into some kind of error mode after the upgrade (and did not recover yet), but that is a story for another thread :wink:

Nextcloud is running again, and I see I lost my shares.

I am quite sure the losing of shared files and directories is not a result of the upgrade, but of truncating oc_filecache (I remember reading somewhere that oc_filecache is needed for the sharing).

So, keep in mind: don’t truncate oc_filecache when you just shared a bunch of party photo’s with your friends!

For the actual error after the upgrade, I found it in /var/log/daemon.log. Many lines of:


Oct  3 15:25:07 online mysqld[553]: 2021-10-03 15:25:07 106 [ERROR] mysqld: Out of sort memory, consider increasing server sort buffer size
Oct  3 15:25:07 online mysqld[553]: 2021-10-03 15:25:07 106 [Warning] Sort aborted, host: localhost, user: nextcloud, thread: 106, query: SELECT `share`.`id`, `share`.`share_type`, `share`.`share_with`, `share`.`uid_owner`, `share`.`uid_initiator`, `share`.`parent`, `share`.`item_type`, `share`.`item_source`, `share`.`item_target`, `share`.`file_source`, `share`.`file_target`, `share`.`permissions`, `share`.`stime`, `share`.`accepted`, `share`.`expiration`, `share`.`token`, `share`.`mail_send`, `share_circle`.`unique_id` AS `share_circle_unique_id`, `share_circle`.`name` AS `share_circle_name`, `share_circle`.`display_name` AS `share_circle_display_name`, `share_circle`.`sanitized_name` AS `share_circle_sanitized_name`, `share_circle`.`source` AS `share_circle_source`, `share_circle`.`description` AS `share_circle_description`, `share_circle`.`settings` AS `share_circle_settings`, `share_circle`.`config` AS `share_circle_config`, `share_circle`.`contact_addressbook` AS `share_circle_contact_addressbook`, `share_circle`.`contact_groupname` AS `share_circle_contact_groupname`, `

I changed the value of
mariadb.cnf:sort_buffer_size = 64K
to
mariadb.cnf:sort_buffer_size = 16M
and
service mysql restart
for good measure.

Oh ! I didn’t know that ! :frowning:
But can it build the cache again ? (those files would not be available in the mid-time, but then… ?)

I am not so sure about that. Shares are in oc_share, and poin to a record in oc_filecache by fileid.
After truncating oc_filecache, I was happy enough to be able to upgrade, and did not take time to check if anything changed. In theory it could be that the upgrade from NC18 to NC22 messed up shares, but I doubt that (there would have been outcry if an upgrade did that :-P)

With the current data base scheme there would be no point in keeping oc_share-records after deleting records from oc_filecache: there’s no way to guarantee the correct relation between the share indications and the actual file being shared after rebuilding the cache (with new fileid’s).

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.