0612182441
+31 (0)85 060 9913 | service@powercraft.nl

Upgrading large installations of ownCloud 8.2.7.1 to 9.0.5.2 and further

This week we upgraded some of our larger installations of ownCloud 8.2.7.1 to 9.0.5.2. In the past running the ownCloud update from the command line worked well for us:
sudo -u apache ./occ upgrade --skip-migration-test
Unfortunately some of our installations now have 30GB of MariaDB data files. And running the installer will increase the size of MariaDB to >60GB and this is a little much for comfort. Looking into the database we found a number of tables with oc_oc_ prefix, they seem to be a left over from previous upgrades. So we removed those. Dumping and re-importing using mysqldump the database was still too large. Digging into the tables we found that over 90% of the storage was used in the oc_filecache table.

First we opted to just TRUNCATE TABLE oc_filecache. Then the upgrade went well and MariaDB datafiles went to around 7GB (yes we removed the old data files). Unfortunately all shares (public links and between users) where no longer in the upgraded ownCloud. So we looked a bit more in the source code and found that the oc_share table is inner joined with the oc_filecache table, this explained why the shares no longer showed up. So we decided to:
DELETE FROM oc_filecache WHERE oc_filecache.fileid NOT IN (SELECT oc_share.file_source FROM oc_share);
That query took a little while:

alter-table-oc9

But after the upgrade went smoothly and MariaDB datafiles are still very small compared to what they used to be. Unfortunately a re-download is triggered for users of the ownCloud sync app, but for this instance there where not many users affected, so we could accept that.

We hope the cache table does not need much changing in the future, as it seems quite difficult to upgrade large installations this way. Update: When migrating to Nextcloud 10/11 we found you can also speed up the upgrade process by running:
`TRUNCATE TABLE oc_file_locks`